• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

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
0
anushahanna
Asked:
anushahanna
  • 5
  • 4
  • 2
3 Solutions
 
tigin44Commented:
you can use Performance Data Collection (Data Collector) feature of sql server 2008. That feature is designed for such cases...
0
 
anushahannaAuthor Commented:
Apart from Data Collector, are the DMV's supposed to refresh themselves everyday, by default?
0
 
tigin44Commented:
No. The dmvs are tend to store data since the start of sql server.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
anushahannaAuthor Commented:
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.
0
 
tigin44Commented:
do you have any job, or operation i.e. backup that changes database operation mode? For example takes the database to single mode... backups it then restores it to normal mode... This kind of operations should reset the dmvs...
0
 
anushahannaAuthor Commented:
The DB is being restored everyday from another location. Could that be the reason?
0
 
tigin44Commented:
yeap that was the reason... after restore the services restarted and that causes the dmvs reset.
0
 
Mark WillsTopic AdvisorCommented:
>> The DB is being restored everyday from another location. Could that be the reason?

Yes, absolutely. There are some DMV's that only start building data based on actual usage sys.dm_db_index_usage_stats is one of those. Really, that particular DMV is more in tune with when the query plans are executed...

Restoring a database initialises any of the currrent usage. In many ways, you should regard it like a new database and update stats etc, but is not always possible to do so (so most people don't think about restored databases too much). There are also some indexes that live within the database as well and should be preserved.

There is an Article, a bit similar in nature to what you are talking about : http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Making-Sense-of-Index-DMV's-in-SQL-Server.html

0
 
anushahannaAuthor Commented:
Thanks Experts.

Mark,Thanks for the useful article on Usage Vs Operational. Your exercises helped to understand it more clearly. Good job.
0
 
anushahannaAuthor Commented:
would you say that all database specific DMVs would have been reset for this database, on a daily basis, when it was restored?
0
 
Mark WillsTopic AdvisorCommented:
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 :)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now