Sql-server – Why are Op Stats all 0’s and yet Usage Stats show 29 seeks

You said you rebuilt the index and now both queries are performing index seeks, as desired.

This is most likely due to the index rebuild operation recreating the statistics for the affected columns. Now that that query optimizer has updated statistics, it knows it would be more efficient to seek the index.

You may want to ensure you have an index rebuild job that runs on a schedule - I'd recommend looking at Ola Hallengren's solution for that.

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.