Chapter 6 Query Performance Metrics

The well-known SQL Server Profiler/Trace tool has been deprecated and replaced with a lightweight Extended Events (XEs) tool. This chapter provides a step-by-step walkthrough on setting up an XE session via the GUI that’s included within SQL Server Management Studio (SSMS). The walkthrough examines each of the tabs on the GUI screen and explains the relevant fields. Later, T-SQL equivalent XE code is provided (useful for examining multiple servers etc).

There’s a useful discussion on the built-in system_health XE, that automatically collects performance metrics, including detailed deadlock information. The use of Causality Tracking to link related SQL statements together is briefly discussed.

Various XE recommendations are discussed, including: 

• set max file size appropriately

• avoid debug events

• avoid No_Event_Loss 

In addition to using XEs, DMVs can also be used to obtain metrics, especially if you’re interested in the accumulative values, the DMV sys.dm_exec_query_stats is briefly examined.

This chapter provides a useful discussion on the preferred tool for gathering query metrics. A helpful list of common events together with their meaning is provided. The XE recommendations should help you reduce the impact of monitoring on the system being investigated.

Chapter 7 Analyzing Query Performance

This chapter begins by discussing various methods of obtaining the costliest queries.  Specifically, single execution queries are examined via XEs, and multiple executed queries via XEs and DMVs. The second part of the chapter looks at execution plans, with a view to identifying why the query might be costly. Aspects of the plan examined include: 

• Identifying costly steps

• Index effectiveness (seek v scan)

• Join types (nested, hash, merge, adaptive) 

There’s a useful section on Execution Plan Tooling, showing how you can search for a given plan node, compare execution plans, and use Scenarios to highlight potential plan concerns. Monitoring a live execution plan using Live Query Statistics is explained with useful example code.

This chapter contains some useful techniques for measuring query performance (e.g. XEs, DMVs, STATISTICS TIME/IO). There’s some helpful code for reading an XE file to identify costly queries by logical reads and duration. There’s a good tutorial on how to read an execution plan, including a list of operators that might indicate the cause of the query’s slowness.

Code is provided to obtain a cached plan via the DMVs, however this code would be much more useful with a WHERE clause added to it (i.e. get a specific query plan rather than just get all plans). Query Store is briefly mentioned several times in the chapter, but its use deferred until chapter 11.

Chapter 8 Index Architecture and Behavior

This chapter looks at the importance of indexes on query performance. It opens by discussing what an index is, before moving on to describing B-trees and heap 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 use of STATISTICS IO/TIME to measure and compare query performance was helpful. The index design guidelines were especially useful. There was a useful comparison of clustered and non-clustered indexes. Parts of the chapter felt awkwardly organised.

Chapter 9 Index Analysis

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)

• Columnstore indexes (cols not rows. Compressed. Limitations. Batch mode) 

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 addition of the much faster Batch Mode processing for columnstore indexes, are especially noteworthy. Useful code examples are provided throughout to support the assertions made.

Chapter 10 Database Engine Tuning Advisor

The Database Engine Tuning Advisor (DTA) recommends indexes based on the workload provided. The chapter opens with a step-by-step walkthrough on how to create a DTA session, set the tables to investigate, set a session time limit, run the session, view reports, and apply recommendations.

Various DTA examples are provided, including how to tune a single query, a workload, using the procedure cache or Query Store, as input to the DTA. The limitations of the DTA are discussed, perhaps the most important being that recommendations depend on the quality of the workload supplied.

This chapter provides a good overview on how to use the DTA, with helpful screenshots throughout. Perhaps the advantages and disadvantages of using the procedure cache as DTA input could have been expanded.

Chapter 11 Query Store

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 (see reports via 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.

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

Chapter 12 Key Lookups and Solutions

Indexes typically improve performance, however if all the columns required by a query are not on the index, some data is obtained from the underlying table via a key lookup. When a ‘large’ amount of data is required, this can give slow performance, since a table scan may have been a better choice. This chapter explains the key lookup problem, providing helpful code to illustrate the concept. It provides useful techniques to investigate and remove key lookups.

It might have been useful to link parameter sniffing to this chapter, where a ’valid’ execution plan is created for a small number of rows but reusing the plan (involving the lookups) might be inefficient for a larger number of rows.

Chapter 13 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). Various statistics DMVs (i.e. sys.dm_db_stats_properties, sys.dm_db_stats_histogram) are examined, as a preferred way to inspect statistics, instead of the older DBCC SHOW STATISTICS command. The importance of statistics maintenance is discussed, together with the various methods of undertaking the maintenance.

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). Perhaps the section on when to use the 2014 or pre-2014 cardinality estimator could have mentioned the impact of correlated columns on the cardinality estimator.

Chapter 14 Index Fragmentation

This chapter opens with an examination of the different types of fragmentation and their causes for both rowstore and columnstore indexes. The hot topic of whether you really need to defrag your indexes is discussed (the author favours less full pages with subsequent slower reads, over fragmenting indexes with the subsequent resultant blocking). Queries that access fragmented indexes need to perform more reads to obtain the required data, thus reducing performance. Code is provided to examine the amount of fragmentation via the DMV sys.dm_db_index_physical_stats (for rowstore indexes) and the system view sys.column_store_row_group (for columnstore indexes). Various methods of resolving fragmentation are examined, including: 

• Drop existing - avoids rebuilding non-clustered index twice

• Rebuild - blocks

• Reorg – non blocking

• Defrag and partitions – break table into parts, then rebuild these separately 

The fill factor is examined as a method of reducing fragmentation, however it results in less-full pages, and thus more reads may be needed to obtain the required data. Code is provided to rebuild or reorganize fragmented indexes, and to automate this via SQL Agent.

This chapter provides a good overview of what fragmentation is, the problems it causes, and how it can be resolved. Helpful diagrams are provided that describe fragmentation. Useful code is provided that illustrates page splits as the result of an update and an insert. There’s a useful step-by-step walkthrough on how to set up a SQL Agent job to automate defragmentation on a scheduled basis. Several useful third-party utilities are also mentioned (i.e. from Minion Reindex application and Ola Hollengren).

Perhaps it’s an obvious point (?) but, maybe it should be mentioned that once the data is read from the physical disks and cached into memory, fragmentation is no longer a concern.

<ASIN: B07H49LN75>