Page 1 of 4
Author: Grant Fritchey
Audience: DBAs and developers
Reviewer: Ian Stirk
A popular performance tuning book gets updated for SQL Server 2014, how does it fare?
This well-liked SQL Server book examines performance from various angles, but concentrates on improvements related to the SQL code itself. The book has been updated for SQL Server 2014, additionally various chapters have been restructured. The book is aimed at anyone responsible for SQL Server performance, especially DBAs and developers.
Below is a chapter-by-chapter exploration of the topics covered.
Chapter 1 SQL Query Performance Tuning
This chapter opens with a discussion of the performance tuning process steps i.e. identify bottlenecks, prioritize issues, troubleshoot issues, apply resolutions, quantify change, and repeat. The price of any performance improvement is examined in terms of targets and having a good-enough solution. The importance of having a baseline is introduced early, allowing you to determine if current processing is ‘normal’.
Performance problems can be due to a variety of causes, including problems with hardware, operating system, network, database design and SQL code. Most problems are due to poor SQL code. The author discusses the major performance killers in outline here, and expands on them in the remainder of the book. Problems examined include:
Improper query design
Poorly generated execution plans
Excessive blocking and deadlocks
Non-set-based operations, usually T-SQL cursors
Inappropriate database design
In many ways this chapter is a summary of the rest of the book. It provides a good overview of the performance tuning process, including a helpful flowchart. There’s a useful overview of the major causes of bad performance, each of which is expanded upon in subsequent chapters. There are good links to related chapters in the book.
Chapter 2 Memory Performance Analysis
This chapter opens with a brief overview of the Performance Monitor tool (PerfMon), and an equally brief overview of Dynamic Management Views (DMVs). This is followed with a discussion about hardware resources i.e. memory, disk, CPU, and network.
Only now does the chapter consider memory problems. Memory bottleneck analysis is primarily examined using various perfMon counters, additionally DBCC MEMORYSTATUS and memory related DMVs are discussed. Various aspects of memory bottleneck resolution are examined, including:
This chapter contains a good discussion of memory related problems, how to analyse them, and various methods of fixing them. The flowchart for memory bottleneck resolution is helpful. The typical values for perfMon counters, and the summary grid of memory related perfMon counters should prove useful.
It may have been better to introduce the tools (e.g. perfMon, DMVs etc) separately in their own chapter, they could then be explained in depth in one place, and referenced when needed. As it is, they are introduced in various places, and at various depths (e.g. perfMon is introduced here, but only explained in chapter 5). Additionally, the examples provided should have related to memory, this was not always the case (e.g. DMVs).
Chapter 3 Disk Performance Analysis
Disk bottleneck analysis is primarily examined using various perfMon counters, additionally disk related DMVs are discussed. Various aspects of disk bottleneck resolution are examined, including:
This chapter contains a good discussion of disk related problems, how to analyse them, and various methods of fixing them. The typical values for perfMon counters, and the summary grid of disk related perfMon counters should prove useful in your own investigations.
Chapter 4 CPU Performance Analysis
CPU bottleneck analysis is primarily examined using various perfMon counters, additionally CPU related DMVs are discussed. Various aspects of CPU bottleneck resolution are examined, including:
Optimizing application workload
Eliminating or reducing excessive compiles/recompiles
Not running unnecessary software
The chapter then diverges into examining network-related bottleneck analysis and resolution, and then SQL Server overall performance with reference to various perfMon counters.
This chapter contains a good discussion of CPU related problems, how to analyse them, and various methods of fixing them. The typical values for perfMon counters, and the summary grid of CPU related perfMon counters should prove useful. I’m not sure why both network and SQL Server overall performance were examined in this CPU-related chapter, they might have been better in their own distinct chapters.
For memory, disk, and CPU problems, a common thread relates to optimizing the application workload – most of the remainder of the book expands on this.
Chapter 5 Creating a Baseline
Creating a baseline allows you to determine if subsequently observed values are typical or not, additionally baselines provide input into trending and capacity analysis.
The chapter looks at creating a baseline using a reusable set of perfMon counters. These counters can be saved and reused later to examine how the current processing varies from the baseline. There’s a helpful step-by-step walkthrough on setting up the perfMon counters, running the session, and writing the output to log files for later analysis. Various methods of minimizing the impact of perfMon are examined, including:
Limit the number of counters, specifically performance objects
Save the counter log file to a different local disk
Increase the sampling interval
Differences between the baseline and current system behaviour are discussed, including data volumes, usage patterns, and user numbers. Finally, it’s noted that the baseline loses its meaning over time, and should be refreshed.
This chapter provides a good step-by-step walkthrough on how to use perfMon, which perfMon counters to include, and how to log the output. There are useful links to further perfMon articles. The current perfMon counter values together with the baseline can be used to determine the nature of your current system behaviour e.g. is more memory needed.
Perhaps this chapter could have separated out the use of perfMon tool from the creation of a baseline? The chapter should have mentioned the Performance Analysis of Logs (PAL) tool, which is very helpful in ‘automatically’ determining how your perfMon counter values relate to expected values for your type of system - it’s a great starting point for further analysis.