asked on

Index DMV - hold interval

I see that sys.dm_db_index_usage_stats and sys.dm_db_missing_index_details are reset every day in my SQL Server, without any server reboot or restart. Is this normal? Is there anyway to set it to keep the records till the next restart?

Thanks

tigin44

you can use Performance Data Collection (Data Collector) feature of sql server 2008. That feature is designed for such cases...

anushahanna

ASKER

Apart from Data Collector, are the DMV's supposed to refresh themselves everyday, by default?

tigin44

No. The dmvs are tend to store data since the start of sql server.

anushahanna

ASKER

mine, for sure, loses the info every day, at least in one database, and starts fresh. the last restart was last month.

I noticed this first yesterday evening,
sys.dm_db_index_usage_stats for a particular database was bringing only 2 valid records (Users Seeks/Scans/Lookups/Updates).
There were 10 more system indexes with system scans. dm_db_missing_index_details was empty for that database.

Then at a particular time yesterday, sys.dm_db_index_usage_stats started getting filled in, slowly, one by one.

Then, this morning, again, sys.dm_db_index_usage_stats has the same 12 records I saw yesterday morning. Seems like it almost
just reset itself. I looked at another database, and that does not have this issue, which has historical index usage from the system restart date.

any thoughts.

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anushahanna

ASKER

The DB is being restored everyday from another location. Could that be the reason?

SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anushahanna

ASKER

Thanks Experts.

Mark,Thanks for the useful article on Usage Vs Operational. Your exercises helped to understand it more clearly. Good job.

anushahanna

ASKER

would you say that all database specific DMVs would have been reset for this database, on a daily basis, when it was restored?

Mark Wills

No, not necessarily. Some of them are based on plans and all kinds of interesting things. You really have to understand those DMV's to make best use of them otherwise they will catch you out.

Some of them are not views - they are functions and replace some of the DBCC type commands. For example, sys.dm_db_index_physical_stats (is a function) replaces the DBCC SHOWCONTIG. Also, some of them are based on system/server information and some on the database.

As a rule of thumb, if you were to consider that a restored database is akin to a recovery, then you would most likely run some maintenance tasks - like updating statistics and such like - especially if your restored database is not performing well. Doing those tasks will also give you more accurate information from the DMV's

Sounds a bit wishy washy, but heck, there are a lot of DMV's out there :)