Chapter 8 ​Performance Troubleshooting 

Performance problems can be investigated using various tools, this chapter looks at some of the more traditional tools.  

The chapter opens with a look at Performance Counters, these record information about Windows OS, services, applications etc (e.g. CPU usage). The importance of having baseline values is highlighted, allowing you to decide if current values deviate significantly and deserve further investigation. The Performance Counters discussed include:  

• Page Reads/​Sec – indicates time spent reading data

• Page Writes/​Sec -indicates time spent writing data

• Page Life Expectancy - how long data lives in cache

• Buffer Cache Hit Ratio – is data coming from cache or disk

• % Processor Time – CPU usage

• Processor Queue Length – waiting on CPU

• Locks – indicates reduced concurrency  

In most cases the meaning of the counter is discussed together with reasons for high values, and some potential solutions. There’s a helpful mention of the Performance Analysis of Logs (PAL) tool for analysing your results – it can quickly highlight potential areas of concern.

Next, the chapter looks at some useful DMVs, including:  

• sys.​dm_​io_​virtual_​file_​stats – IO and wait information

• sys.​dm_​db_​index_​usage_​stats – how a table/index is used

• sys.​dm_​exec_​query_​stats – records time, IO, CPU etc used by queries

• sys.​dm_​os_​sys_​info – OS information e.g. CPU count, server memory  

In each case the meaning of the salient columns of the DMV is discussed, and its usage in troubleshooting highlighted.

The chapter ends with a discussion about a miscellany of features: Extended Events (lightweight monitoring/tracing), Data Collector (mechanism for centrally storing monitoring information), Operator-Level Performance Statistics (performance info inside the execution plan), and Trace flags (these influence how SQL Server work).

This chapter provides a useful overview of some traditional monitoring and troubleshooting tools. Little or no detail on how to use the tools is given, this is left to the reader to discover. I would have liked to have seen more discussion on combining the various tools (e.g. DMVs and Performance Counters) to corroborate any underlying problems/solutions.

Chapter 9​ Indexing 

Indexes are the primary means of improving the performance of SQL queries. The chapter opens with a look at how and where indexes can be used, including helpful code examples. There’s a useful section on validating index usage - to ensure your indexes are being used, else they may decrease query performance.

Next, we look at index maintenance, an important topic if you want your indexes to perform optimally (else indexes become fragmented and queries can perform significantly slower). Another mention of Ola Hallengren’s maintenance scripts might have been useful here.

The chapter then discusses index structures, including heaps (rows have no specific order), clustered indexes (ordered), nonclustered indexes (duplicate data, often improve query performance), and filtered indexes (index based on a WHERE condition). Various code examples of index usage are discussed.

There’s a short discussion on identifying missing indexes via the DMVs, and helpful code is supplied to identify these. The chapter ends with a look at the Database Engine Tuning Advisor (DTA), a very useful tool that takes your workload and identifies the optimal indexes for that SQL workload. A useful walkthrough of the DTA is provided.

This chapter provides a useful overview of the importance of indexes, the different types, how to identify missing indexes, and how to use the DTA to optimise your tables/indexes for your workload. There’s a helpful recommendation to not apply indexes blindly, but only after appropriate analysis.

Chapter 10​ Intelligent Query Processing 

Microsoft has been working on automating performance improvements that require no code changes, just runtime adjustments to execution plans and optimizer stats. There were 3 such improvements in SQL Server 2017, and a further 5 with 2019, with more expected in the future. This functionality was previously called Adaptive Query Processing (why change the name?! – to my mind it only creates confusion). Note ‘Memory Grant Feedback’ consists of two of these features: batch mode (in 2017) and row mode (in 2019). The Intelligent Query Processing features are: 

• Batch Mode Adaptive Joins (determine join type at runtime)

• Memory Grant Feedback (feedback the query’s last memory requirement into its next run)

• Interleaved Execution (change execution plan based on actual row counts)

• Batch Mode on Rowstore (process blocks of rows at a time)

• Table Variable Deferred Compilation (enables better stats and subsequent execution plan)

• Scalar UDF Inlining (uses SET processing model to improve performance)

• Approximate Count Distinct (aims to improve responsiveness when have large data volumes) 

In each case, the feature is explained and includes any obvious limitations. In a few cases, useful example code is provided. In some areas the content seemed slim. 

This chapter provides a useful, if brief, overview of Intelligent Query Processing. It will certainly be an interesting area to watch out for in the near future. 

Chapter 11​ SQL Server Storage

Traditionally, disk storage has been the slowest component of database systems, this should change in the future as more systems move to using In-Memory technologies. The chapter opens with a look at the various storage types, including: Storage area networks (SANs), Direct attached storage (DAS), and file shares/servers. The growing usage of faster flash-based storage is briefly discussed.

We next look at aspects of database configuration, including: file placement, fragmentation, and compression – and how these can impact performance. This is followed with a look at some of the common tools for measuring and monitoring disk performance, including: Resource Monitor, SQLIOSim (simulates a workload), and various DMVs.

The chapter ends with a look at the various RAID (redundant array of independent disks) configurations, and how they impact performance (typically SAN admins use RAID 5 as a best practice, but DBAs often prefer RAID 10 – especially for log files).

This chapter provides a useful reminder of the impact the SQL Server storage system can have on the performance of your queries.

Conclusion 

The aim of this book is to improve the performance of your SQL Server queries by optimizing your configuration settings and database design, and it succeeds.

The book is generally easy to read, contains useful links for further information, helpful diagrams, inter-chapter references, and useful code snippets. It has useful detail of what’s new in SQL Server 2019, together with some of the more traditional features, from a performance perspective. Although the book focuses primarily on SQL Server 2019, much of it is applicable to earlier versions.

This is not a book for beginners (e.g. it assumes you’re familiar with DMVs), I suspect the more you already know about SQL Server performance, the more you will get out of this book.

In a small number of areas the content seemed to lack detail. If you ignore the book’s 2 new chapters, I would think this edition is 85% similar to the book’s first edition. 

In many ways you need the author’s companion book “SQL Server 2014 Query Tuning & Optimization”, which I previously reviewed, in addition to the current book, to get a fuller picture of performance tuning. Indeed I did wonder if the sections on indexes and in-memory technologies belonged to a book that focuses on configuration settings – but this illustrates the interconnection between the two topics.

Overall, a very useful account of the various factors, largely configuration based, that can affect the performance of your SQL Server. Recommended.

• For more recommendations of books on SQL Server see Pick Of The Shelf - SQL Server in our Programmer's Bookshelf section.

To keep up with our coverage of books for programmers, follow @bookwatchiprog on Twitter or subscribe to I Programmer's Books RSS feed for each day's new addition to Book Watch and for new reviews.