How To Turn Off the New 2014 Cardinality Estimator
Produced: 01/05/2016 11:33:00
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.

Continued...
Help for Undocumented DBCC Commands
Produced: 24/04/2016 19:55:00
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.

Continued...
Returning DBCC Messages to SSMS
Produced: 17/04/2016 19:33:00
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.

Continued...
Better Row Estimates with Table Variables
Produced: 06/12/2015 12:54:00
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)…

“FIX: Poor performance when you use table variables in SQL Server 2012 or SQL Server 2014”. As I’m not a complete fan of table variables (they’re good, but for limited uses), I was intrigued.

Continued...
Revealing Predicates in Execution Plans (TF 9130)
Produced: 27/09/2015 21:21:00
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.

Continued...
Automatic Update Statistics Threshold
Produced: 14/09/2014 17:25:00
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.

Inside SQL Server, if you have Automatic Update Statistics turned on in your database, your statistics will be updated each time 20% of your data has changed. This flag changes that behaviour for the better.

Continued...
What Statistics Are Being Used
Produced: 07/09/2014 17:18:00
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?

Continued...
Deadlock Alerts via Email
Produced: 06/07/2014 11:36:00
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.

Continued...
Forcing Parallelism (TF 8649)
Produced: 18/05/2014 16:54:00
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.

So why is it my favourite? Well, basically it causes the SQL Server optimizer to attempt to provide you with a parallel plan for your queries. It’s that simple.

Continued...