Cardinality Estimator Trace Flags

Kim Tripp shows how to set which cardinality estimator you want to use on a per-query basis:

However, the bad news is the QUERYTRACEON is limited to SysAdmin only (be sure to read the UPDATEs at the end of this post). 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: Wanting your non-sysadmin users to enable certain trace flags without changing your app? Now, 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 are a couple of posts to help warn you of the danger:

A warning about the TRUSTWORTHY database option
Guidelines for using the TRUSTWORTHY database setting in SQL Server

Read on for a couple of options.

Related Posts

Straight Talk On Trace Flags

Pam Lahoud explains the purpose of trace flags and talks about a very important trace flag, 4199: Some trace flags are used to enable enhanced debugging features such as additional logging, memory dumps etc. and are used only when you are working with Microsoft Support to provide additional data for troubleshooting. These trace flags are not ones […]

Read More

Do You Have Trace Flag 4199 Enabled?

Andy Galbraith recommends that you enable trace flag 4199 in SQL Server: The session was titled “Modernize Your SQL Server with Bob Ward, the Tiger Team, and CSS Escalation Engineers” and it…was…awesome! One of the presenters was Pedro Lopes (blog/@SQLPedro), a Senior PM for the Relational Engine.  In his part of the day he talked […]

Read More

Categories

June 2016
MTWTFSS
« May Jul »
 12345
6789101112
13141516171819
20212223242526
27282930