|SQL Server 2017 Query Performance Tuning, 5th Ed|
Page 3 of 4
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:
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:
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:
Avoiding unnecessary compilations can improve performance, methods discussed include:
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:
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:
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:
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:
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:
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.
|Last Updated ( Saturday, 03 November 2018 )|