June 10, 2011 at 9:01 am
In BOL - http://msdn.microsoft.com/en-us/library/ms188755(v=SQL.100).aspx - it is stated that:
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.
Can the data in sys.dm_db_index_usage_stats also be purged when there is memory pressure?
I haven't been able to find a definitive answer on this anywhere.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 10, 2011 at 9:50 am
To my knowledge it can't be, but these tables are all very small. If you have that much memory pressure, you should be looking at your processes, not attempting to blow out data from dynamic management views.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2011 at 11:35 am
I'm asking because I'm trying to gauge whether it makes sense to be polling this view at regular intervals (say once a day) and storing the results in a table for analysis;
or whether a one-time query of this view is enough to draw conclusions on index statistics (assuming last instance restart was sufficiently long ago).
If it is possible for data in this view to get purged in between instance restarts, then storing "snapshots" at frequent intervals is a must.
If not, storing snapshots is still a good idea, though perhaps not as critical.
BTW, I "love" the data in the DMVs, wouldn't dream of ever blowing it away! 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 10, 2011 at 12:09 pm
Ah, well, since this is one of the DMOs that is only reset when the instance is restarted, I suspect it's not that vital.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply