| SQL Server 2017 Query Performance Tuning, 5th Ed |
Page 1 of 4 Author: Grant Fritchey A popular performance tuning book gets updated for SQL Server 2017, 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 2017, two new chapters have been added, and various other chapters updated and extended. 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:
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. The chapter is easy to read, with useful discussions, diagrams, inter-chapter links, helpful sample code (in other chapters), and useful website links for further information. These traits apply to the whole of 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).
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:
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 and network related problems, how to analyse them, and various methods of fixing them. The typical values for perfMon counters, and the summary grids of 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. Additionally, it may have been useful to discuss the common DMV query that retrieves the CPU usage, for both SQL Server and other processes, for each of the last 256 minutes. 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:
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. There’s a brief look at baselining Azure SQL Databases, where the Database Transaction Unit (DTU) and the DMV sys_dm_db_resource_stats can be examined since the more traditional ‘physical’ counters are unavailable. 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. The impact of Virtual Machines (VMs) on the unreliability of CPU and memory perfMon counter values is noted. Perhaps this chapter could have separated out the use of perfMon tool from the creation of a baseline? The chapter should have mentioned the use of the Performance Analysis of Logs (PAL) tool for ‘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. 
|
|||||
| Last Updated ( Saturday, 03 November 2018 ) |