SQL Server Optimizer Secrets With Trace Flag 86xx - SentryOne Team Blog

SQL Server Optimizer Secrets With Trace Flag 86xx

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.

Comments ( 3 )

  • John DePrato says:

    Kevin,

    I attended this same or similar session at the pass summit this year. I really enjoyed the content and I know I'm going to be able to use the information to more effectively troubleshoot and performance tune my customers SQL Servers!

    Cheers!

    John DePrato

  • Kevin Kline says:

    CorpDB is the creation of the brilliant Brian Hansen. You can get the database creation script and lots of other goodies on Brian's website at https://www.tf3604.com/presentations/optimizer/. Hope that helps!

    -Kev

  • Adrian Tompkins says:

    Hi Kevin

    I really enjoyed the talk. I would have been quite happy to sit there for another 2 hours :)

    Going through the scripts I have a small question. What is CorpDB and where can I get a copy? My Googlefoo is failing me this morning!

    Thanks again and see you next year
    Ad

Leave A Comment

Your email address will not be published.