October 7, 2011 at 9:49 am
A bit of an odd question, but looking for methods of finding inefficient indexes. As a basic example
I have a query that in its original state ran with 66 logical reads, clustered index seek and a profiler duration of 205 (cached) returning 1 record. So nothing that screams "FIX ME"!! With the addition of a non clustered index I got it down to 5 logical reads, non cluster index seek (could be a touch better, but doing a SELECT * - its only a test) and a (cached) duration of 9.
The only reason that I looked at this, was because I am a little bored and its a section of the DB that I already had select statements for. Im not looking for specific advise about this table and query, but more of a general approach on finding these little inefficiencies.... I have been doing a fair bit of looking on here and google, but everything I find is about the blatantly obvious.
October 7, 2011 at 1:44 pm
Have a look at the sys.dm_db_missing_index_details (http://msdn.microsoft.com/en-us/library/ms345434.aspx)view.
also here is a blog post with some handy scripts using the dmv
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply