TLDR – don’t use IS_MEMBER()
The background
One of our customers was recently testing the performance of their database in advance of a planned migration from one AWS account to another. The source and destination EC2 servers were identical in spec as were the SQL Server build numbers.
Once the databases from the source environment were restored at the destination, the customer noticed that various queries that would ordinarily take a second or less to run, were now taking minutes to complete.
We explored the execution plans for the same query across both environments and found them to be identical. Statistics IO, also proved to be identical between both environments as was the CPU time. So clearly, the query in the target environment was waiting on something that it wasn’t waiting for in the source environment.
Looking at the wait type in sys.dm_exec_requests showed that it was waiting on PREEMPTIVE_OS_AUTHORIZATIONOPS. This wait type is associated with queries that are run against Active Directory. As it turns out, a lot of the tables in in this database were controlled by row-level security and the predicate function made use of the IS_MEMBER() function, which is used to establish whether the currently logged on Windows Login is a member of a particular Active Directory Group.
So, on initial inspection it appeared that AD queries on the destination environment were taking longer than in the source environment so we turned our focus to IS_MEMBER().
Through the course of our subsequent investigation, we explored one common issue with this function and uncovered a less-commonly known issue, which ultimately explained the behaviour we were observing.
Before exploring both of these problems in detail, let’s take a quick look at how IS_MEMBER() is typically used in an implementation of Row Level Security.
How would you use IS_MEMBER for row level security?
Using row level security to control the rows that are visible to users that are selecting data from tables is a common practice, particularly in analytics deployments. It is also common to use IS_MEMBER() to establish whether the currently logged on Windows Login is a member of a particular Active Directory Group.
There are many documented examples on how to set up row level security in SQL Server, which is beyond the scope of this post, but if you’re interested in learning more about this, there’s a good article with various examples by Tommy Mullaney, Limiting access to data using Row-Level Security – Microsoft SQL Server Blog. Note there’s a broken link in the post, for a detailed example that uses IS_MEMBER() the actual link is here: RLS-Hospital-Demo.sql
For the sake of brevity, here’s a simple example that you can set up easily yourself just to understand the concept. Note that throughout the examples used in this post, you’ll need to set up an Active Directory group named “UK Servers” and “US Users” if you want to create and test the example for yourself.
IS_MEMBER for row level security example
--create test tables and populate create table rls_test_table ( col1 int identity, col2 varchar(256), country varchar(2) ); create table dim_country ( country varchar(2) ); insert into dim_country values ('UK') insert into dim_country values('US') insert into rls_test_table values('Test Row', 'UK') insert into rls_test_table values('Test Row', 'US');
Now you’ve created the table that you want to control with row level security, you need to create a function that acts as an access predicate:
create function accessPredicate(@country varchar(2)) returns table with schemabinding as return select 1 as result from dbo.dim_country where ( country = @country and is_member('<your domain>\' + @country + ' Users') = 1) or is_member('<your domain>\SQLAdmin') = 1 --return all rows if user is in SQLAdmin ; create security policy dbo.rls_security_policy add filter predicate dbo.accessPredicate(country) on dbo.rls_test_table ;
The above function will return a value of 1 (i.e. grant access) if the row being read has a value for country that exists in dim_country and the current user is a member of a corresponding country group (e.g. “UK Users” or “US Users”). Alternatively, if the current user is a member of an AD group named “SQLAdmin” it will always return 1.
This means that if the currently logged on user is a member only of the “UK Users” AD group, queries against the rls_test_table will only return rows where the country is “UK”.
What’s the problem with IS_MEMBER()?
There are a couple of problems to understand here.
IS_MEMBER() problem #1
The first issue is quite widely documented and is easy to illustrate. Essentially, for every row that is being evaluated in the rls_test_table against the access Predicate() function, the function IS_MEMBER()executes once.
Go ahead and run the following query to illustrate the problem:
select count(*) from rls_test_table
If we look at the execution plan, we can see that there’s a table scan being performed on dim_country, as we’d expect, due to the function running a select against the dim_country table. We can also see that it read 2 rows from this table.
Let’s take it one step further and add 10,000 rows to the table:
insert into rls_test_table select top 10000 'Test Row', 'UK' from sys.objects cross join sys.columns
Now if we rerun the select query you’ll notice how it now results in 10,002 rows being read from dim_country table, even though there are only two rows in the table.
The key point about the above execution plans is that there’s a nested loop, meaning that for every row in rls_test_table, the bottom input is scanned. The bottom input is the predicate function we created, which means that for every row in rls_test_table, the function IS_MEMBER() is going to run. In the above example, IS_MEMBER() ran 10,002 times!
The performance of IS_MEMBER()is pretty fast but we found it to be quite variable between different environments. In our customer’s production environment, IS_MEMBER() was consistently running at around 30 microseconds. Whereas in our own test environment we found it to be running in around 500 microseconds – hence why the above query took just over 5.28 seconds to execute in our test environment. That’s not bad, but if you multiply it up over several million rows, even a 30 microsecond execution time for IS_MEMBER() is going to add up to quite a lot, quickly.
This doesn’t explain why our customer’s queries were running so much slower when we moved to their destination environment, we tested IS_MEMBER() and it was consistently running at around 30 microseconds in both environments. So why was their query running so much slower in the destination environment?
IS_MEMBER() problem #2
During our investigation we discovered an interesting quirk in the performance of IS_MEMBER(). Whilst it is true that IS_MEMBER() was consistent in its performance between the two environments, we found that if IS_MEMBER()is evaluated against a non-existent AD group, the execution time shoots up dramatically.
Regardless of which environment we’ve tested this in, the execution time of IS_MEMBER() is anywhere between 2 and 15 milliseconds when it is evaluated against a group that doesn’t exist in AD. This compares with 30 microseconds in our customer’s environment (an increase of around 100 times or greater!). As it turned out, the destination environment didn’t contain 1 group that was contained in their source environment and this caused the performance issue.
So how do we deal with this? The obvious solution was to create the group in AD, which would ensure that IS_MEMBER() would continue to perform as optimally as we could get it to (30 microseconds). And whilst this worked to correct the immediate disparity between the two environments, the very nature of IS_MEMBER() is quite inefficient and it won’t scale particularly well as the number of rows increase.
What’s an alternative solution to using IS_MEMBER() for row level security?
One way to completely avoid using IS_MEMBER()is to maintain a local table of group members within the database itself, which would then enable you to join directly to this local table instead of having to call an external function repeatedly.
As with most solutions, there are multiple ways to achieve the same goal, one of which is to create a linked server using Active Directory Services Interface and directly query Active Directory. Another way is to use the extended procedure xp_logininfo…
The first thing to do is create a group_members table that contains a list of groups and their associated group members:
create table group_members ( group_name varchar(128), login_name varchar(128) );
Once this is done, you can populate the table using xp_logininfo:
create table #logininfo ( account_name sysname, type varchar(8), privilege char(9), mapped_login_name sysname, permission_path sysname ); insert into #logininfo exec xp_logininfo '<your domain>\SQLAdmin', 'members'; insert into #logininfo exec xp_logininfo '<your domain>\US Users', 'members'; insert into #logininfo exec xp_logininfo '<your domain>\UK Users', 'members'; truncate table group_members; insert into group_members select distinct permission_path, account_name from #logininfo; drop table #logininfo;
Now that you have a local table that maps group membership to AD user accounts, you can modify the predicate function:
alter function accessPredicate(@country varchar(2)) returns table with schemabinding as return select 1 as result from dbo.group_members where (group_name = '<your domain>\' + @country + ' Users' and login_name = system_user) or (group_name = '<your domain>\SQLAdmin' and login_name = system_user);
Don’t forget to drop and re-create the security policy as you cannot modify the schema bound function whilst the policy still exists.
If you now run the following query, the performance should be much better. Your “mileage may vary” depending on how rapid the execution of IS_MEMBER() is in your environment so add more rows to rls_test_table until you notice an appreciable difference in time when you execute it.
select count(*) from rls_test_table
The resulting execution plan should look something like this:
In our environment, this took 85 milliseconds to execute but note how we’re still performing a table scan on group_members that reads through 20,004 rows against group_members.
If you really want to speed this up further, evaluate whether you really need the OR statement which is the most expensive part.
alter function accessPredicate(@country varchar(2)) returns table with schemabinding as return select 1 as result from dbo.group_members where (group_name = '<your domain>\' + @country + ' Users' and login_name = system_user) --or (group_name = '<your domain>\SQLAdmin' and login_name = system_user)
After commenting out the OR statement above, the execution time dropped from 85ms to 10ms and now completely avoids all those logical reads against group_members, with only 1 logical read.
In Summary
Avoid Using IS_MEMBER() entirely
Regardless of the performance quirk that appears to exist when IS_MEMBER() is evaluated against a non-existent Active Directory group, we recommend avoiding the use of IS_MEMBER() all together if at all possible. Even without the performance impact in this one edge case, you will eventually hit a performance wall that’s best to avoided.
Store AD Group membership locally on your SQL Server
Obtaining the contents of group membership from Active Directory is relatively straight forward and even if you must dynamically determine which groups you require members from, it is relatively straight forward to use xp_logininfo. It’s low-cost and something you can schedule to run once every 15-30 minutes to ensure that you have a reasonably up to date view of Active Directory group membership. If you have a large Active Directory with thousands of users and groups, or if you want to have a little more direct control over what and how you query Active Directory, you might want to consider using an ADSI linked server and LDAP to directly query Active Directory.
Avoid Using Logical OR Operators in your predicate function
As you’ve seen, the removal of the OR operator entirely removed the scan on group_members, and dramatically reduced the execution time.
If you must also allow administrators access to all the rows in the database, consider adding the admin group as a member of the relevant access groups.
There’s a great Microsoft article that goes into this in more detail here: Row-Level Security for Middle-Tier Apps – Using Disjunctions in the Predicate – Microsoft Tech Community
Keep it simple
Ultimately, the simpler and more efficient the predicate function is, the easier time SQL Server will have in getting the results back to you. If you don’t have to look things up in a table, don’t. For example, you could store user_names or user_ids directly in the target table (e.g. rls_test_table) and then you don’t need to query any table in the predicate function. There’s a good article, describing various design patterns with regards to performance on this subject here: Row-Level Security: Performance and common patterns – Microsoft Tech Community
