0

I am using Ola's scripts for stats updates and for one particular index I want to do full scans when updating stats (to solve for the Ascending Key problem in conjunction with trace flag 2389). We have thousands of identical databases (this is how our vendor software works) and I'd like to make sure that all stats for this index are in fact doing full scans, not sampling. How do I go about running a query to check all DBs?

I can use DBCC SHOW_STATISTICS(), but that returns three result sets -- is it even possible to put just the first result set into a table and ignore the other two? Is there a better option?

2

You can see how many rows were sampled (among other things) by using the sys.dm_db_stats_properties DMF:

SELECT object_name(s.object_id), s.name, 
       sp.last_updated, sp.rows, sp.rows_sampled, 
       sp.steps, sp.unfiltered_rows, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties (s.object_id, s.stats_id) sp
WHERE s.object_id = object_id('TableName')
AND s.name = 'IndexName';

You can add some math to do the comparison for rows_sampled vs rows. Given that there will be data movement in the table after you update stats, I wouldn't expect this to be 100%, but you can assume that if a full scan was done, you'll see at least 80% of the current table size:

SELECT object_name(s.object_id), s.name, 
       sp.last_updated, sp.rows, sp.rows_sampled, 
       --
       sample_percent = (100*sp.rows_sampled/sp.rows),
       --
       sp.steps, sp.unfiltered_rows, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties (s.object_id, s.stats_id) sp
WHERE s.object_id = object_id('TableName')
AND s.name = 'IndexName';

Now, you just have to use some tool to run it against your thousands of databases. I suspect that if you're managing thousands of databases, you already have an established way to do this. As a PSA, I'll mention that sp_MSforeachdb sometimes misses databases, so consider avoiding that stored procedure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.