Missing Indexes Script v2

Comments: 8 Comments
Published on: May 15, 2014

missing_idx

Frequently we hear the analogy that <insert item here> is like opinions, everybody has one and not all of them are good (some may stink).

Well, this may just be another one of those <items>.  Whether it stinks or not may depend on your mileage.

I had shared a similar script back in January 2012 and wanted to share something a little more current.  As is the case for many DB professionals, I am always tweaking (not twerking) and refining the script to try and make it more robust and a little more accurate.

This version does a couple of things differently than the previous version.  For one, this is a single database at a time (the prior version looped through all of the databases with a less refined query).  Another significant difference is that this query is designed to try and pull information from multiple places about the missing indexes and execution statistics.  I felt this could prove more advantageous and useful than to just pull the information from one place.

Here is the current working script.

The following script gets altered on display.  n.VALUE is displayed but in the code it is actually n.value.  The code display is wrong but it is correct in the code as presented in the editor.  If copying from this page, please change the U-cased “VALUE” in the XML segment to “value” so it will work.  A download of the script has been added at the end.

[codesyntax lang=”tsql”]

[/codesyntax]

As has been the case in the past, this is not the end query.  I have a backlog of updates I want to make to the query and try to create something even more useful from it.

As a bit of fair caution, just because an index shows up as a recommended implementation from this script, it does not mean you should blanket implement it.  Also, like the previous script, the higher the impact value, the more probable the index will have a positive impact on your system.

In addition to the impact, I also look at the “Similar Queries” and “Similar Plans” numbers from the result set.  These values can sometimes lead to an understanding that their are other queries or other plans that could also benefit from the creation of the index.  Your mileage may vary.

Download script Missing_Index.

8 Comments - Leave a comment
  1. William Stevenson says:

    Get the following error with this script

    Msg 4121, Level 16, State 1, Line 13
    Cannot find either column “n” or the user-defined function or aggregate “n.VALUE”, or the name is ambiguous.

  2. Garry Bargsley says:

    Script fails for me when I run it. I tried on SQL 2005, 2008 and 2012. Error below.

    Msg 4121, Level 16, State 1, Line 11
    Cannot find either column “n” or the user-defined function or aggregate “n.VALUE”, or the name is ambiguous.

  3. Jason Brimhall says:

    It looks like the script plugin U-cased the word value in the XML when pasting it into the blog. I will fix that.

    n.VALUE should be n.value

  4. Robert Klimes says:

    There a numerous case discrepancies that cause errors on a case sensitive server. The table aliases MII, CA, and SI as well as the columns SI.Impact and sub.NAME.

  5. Ken Klaft says:

    @Jason
    FYI: Your table aliasing breaks when used on a case-sensitive server: ca != CA

  6. JSWhite says:

    Excellent script, thank you!

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








Calendar
May 2014
M T W T F S S
« Apr   Jul »
 1234
567891011
12131415161718
19202122232425
262728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Saturday, November 29, 2014