Chapter 15 Execution Plan Generation

The optimizer uses data from various sources (e.g. statistics, constraints, data volumes) to produce 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).

Chapter 16 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 17 Parameter Sniffing

The values of the parameters provided when a query is first run, are used to create the cached 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).

Chapter 18 Query Recompilation

Query recompilation can be good and 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. The section on using XEs to identify what SQL causes the recompilations was a bit incomplete, since it doesn’t discuss how to set up the session via the GUI, this is specified in chapter 6, but there is no reference to it – another reason to have a separate detailed tools chapter.

Chapter 19 Query Design Analysis

This chapter contains design tips 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 20 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 (db readonly, 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?!  

Chapter 21 Blocking and Blocked Processes

When SQL code runs it takes out locks, if other SQL code also wants the same data, blocking can result. The price for this data consistency is reduced concurrency/performance. The chapter opens with a discussion of the fundamentals of blocking (e.g. locking, blocking, deadlock), and moves onto understanding blocking within the ACID (Atomic, Consistent, Isolated, Durable) framework.

The chapter moves on to explaining the various levels of locks within the lock object hierarchy e.g. Page (PAG), before examining lock modes e.g. Shared (S), and lock escalation. The effects of the various isolation levels are discussed in terms of locking and query performance.

The practical part of the chapter then considers how to capture blocking data (use DMVs to see current blocks, and XEs session for historical data). Recommendations for reducing blocking include: 

• Optimize the queries executed by blocking and blocked SPIDs

• Decrease the isolation level

• Partition the contended data 

The chapter ends with a look at the automatic collection of blocking data (use SQL Agent to capture perfMon counters [Locks: Average Wait Time and Lock Wait Time]).

This chapter provides a very good discussion of many aspects of blocking, including why it is needed, how it affects performance, how it can be captured, and how it can be reduced. There is some useful code for notifying the DBA that blocking is occurring.

Chapter 22 Causes and Solutions for Deadlocks

Deadlocks are essentially blocking that can’t be resolved in the normal manner (i.e. by waiting), so SQL Server chooses one of the pieces of SQL to rollback. The chapter opens with a discussion about what deadlocks are, and how they often result from lock escalation. Code is provided to illustrate how a deadlock can be handled in SQL code via retries. Various methods of collecting deadlock data are discussed, including: 

• Use Extended Events (xml_deadlock_report), probably best

• Set trace flag 1204

• Use trace events 

A detailed discussion on how to interpret the deadlock data is provided (deadlock graph, and various XML sections). The chapter ends with a look at how deadlocks can be reduced, including:

• Access resources in the same physical order

• Decrease the number of resources accessed (use a new covering index) 

The chapter provides a good discussion of what deadlocks are, how they can be captured, analysed, and reduced. I suspect the deadlock retry code provided would be enhanced if it contained a WAITFOR DELAY command, so the code retries after a given time, when the other process has hopefully ended.

<ASIN: B07H49LN75>