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…Read More
Short answer: the SQL Server optimizer will know that the table was truncated, but statistics might not update when you expect. For the long answer, let’s walk through an example using the WideWorldImporters sample database. I’ll be using Trace Flag 3604 and 2363 to get SQL Server to print information about how it optimized my query out to the messages tab. (Thanks to Paul White for blogging about this trace flag.) First, a fresh restore of WideWorldImporters USE master; GO IF DB_ID('WideWorldImporters') IS NOT NULL ALTER DATABASE WideWorldImporters SET OFFLINE WITH ROLLBACK IMMEDIATE RESTORE DATABASE WideWorldImporters FROM DISK= 'S:\MSSQL\Backup\WideWorldImporters-Full.bak' WITH REPLACE GO USE WideWorldImporters; GO Before we do anything, what do the statistics look like on Sales.OrderLines? Here’s the query that I’m using to inspect the statistics: SELECT sp.last_updated, stat.name as stats_name, STUFF((SELECT ', ' + cols.name FROM sys.stats_columns AS statcols JOIN sys.columns AS cols ON statcols.column_id=cols.column_id AND statcols.object_id=cols.object_id WHERE statcols.stats_id = stat.stats_id…Read More
When Did SQL Server Last Update That Statistic? How Much Has Been Modified Since? And What Columns are in the Stat?
Whether I’m working as a DBA, a consultant, a teacher, or just answering questions in my inbox, I always end up needing a script to inspect statistics one way or another. Here are some freshly written scripts for a classic DBA question: what’s going on in my stats? How to get statistics details on SQL Server 2008 R2 and higher For most modern versions of SQL Server, I like to join to sys.dm_db_stats_properties() — you can get a LOT of detail in a single query! (This works with SQL Server 2008 R2 SP2+ / SQL Server 2012 SP1+ / All higher versions) Here’s the query, looking at a sample table in the WideWorldImporters database: SELECT stat.auto_created, stat.name as stats_name, STUFF((SELECT ', ' + cols.name FROM sys.stats_columns AS statcols JOIN sys.columns AS cols ON statcols.column_id=cols.column_id AND statcols.object_id=cols.object_id WHERE statcols.stats_id = stat.stats_id and statcols.object_id=stat.object_id ORDER BY statcols.stats_column_id FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),…Read More
Should you look at automatically created statistics on your tables in SQL Server to help you design better indexes? Learn why in this 20 minute video, or subscribe to the Dear SQL DBA podcast. No time to watch? Scroll on down, everything is written in article form below the video. Here’s this week’s question: Dear SQL DBA, I’ve noticed that many indexes in my data warehouse aren’t used frequently. Is there a way to use the automatically generated statistics to make useful indexes? … (insert witty pun about indexes) I’ve been asked this question several times, and I even remember once wondering this myself. There’s no good way to analyze the column based statistics that SQL Server automatically generates for the purpose of index design. Let’s talk about why. First: Why do we have ‘statistics’, anyway? Let’s say we have a table named dbo.Grades. It has columns for GradeId, ClassId, StudentId,…Read More
Want to learn more about managing statistics updates in SQL Server? Watch my free 27 minute presentation on managing statistics: You can also watch 12 minutes of audience Q&A on statistics from when I presented this on a live Hangout on Air. Questions include: Should I create single column statistics for non-leading key columns in an index? What is asynchronous statistics update? What are these statistics with “WA_sys” names? I have a bunch of user created statistics and it’s blocking a release. It is safe to drop them? Got more questions on statistics? Check out my reference post on Updating Statistics in SQL Server, and ask away in the comments of the post!Read More
I’ve been asked a lot of questions about updating statistics in SQL Server over the years. And I’ve asked a lot of questions myself! Here’s a rundown of all the practical questions that I tend to get about how to maintain these in SQL Server. I don’t dig into the internals of statistics and optimization in this post. If you’re interested in that, head on over and read the fahhhbulous white paper, Statistics Used by the Query Optimizer in SQL Server 2008. Then wait a couple days and chase it with it’s charming cousin, Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator. I’m also not talking about statistics for memory optimized tables in this article. If you’re interested in those, head over here. Quick Links to Jump Around This Article General Advice on Statistics Maintenance Which Free Third Party Tools Help Update Statistics? What are Statistics? What Creates Column Statistics? What are…Read More