question

Magnus Ahlkvist avatar image
Magnus Ahlkvist asked

QUERYTRACEON 9481 to non sysadmin users

Hi! We have some table valued functions compiled with OPTION(QUERYTRACEON 9481) because the new cardinality estimation messes up the Query plan (we get a key lookup on more than a million rows because cardinality estimation gives a one row estimation). Running the TVF without the traceflag is half a minute of waiting for results, running it with the traceflag is a subsecond wait. Our developers don't have sysadmin permissions on the server, and therefore they can't compile the function(s) whenever there's a requirement change. Is there any way to grant developers the permission to use QUERYTRACEON without making them sysadmins? Our alternative would be to have members of the sysadmin role create the functions (which is what we do now), but that's not really a way to go - we don't want the development teams rely on sysadmins to alter queries and objects in the databases.
permissionssql-server-2014querytraceon
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanx Dave! Just so I'm understanding this: First off I need to get a parameterized version of the Query that the TVF is using, using sp_get_query_template, and using that I would then create a plan guide using sp_create_plan_guide, and include both OPTION(QUERYTRACEON 9481) and OPTION(PARAMETERIZATION FORCED) to make sure this specific plan guide is picked up every time the underlying Query is run? But what about when the logic of the TVF needs to change - then the Query inside the TVF Changes and the plan guide is rendered useless and the developers will need me or Another DBA create a new plan guide. Feels like a Catch 22 :)
0 Likes 0 ·
automate your deployments! :)
0 Likes 0 ·
That would be a good idea. But even with automatic deployments, I wouldn't want the deployment server to run as a Sysadmin.. Call me paranoid, but if I give sysadmin priveleges to the deployment server, I basically grant sysadmin privileges to anyone who can check in code for deployment. Then I might as well give out sysadmin privileges to the whole development team..
0 Likes 0 ·
That's where the plan guides come in. They don't modify the query, and can be applied separately (e.g. by a specific process), thus only that part is done by the higher privileged user. However, that then means you need to automate generating the plan guide... and that it is always needed (e.g. that some Service pack doesn't change the behaviour).
0 Likes 0 ·
Thanks for the input. I have half a solution for how this can be automated in my head, I just need to contemplate how to identify which specific queries we need to create and maintain plan guides for.
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
Dave_Green avatar image
Dave_Green answered
Depending on how the changes affect the plan, a plan guide may help you - see the blog post at http://spaghettidba.com/2013/02/08/using-querytraceon-in-plan-guides/ . You could set the same up on the Development server so at least your development teams only come to you when the plan has changed?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.