Are Bad Statistics Making My Query Slow? (Dear SQL DBA Episode 39)

An important query is suddenly slow. Is it because statistics are out of date? This is tricky to figure out, and updating statistics right away can make troubleshooting even harder. Learn how to use query execution plans to get to the heart of the question and find out if stats are really your problem, or if it’s something else.

In this 35 minute episode:

  • 00:39 SQL Server 2017 Announced
  • 01:10 New video from Microsoft’s Joe Sack demonstrating Adaptive Query Processing
  • 03:05 This week’s question: Are bad stats making my query slow?
  • 05:26 Demo of finding plan in cache and analyzing stats begins
  • 28:17 What to do when stats ARE the problem

Code samples: https://gist.github.com/LitKnd/f07848d59cedc61fd057d12ab966f703

Audio-only version (downloadable)

Video version

Related links

SQL Server 2017 Adaptive Query Processing video by Joe Sack

Michael J Swart on finding Dark Matter Queries

Slow in the Application, Fast in SSMS? An SQL text by Erland Sommarskog

Got a question for Dear SQL DBA? Ask!

Previous Post
Index Maintenance and Performance (Dear SQL DBA Episode 38)
Next Post
How Does a DBA Build Confidence After Making Mistakes? (Dear SQL DBA Episode 40)

Related Posts

4 Comments. Leave new

  • Hello Kendra,

    When using a #temp table in a SP. Does SQL Server use stats for #temp tables? If so should they be updated, within the proc?

    Reply
    • SQL Server can automatically create column statistics on the temporary table if it needs to. You don’t usually need to manually update those statistics.

      This is one of those areas where if you look at it closely, it gets REALLY weird. Statistics on temporary tables used in stored procedures can be cached and re-used across different sessions executing that stored procedure.

      In some (rare) cases, not only updating statistics on the temporary objects, but using recompile hints can be required to get good performance because of bad re-use of cached statistics. Paul White has a detailed post on it here: http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

      Reply
  • Hi,

    Have you used 2 different tables here in the Step 6: Query?

    sc.name= 'Sales'

    and so.name='Invoices'

    Thank you!!

    Reply
  • Leave a Reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Menu