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 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 (e.g. create a covering index).

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. 

This chapter provides a good overview of a common and potentially serious problem, together with useful solutions. The second sentence is incorrect, the word ‘all’ is missing, it should read: “Nonclustered indexes help query performance in all manner of ways. However, unlike clustered indexes, all the data isn’t stored with nonclustered indexes.”

Chapter 12:​ Dealing with 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 importance of 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. 

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

Chapter 13:​ Parameter-Sensitive Queries -​ Causes and Solutions

The values of the parameters provided when a query is first run, are used to create the execution plan. It should be noted that this is generally a good thing. A problem can arise if these parameter values are atypical, because subsequent runs of the query can be slow due to an inappropriate plan being used. This is the problem of parameter sniffing. A similar problem can occur if the statistics are stale. The chapter provides details on how to identify the problems of parameter sniffing (i.e. intermittent or just bad performance). 

This chapter provides a good description of what parameter sniffing is, how it goes bad, how to identify it, and how to correct it (e.g. sp_recompile, assign parameters to local variables etc). There’s a useful discussion on Parameter Sensitive Plan Optimization (new in SQL Server 2022,) that allows more than 1 execution plan to be in memory, and can be selected automatically based on the parameters used. I’m not sure why this chapter follows Index Fragmentation, a more natural home would be to follow the chapter on Execution Plans or Key Lookups.

Chapter 14:​ Query Design Analysis

This chapter contains design tips to help with performance, that you should certainly consider when creating SQL code. All the advice is sensible, and includes:  

• Operate on small result sets (rows and columns)

• Use indexes effectively

• Avoid non-sargable search conditions

• Avoid arithmetic operators on the WHERE clause columns

• Avoid functions on the WHERE clause columns

• Avoid optimizer hints (since they may vary with releases)

• Use domain and referential integrity (helps the optimizer produce a better plan)  

This chapter contains sound background advice that you should use whenever you create SQL code. 

Chapter 15:​ Reduce Query Resource Use

This chapter contains tips to reduce resource usage and thus improve performance. Again, all the advice is sensible, and includes:  

• Avoid resource-intensive queries

• Avoid data type conversion

• Use UNION ALL over UNION

• Use indexes for aggregate and sort operations

• Reduce the number of network round-trips

• Execute multiple queries together

• Use SET NOCOUNT

• Reduce the transaction cost

• Reduce logging overhead (wrap block of trans together)

• Reduce lock overhead (readonly database, nolock, etc)   

Like the last chapter, this chapter contains sound background advice, with plenty of examples, that you should consider whenever you create SQL code. Maybe the Resource Governor, which forcibly limits resources (CPU, memory, I/O) to groups of users/queries, could have been discussed here too? 

I was surprised there was no link to Query Store’s “Top Resource Consuming Queries” report, this is often the easiest way to identify resource-intensive queries (using various measure of resource usage).