{"id":2805,"date":"2016-06-20T09:25:24","date_gmt":"2016-06-20T16:25:24","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/kimberly\/?p=2805"},"modified":"2016-06-21T03:15:04","modified_gmt":"2016-06-21T10:15:04","slug":"sp_settraceflag","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/sp_settraceflag\/","title":{"rendered":"Setting CE TraceFlags on a query-by-query (or session) basis"},"content":{"rendered":"

IMPORTANT NOTE:<\/strong> Be sure to read the ENTIRE post as there’s a workaround that QUERYTRACEON is ALLOWED inside of stored procedures… just NOT in an adHoc statement. Not documented but I think this is excellent! There are still some uses of what I’ve written but this specific use is much easier because of this undoc’ed “feature.” :-)<\/p>\n

While the new cardinality estimator\u00a0can offer benefits for some<\/em> queries, it might not be perfect for ALL<\/strong> queries. Having said that, there is an OPTION clause that allows you to set the CE for that query. The setting to use depends on the CE that you\u2019re running under currently. And, as of SQL Server 2016, even determining this can be difficult.\u00a0At any given time, there are multiple settings that might affect your CE.<\/p>\n

In SQL Server 2014, your CE\u00a0was set by the database compatibility model. If you\u2019re running with compatibility mode 120 or higher, then you\u2019re using the new CE. If you\u2019re running with compatibility mode 110 or lower, then you\u2019re using the Legacy CE. In SQL Server 2016, the database compatibility mode is not the only setting that can affect the CE that you\u2019re using. In SQL Server 2016, they added ‘database scoped configuations’ and introduced:<\/p>\n

ALTER DATABASE SCOPED CONFIGURATION LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}<\/p>\n

Having said that, an administrator\u00a0can always override this setting by setting\u00a0one of the CE\u00a0trace flags globally [using DBCC TRACEON (TF, -1) \u2013 but I don\u2019t recommend this!].<\/p>\n

To use the LegacyCE when the database is set to the new CE, use Trace Flag 9481.
\nTo use the New CE when the database is set to the LegacyCE, use Trace Flag 2312.<\/p>\n

Generally, I recommend that most people STAY with the LegacyCE until they\u2019ve thoroughly tested the new CE. Then, and only then, change the compatibility mode. But, even with extensive testing, you might still want some queries to run with the LegacyCE while most run with the new CE (or, potentially the opposite). What I like most about the addition of the new CE is that we have the ability to set EITHER!<\/p>\n

But, before setting this (or, overriding how the database is set), let\u2019s make sure we know how it\u2019s set currently\u2026\u00a0If you\u2019re wondering which CE you\u2019re running under, you can see it within the graphical showplan (in the Properties [F4] window, use: CardinalityEstimationModelVersion OR search for that within the showplan XML).<\/p>\n

\"CardinalityEstimationModelVersion\"<\/a><\/p>\n

Above all, what I like most is \u2013 CHOICE.\u00a0I can even set this for a specific query:<\/p>\n

SELECT m.*
\nFROM [dbo].[Member] AS [m]
\nWHERE [m].[firstname] LIKE ‘Kim%’
\nOPTION (QUERYTRACEON 9481);<\/p>\n

However, the bad news is the QUERYTRACEON<\/strong> is limited to SysAdmin only (be sure to read the UPDATEs at the end of this post)<\/strong>. Jack Li (Microsoft CSS) wrote a great article about a problem they solved by using a logon trigger to change the CE for an entire session:\u00a0Wanting your non-sysadmin users to enable certain trace flags without changing your app?<\/a>\u00a0Now, I do want to caution you that setting master to trustworthy is not something you should take lightly. But, you should NOT let anyone other than SysAdmin have any other rights in master (other than the occasional EXEC on an added user-defined SP). Here\u00a0are a couple of\u00a0posts to help warn you of the danger:<\/p>\n

A warning about the TRUSTWORTHY database option<\/a>
\nGuidelines for using the TRUSTWORTHY database setting in SQL Server<\/a><\/p>\n

Having said that, what I really want is\u00a0to set this on a query by query basis AND I don\u2019t want to elevate the rights of an entire stored procedure (in order to execute DBCC TRACEON). So, I decided that I could create a procedure in master, set master\u00a0to trustworthy (with the caution and understanding of the above references\/articles<\/em>), and then I can reference it within my stored procedures NOT having to use use\u00a03-part naming (for the sp_ version of the procedure):<\/p>\n

ALTER DATABASE master
\nSET TRUSTWORTHY ON;
\nGO<\/p>\n

USE master;
\nGO<\/p>\n

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

GRANT EXECUTE ON sp_SetTraceFlag TO PUBLIC;
\nGO<\/p>\n

