Missing Indexes

Comments: 11 Comments
Published on: January 12, 2012

SQL Server has means built into it to track possible missing indexes.  This used to be found through the use of the Index Tuning Wizard.  The process has improved over time (you can sort of see that from my April Fools post).

As luck would have it, I was recently asked to help fix a query that was somewhat related to the whole process.  You see, since SQL Server 2005, there are DMOs that help to track metadata related to column and index usage.  And if there a query is repeated enough that doesn’t have a good matching index, then the engine may think that a new index is needed.  This potential index information is recorded and becomes visible via the DMOs.

The query that I was asked to help fix was a dynamic query within a cursor that read information from the DMOs in order to generate some missing index information.  That particular query was failing for a couple of reasons, but on the same token it gave me an idea to modify and adapt the query to something more in line with what I might use.  After all, the queries that I used were in need of updating and this gets me started in that direction.

First, a little on why the query was failing.  A common problem with dynamic queries is the placement of quotes and having enough quotes in all required locations.  When you start nesting more levels into a dynamic query, the more confusing the quotes can get.  When running into something like this, I like to print the statement that I am trying to build dynamically.  If it doesn’t look right, then adjust the quotes until it looks right.

The second reason it was failing was a simple oversight.  Whether building dynamic queries or just using variables in your code, make sure you use properly sized variables.  In this case, the dynamic query variable was substantially inadequate.  The use of a print statement also helps to catch these types of errors rather quickly.

There were also a few things that would cause me to not use the original query in any environment.  The first problem is that the script contains a column which is the create statement for each proposed index.  In this create statement, all indexes were given the same name.  That would be a bit of a problem.

The next issue is my concern with the creation of indexes without ensuring that the index is going to provide greater benefit than cost.  Better stated is that the creation of these indexes just because the script spewed them out is no better than to create all of the indexes proposed by the Database Engine Tuning Advisor.  For this, I added a cautionary statement next to every index create statement.

So with these tweaks, as well as other less significant tweaks, here is the query.

[codesyntax lang="tsql"]

[/codesyntax]

As I post this message, as I tend to do, I am looking for ways to improve upon the query and make it better.  This script should only be used with caution.  It is to provide an insight into potential missing indexes in each database.  A score is assigned to each potential index.  It is with the highest score indexes, that I typically begin analysis to improve performance.  I typically start from a query and execution plan to performance tune.  There are times when an alternative starting point is necessary.  This script is a tool for those times.  Please head the warning that these should be created with extreme caution.

11 Comments - Leave a comment
  1. Neil says:

    A quick question about the Impact column. I just want to check that the higher the number, the better it would be to implement the index?

  2. David says:

    does this only apply to SQL 2005? I get an error on SQL 2008 R2:
    Msg 102, Level 15, State 1, Line 8
    Incorrect syntax near ‘.’.
    Msg 156, Level 15, State 1, Line 31
    Incorrect syntax near the keyword ‘ORDER’.

    • Jason Brimhall says:

      I created the script on 2008 R2. Are you using the script as is, or did you modify? I added brackets around object names when I saw that in testing and it resolved it for me. This might be something different, so I ask.

  3. david says:

    I found that the problem was related to the database being in compatibility mode 80. It works fine for compatibility mode 100.

  4. Neil says:

    I have worked an alternative without a cursor.

    DECLARE @STATEMENT NVARCHAR(MAX)

    SET @STATEMENT = N’USE [?]‘+ CHAR(13) +';’ +CHAR(13)
    + N’
    SELECT SO.name
    , ((CONVERT(Numeric(19,6), migs.user_seeks)+CONVERT(Numeric(19,6), migs.unique_compiles))
    *CONVERT(Numeric(19,6), migs.avg_total_user_cost)
    *CONVERT(Numeric(19,6), migs.avg_user_impact/100.0)) AS Impact
    ,”DO NOT AUTO CREATE THESE INDEXES – Creating these indexes could be as bad as blindly using DTA” as Note
    , ”CREATE NONCLUSTERED INDEX IDX_” + SO.name +”_” + STUFF (
    (SELECT ”_” + column_name FROM sys.dm_db_missing_index_columns(mid.index_handle) WHERE column_usage IN (”Equality”,”InEquality”) FOR XML PATH (””))
    , 1, 1, ””) + ” ON [?].” + schema_name(SO.schema_id) + ”.” + SO.name COLLATE DATABASE_DEFAULT + ” ( ” + IsNull(mid.equality_columns, ””) + CASE WHEN mid.inequality_columns IS NULL
    THEN ””
    ELSE CASE WHEN mid.equality_columns IS NULL
    THEN ””
    ELSE ”,” END + mid.inequality_columns END + ” ) ” + CASE WHEN mid.included_columns IS NULL
    THEN ””
    ELSE ”INCLUDE (” + mid.included_columns + ”)” END + ”;” AS CreateIndexStatement
    , mid.equality_columns
    , mid.inequality_columns
    , mid.included_columns
    FROM sys.dm_db_missing_index_group_stats AS migs
    INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
    AND mid.database_id = DB_ID()
    INNER JOIN sys.objects SO WITH (nolock)
    ON mid.OBJECT_ID = SO.OBJECT_ID
    WHERE (migs.group_handle IN
    (
    SELECT TOP (500) group_handle
    FROM sys.dm_db_missing_index_group_stats WITH (nolock)
    ORDER BY ((CONVERT(Numeric(19,6), migs.user_seeks)+CONVERT(Numeric(19,6), migs.unique_compiles))
    *CONVERT(Numeric(19,6), migs.avg_total_user_cost)
    *CONVERT(Numeric(19,6), migs.avg_user_impact/100.0)) DESC))
    AND OBJECTPROPERTY(SO.OBJECT_ID, ”isusertable”)=1
    ORDER BY 2 DESC , 3 DESC’

    EXEC sp_MSforeachdb @STATEMENT

  5. Jason Brimhall says:

    @Neil – thanks for the additional script. Be careful of sp_msForeachdb. It has limitations and will throw some errors depending on the script being employed. The looping mechanism in it is a little less refined than other methods.

    Here is an article on that.
    http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

  6. David says:

    Has anyone figured out how to get this to work in a database at compatability level 80 on a 2005/2008 SQL server?

  7. […] 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. […]

  8. […] 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. […]

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
January 2012
M T W T F S S
« Dec   Feb »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
Content
SQLHelp

SQLHelp


Welcome , today is Tuesday, September 30, 2014