Enrico van de Laar - Pro SQL Server 2019 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server

Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub via the books product page, located at www.apress.com/9781484249154 . For more detailed information, please visit http://www.apress.com/source-code .

Performance is a hot issue on a lot of database implementations. Many businesses run into performance-related issues when their databases experience more load or grow larger in size. There are many methods available for increasing the performance of your SQL Server(s) on all types of levels. Many of these performance-optimization methods we consider best practice, like running index maintenance to make sure fragmented indexes dont slow down your queries, or updating statistics so the SQL Server Database Engine has the correct information to generate a good execution plan. Besides these database maintenance methods, you can also choose to dive a little bit deeper into specific query performance troubleshooting, optimizing queries by making sure expensive operators are replaced by less expensive ones, for instance. And of course there is always the sledgehammer approach, replacing your current hardware for newer, better performing hardware, hoping that will solve the performance issues you are experiencing.

No matter what approach you choose to optimize or troubleshoot SQL Server performance, there are always two common resources involved: time and money. Ideally we want to spend as little time and money as possible while we are working on increasing performance. Knowing where to focus your time and money is very important. If you can find the source of the performance problem and resolve it at that level, you can save a lot of time and money that you would have spent on analyzing symptoms.

In a way, we can compare our search for the heart of our performance issues with a medical examination. Instead of giving out different types of medication until something actually works, a physician is always trying to find the source of the problem so he or she can prescribe the right medication that works best for that specific condition without causing side effects. The same approach works for SQL Server. Implementing all types of possible solutions without looking at the real source of the problem will probably not solve the real underlying issue (unless youre really lucky) and can possibly make matters worse.

This is where wait statistics can help. Wait statistics are generated and maintained at the heart of the SQL Server Database Engine where queries are being executed, giving valuable insight into what is slowing down your queries. There are 921 different types of wait statistics in the latest edition of SQL Server (SQL Server 2017), and with every edition that number grows as new features are introduced or existing features are modified or expanded. That is a lot of information that is freely available to help you troubleshoot!

This book is my attempt to help you understand SQL Server wait statistics. It will go into detail how wait statistics are being generated and how you can use that information to optimize, or troubleshoot, the performance of your SQL Server installation. I will also describe specific wait statistics and give you pointers on how you can resolve problems yourself. In the case of this book, I personally believe the journey is more important than reaching the destination. For that reason, I spend more time describing and explaining what is causing the specific wait types to occur than I do writing down every possible way you can lower their wait times. If you understand why a wait type is generated, and to what part of SQL Server it is related, you will quickly discover methods of your own to lower their wait times.

Because of the sheer number of different wait statistics, it is sadly impossible to describe and discuss all of them. For this reason, I had to make a selection of wait statistics to include in this book. The way I did this was by gathering wait statistics information from many different SQL Server installations and selecting the most common or most performance-degrading ones, resulting in a selection of 45 different (or grouped) wait types.

As I wrote in the introduction, the goal of this book is to give you a deeper understanding of SQL Server wait statistics and also to describe various wait statistics in detail. For this reason this book has been split up into two parts, Part I describing the foundation of wait statistics analysis and Part II describing various specific wait statistics in detail. I tried to categorize the wait statistics in Part II by the part of the system they affect (i.e., CPU, Memory, etc.). Some wait statistics arent that easily categorized, since they can affect multiple system parts. In those cases, I tried to categorize them with the part they have the most effect on.