Microsoft suggests using OPTION (QUERYTRACEON 9481) for queries that have "degraded" performance against SQL Server 2014 databases running under compatibility level 120.
In my situation, "degraded" means going from a few seconds to several minutes or more. Performance wise, 9481 works extremely well; however, regular users get a nice error instead of query results (running SSRS reports):
User 'xxxxx' does not have permission to run DBCC TRACEON.
The documentation indicates that users must be members of the sysadmin role to run DBCC TRACEON.
Are there any workarounds to allow non-sysadmins to run queries that use this hint?
I'd rather not drop the compatibility level back down to 110 because the majority of our queries are acceptable or working better.
Best Answer
You can create a plan guide as sysadmin for the query, which would then allow the user to run the pre-existing plan. Otherwise, rewriting the query is about all you can do.
Using
QUERYTRACEONin plan guides