The QUERYTRACEON hint can be particularly useful for testing the new cardinality estimator in SQL Server 2014 on individual queries using Trace Flag 2312, if your database is still using compatibility level 110:
|
1 2 3 4 5 |
SELECT ClosedDate, Id FROM dbo.Posts WHERE LastActivityDate > '2013-09-05 11:57:38.690' OPTION (QUERYTRACEON 2312); GO |
Conversely, if you’re using the new estimator everywhere by having database compatibility set to 120, you can use the old estimator for an individual query by using QUERYTRACEON 9481:
|
1 2 3 4 5 |
SELECT ClosedDate, Id FROM dbo.Posts WHERE LastActivityDate > '2013-09-05 11:57:38.690' OPTION (QUERYTRACEON 9481); GO |
But… Permissions?!?!
There’s a little problem. Turning on a trace flag requires high permissions. Let’s say that I’m attempting to use this hint from an account which doesn’t have superpower permissions. Here we create a login and user for app_account and grant it data reader, and execute on the dbo schema:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE [master] GO CREATE LOGIN [app_account] WITH PASSWORD=N'DontBeLikeMeUseWindowsAuth', DEFAULT_DATABASE=StackOverflow, CHECK_EXPIRATION=ON, CHECK_POLICY=ON GO use StackOverflow; GO CREATE USER [app_account] FOR LOGIN [app_account]; GO ALTER ROLE [db_datareader] ADD MEMBER [app_account]; GO GRANT EXECUTE ON SCHEMA::dbo to [app_account]; GO |
We can now impersonate app_account to run some tests!
|
1 2 |
EXECUTE AS LOGIN='app_account'; GO |
Meet Error 2561, User Does Not Have Permission to Run DBCC TRACEON
When I try to run the query, things screech to a halt:
Even if I try to get tricky, I can’t sneak this into a temporary procedure as app_account, either:
Stored Procedures to the Rescue
We need a little help from a stored procedure. We’re going to revert back to our sa role, and create a procedure around our query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
REVERT GO CREATE PROCEDURE dbo.RecentPosts @DateVal DATETIME AS SELECT ClosedDate, Id FROM dbo.Posts WHERE LastActivityDate > @DateVal OPTION (QUERYTRACEON 2312, RECOMPILE); GO /* Now we go back to running as app_account again */ EXECUTE AS LOGIN='app_account'; GO |
Due to the magic of ownership chaining, we can now run the query as app_account:
This Means You Don’t have to Give Your applications Sysadmin Rights
… but you may have problems if those applications need to run a lot of adhoc queries.
Want to learn more about statistics and the new cardinality estimator? Join me and Jeremiah Peschka for our Advanced Querying and Indexing course!
9 Comments. Leave new
Great write up, Kendra.
Another option is a plan guide. See http://spaghettidba.com/2013/02/08/using-querytraceon-in-plan-guides/
I was just banging my head against the wall with this last night, and ended up rolling back to compatibility level 110 instead. Now I can do some more experimenting, and hopefully resolve the issues that will enable it to go back to 120.
We tried this as a quick test and found that the OPTION (QUERYTRACEON XXXX) worked so we started looking closer at moving from 2012 to 2014 as we thought there was a relatively simple switch for queries to use the new CE. But now we’re trying to put it in we’ve found that this works for lower permission users in procedures but not if the statements in the procedures are in dynamic SQL. Unfortunately due to the nature of the application we have, dynamic SQL is heavily used so is there any way to get OPTION (QUERYTRACEON XXXX) to work in dynamic SQL in a procedure which is run by a user who is not sysadmin?
Technically you can use a plan guide. Getting SQL Server to use your plan guide can sometimes be really difficult and maddening, so approach with caution!
Disappointed that the stored procedure ‘trick’ doesn’t work with dynamic SQL (still get the DBCC TRACEON permission problem)… Any ideas why it doesn’t work? Or alternatively, would welcome any ideas you might have to overcoming this problems (besides don’t use dynamic sql ;-). Thanks.
Yeah, dynamic sql doesn’t use ownership chaining and executes in the context of the caller. I can’t immediately think of a workaround that doesn’t require them to have high permissions in some way, but I’ll keep noodling on it– wonder if there’s some creative option that I’m not thinking of.
The apps don’t need SA but the developers of the SPs do! Even that can be a problem. Is a problem where I am.
Excellent information, thank you.
But when I create a procedure, the default is “with execute as caller”. So you would need to add a “with execute as owner” clause to the creation of the procedure. Right?
It’s way beyond what we can do justice to here in blog post comments, but check this out: http://www.sommarskog.se/grantperm.html