SQLBits 2017

Inside statistics-a guessing game

A query was running fast yesterday and today it suffers from performance problems. What will you do? - restart the server (did work) - drop the procedure cache (read in a blog article) - get a coffee and pass in review this session about statistics
You know the situation that a query yesterday still worked
quickly and satisfactorily and today it suffers from performance problems?

What will you do in such a situation?

- you may restart SQL Server (did work all the other times before)
- you drop the procedure cache (has been told by a dba)
- you  get yourself a coffee and think about what you learned in this session

Microsoft SQL Server requires statistics for ideal execution
plans. If statistics are not up-to-date, Microsoft SQL Server may create execution
plans that run a query many times slower. In addition to the basic
understanding of statistics, special situations are shown in this session that
are only known to a small group of experts.

After a brief introduction to the functionality of
statistics (Level 100), the special query situations, which lead to wrong
decisions without experience, are immediately apparent. The following topics
are covered by usage of large number of demos:

- When will statistics get updated?
- examples for estimates and how they can go wrong?
- outdated statistics and ascending keys?
- when will outdated statistics updated for unique indexes?
- drawback of statistics in empty tables?

Follow me on an adventurous journey through the world of statistics of Microsoft SQL Server.