Author Archives: sqlserverscot

Update Multiple Statistics On The Same Table Concurrently – Locks & Trace Flag 7471

Default Behaviour By default, only one statistic object (index statistic or column statistic) on a single table can be updated at a time.  This is down to the locks acquired by the database engine during the statistic update operation.  For … Continue reading

Posted in Statistics, CE & QO | 1 Comment

Statistics – Single Threaded & Parallel Operations

With default database settings, SQL Server automatically creates and updates statistics to use during query optimisation in order to build efficient query execution plans.  Let’s take a look at how these statistics operations are processed internally and determine which operations … Continue reading

Posted in Statistics, CE & QO | 1 Comment

Sync Vs Async Statistics: The Old Debate

Statistic objects in SQL Server are light-weight objects containing statistical information about the distribution of values in one or more columns.  Statistics are used to calculate cardinality estimates during optimisation allowing the query optimiser to generate efficient query execution plans. … Continue reading

Posted in Statistics, CE & QO | 2 Comments

Trace Flag 4199 Query Optimiser HotFixes

Background & History Of Trace Flag 4199 To summarise what Microsoft have documented here – Starting with SQL Server 2005 SP3 CU6, SQL Server 2008 SP1 CU7 & SQL Server 2008 R2 RTM, Trace Flag 4199 has been used as a … Continue reading

Posted in Statistics, CE & QO | 1 Comment

A Guide On Forcing The Legacy CE

Trace Flag 9481 In my previous blog post I showed that when the database compatibility level is 120 or higher, trace flag 9481 can force the query optimiser to use the legacy CE (cardinality estimator model version 70) when compiling an execution … Continue reading

Posted in Statistics, CE & QO | 1 Comment

Troubleshooting Query Regressions Caused By The New Cardinality Estimator

In this blog post I’ll show methods that can be used to identify and troubleshoot query plan regressions caused by the new cardinality estimator.  I’ll show how this was a very manual task in SQL Server 2014 and how the … Continue reading

Posted in Statistics, CE & QO | 6 Comments

Statistics & Cardinality Estimator Model Variations

This blog post covers some of the fundamentals of statistics in SQL Server, the cardinality estimation models across different versions of SQL Server, how cardinality estimates are derived in different scenarios and variations in estimates under the different cardinality estimation … Continue reading

Posted in Statistics, CE & QO | Leave a comment