?
Solved

archive missing index info

Posted on 2009-12-17
14
Medium Priority
?
202 Views
Last Modified: 2012-05-08
The new DMV's
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_details
are helpful to see the missing indexes since the last restart.

Is there any idea/way to archive this, so we can see the missing index history for a long period of time?

thank
0
Comment
Question by:anushahanna
  • 7
  • 6
14 Comments
 
LVL 9

Accepted Solution

by:
Hwkranger earned 336 total points
ID: 26075567
Yes,

Create a job that archives that data to tables.  You can use a procedure or SSIS package -- and just schedule it.
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1664 total points
ID: 26077240
>> Is there any idea/way to archive this, so we can see the missing index history for a long period of time?

Yes.. Just follow this

1. Create 3 Archive tables ( one for each table mentioned above) along with one more additional column defaulted with getdate() to capture time information
2. Create INSERT statements to insert records from the above DMV's into your Archive tables.
3. Create these INSERT statements as a job.
4. Schedule it to run as per the frequency needs..
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26095864
Thanks for the idea. Can we add a trigger to a DMV insert?
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1664 total points
ID: 26095899
>> Can we add a trigger to a DMV insert?

No.. don't do that..
Because it would be inserting all sorts of records increasing the system load thus making your system suffer in performance.

A job would be good compared to that..
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26096101
Now, I would need an UPDATE also, right? (other than the insert) For example, the number of unique_compiles may have changed etc
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1664 total points
ID: 26096321
>> the number of unique_compiles may have changed etc

If you want to store unique entries only, then you might need to update the older values..
But I would prefer dumping records into that table along with getdate() column so that I might be able to find out the most commonly missed index over a period of time by finding the max no of times an index miss occured out of the records we have in our table..

I think I answered your question..
But I have a doubt on why are you going with this approach since that would help you to guide exactly on what you require either UPDATE or an INSERT itself..
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26096546
Thanks for pointing that out.

How often would you recommend the dump? In this approach, can the Estimate of benefit be derived?


0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1664 total points
ID: 26096635
Once per day..

* Do an analysis once per week and create the missing indexes..
* Continue this activity once your application is performing well.

Note: Make sure the indexes created are useful and not adding more load to the application..
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26097367
Thanks. that makes sense.

With the DMV, after joining the 3 tables, avg_user_impact was available.

after dumping it all together, now, we will have multiple PKs. Is there any query that can be used to efficiently analyze the data together, or would you analyze manually?

For example,
On Monday, table1.index1 has 10 UniqueCompiles, 200 UserSeeks
On Tuesday, table1.index1 has 2 PK entries and the second entry has 12 UniqueCompiles, 270 UserSeeks (2 new Compiles, and 70 new Seeks)
etc

What will be the method you use to deduce best indexes to implement with the archving method?

thanks
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1664 total points
ID: 26097521
This should give you the list of columns that needs to be considered for indexing

select COUNT(statement) over ( partition by database_id, object_id, statement) cnt, database_id, object_id, statement, equality_columns, inequality_columns, included_columns
from sys.dm_db_missing_index_details
order by 1 desc

>> after dumping it all together, now, we will have multiple PKs.

Keep an identity column as PK by adding a new column to the table in addition to the datetime column mentioned earlier..
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26097633
The identity column is only for my satisfaction, right? -  no practical use to it in the analysis?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26097670
yes.. It wont be of much use for your analysis but that would help you get rid of the PK issue which you thought..
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26097849
Thanks a lot of your detailed help.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26097952
Welcome..
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question