Fun With Flags

Before I start, I want to point out that I like the new estimator and it does have its advantages over the old one, BUT because it does produce different estimates in certain circumstances it can cause you to get completely different execution plans and therefore, as part of upgrade testing, you will likely need to turn it on and off, hence this post.

This episode of Fun with Flags is used in conjunction with my previous post about TF 3604.


Undocumented DBCC commands are written about all over the place and, although undocumented and therefore also generally unsupported, people seem to love them. I use them myself in all manner of places, never in production code, but they still get used. The biggest problem though, is knowing how to use them.

There are some DBCC commands, such as DBCC PAGE which seem not to do anything because you see nothing in SSMS to tell you otherwise. As it turns out this isn’t the case just that SQL Server is suppressing the information messages.

In this episode of “Fun with Flags” we’ll be looking at TF 2453.


Having been recently playing with SQL Server 2014 I was intrigued to see the following in the list of fixes included for Cumulative Update 3 (note this is also included in SQL Server 2012 Service Pack 2)…

In this episode of Fun with Flags I'm going to discuss trace flag 9130. I found this flag when watching a video on performance tuning by Joe Sack… it’s something that I never knew existed, but that I’ve actually now used a few times because it turned out to be surprisingly handy.

This is a trace flag I only learnt about recently which actually fixes something that has been a large bug bear of mine for quite some time.


Basically we all know that statistics are one of the most important things in SQL Server and therefore it’s also critical to know how they’re maintained.

You’re running a query and you’re looking at the execution plan… it’s all well and good, there are tables, indexes, cardinality estimates… all manner of information. We know these are all derived from statistics and that our statistics should be kept up to date. But the question is… exactly which statistics were used or considered by the optimizer?


This is where these trace flags come into their own… they will give you just that information.

In the second episode of Sheldon Cooper’s Fun with Flags, we’re going to be looking for deadlocks.


There are numerous ways of tracking deadlocks within SQL Server but as we rarely know when they’re going to occur, don’t want traces running all day, and want to react quickly, the best method I’ve found is to have them sent to me in an email each time they appear.

In this first episode of Sheldon Cooper’s Fun with Flags I’m going to be looking at my favourite flag of the moment… Trace Flag 8649.


And yes, before anyone points it out, I know having a favourite trace flag is infinitely more disturbing than having a favourite real life flag but I’ve learnt to live with it and you should too.