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.

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 most costly queries. Specifically, single execution queries are examined via XEs, and multiple executed queries via 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) 

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.  

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 onto 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 a well trodden area. 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) 

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 is particularly noteworthy. The chapter ends with “I will cover the Database Engine Tuning Advisor tool in more depth in Chapter 5.” It is of course in the following chapter, Chapter 10.

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, and using the procedure cache 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 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 better. This chapter explains the key lookup problem, providing helpful code to illustrate the concept. It provides useful techniques to investigate and remove key lookups. However, the code provided is a little artificial, since it uses an index hint to force the access method and show the key lookup problem. 

Chapter 12 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.

Statistics are then examined in detail, including density, multicolumn statistics, statistics on filtered indexes, and the cardinality estimator (both 2014 and pre-2014). 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 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 estimator.

Chapter 13 Index Fragmentation

This chapter opens with an examination of the different types of fragmentation and their causes. Queries that access fragmented indexes need to perform more reads to obtain the required data, thus reducing performance. Code is provided to examine the DMV sys.dm_db_index_physical_stat that shows the amount of index fragmentation. 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 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 Michelle Ufford and Ola Hollengren).  

Chapter 14 Execution Plan Generation

The optimizer uses data from various sources (e.g. statistics, constraints, data volumes) to produce a the execution plan. This chapter discusses the factors involved in producing a good plan, and then examines the various steps involved in plan generation, namely: 

• Parser (syntax checking)

• Binding (resolves name/types, data conversion)

• Optimization (simplification, trivial plan match [no optimizations], multi optimization phases [join types, etc], parallel plan optimizations [cpu affinity, MAXDOP etc )

• Caching (stored in plan cache) 

This chapter provides a useful understanding of how the execution plan is produced, the steps involved, including the many potential optimizations. There are helpful flowcharts describing both the optimization process and the optimization steps. There’s an interesting point about deciding the value of “cost threshold for parallelism” i.e. look at the cost of OLTP SQL v reporting SQL plans in the cache.

<ASIN:1430267437>

<ASIN:B01JC6P8MC>