Scott and Rich are kings of the disco at the SQLBits party!
Enjoyable, But Room for Improvement
I presented a new, 400-500 level session on deep internals of the SQL Server optimizer at the wonderful SQLBits conference in Telford, UK*. I originally wrote the content for a multi-hour time slot and, to be honest, I’m still struggling to get all of that fine content to fit into 60 minutes while still allowing some time for Q&A. The personal feedback I got from attendees was “The session was great. Really enjoyable. But it didn’t quite follow the abstract and, thus, there was room for improvement”.
In the abstract, I promised:
You already know a thing or two about tuning a SQL query on Microsoft SQL Server. You can read an execution plan and know the most significant red flags to look for. And you have also learned about the important information revealed by SET STATISTICS statements. But you want to take it up another level! In this session, go even deeper with query tuning with three new lessons. First, we’ll examine a few new and seldom used features inside of SSMS specifically for query performance. Second, we’ll spend a bit of time learning query related DMVs and how to read query plans in directly within XML. Finally, we will discuss and demo a set of powerful Trace Flags, the 8600 series, that reveal additional details about how the query optimizer behaves as it processes a query.
After discussing the session with some close friends, I’ve got several good ideas how to make this content fit into the time allowed. So look for new variations of this presentation in the future. In addition, I definitely need to change the abstract since those I talked with didn’t want to spend much time on SSMS, something they all already knew quite well. Most of those I spoke with enjoyed what we were able to cover in the time available, but wanted to spend much more time on the 8600 series of trace flags and get a better understanding of the internal information they reveal, such as the depth of the search space the query optimizer considers for a given query, what heuristics it applies (or ignores), and how to beg more search opportunities from the query optimizer.
The Slides and Scripts
In spite of timing, the demo and slide decks were packed with all of that great content for the long-form of the session. Consequently, many folks wanted to dive into the content on their own time to review the slides and play with the demos. I have attached those HERE for your edification.
Let me know your thoughts and comments! Many thanks,
-Kev
Postscript:
* I stand on the shoulders of giants. Much of the demo content and the knowledge that informed the slides came from others. In particular, I’d like to call out Brian Hansen for the CorpDB examples, and send thanks to Paul White for the optimizer internals information.
