As a DBA, SQL Developer, or GUI Developer, it is crucial to have a deep understanding of SQL Server execution plans in order to identify and resolve performance issues in your code. One tool that can greatly assist in this process is SQL Server Profiler, which allows you to measure various metrics such as CPU usage, logical reads, writes, duration, and frequency counts.
However, once you have identified problematic code using SQL Server Profiler, it is important to know how to interpret and analyze the execution plans to effectively tune your queries. This is where “Dissecting SQL Server Execution Plans” comes in. This book, written by Grant Fritchey, is a comprehensive guide that teaches you how to dissect SQL Server execution plans and provides insights on how to address performance problems.
Unlike many technical books that can be intimidating for beginners or boring for seasoned veterans, “Dissecting SQL Server Execution Plans” strikes a perfect balance. The author starts with an introduction to execution plans and the optimizer’s role in compiling and executing SQL. The explanations are easy to understand for beginners, yet captivating for advanced technical readers.
The book covers a wide range of topics related to execution plans, including the interpretation of common symbols, row counts of connecting arrows, and the use of “Tool Tip” pop-ups. It also delves into important tuning aspects, such as the usage of commands like SET STATISTICS IO and how to interpret their output. Additionally, it provides guidance on setting up SQL Profiler to capture execution plans as XML for later analysis.
One of the highlights of the book is its coverage of join types and how the optimizer chooses them. It explains the three types of joins, why the optimizer may select them, and how to override the optimizer’s choices if necessary. The book also explores other operations, such as the differences between clustered and non-clustered indexes, the optimization of cursors, and the selection of the most appropriate XML commands.
Furthermore, “Dissecting SQL Server Execution Plans” addresses the impact of parallelism on query performance and provides insights on when parallelism may hinder rather than help. It also offers guidance on troubleshooting scenarios where a specific index is not being utilized effectively.
The book is filled with code examples and graphics that enhance the learning experience. The code examples are simple enough for beginners to grasp, yet complex enough to illustrate the concepts effectively. The graphics are well-timed and thoroughly explained, leaving no room for confusion.
Aside from the main topics, the book also includes several useful tips that may seem unrelated but are highly valuable. For instance, it teaches you how to simulate a multi-processor system on a single CPU system for analyzing the impact of parallelism. It also provides guidance on sharing graphical execution plans for collaborative analysis and navigating large execution plans efficiently.
In conclusion, “Dissecting SQL Server Execution Plans” by Grant Fritchey is an essential resource for anyone working with SQL Server. Its comprehensive coverage of execution plans, combined with its accessible writing style and practical tips, make it a must-have for both beginners and seasoned professionals. Whether you are a DBA, SQL Developer, or GUI Developer, this book will greatly enhance your understanding of SQL Server performance tuning.
Title: SQL Server Execution Plans
Author: Grant Fritchey
Publisher: SimpleTalk Publishing
ISBN: 978-1-906434-01-4
Copyright: 2008
Available from: Amazon (print) or SQLServerCentral (ebook)