| SQL Server 2022 Query Performance Tuning (Apress) |
Page 1 of 5 Author: Grant Fritchey A popular performance tuning book gets updated for SQL Server 2022, 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 2022, two new chapters have been added, other chapters have been merged or split, and rearranged. 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: Query Performance Tuning This book 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 example code (in other chapters), and useful website links for further information. These traits apply to the whole of the book.
Chapter 2: Execution Plan Generation and the Query Optimizer 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 3: Methods for Capturing Query Performance Metrics SQL Server has various tools to capture query performance information. This chapter looks at Dynamic Management Views (DMVs), Query Store (in passing), and Extended Events (XEs). There’s a useful overview of various methods of getting query metrics (e.g. duration), together with some interesting insights into why some methods are less suitable. This is followed with a brief overview of DMVs, with some useful starter queries for those new to the power of DMVs. Next, Query Store is mentioned in passing, with a reference to its own later chapter. The well-known SQL Server Profiler/Trace tool has been deprecated and replaced with a lightweight XEs tool. This chapter provides a step-by-step walkthrough on setting up an XE session via the GUI that’s included within SQL Server Management Studio (SSMS). The walkthrough examines each of the tabs on the GUI screen and explains the relevant fields. Later, T-SQL equivalent XE code is provided (useful for programmatically examining multiple servers etc). The use of Causality Tracking to link related SQL statements together is briefly discussed. Various useful XE recommendations are discussed, including:
This chapter provides a useful discussion on the preferred tools for gathering query metrics. A helpful list of common events together with their meaning is provided. The XE recommendations should help you reduce the impact of monitoring on the system being investigated. Chapter 4: Analyzing Query Behavior Using Execution Plans The Execution Plan is produced by the optimizer, it details how your logical SQL query will be physically implemented, hopefully fast. The differences between estimated and actual execution plans are briefly discussed, the main difference is the latter contains runtime information (e.g. wait stats, row counts). Various methods of capturing execution plans are given, including: SSMS, DMVs, XEs, and Query Store. In each case the advantages and disadvantages of the tool is discussed, and example code provided. The section on how to read the execution plan should prove very useful for readers. This is followed by a helpful section on things to look out for in the execution plan, a set of rules-of-thumb that often indicate potential problem areas. These include:
I was a little surprised the discussion on Estimated vs Actual counts didn’t highlight stale statistics, the primary cause of the differences, and often an easy performance fix. The chapter ends with a look at some useful tools to help you work with execution plans, including the author’s book on execution plans, and various third-party tools (most readers should get familiar with Solar Winds Plan Explorer). The was a very useful chapter, providing lots of tools and example code to extract and decipher execution plans. The author should have included a link to his free execution plan book, which can be found here: www.red-gate.com/simple-talk/books/sql-server-execution-plans-third-edition-by-grant-fritchey/ |
||||||
| Last Updated ( Tuesday, 14 February 2023 ) |
