February 8, 2008 at 6:57 am
Is there a way in SQL 2005 to combine the very useful missing index functions
sys.dm_db_missing_index_group_stats migs
sys.dm_db_missing_index_groups mig,
sys.dm_db_missing_index_details mid
and this func
sys.dm_exec_sql_text (sql_handle)
and maybe
sys.dm_db_index_usage_stats
to somehow log the actual query that is being executed at the time the statistics for the missing index functions get updated so that I can see which queries are those that are causing lookups, scans and count towards missing index recommendations.
I already am using the auto_index recommendation system that polls the system every 30 mins to create recommendations for new indexes or to drop existing indexes which has proven to be very useful. But what would be even more useful is if I could actually see those queries that cause new recommendations.
I don't know if its possible but I would like to find out.
Thanks in advance for any help I may receive.
February 8, 2008 at 7:26 am
You may want to look at the thread: http://www.sqlservercentral.com/Forums/Topic438574-360-1.aspx
Although it does not give you a way to find out the queries that the suggested missing indexes are based on, it is right that these queries are usually the ones that use up the most resources, and have the largest number of reads and writes.
Regards,
Andras
February 8, 2008 at 9:12 am
Yes thats a good link. Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply