7

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.

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

| improve this answer | |
  • How to use degraded compatibility level inline in a query - SELECT * FROM <yourTableName> option (querytraceon 9481) – RBT Oct 10 '16 at 8:04
0

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

OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

| improve this answer | |
  • That only works for 2016, and OP is asking about 2014. Maybe update your answer to reflect this, its useful for others but doesn't really answer the original question. – HandyD Nov 13 '19 at 6:37
-1

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"

  1. 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).

  2. 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.