8 Comments

1. James L says:

   December 15, 2011 at 8:55 am

   Thanks for this…using it right now!

   Reply to this comment
2. praful says:

   September 6, 2011 at 1:52 am

   my stored proecedure run 4 min off peak time and 30 mins in peak time, i am not able to find the cause for 30 mins duration.Please tell me the various ways to find the bottleneck cause solution.
   we has transaction replication setup on the same server.

   Reply to this comment
   • Derek Dieter says:

     September 16, 2011 at 1:02 pm

     Hi Praful,

     What sounds like is happening, is during peak time the data returned for the query is not in memory and needs to be retrieved from disk. It definitely sounds like you are missing indexes or possibly using table variables. You should get the execution plan for the query and see if there are any indexes missing.

     Derek

     Reply to this comment
3. Curt Mader says:

   February 11, 2011 at 2:10 pm

   Why do the these queries that are supposed to show indexes that the system feels are needed often contain indexes that already exist?

   Reply to this comment
   • Derek Dieter says:

     February 17, 2011 at 1:50 pm

     Hi Curt,

     This is because a lot of times, the indexes are suggested in order to avoid a “bookmark lookup”. In other words, the index used to find the fields does exist, however it needs to go back to the clustered index (or heap) in order to get the required columns. The query optimizer does not like this scenario, so it will often suggest new indexes that contain “include” columns. These suggestions can be misleading. The other problem with the suggestions is that it does not take into account the fact that you may need to change your clustered index.

     With all that said, the index suggestions are still very useful and something I find myself considering a lot. Much better than the suggestions in SQL 2000..

     Reply to this comment
     • Curt Mader says:

       February 20, 2011 at 10:57 am

       Thanks Derek!

       Reply to this comment
4. Joseph Mills says:

   December 14, 2010 at 11:49 am

   Thanks for the script. Makes using the DMVs much faster.

   Reply to this comment
5. Troubleshooting SQL Server Slowdowns says:

   June 20, 2009 at 12:43 am

   [...] CPU – (Historically Medium/High) For a CPU that has historically been high, the most common issue is a lack of indexes. This is an easy fix with the new DMVs introduced in SQL Server 2005. Usually adding indexes for the top 5 offending queries will resolve this issue. Follow this to Find Missing Indexes. [...]

   Reply to this comment