Odd Results from sys.dm_db_index_usage_stats

Question

Odd Results from sys.dm_db_index_usage_stats

Grizzly Bear

Ten Centuries

Points: 1192
More actions
May 8, 2012 at 8:04 am

#256089

When I ran select * from sys.dm_db_index_usage_stats
on a server which contains about 50 Databases, the database_id column value all show up as 6.:crazy:
What could be the reason ?
Any pointers to try and dig into this problem will be appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Only the indexes in database 6 have been used since the last time SQL started? All the other databases have autoclose on and hence their entries got flushed out?

That DBV only shows indexes that have been used in some way since the last time the database was started up.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grizzly Bear Ten Centuries Points: 1192 More actions · Answer 2

Good Point Gila.

Now if I detach and reattach the DB -- do you think the rows -- in the DMV/DMF sys.dm_db_index_usage_stats will vanish ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yes. The detach closes the database and all rows for that DB from the index usage DMV (and the index operational stats and missing index DMVs) will be cleared.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grizzly Bear Ten Centuries Points: 1192 More actions · Answer 4

Actually they do vanish

This is what BOL says

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

Thank you -- now that answers quite a bit.

Now to find out when were they last attached/detached from the System Engineers.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Grizzly Bear (5/8/2012)
Actually they do vanish

Which is what I said....

Now to find out when were they last attached/detached from the System Engineers.

It's not just detached.

Restored, closed (via the autoclose setting), set offline or SQL restarted.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass