Something to watch out for when using IS_MEMBER() in TSQL
I recently worked on an interesting issue with my good friend Igor (@sqlsantos), where we were facing performance issues with a piece of code that used the IS_MEMBER () function. Basically, the IS_MEMBER function is used to find out whether the current user (windows/sql login used for the current session) is a member of the specified Windows group or SQL server database role.
In the specified code, the IS_MEMBER function was being used to determine the windows group membership of the windows login. The windows groups were segregated according to geographical areas, and based on the user's group membership, the result set was filtered to show rows for only those geographical areas for which the user was a member of the corresponding groups in Active Directory.
Here's an example of a piece of code where we perform this check:
With SalesOrgCTE AS(
SELECT
MinSalesOrg, MaxSalesOrg
FROM
RAuthorized WITH (NOLOCK)
WHERE
IS_MEMBER([Group]) = 1
)
The problem was that the complete procedure where we were using IS_MEMBER was taking several minutes to complete, for a table where the max result set cardinality was in the range of 18000-20000. We noticed the following wait types while the procedure was executing:
PREEMPTIVE_OS_AUTHORIZATIONOPS
PREEMPTIVE_OS_LOOKUPACCOUNTSID
I did some research on these waits, and found that since both of these are related to the communication/validation from Active Directory, they lie outside of SQL server, and there's no changes we can do from a configuration standpoint to help reduce/eliminate these waits.
Next, we studied the code, broke it down and tested the performance of the various sections that used the IS_MEMBER function, and found that the main section responsible for the execution time was the "WHERE" condition where we were using the result set of the code mentioned above. This is what the "WHERE" clause looked like:
(SELECT
COUNT(*)
FROM
SalesOrgCTE
WHERE
SORGNBR BETWEEN MinSalesOrg AND MaxSalesOrg) > 0
Notice that in this code, we've asked SQL to check the value of SORGNBR for each row, and if it's between MinSalesORG and MaxSalesOrg, then add it to the rowcount. We observed that due to this design, it had to make a trip to AD for validating each row, which meant quite a long time for a 18000-20000 row result set, which was responsible for the slow performance of the procedure.
We did some more research with different approaches for the where clause, and the combined efforts of myself, Igor and his team resulted in the following where clause whose performance was acceptable:
WHERE
SORGNBR IN
(
SELECT MinSalesOrg FROM SalesOrgCTE
)
AND SORGNBR IN
(
SELECT MaxSalesOrg FROM SalesOrgCTE
)
If you look carefully, you'll notice that in this code snippet, we'll need to communicate with AD only twice, thereby improving the performance of the procedure as a whole.
Summing up: The importance of writing good code cannot be over-emphasized. It's good coding practices like this that lead to performance gains most of the time.
Hope this helps.
Comments
Anonymous
August 05, 2013
That indeed is helpful piece of information. Thanks for sharing.Anonymous
August 05, 2013
Thanks for appreciating my friend...!!! :)Anonymous
August 05, 2013
Clear and Crisp Explanation. Thanks HarshdeepAnonymous
August 05, 2013
You're welcome...!!! Thanks for appreciating Azad...!!!Anonymous
August 06, 2013
Great post .. Thanks for Sharing Harsh ..Anonymous
August 06, 2013
You're very welcome...!!!! Thanks for appreciating Gurpreet...!!!Anonymous
February 23, 2015
Great post! I have a question though... MSDN says that the function IS_MEMBER determines Windows group membership by examining an access token that is created by Windows. So, does it actually communicate to AD every time it is called or gets the information from the access token?Anonymous
April 20, 2015
Hi Ranjit, Thanks for your kind words. And yes, from what I understand, the is_member function communicates with AD each time it is called. Regards, Harsh