Over the past few years, Adam and I have won several books at the local MadPASS chapter meetings. I’ve intended to write a book review for quite awhile and thought I’d start off with this book for several reasons:
- It can easily be read from cover-to-cover. This book is organized in a user-friendly format and at 342 pages is manageable in length. It does not try to cover every aspect of SQL Server, but instead focuses on practical tools and techniques that can be used from day-to-day to troubleshoot common SQL Server issues.
- The content of the book is appropriate not only for ‘Accidental DBAs’, but also for other IT professionals who want to gain a better understanding of SQL Server. As a BI developer, I read this book to enhance my understanding of SQL Server to write better code and implement more effective systems. I found Chapters 3 and 5 (‘High CPU Utilization’ and ‘Missing Indexes’) especially useful.
- I also wanted to gain a better appreciation and understanding of a DBAs role and felt this book would provide a good overview.
Many good reviews of this book already exist and provide a thorough overview of the topics covered. I have included links to two of them at the end of this post. Therefore, I am not going to review all the content covered. Instead, I have listed some points that I found interesting and useful in each chapter.
- Chapter 1 (A Performance Troubleshooting Methodology) – When troubleshooting issues in SQL Server it is important to analyze multiple pieces of data and not a single data point. A single piece of data can be misleading and set you on the wrong troubleshooting path.
- Chapter 2 (Disk I/O Configuration) – Performance and throughput (in addition to storage capacity) are important considerations when planning a disk I/O subsystem for SQL Server.
- Chapter 3 (High CPU Utilization) – A few common causes of high CPU utilization are missing indexes, outdated statistics, non-SARGable predicates, implicit conversions and parameter sniffing.
- Chapter 4 (Memory Management) – SQL Server will use as much memory as it is allocated and will not release memory unless the operating system has the ‘memory low’ resource notification flag set.
- Chapter 5 (Missing Indexes) – To seek on an index, a query must filter on a left-based subset of the index key.
- Chapter 6 (Blocking) – Inefficient queries hold locks longer than necessary and may cause blocking. To minimize blocking, adhere to the following principles:
- Keep transactions as short as possible.
- When comparing or joining columns, make sure they have the same data types to avoid implicit conversions.
- Keep queries as simple as possible.
- Use set-based methods instead of row-by-row analysis whenever possible.
- Chapter 7 (Handling Deadlocks) – The most common types of deadlocks are bookmark lookup deadlock, serializable range scan deadlock, cascading constraint deadlock, intra-query parallelism deadlock and accessing objects in different orders deadlock.
- Chapter 8 (Large or Full Transaction Log) – A very high number of small VLFs impacts performance of operations that read the log file (e.g restores and backups). A small number of large VLFs conversely can lead to rapid growth of the log if truncation is delayed.
- Chapter 9 (Truncated Tables, Dropped Objects and Other Accidents Waiting to Happen) – Prevention is worth a pound of cure; strict change control policies and appropriate security permissions are paramount to protecting data.
Overall, as a non-DBA I enjoyed this book more than I expected. It is written in a very user-friendly and well-organized format. The content covered is very practical; the tools and techniques discussed in this book may be used to troubleshoot common SQL Server issues on a day-to-day basis. The authors also provide references to online resources such as blog posts, articles and websites that cover additional content.
Check out a few other reviews of the book ‘Troubleshooting SQL Server A Guide for the Accidental DBA”:
