Grant users privilege to use QUERYTRACEON 9481

Question

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.

score 4 · Answer 1 · edited Aug 21 '15 at 03:13

4

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 QUERYTRACEON in plan guides

edited Aug 21 '15 at 03:13

Paul White

94,921
30
437
687

answered Aug 21 '15 at 02:09

Joel

171
3

score 0 · Answer 2 · answered Nov 13 '19 at 01:29

0

Please use hint option as it does not need sa permission.

OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

answered Nov 13 '19 at 01:29

lostinbrisbane

9
1

score -1 · Answer 3 · answered Oct 17 '19 at 13:41

Instead of using OPTION (QUERYTRACEON 9481), here is a proposed solution/workaround .
It allows a non-sa user to change the Cardinality Estimator for the specific statement, query, or procedure call (for the current session).

Solution:

Wonderfully explained by Kimberly Tripp in her post on sqlskills.com:
"Setting CE TraceFlags on a query-by-query (or session) basis"

Create a Stored Procedure on msdb database which allows setting of a desired Trace Flag without sysadmin permissions.
(of course, sysadmin takes care of setting-up a list of allowed Trace Flag values).
Wrap any problematic statement (dynamic sql, ad-hoc query, or procedure call) with a call to this Stored Procedure and change the session trace flag for execution.
This allows users with lower permissions to change the Cardinality Estimator for execution of the problematic statement.

Example usage:

EXEC msdb.dbo.msdbSetTraceFlag 9481, 1; 
GO  

Problematic STATEMENT or PROCEDURE   

EXEC msdb.dbo.msdbSetTraceFlag 9481, 0;  -- don't remember to turn it back off!    
GO

Stored Procedure code:

USE msdb;
GO

CREATE PROCEDURE msdbSetTraceFlag
    (@TraceFlag int,
     @OnOff bit = 0)
WITH EXECUTE AS OWNER
AS
DECLARE @OnOffStr char(1) = @OnOff;
-- Sysadmins can add supported trace flags and then use this
-- from their applications
IF @TraceFlag NOT IN (
              9481 -- LegacyCE if database is compat mode 120 or higher
            , 2312 -- NewCE if database compat mode 110 or lower
                     )
     BEGIN
         RAISERROR('The Trace Flag supplied is not supported. Please contact your system administrator to determine inclusion of this trace flag: %i.', 16, 1, @TraceFlag);
         RETURN
     END
ELSE
     BEGIN
         DECLARE @ExecStr nvarchar(100);
         IF @OnOff = 1
             SELECT @ExecStr = N'DBCC TRACEON(' + CONVERT(nvarchar(4), @TraceFlag) + N')';
         ELSE
             SELECT @ExecStr = N'DBCC TRACEOFF(' + CONVERT(nvarchar(4), @TraceFlag) + N')';
         -- SELECT (@ExecStr)
         EXEC(@ExecStr)
         -- RAISERROR (N'TraceFlag: %i has been set to:%s (1 = ON, 0 = OFF).', 10, 1, @TraceFlag, @OnOffStr);
     END;
GO

GRANT EXECUTE ON msdbSetTraceFlag TO PUBLIC --or to a specific set of users;
GO

Note: This Stored procedure is created in msdb, and not on master, because of "trustworthy" prerequisite, which is default for msdb.

Grant users privilege to use QUERYTRACEON 9481

3 Answers3