SQL Server performance – missing indexes

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

As you already know by now, indexes are very important for SQL server performance, and finding the best indexes can be hard and time consuming. There are some tools that can help you or make things even worse. Some of the tools you can use are included in the SQL server platform, like:

  • Database engine tuning advisor
  • Missing index DMV:s
  • Estimated / Actual Execution plan

They all have capabilities to give you suggestions of indexes, but they also have their issues. I will not describe these features in detail, but if you use these tools to implement all the suggested indexes you can even decrease the performance. They give you suggestions and you still have to test the performance before and after.

Tool

Benefit

Concern

Database Tuning   advisor

Give you good   suggestions

-Uses a lot of   resources

-Give you a lot of   similar indexes

Missing indexes

Give you good   suggestions based on execution plans from the cache

Give you a lot of   similar indexes

Gives you suggestion   of indexes that already exists because one of the columns need a different   sort order, but there is no suggestion of the sort order

Estimated or Actual   Execution plan

Give you the   suggestion of index of the query you are running right now

Doesn’t display all   the missing indexes for the query and you have to implement the index and   execute one more time to get the next suggestion.

The index suggestion   is not displayed for the actual statement that needs the index

 

There is a connect item for the missing index DMV, please vote for it at :

The problem with the estimated or actual execution plan is that you can only see one suggestion at the time and it’s not correlated with the statement.

 

In SQL server 2012 you can get all the missing indexes in the estimated or actual execution plan GUI, but not in SQL server 2008. BUT you can find all the missing indexes in the execution plan XML, just right click the exection plan and click the “Show Execution Plan XML” menu item, search the XML for MissingIndex and you’ll find all of them! It’s only a GUI issue, all the information is in the XML plan.

 

How can you use this information? Well, if you now how to write some XQueries you can actually list all the missing indexes with the SQL statement attached. With this list you can investigate your index based on the statement that needs it. You can actually query the cache for all execution plans that have missing index suggestions, but beware, it is a heavy query that can cause performance issues during the execution.  

The best solution is to pick the worst performing queries from the cache by using the sys.dm_exec_procedure_stats DMV and store the result in a temp table with an XML index.

Using XML in SQL server can be tricky unless you are very skilled in XQuery and I would like to help you a little bit to get started. In the following code, I have removed some interesting statements for other interesting information in the XML plan.

/*

SELECT  top 10 OBJECT_NAME([ps].[object_id], [ps].[database_id]), DB_NAME(database_id), *
FROM    sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
order by ps.[last_logical_reads] desc

*/

CREATE PROCEDURE DBO.[spCheckSP](@dbName sysname, @schemaName sysname, @procedureName sysname)
AS
  -- +----------------------------------------------------------------------------------------------------------------
  -- ! O b j e c t                                            [ADMIN].[spCheckSP}
  -- ! R e t u r n s                                          INT
  -- ! P a r a m e t e r s           Name, DataType, Description
  -- + =========================================================================================
  -- !                                                                                             @dbName                                           sysname
  -- !                                                                                             @schemaName                          sysname
  -- !                                                                                             @procedureName                       sysname
  -- + ---------------------------------------------------------------------------------------------------------------
  -- ! O b j e c t i v e    
  -- + ---------------------------------------------------------------------------------------------------------------
  -- !   S A M P L E
  -- !                                                                     EXEC [ADMIN].[spCheckSP] 'AdventureWorks2008R2', 'Sales', 'uspGetBillOfMaterials'
  -- + ---------------------------------------------------------------------------------------------------------------
  -- ! H i s t o r y                                                       Date                    Who         What
  -- +========== ===== =========================================================================
  -- !                                                                     2011-01-26  HAWI            Initial version ...
  -- + ---------------------------------------------------------------------------------------------------------------

