SQL Server Query Performance Tuning (4th Ed)
Article Index
SQL Server Query Performance Tuning (4th Ed)
Chapters 6 -14
Chapters 15 -22
Chapters 23 - 26, Conclusion

Author: Grant Fritchey
Publisher: Apress
Pages: 612
ISBN: 9781430267430
Print: 1430267437
Kindle: B01JC6P8MC
Audience: DBAs and developers
Rating: 4.8
Reviewer: Ian Stirk

**There is also a review of the 5th Edition of this book, see SQL Server 2017 Query Performance Tuning

 

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: 

  • Insufficient indexing

  • Inaccurate statistics

  • Improper query design

  • Poorly generated execution plans

  • Excessive blocking and deadlocks

  • Non-set-based operations, usually T-SQL cursors

  • Inappropriate database design

  • Excessive fragmentation

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: 

  • Optimizing application workload

  • Allocating more memory to SQL Server

  • Addressing fragmentation 

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:   

  • Optimizing application workload

  • Using a RAID array

  • Moving the log files to a separate physical drive

  • Using partitioned tables 

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.

 

Banner



Last Updated ( Tuesday, 30 October 2018 )