SQL SERVER – 5 Resources from SQLPASS 2019 – Seattle

Earliest this week, I presented at SQLPASS, Seattle. It has been my 10th SQLPASS and I am very excited about it. I presented a session on Query Optimizer. In this blog post, we will see 5 important resources related to my session at SQLPASS 2019. 

Elementary! The Curious Case of Deceptive Query Optimizer at SQLPASS 2019

Abstract:  We will see two important case studies where the optimizer is obviously a criminal who has killed the query performance. We will carefully investigate inside the Query Optimizer together and attempt to understand why it often behaves in a manner that is beyond common sense. While we investigate our crime scene of a poorly running query, we will come across many unusual and usual suspects, like – statistics, query hints, indexes, cardinality, and compatibility. Just like a true investigator, we will figure out why Query Optimizer is not a criminal, but rather a victim of circumstances. Before we end the session, we will have a revelation of the true culprits. The journey would not be an easy one, join only if you have a keen desire to solve the mystery and learn advanced level concepts of Query Optimizer.

Here are the important resources, which I had discussed in the session and said I will blog about it.

I was very thankful to two individuals Erik Darling (Twitter | Blog) and Hugo Kornelis (Twitter | Blog) to review and help me prepare this advanced-level session.

Missing Indexes and Unused Indexes

Another major discussion which we had done was about how to maintain your indexes. Here are two of the most popular scripts which I had written on the topic of the missing indexes and unused indexes.

• SQL SERVER – Missing Index Script – Download
• SQL SERVER – Unused Index Script – Download

Index and Performance

During the demonstrations, we had a long conversation about how indexes not only reduce the performance of the Insert, Update and Delete statements but also reduces the performance of the SELECT statement. I think lots of people were surprised by this one. I am including the link to my earlier video on this topic here.

• SQL SERVER – An Index Reduces Performance of SELECT Queries
• SQL SERVER – 3 Questions: An Index Reduces Performance of SELECT Queries

Statistics

The emphasis of the session was how the query optimizer works and how different results are produced as an impact of the Statistics. It was very important to understand the impact of the statistics on the overall performance of the system. Here are blogs that discuss the statistics.

• Query Specific Wait Statistics and Performance Tuning

If you have attended my SQL Server Performance Tuning session at SQLPASS 2019. You are eligible to get 40% discount on my recorded class, here is the link for the same: SQL Server Performance Tuning Practical Workshop for EVERYONE – Instant Learning. (Normal Price USD 1000, Discounted Price USD 600 for next 24 hours).

Reference: Pinal Dave (https://blog.sqlauthority.com)