Denny's SQL Secrets

When Should You Create New SQL Server Indexes?

Here's how to spot when new indexes are needed to improve performance.

• By Denny Cherry
• 04/11/2014

The most important performance-tuning technique available is having the needed indexes created on the tables to ensure that the SQL Server database engine is able to get the data that it needs to as quickly as possible.  Having proper indexes is even more important than having the fastest possible storage because even with several Fusion ioDrive cards in a SQL Server, having the proper indexes on the tables are still required.  The reason for this is because without the indexes there will simply be too much data for the database engine to scroll through for each query (which is run for the database engine to get through the data quickly).  Not only would these queries begin running slowly, but other queries would be blocking due to locking within the database engine.

New Queries
The first way that we know that we may need to add indexes to a SQL Server database is because new queries are being run against the database engine.  Often when an application is upgraded and new queries are being run against the database engine they don't have the indexes needed to perform well.  Every time there are major software upgrades performed, the system should be reviewed and index tuning should be performed.

CXPACKET Wait Type
Another great way to know that it is time to add indexes to a SQL Server database is by looking at the specific wait types for the queries which are running longer than expected.  Using only the native functionally this can be done by using the sys.dm_os_waiting_tasks dynamic management view.  By looking at the wait_type column finding rows which are waiting for the CXPACKET wait type will give you the sessions which are running queries which could need to have indexes added to improve performance.  This will require joining the sys.dm_os_waiting_tasks dynamic management view to the sys.dm_exec_requests dynamic management view and cross apply against the sys.dm_exec_sql_text dynamic management function as shown below:

SELECT *
FROM sys.dm_os_waiting_tasks  wt
JOIN sys.dm_exec_requests c ON wt.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle)

There is a fantastic third-party stored procedure which can show you similar information as well.  Written by Adam Machanic, this third-party stored procedure is called sp_whoisactive and can be downloaded here.  By simply running this stored procedure it will return an output which includes wait_type column (which shows the same information from sys.dm_os_waiting_tasks).

Locking and Blocking
When locking and blocking is shown within the output of sys.dm_os_waiting_tasks or sp_whoisactive this may indicate that there are indexes which are missing from the database tables.  Resolving this requires finding the query which is causing the blocking, which will be shown in the blocking_session_id column of the sys.dm_exce_requests dynamic management view.  The query which is causing the performance problems will probably have one of the other problems talked about in this article.

Key Lookups
Another way to know that some changes to indexes need to be done is by reviewing the execution plans of slowly running queries.  If there are Key Lookup operators which are shown when looking at the execution plans, this tells you that index changes are needed to the non-clustered index which is being used by the query.  In this case we don't want to add more indexes, but instead we want to change the existing index.  The problem which is causing the key lookup is that additional columns need to be added to the non-clustered index which is already being used.  By looking at the key lookup operator you can see the columns which need to be added to the non-clustered index.

Missing Indexes Dynamic Management Views
SQL Server includes a set of dynamic management views which tell you what indexes the SQL Server database engine thinks need to be added.  These dynamic management views are based on the queries which have been run since the database engine was last started -- it is not based on all of time.  These dynamic management views are called sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats.  By querying these dynamic management views using a query similar to the one shown below you can see all of the indexes which the SQL Server recommends creating.  Do NOT simply create these indexes.  The SQL Server does not take into account indexes which exist which are similar to the current indexes, nor does it take indexes which are similar to each other into account within its recommendations.  Because of this you will often get duplicate indexes in these dynamic management views.

select a.avg_user_impact * a.avg_total_user_cost * a.user_seeks, db_name(c.database_id), 
       OBJECT_NAME(c.object_id, c.database_id), c.equality_columns, c.inequality_columns, c.included_columns, c.statement
from sys.dm_db_missing_index_group_stats  a
join sys.dm_db_missing_index_groups  b on a.group_handle = b.index_group_handle 
join sys.dm_db_missing_index_details  c on b.index_handle = c.index_handle 
join sys.objects d on c.object_id = d.object_id
where c.database_id = db_id()
order by DB_NAME(c.database_id), OBJECT_NAME(c.object_id, c.database_id), ISNULL(equality_columns, '') + ISNULL(c.inequality_columns, ''),
a.avg_user_impact * a.avg_total_user_cost *  a.user_seeks desc

The above query uses a custom formula which is the impact percentage to the query times the query cost times the number of seeks which would have been performed against this index to sort the data (which is output in the first column of the output) to sort the data (after sorting by database name and object name).  This gives a decent idea of which indexes will be the most important to create on the database first.  Getting the CREATE INDEX statement is as simple as adding in one more column, shown below, to this query which then generates the T-SQL CREATE INDEX statement.

'USE [' + DB_NAME(c.database_id) + ']; 
  CREATE  INDEX mrdenny_' + replace(replace(replace(replace(ISNULL(equality_columns, '') + ISNULL(c.inequality_columns, ''), ', ', '_'), '[', ''), ']', ''), ' ', '') + ' ON [' + schema_name(d.schema_id) + '].[' + OBJECT_NAME(c.object_id, c.database_id) + ']
  (' +  ISNULL(equality_columns, '') + CASE WHEN c.equality_columns IS NOT NULL AND c.inequality_columns IS  NOT NULL THEN ', ' ELSE '' END + ISNULL(c.inequality_columns, '') + ')
  ' +  CASE WHEN included_columns IS NOT NULL THEN 'INCLUDE (' + included_columns + ')' ELSE '' END + '
WITH  (FILLFACTOR=70, ONLINE=ON)' AS 'CreateCode'

Simply table the above column and add it to the recordset above, then execute.  The code to create the missing indexes will be shown in the specific column.

As you've seen there are several things that you need to look for when deciding what indexes should be added to the SQL Server database engine.  While Microsoft has tried to make it easier to find the indexes which need to be added, it still requires knowing where to look.  While this list isn't a complete list, these are the most common places to look within the database engine to solve missing index problems.