?
Solved

Index DMV - hold interval

Posted on 2009-12-24
11
Medium Priority
?
324 Views
Last Modified: 2012-05-08
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
Comment
Question by:anushahanna
  • 5
  • 4
  • 2
11 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 26119127
you can use Performance Data Collection (Data Collector) feature of sql server 2008. That feature is designed for such cases...
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26119164
Apart from Data Collector, are the DMV's supposed to refresh themselves everyday, by default?
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26119232
No. The dmvs are tend to store data since the start of sql server.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 6

Author Comment

by:anushahanna
ID: 26119337
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
 
LVL 26

Accepted Solution

by:
tigin44 earned 1332 total points
ID: 26119385
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
 
LVL 6

Author Comment

by:anushahanna
ID: 26119718
The DB is being restored everyday from another location. Could that be the reason?
0
 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 1332 total points
ID: 26121783
yeap that was the reason... after restore the services restarted and that causes the dmvs reset.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 668 total points
ID: 26123355
>> 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
 
LVL 6

Author Comment

by:anushahanna
ID: 26131667
Thanks Experts.

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

Author Comment

by:anushahanna
ID: 26131670
would you say that all database specific DMVs would have been reset for this database, on a daily basis, when it was restored?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 26132770
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question