Men Without Hats – now those guys were cool:
Statistics Without Histograms – not so much.
If you have a database that’s been passed along from one SQL Server to another, gradually upgraded over the years, or if you’ve had a table that’s been loaded but never queried, you can end up with a curious situation. For example, take the StackOverflow2010 database: I build it on SQL Server 2008, detach it, and then make it available for you to download and play with. The nice thing about that is you can attach it to any currently supported version – SQL Server 2017 attaches the database, runs through an upgrade process, and it just works.
But when you go to look at statistics:
That means when we ask SQL Server to guess how many rows are going to come back for queries, it’s going to act real rude and totally removed.
Finding This Problem in SQL Server 2016 & Newer
Starting with SQL Server 2016 Service Pack 1 Cumulative Update 2, you can run this in an affected database to find the tables with missing stats, and generate an UPDATE STATISTICS command to fix them.
Warning: before you run this, test it in development. In the comments on this post, we’re getting reports that querying this new DMV is causing some servers to restart! Michael J. Swart reports that it was fixed in 2017 Cumulative Update 8.
SELECT DISTINCT SCHEMA_NAME(o.schema_id) AS schema_name,
o.name AS table_name,
'UPDATE STATISTICS ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name) + ' WITH FULLSCAN;' AS the_fix
FROM sys.all_objects o
INNER JOIN sys.stats s ON o.object_id = s.object_id AND s.has_filter = 0
OUTER APPLY sys.dm_db_stats_histogram(o.object_id, s.stats_id) h
WHERE o.is_ms_shipped = 0 AND o.type_desc = 'USER_TABLE'
AND h.object_id IS NULL
AND 0 < (SELECT SUM(row_count) FROM sys.dm_db_partition_stats ps WHERE ps.object_id = o.object_id)
ORDER BY 1, 2;
That gives you a list of fixes:
Run those in a way that makes you feel – what’s the word I’m looking for – safe. Depending on your server’s horsepower and the size of the objects, you may want to do it after hours.
Updating statistics can be a read-intensive operation since it’s going to scan the table, but at least it’s not write-intensive (since statistics are just single 8KB pages.) However, be aware that this can also cause recompilations of query plans that are currently cached.
Finding This Problem in Earlier Versions
That’s left as an exercise for the reader. Parsing DBCC SHOW_STATISTICS would be a bit of a pain in the rear, and I’m already dancing to a list of YouTube’s related videos. Good luck!