SET NOCOUNT ON;
 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
            DECLARE @objectName SYSNAME = @dbName + '.' + @schemaName + '.' + @procedureName
            SELECT ProcedureName = @objectName;
 

           SELECT
                         query_plan = CONVERT(XML,qp.query_plan),
                         ps.*
            INTO #tmpPlan
            FROM sys.dm_exec_procedure_stats ps
            CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
            WHERE OBJECT_ID = OBJECT_ID(@objectName)
            AND database_id = DB_ID(@dbName);
 

            SELECT * FROM [#tmpPlan];
 

 

            ----------------------------------------------------------------------------------------------------------------
            -- get missing index stats
            ----------------------------------------------------------------------------------------------------------------
            WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
            ,cteMissingIndexes
            AS (
                          SELECT tp.query_plan --, cp.usecounts
                          FROM #tmpPlan tp  
                          WHERE tp.query_plan.exist('//MissingIndex')=1
            )
            SELECT
                         databaseName             = stmt.value('(.//MissingIndex/@Database)[1]', 'sysname'),
                         [schemaName]             = stmt.value('(.//MissingIndex/@Schema)[1]', 'sysname'),
                         [tableName]                          = stmt.value('(.//MissingIndex/@Table)[1]', 'sysname'),
                         sql_text                                          = stmt.value('(@StatementText)[1]', 'VARCHAR(4000)'),
                         impact                                            = stmt.value('(.//MissingIndexGroup/@Impact)[1]', 'FLOAT'),
                         stmt.query('for $group in .//ColumnGroup
                                       for $column in $group/Column
                                       where $group/@Usage="EQUALITY"
                                       return string($column/@Name)
                                       ').value('.', 'varchar(max)') AS equality_columns
                          ,stmt.query('for $group in .//ColumnGroup
                                       for $column in $group/Column
                                       where $group/@Usage="INEQUALITY"
                                       return string($column/@Name)
                                       ').value('.', 'varchar(max)') AS inequality_columns
                          ,stmt.query('for $group in .//ColumnGroup
                                       for $column in $group/Column
                                       where $group/@Usage="INCLUDE"
                                       return string($column/@Name)
                                       ').value('.', 'varchar(max)') AS include_columns
                          ,pmi.query_plan
            FROM cteMissingIndexes pmi
                          CROSS APPLY pmi.query_plan.nodes('//StmtSimple') AS qp(stmt)
            WHERE stmt.exist('.//MissingIndex/ColumnGroup[@Usage="EQUALITY"]')=1 OR stmt.exist('.//MissingIndex/ColumnGroup[@Usage="INEQUALITY"]')=1
            UNION ALL
            SELECT
                         databaseName             = stmt.value('(.//MissingIndex/@Database)[1]', 'sysname'),
                         [schemaName]             = stmt.value('(.//MissingIndex/@Schema)[1]', 'sysname'),
                         [tableName]                          = stmt.value('(.//MissingIndex/@Table)[1]', 'sysname'),
                         sql_text                                          = stmt.value('(@StatementText)[1]', 'VARCHAR(4000)'),
                         impact                                            = stmt.value('(.//MissingIndexGroup/@Impact)[1]', 'FLOAT'),
                         stmt.query('for $group in .//ColumnGroup
                                       for $column in $group/Column
                                       where $group/@Usage="EQUALITY"
                                       return string($column/@Name)
                                       ').value('.', 'varchar(max)') AS equality_columns
                          ,stmt.query('for $group in .//ColumnGroup
                                       for $column in $group/Column
                                       where $group/@Usage="INEQUALITY"
                                       return string($column/@Name)
                                       ').value('.', 'varchar(max)') AS inequality_columns
                          ,stmt.query('for $group in .//ColumnGroup
                                       for $column in $group/Column
                                       where $group/@Usage="INCLUDE"
                                       return string($column/@Name)
                                       ').value('.', 'varchar(max)') AS include_columns
                          ,pmi.query_plan
            FROM cteMissingIndexes pmi
                          CROSS APPLY pmi.query_plan.nodes('//StmtCond') AS qp(stmt)
            WHERE stmt.exist('.//MissingIndex/ColumnGroup[@Usage="EQUALITY"]')=1 OR stmt.exist('.//MissingIndex/ColumnGroup[@Usage="INEQUALITY"]')=1;
 

As you can see, you need to query two different nodes (StmtSimple and StmtCond) to get all the missing indexes. The result might look something like this when you pass the name of a stored procedure into this procedure I gave you.

In this case, I have to statements that need an index each and I can investigate the query to see if I need to change something in the query or if I should create the index. The Impact is an estimate on how many percent improvement the index would give you, but it should be handled as a guess based and it is based on statistics. (read my other blog posts about statistics to understand why this not to be trusted.)

If you give me positive feedback (on twitter or as a comment) of the blog post, I’ll consider to post some other blog posts with other interesting parts of the XML plan and queries to extract it.

/Håkan Winther
twitter: @h_winther