Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Clearing Missing index usage Expand / Collapse
Author
Message
Posted Wednesday, May 19, 2010 2:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:37 AM
Points: 91, Visits: 213
Dear All,

Is there a way to clear the missing index usage views.
We worked during last period on index tuning, adding new indexes ,removing duplicate and unused ones.
I need to clear sys.dm_db_missing_index_group_stats ,
sys.dm_db_missing_index_groups and sys.dm_db_missing_index_details
without having to restart SQL server process and without affecting caching on server becuase it will be done on live enviroment.

Thanks in advance and looking forward for ur replies.

Nader Galal
Post #924105
Posted Wednesday, May 19, 2010 9:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 14,034, Visits: 28,406
From the Books Online:

The missing indexes feature is on by default. No controls are provided to turn the feature on or off, or to reset any of the tables returned when the dynamic management objects are queried. When SQL Server is restarted, all of the missing index information is dropped.

This feature can only be disabled if an instance of SQL Server is started by using the -x argument with the sqlservr command-prompt utility. For more information, see sqlservr Application.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #924404
Posted Wednesday, May 19, 2010 11:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 40,438, Visits: 36,894
I believe it is cleaned when the database closes, hence take the DB offline and bring it online and it should be cleared.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #924520
Posted Wednesday, May 19, 2010 11:18 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
To clear the data in those views, you need to offline and online the database at a minimum. Restarting SQL Server is an alternative. There is no other way to clear that information without doing one of those two things (restart or offline/online the database).



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #924526
Posted Wednesday, May 19, 2010 4:14 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, November 9, 2014 7:56 PM
Points: 450, Visits: 1,343
I agree with the others EXCEPT...

If you have created indexes that closely match those recommended in the DMVs SQL will clear the matching indexe information from the vews.

Leo
Striving to provide a better service.
Post #924728
Posted Thursday, May 20, 2010 12:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:37 AM
Points: 91, Visits: 213
Thanks for all replies.
Unfortunately this is a live enviroment so its not possible to take db offline and bring it back.
Post #924835
Posted Thursday, May 20, 2010 10:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
nadersam (5/20/2010)
Thanks for all replies.


You're welcome.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #925332
Posted Tuesday, February 25, 2014 1:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 5:36 AM
Points: 1, Visits: 3
I've managed to clear out that information by creating the indexes suggested. And by deleting those again and other indexes, new and different suggestions came up.
Post #1544795
Posted Tuesday, February 25, 2014 4:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 14,034, Visits: 28,406
Just remember that those are suggestions. Don't blindly implement them. Always test them and validate them.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1544827
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse