Chapter 5:​ Statistics, Data Distribution, and Cardinality

Database statistics describe the distributing and density of column values, they are important in helping the optimizer determine if an index should be used, and if so, how it should be used. 

The chapter opens with a look at the role of statistics in helping the optimizer create a good plan. The database-level statistics settings are examined, and recommended to keep their default setting. Statistics on index and non-index columns are discussed, together with the threshold changes required for automatic update of the statistics. The problems of stale statistics are examined, with helpful example code. 

Statistics are then examined in detail, including density, multicolumn statistics, statistics on filtered indexes, and the cardinality estimator (both 2014 and pre-2014). The book’s previous edition looked at various statistics DMVs (i.e. sys.dm_db_stats_properties, sys.dm_db_stats_histogram) however, this edition looks instead at the older DBCC SHOW STATISTICS command. The importance of statistics maintenance is discussed, together with the various methods of undertaking the maintenance. It’s noted that the auto update stats algorithms have changed since SQL Server 2016, typically resulting in a smaller number of changes needed to initiate a stats update. 

This was a useful and wide-ranging chapter on all things to do with statistics. There’s some useful code that illustrates how stale statistics need more reads, decreasing performance. There’s a good description of the statistics output (header, density graph, histogram).  

Chapter 6:​ Using Query Store for Query Performance and Execution Plans

The Query Store allows execution plans and their metrics to be stored, this allows subsequent analysis to determine if a better version of query’s plan exists, and this plan can then be used. 

The chapter opens with a look at how the Query Store works together with details of the metrics it collects (e.g. execution plan, runtime stats). Useful example code is provided to query the Query Store. A useful overview of its use is provided. 

Next, some of the built-in reports that analyse the Query Store are examined. These include:  

• Regressed queries (what queries are performing more slowly)

• Top resource consuming queries (what’s using most resources)

• Tracked queries (reports on queries you’re interested in)   

The chapter next looks at using the Query Store to force a plan (i.e. you choose a plan you want to use), this can be done with relative ease compared with other methods. 

Finally, there’s a look at using the Query Store to help protect your system from degradation after a software update. This should give you more confidence in your systems ability to perform successfully after any changes. 

There’s a small error in the section about ‘Query Store for Upgrades’, it omits that the tests need to be rerun after changing the SQL Server’s compatibility level, and then the Regressed Queries report can be run. 

This chapter provides a helpful practical overview of the Query Store, its content, and its major uses. 

Chapter 7:​ Execution Plan Cache Behavior

The chapter opens with a look at the use of the DMV sys.dm_exec_cached_plans to obtain information about cached plans. Creating a plan costs resources (CPU etc), so caching a plan should mean subsequent runs of the query use less resources. Sometimes queries are not cached, techniques are examined to reduce this, and these include the “optimize for ad hoc workloads” setting and Forced Parameterization. 

Various recommendations are made to improve the likelihood of plan caching, these include:  

• Explicitly parameterize variable parts of a query

• Use stored procedures to implement business functionality

• Use sp_executesql over EXECUTE for dynamic queries

• Avoid modifying environment settings between connections 

This chapter provides useful instructions that enable you to produce reusable execution plans. Useful sample code is provided to illustrate the points being made. There’s a good point about not running DBCC FREEPROCCACHE on production systems since it can cause spikes in resource usage. 

Chapter 8:​ Query Recompilation

Query recompilation can be good or bad, good because it can pick up a potentially new index, bad because it might be unnecessary and thus waste resources. The chapter starts by looking at how to identify the SQL statement responsible for the recompilation, using XEs. Various causes of recompilation are discussed, including: 

• The schema has changed

• Statistics changed

• SET options have changed   

Avoiding unnecessary compilations can improve performance, methods discussed include:  

• Don’t interleave DDL and DML statements

• Avoid recompilation caused by statistics changes

• Use the OPTIMIZE FOR query hint

• Use Query Store to Force a Plan   

This chapter had some interesting discussions on recompilations and how to reduce them.  

Chapter 9:​ Index Architecture

This chapter looks at the importance of indexes on query performance. It opens by discussing what an index is (both rowstore and columnstore), before moving on to describing the different index structures. Various index design recommendations are discussed, including: 

• Examine the WHERE clause and JOIN criteria columns (index? constraints? stats?)

• Examine the column data type (space, type conversion)

• Consider the type of index (clustered versus non-clustered)  

Heaps, clustered and non-clustered indexes, are described, compared, and recommendations made for each. Code is provided to back up the assertions made. Throughout the chapter (and book), the very sensible mantra is “don’t blindly apply recommendations, test they work for your systems”. 

This chapter covers well-trodden ground. There is a useful overview of the impact of indexes on performance. The index design guidelines were especially useful. There was a useful comparison of clustered and non-clustered indexes. 

Chapter 10:​ Index Behaviors

This chapter follows on naturally from the previous chapter on index architecture, containing more advanced topics, these include:  

• Covering indexes (index needed to satisfy a query. Include)

• Filtered indexes (WHERE clause on index e.g. not null)

• Indexed views (materialised view. Physical structure. Many restrictions)  

For completion purposes only, full-text, spatial, and XML indexes are very briefly examined.  

This chapter provides an interesting insight into the more advanced indexing techniques available. Useful example code is provided to illustrate the concepts being examined. The use of the INCLUDE statement to help prevent the key lookup problem, and the use of compression, are especially noteworthy.