As for using this procedure, you have TWO options.\u00a0If you can modify the stored procedure then you can wrap a single statement with the change in trace flag. But, to make it take effect, you\u2019ll need to recompile that statement. So, if your stored procedure looks like the following:<\/p>\n

CREATE PROCEDURE procedure
\n( params )
\nAS
\nstatement;
\nstatement; <<<— problematic statement
\nstatement;
\nstatement;
\nGO<\/p>\n

Then you can change this to:<\/p>\n

CREATE PROCEDURE procedure
\n( params )
\nAS
\nstatement;
\nEXEC sp_SetTraceFlag 2312, 1
\nstatement OPTION (RECOMPILE); <<<— MODIFIED problematic statement
\nEXEC sp_SetTraceFlag 2312, 0
\nstatement;
\nstatement;
\nGO <\/p>\n

If you don\u2019t want to set master to trustworthy then you can add a similar procedure to msdb (which is already<\/strong> set to TRUSTWORTHY) and then use 3-part naming to reference it.<\/p>\n

USE msdb;
\nGO<\/p>\n

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

GRANT EXECUTE ON msdbSetTraceFlag TO PUBLIC;
\nGO<\/p>\n

To use this, you’ll need to use 3-part naming:<\/p>\n

CREATE PROCEDURE procedure
\n( params )
\nAS
\nstatement;
\nEXEC msdb.dbo.msdbSetTraceFlag 2312, 1
\nstatement OPTION (RECOMPILE); <<<— MODIFIED problematic statement
\nEXEC msdb.dbo.msdbSetTraceFlag 2312, 0
\nstatement;
\nstatement;
\nGO <\/p>\n

Finally, another option is to wrap a statement with the change in trace flag.<\/p>\n

EXEC sp_SetTraceFlag 2312, 1;
\nGO
\nSTATEMENT or PROCEDURE
\nGO
\nEXEC sp_SetTraceFlag 2312, 0; — don’t remember to turn it back off!
\nGO<\/p>\n

Now, you have strategic access to EITHER CE and you don’t have to elevate anyone’s specific rights to SysAdmin. You can even let developers use the changes to the new CE or the LegacyCE in their code which is incredibly\u00a0useful!<\/p>\n

UPDATE:<\/strong> If you can’t change the code then another excellent option would be use to a plan guide (permissions are only at the object\/database level: To create a plan guide of type OBJECT, requires ALTER permission on the referenced object. To create a plan guide of type SQL or TEMPLATE, requires ALTER permission on the current database.<\/em>). Here’s a good post on how to add QUERYTRACEON into a plan guide:\u00a0Using QUERYTRACEON in plan guides<\/a> by Gianluca Sartori<\/a>.<\/p>\n

UPDATE #2:<\/strong><\/span> Wow! A bit of digging and I stumbled on this ConnectItem: QUERYTRACEON with no additional permissions<\/a>\u00a0and after I up-voted it, I noticed the workaround by Dan Guzman:\u00a0There is currently undocumented behavior in SQL 2014 that allows use of QUERYTRACEON in a stored procedure without sysadmin permissions regardless of module signing or EXECUTE AS. This is a workaround in situations where the query is already in a stored procedure or when a problem ad-hoc query needing the trace flag can be encapsulated in a proc. However, until it’s documented, this workaround is at-your-own-risk.<\/em>\u00a0I did NOT know this…<\/strong>\u00a0So, QUERYTRACEON CAN be used\u00a0in a stored procedure<\/strong><\/span> even without SysAdmin permissions (I actually like this!). And, I tested this in BOTH 2014 AND 2016 and it works! It’s not documented nor is it recommended but I’m happy to see that you do NOT need to do this for QUERYTRACEON. It looks like it evens works in earlier versions. Most of us ran into problems with the clause erroring on adhoc statements so we just didn’t expect it to work INSIDE of a proc. Well… better late than never!<\/strong><\/p>\n

Thanks for reading,
\nKimberly<\/p>\n

<\/h1>\n","protected":false},"excerpt":{"rendered":"

IMPORTANT NOTE: Be sure to read the ENTIRE post as there’s a workaround that QUERYTRACEON is ALLOWED inside of stored procedures… just NOT in an adHoc statement. Not documented but I think this is excellent! There are still some uses of what I’ve written but this specific use is much easier because of this undoc’ed […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[95,97,94,91,96,72,78],"tags":[],"class_list":["post-2805","post","type-post","status-publish","format-standard","hentry","category-application-development","category-cardinality-estimator","category-database-development","category-sql-server-2014","category-sql-server-2016","category-statistics","category-tips"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2805","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=2805"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2805\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=2805"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=2805"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=2805"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}