Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Execution Plan Caching and SUSER_NAME() Expand / Collapse
Posted Wednesday, August 13, 2008 9:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 4, 2010 4:01 AM
Points: 5, Visits: 30

I've got a data warehouse which is effectively a star schema. However, one of the dimensions requires row-level security to prevent access to some of the data.

I've essentially achieved this by using a view to combine the underlying dimension (table) and another table (which basically contains a list of all the surrogate keys in the dimension that each user has access to) to get the correct level of access. The view then uses the SUSER_NAME() function to create the "secure view" of the dimension.

Now, different people can see different quantities of data ie some may see all, some may see a subset and some may see only one row.

When I run these queries in SSMS I get different query plans every time depending on the number of rows, usually an index scan of the dimension table for a large number of rows or a seek for a small number of rows. I'm wondering what's happening when i'm not looking ie does the execution plan get cached in the case that SUSER_NAME() is being used?

Post #552034
Posted Wednesday, August 13, 2008 11:36 AM



Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 17,173, Visits: 32,140
If the first guy through creates a plan that uses a scan, unless something fires a recompile of the procedure or the plan ages out of cache, it's going to stay in place. You might want to take a look at the OPTIMIZE FOR query hint as a way to avoid this issue.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #552142
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse