Dashboard "Missing Index" Report and Resetting Indexes
in [Programming SQL]
|
Prev: Database is in Transistion. There is already an open datareader as
Next: Dashboard "Missing Index" Report and Resetting Indexes
From: sloan on 22 Jul 2008 16:35 Is there a DBCC SQLPERF (or other syntax) command to "reset" the indexes that show up in the "Missing Index" report in the Dashboard reports? ... Thanks. Sql Server 2005 (Standard)
From: Andrew J. Kelly on 22 Jul 2008 20:40 No but the information for each of these DMV's in which the report is based on are all memory resident. So a restart will clear them out. I believe a DBCC DROPCLEANBUFFERS might as well but that also removes any data and indexes from the cache. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "sloan" <sloan(a)ipass.net> wrote in message news:OSNH6pD7IHA.4468(a)TK2MSFTNGP02.phx.gbl... > Is there a > DBCC SQLPERF > > (or other syntax) > > command to "reset" the indexes that show up in the "Missing Index" report > in > the Dashboard reports? > > > > .. > > Thanks. > > > Sql Server 2005 (Standard) > > > > >
From: TheSQLGuru on 23 Jul 2008 17:24 Andrew, do you have a reference about the dropcleanbuffers flushing the missing indexes stuff? Can't find anything along those lines and it sure would be a good piece of information to have verified! -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message news:O2ZCFzF7IHA.5164(a)TK2MSFTNGP02.phx.gbl... > No but the information for each of these DMV's in which the report is > based on are all memory resident. So a restart will clear them out. I > believe a DBCC DROPCLEANBUFFERS might as well but that also removes any > data and indexes from the cache. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "sloan" <sloan(a)ipass.net> wrote in message > news:OSNH6pD7IHA.4468(a)TK2MSFTNGP02.phx.gbl... >> Is there a >> DBCC SQLPERF >> >> (or other syntax) >> >> command to "reset" the indexes that show up in the "Missing Index" report >> in >> the Dashboard reports? >> >> >> >> .. >> >> Thanks. >> >> >> Sql Server 2005 (Standard) >> >> >> >> >> >
From: Andrew J. Kelly on 23 Jul 2008 20:04 Here is a section from BOL that explains how the stats are maintained in memory. Essentially if the underlying object that the stats are for goes out of cache due to pressure or what ever the stats are purged as well. So although I have never tested this it makes sense that when you clear all the buffers from the cache with the DBCC it will wipe out all the stats for these type of DMV's as well. -------------------- How the Counters Are Reset The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats. The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available. Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. Other DDL operations against the index may cause the value of the statistics to be reset to zero. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message news:GKSdnRPTmZvfPBrVnZ2dnUVZ_o3inZ2d(a)earthlink.com... > Andrew, do you have a reference about the dropcleanbuffers flushing the > missing indexes stuff? Can't find anything along those lines and it sure > would be a good piece of information to have verified! > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message > news:O2ZCFzF7IHA.5164(a)TK2MSFTNGP02.phx.gbl... >> No but the information for each of these DMV's in which the report is >> based on are all memory resident. So a restart will clear them out. I >> believe a DBCC DROPCLEANBUFFERS might as well but that also removes any >> data and indexes from the cache. >> >> -- >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> >> "sloan" <sloan(a)ipass.net> wrote in message >> news:OSNH6pD7IHA.4468(a)TK2MSFTNGP02.phx.gbl... >>> Is there a >>> DBCC SQLPERF >>> >>> (or other syntax) >>> >>> command to "reset" the indexes that show up in the "Missing Index" >>> report in >>> the Dashboard reports? >>> >>> >>> >>> .. >>> >>> Thanks. >>> >>> >>> Sql Server 2005 (Standard) >>> >>> >>> >>> >>> >> > >
From: Razvan Socol on 24 Jul 2008 02:49
Andrew J. Kelly wrote: > [...] I believe a DBCC DROPCLEANBUFFERS might as well [...] Hello, Andrew I have tested (on RC0) and I see that a DBCC DROPCLEANBUFFERS does not clear the information returned by the sys.dm_db_missing_index_* DMV-s. -- Razvan Socol SQL Server MVP |