Dashboard "Missing Index" Report and Resetting Indexes

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" wrote in message

> 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" wrote in message
>
>> 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)
>>
>>
>>
>>
>>
>
 >> Stay informed about: Dashboard ""Missing Index"" Report and Resetting Indexes 

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" wrote in message

> 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" wrote in message
>
>> 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" wrote in message
>>
>>> 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)
>>>
>>>
>>>
>>>
>>>
>>
>
>
 >> Stay informed about: Dashboard ""Missing Index"" Report and Resetting Indexes 

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
 >> Stay informed about: Dashboard ""Missing Index"" Report and Resetting Indexes 

I will have to do some testing myself to see where that stands. But in this
case I can see this particular DMV not falling into that category. My theory
is that since this dmv is reporting on stuff that isn't there in the first
place it would not be affected by the clearing of the cache. What I would
assume would be affected are dmv's such as the
sys.dm_db_index_operational_stats and usage_stats.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Razvan Socol" wrote in message

> 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
 >> Stay informed about: Dashboard ""Missing Index"" Report and Resetting Indexes 

OK I just ran some tests on SQL 2005 and it appears DBCC DROPCLEANBUFFERS
does NOT clear out the index related DMV's as I suggested it might. This
must mean that there is more to it than just the data and indexes being in
the buffer cache. So the only way I know of to reset these manually is to
restart the instance.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Andrew J. Kelly" wrote in message

>I will have to do some testing myself to see where that stands. But in this
>case I can see this particular DMV not falling into that category. My
>theory is that since this dmv is reporting on stuff that isn't there in the
>first place it would not be affected by the clearing of the cache. What I
>would assume would be affected are dmv's such as the
>sys.dm_db_index_operational_stats and usage_stats.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "Razvan Socol" wrote in message
>
>> 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
>
 >> Stay informed about: Dashboard ""Missing Index"" Report and Resetting Indexes 

:<

I'm not :< you, I appreciate the investigation and concrete information.

I'm :< that's the only way to do it.

But thanks for looking. I was off a few days, and now am back at it.

...




"Andrew J. Kelly" wrote in message

> OK I just ran some tests on SQL 2005 and it appears DBCC DROPCLEANBUFFERS
> does NOT clear out the index related DMV's as I suggested it might. This
> must mean that there is more to it than just the data and indexes being in
> the buffer cache. So the only way I know of to reset these manually is to
> restart the instance.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "Andrew J. Kelly" wrote in message
>
>>I will have to do some testing myself to see where that stands. But in
>>this case I can see this particular DMV not falling into that category. My
>>theory is that since this dmv is reporting on stuff that isn't there in
>>the first place it would not be affected by the clearing of the cache.
>>What I would assume would be affected are dmv's such as the
>>sys.dm_db_index_operational_stats and usage_stats.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>>
>> "Razvan Socol" wrote in message
>>
>>> 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
>>
>
 >> Stay informed about: Dashboard ""Missing Index"" Report and Resetting Indexes 

I realize this is late, but in case anyone (like me) finds this thread looking for a solution, there is one thing that works, at least for some cases:

Recreate indexes using DROP_EXISTING=ON, and their sys.dm_db_index_usage_stats data gets zeroed out. You have to bear the cost of an index rebuild, but maybe that's easier than restarting SQL...certainly is for me.

> On Tuesday, July 22, 2008 4:35 PM sloan wrote:

> 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)


>> On Tuesday, July 22, 2008 8:40 PM Andrew J. Kelly wrote:

>> 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" wrote in message
>>


>>> On Wednesday, July 23, 2008 5:24 PM TheSQLGuru wrote:

>>> 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" wrote in message
>>>


>>>> On Wednesday, July 23, 2008 8:04 PM Andrew J. Kelly wrote:

>>>> 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" wrote in message
>>>>


>>>>> On Thursday, July 24, 2008 2:49 AM Razvan Socol wrote:

>>>>> Andrew J. Kelly wrote:
>>>>>
>>>>> 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


>>>>>> On Thursday, July 24, 2008 10:04 AM Andrew J. Kelly wrote:

>>>>>> I will have to do some testing myself to see where that stands. But in this
>>>>>> case I can see this particular DMV not falling into that category. My theory
>>>>>> is that since this dmv is reporting on stuff that isn't there in the first
>>>>>> place it would not be affected by the clearing of the cache. What I would
>>>>>> assume would be affected are dmv's such as the
>>>>>> sys.dm_db_index_operational_stats and usage_stats.
>>>>>>
>>>>>> --
>>>>>> Andrew J. Kelly SQL MVP
>>>>>> Solid Quality Mentors
>>>>>>
>>>>>>
>>>>>> "Razvan Socol" wrote in message
>>>>>>


>>>>>>> On Thursday, July 24, 2008 3:39 PM Andrew J. Kelly wrote:

>>>>>>> OK I just ran some tests on SQL 2005 and it appears DBCC DROPCLEANBUFFERS
>>>>>>> does NOT clear out the index related DMV's as I suggested it might. This
>>>>>>> must mean that there is more to it than just the data and indexes being in
>>>>>>> the buffer cache. So the only way I know of to reset these manually is to
>>>>>>> restart the instance.
>>>>>>>
>>>>>>> --
>>>>>>> Andrew J. Kelly SQL MVP
>>>>>>> Solid Quality Mentors
>>>>>>>
>>>>>>>
>>>>>>> "Andrew J. Kelly" wrote in message
>>>>>>>


>>>>>>>> On Monday, July 28, 2008 11:48 AM sloan wrote:

>>>>>>>> I am not :< you, I appreciate the investigation and concrete information.
>>>>>>>>
>>>>>>>> I am :< that is the only way to do it.
>>>>>>>>
>>>>>>>> But thanks for looking. I was off a few days, and now am back at it.
>>>>>>>>
>>>>>>>> ..


>>>>>>>> Submitted via EggHeadCafe
>>>>>>>> Excel JSON Storing Data
>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/233e58b3-72f4-4220-8d45-37...44e795e
 >> Stay informed about: Dashboard ""Missing Index"" Report and Resetting Indexes