Friday, April 13, 2018

[Statistics] Last 4 stats redux - which stats did I update just now that all recent maintenance didn't do?

This is an addendum to my other "last 4 stats" article.  We had a query that had a bad plan, either due to bad statistics or sniffing or whatnot.  Normally rerunning a stats update would fix it, but not today.

Here's our normal stats maint (separate from index maint).

EXECUTE dba_utils.dbo.IndexOptimize
@Databases = 'eif_cde',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'  

The last time I looked, Ola's code uses the built-in rule for stats maintenance to determine if it should be updated, same as sp_updatestats, I ran a full update (OnlyModifiedStatistics = 'N').  But I was unsure what I had updated that hadn't been before.  Fortunately, due to some other code I had (https://thebakingdba.blogspot.com/2016/08/statistics-get-last-4-stat-update-time.html), I was able to get the last 4 stats updates.  So, let's figure out which ones were JUST updated, and hadn't been since our last update window at 11pm.  Then we get the columns involved in that table, in hopes that we can look at our "hung" query and see that one of the fields in the WHERE clause hadn't been updated.  That gives us an idea where to tweak, or possibly just to force a rebuild next time.

Step 1: run the code from the previous blog post, making sure it doesn't drop the table when done.
Step 2: run the below code, substituting your own windows.  In my case, I wanted to see which newly updated stats may have fixed the issue.

--and now to return which particular stats hadn't been updated.
SELECT stats.the_schema_name,
       stats.table_name,
       stats.stat_name,
       stats.updated,
--       stats.[inserts since last update],
--       stats.[deletes since last update],
   columns.name  FROM #stats_info2 stats
INNER JOIN sys.STATS ss ON ss.NAME = STATS.stat_name
INNER JOIN sys.stats_columns sstatc ON ss.object_id = sstatc.object_id AND sstatc.stats_id = ss.stats_id
INNER JOIN sys.columns ON columns.column_id = sstatc.column_id AND columns.object_id = ss.object_id
WHERE stats.updated >='20180413 14:00'
AND NOT EXISTS
( SELECT 1 FROM #stats_info2 stats2 WHERE
stats2.updated < '20180413 14:00' --AND stats2.UPDATEd >'20180412 22:59:00' but gives the same results
AND STATS.the_schema_name = stats2.the_schema_name
AND stats.table_name =stats2.table_name
AND stats.stat_name = stats2.stat_name
)
ORDER BY the_schema_name, table_name, stat_name