Aaron Bertrand

Book Review : Benjamin Nevarez : Query Tuning & Optimization

October 17, 2014 by in SQL Performance | 1 Comment
Database smarter with deeper insights for Microsoft SQL
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download →

Featured Author

Jonathan Kehayias is a Principal Consultant with SQLskills and the youngest MCM ever.

Jonathan’s Posts →

QTO

My copy of Microsoft SQL Server 2014 Query Tuning & Optimization (also available on Kindle), by Benjamin Nevarez, arrived last week. I wasn't able to get to it immediately, but over the weekend I was finally able to cruise through it in between diaper changes and a trip to the zoo.

TL;DR : Worth the money

Benjamin presents a thorough explanation of the entire query optimization process, and a lot of the background you'll need to make sense of it all. He treats indexes, statistics and plan caching, as well as how to deal with common issues such as parameter sniffing. He explains popular options like Optimize for Ad Hoc Workloads and Forced Parameterization, and how they change the way plans are cached and reused. He also discusses many undocumented features and trace flags that can help you better understand (or coerce) SQL Server's approach to your query, and how to use Trace, Extended Events and DBCC commands to review the decisions the optimizer had made and the impact they've had.

There is also a good portion of content dedicated to many new SQL Server 2014 features, including In-Memory OLTP ("Hekaton"), clustered columnstore indexes, incremental statistics, and the new cardinality estimator. As the book was being written, the AdventureWorks sample databases for SQL Server 2014 were not yet published, but they are available now.

The book ends with a complex but informative chapter on query optimizer limitations that lead to sub-optimal plans, and how to use hints and plan guides to work around them.

Some limitations

While a fairly complete overall reference to query tuning, there were a couple of things I thought could have been better:

  • Benjamin talks about collecting ShowPlan information from Trace and Extended Events, and correctly points out that Profiler is not the way you want to run traces, because of the horrendous and well-documented performance overhead. This is a good thing, since trace is deprecated and Profiler is what people usually default to using. There is a minor warning about the overhead of the query_post_execution_showplan event, that I think could have been given a little more emphasis, with reminders that this should only be used for very targeted tuning and troubleshooting. Collecting this for every query in your workload is likely to do more harm than good.
     
  • He discusses filtered indexes and statistics, but glosses over the fact that they are problematic when it comes to automatic updates, since there is no special algorithm used even though the index only represents a subset of the table. Microsoft has confirmed that this is a problem, but at this point, they have no plans to fix it. For more information, see Connect #509638 and Connect #333532. Also see this blog post for a lot of other filtered index limitations to consider.
     
  • While the coverage of 2014 features was pretty comprehensive, I was surprised to not find any mention of sys.dm_exec_query_profiles, a new DMV for tracking query progress in real time – all the way down to the individual operator. It's not essential information for everyone, though, since it's not an extremely wide use case (IMHO it's only really useful for particularly long-running queries).
     
  • This is a total nit-pick, but it's one that's common among technical titles: the index is slightly inaccurate. For example, the reference to SQL Sentry Plan Explorer points to page 13, but the relevant content is actually on page 14. Like I said, very minor nit-pick, it just stood out to me because that was the very first thing I looked up in the index. :-)

I wouldn't consider these complaints, per se. No book can contain every last detail about everything. So please consider them addenda. :-)

Summary

This has quickly become a convenient reference for me; the dog-ears are already replacing several of my bookmarks to blog posts that happen to mention some obscure detail or trace flag, and in many cases Benjamin has described it in more detail and/or framed it with better context. Between this book and a couple of titles referenced below, I think anyone familiar with T-SQL could get up to speed pretty quickly, even having zero knowledge of the optimizer before diving in.

Complementary materials

Other books I would recommend to complement this title:

And of course there is an endless supply of blog posts and other articles to help with your understanding of tuning and optimization. I'm kind of partial to Paul White's material – they're about as deep as mortals would ever want to go, but you can get a ton out of them by merely skimming, too. Grant and Gail Shaw also have some valuable content on queries and execution plans, and Kimberly Tripp and Erin Stellato are great resources for anything index-related or stats-related.

You may find some overlap, but I'm a big believer in consuming information from multiple sources. Sometimes they're presenting the same information, but the light bulb clicks based on the way one person said it or the context they gave. I also probably missed about 50 other folks with great material.

Oh, and did I mention our free tool, Plan Explorer, can help with this, too? My updated demo kit walks through a bunch of examples.