April 29, 2008 at 8:35 am
Is there a query of system views that will give me the last time cache was cleared?
I am interested in knowing this so I can assess the value of index-stats results I have collected based on queries of Dynamic Management Views (DMVs).
For example, I have DMV queries that give me all indexes that have not been used since the last time cache was cleared. This is most often the time of the last instance restart, but I do not want to rely on that assumption alone.
I would like to be able to tell when was the last time the cache was purged, instance restart or not.
Any ideas?
__________________________________________________________________________________
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]
April 29, 2008 at 9:17 am
Do you every clear cache? I wouldn't think this would ever happen except at restart.
Not sure there's a DMV tracking restart, but you can read the log for the earliest entry, which is the time the instance started.
Or are you looking for something else? Are you trying to determine when all cache was cleared or a particular plan disappeared?
April 29, 2008 at 10:06 am
I think you get a log message whenever a cache is flushed, along the lines of :
SQL Server has encountered 12 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
I know for certain that it occurs when you use dbcc freeproccache, and if you detach a database.
April 29, 2008 at 2:00 pm
you should not use the raw data in the dmv's for index analysis, always port the data to a table at regular intervals and analyse from there. I had some discussions with microsoft about the dmv's as I found not all index data was actually always held in the dmv's - there wasn't a real definitive answer and I wasn't able to reproduce in a test system what i found on a prod system - you can read what work I've done so far here :- http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/02/18/analysing-indexes-summary.aspx
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 5, 2008 at 8:08 am
Thank you all for your responses.
Steve, what I am looking for is a way to tell how long the data stored in DMVs has been kept for so I can make informed decisions on, say, dropping an index that has not been used for a month (for example).
Colin, thanks for the info and link, I'll take a look.
__________________________________________________________________________________
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]
May 5, 2008 at 8:21 am
colin Leversuch-Roberts (4/29/2008)
you should not use the raw data in the dmv's for index analysis, always port the data to a table at regular intervals and analyse from there. I had some discussions with microsoft about the dmv's as I found not all index data was actually always held in the dmv's - there wasn't a real definitive answer and I wasn't able to reproduce in a test system what i found on a prod system - you can read what work I've done so far here :- http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/02/18/analysing-indexes-summary.aspx
Yes, I know, for example, that indexes missing on JOIN columns are not picked up by the existing DMVs - only columns in a WHERE clause are "visible".
Perhaps this is something Microsoft needs to work on in a future release...
__________________________________________________________________________________
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]
May 5, 2008 at 8:56 am
Marios,
if it helps, the following actions flush the proccache:
http://blogs.msdn.com/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx
hopefully MS will work on only flushing the cache of objects for the database the action is upon. If you think they should, please vote here:
https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=341803
---------------------------------------------------------------------
May 5, 2008 at 10:59 am
I've remarked a number of times that whilst the amount of information available on the various ms blogs is fantastic it doesn't always link up team to team, sometimes the posts conflict and mainly there's just no centralised method to put all the info together. The dmv's are great BUT there are a few issues concerning accuracy or data and how and when it updates/maintains itself.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 5, 2008 at 12:27 pm
colin Leversuch-Roberts (5/5/2008)
I've remarked a number of times that whilst the amount of information available on the various ms blogs is fantastic it doesn't always link up team to team, sometimes the posts conflict and mainly there's just no centralised method to put all the info together. The dmv's are great BUT there are a few issues concerning accuracy or data and how and when it updates/maintains itself.
That's good to know.
Certainly not something I was aware of...
I thought (assumed) the DMVs were rock-solid, and that the only drawback was that the data was kept strictly in memory.
__________________________________________________________________________________
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]
May 5, 2008 at 1:11 pm
colin Leversuch-Roberts (5/5/2008)
...The dmv's are great BUT there are a few issues concerning accuracy or data and how and when it updates/maintains itself.
Is this discussed in the link you sent out earlier?
(http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/02/18/analysing-indexes-summary.aspx)
Haven't had a chance to read it carefully yet.
Any other links discussing potential pitfalls of index-analysis DMVs?
__________________________________________________________________________________
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]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply