Via Twitter in October Benjamin Nevarez @BenjaminNevarez solicited help for book reviewers for his new book “Microsoft SQL Server 2014 Query Tuning & Optimization” I jumped at this opportunity as Benjamin is a great author with significant SQL Server experience and this new book further solidifies his place in the community.
Query processing and its inner workings are extremely dense technical concepts that Benjamin is able to present to the reader in a very approachable readable manner. The book elegantly walks you through the major components of the query processor and dives deep where needed.
The code examples in the book are concise and accurately reenforce the key points the author is trying to convey in the text.
The book excels at connecting very complicated query processing topics together for the reader in a simple and approachable manner.
It covers the many new features of SQL 2014, including In-Memory OLTP, incremental statistics, the new cardinality estimator and the new features added to columstore indexes.
There are few shortcomings with this book, I would have preferred citations throughout the book. Key concepts are introduced without citation and I would like to jump into some topics, specifically into the materials the author used when writing. The book does have an exceptional reference section, but there is no connection back to the text.
This book is now an a permanent fixture in my library of SQL Server references, I will likely need to replace it when I wear it out.
If you need help with query tuning or other performance issues with your SQL Server please contact me at: aen@centinosystems.com
Here are some of the (unedited) notes that I took while reading the book, covers the first six chapters:
Chapter 1
Solid intro. Good examples specifically on the warnings sections page 24 with references to upcoming chapters on how to deal with them. Gets off the ground with the appropriate groundwork/fundamentals for QP architecture and reading query plans and to move onto the subsequent chapters.
Chapter 2
The chapter begins with the building blocks of query troubleshooting how to find resource utilization exec_requests and exec_session and also plan hash and text hash for when you need to hunt down your code or plan. Page 51 CPU queries are great splitting on plan hash so each batch is in results. The chapter then moves from the building blocks of hashes and text to tell the reader which tools are available to find troublesome queries on your systems.
Chapter 3
Walks us through the query optimization process with great detail and contrete examples. Toes the line between academic and practical. Disceting a QP at this level will give you great insight into the inner workings of sql server and allow you to create better more well performing queries. Having references to academic and professional papers is a great resource. But I would have loved to have had reference numbers so I could go off on a deep dive when needed. This chapter clears the air and busts some myths that are found on the web such as join order at the query level matters. Also gives you the ability to dive deep on your own questions so you can runoff and experiment.
Chapter 4
The transition into this chapter connects the previous chapters. There is a solid example of this on page 130-131. Ben quickly connects the ideas of operations to indexes and their potential efficacy/efficiency. When discussing tipping point between bookmark lookup and scan, the reader is left wondering why. Lack of explanation. Everything else so far is covered in such great detail but this wasn’t. In the parallel limitations section page 156, lacks any insight as to why these features will not produce a plan.
Chapter 6
Avoid the use of local variables in a where clause. Uses a 30 percent guess. Use parameters or literals. The most concise explanation of the histogram yet. Wanted deeper discussion on stats skew.
The post Book Review – Query Tuning & Optimization appeared first on Centino Systems Blog.
