Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execution Plan Caching and SUSER_NAME() Expand / Collapse
Author
Message
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
Hi,

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?

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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:23 PM
Points: 15,737, Visits: 28,145
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

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

Add to briefcase

Permissions Expand / Collapse