Click here to monitor SSC

Jonathan has been working with SQL Server since 1999. He enjoys performance tuning, development and using SQL Server to provide appropriate business solutions. He is the founder and leader of the PASS SQL South West user group http://www.sqlsouthwest.co.uk , is a moderator at SQL Q + A forum ask.sqlservercentral.com and is on twitter at @fatherjack. He has spoken at SQLBits and SQL in the City, SQL Saturdays and local user groups across the UK and Europe.

How many indexes is the right amount?

Published 14 December 2011 3:00 am

I would be deafened by the chorus of DBA’s shouting “It depends” at me after a question like that in person, I somehow feel safer from the far side of Live Writer.

I have recently been looking at the number of indexes in one of my databases and have found a curiously high number of indexes in comparison to the number of columns.

Right, all the code now and some discussion underneath it

USE [AdventureWorks]
GO

/*
To locate tables that may be over populated with indexes and
list them in order of concern.
Jonathan Allen
Dec 2011

*/
IF OBJECT_ID('tempdb..#IndexStats') > 0
   
DROP TABLE #IndexStats ;
WITH    I_Count
         
AS ( SELECT   [ddips].[object_id] ,
                       
COUNT([ddips].[index_id]) AS [Index_Count]
              
FROM     [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL,
                                                           NULL, NULL)
AS ddips
              
WHERE    [ddips].[index_id] <> 0
              
GROUP BY [ddips].[object_id]
            
),
       
C_Count
         
AS ( SELECT   [c].[object_id] ,
                       
COUNT([c].[object_id]) AS [Column Count]
              
FROM     [sys].[columns] AS c
              
GROUP BY [c].[object_id]
            
),
       
Merged
         
AS ( SELECT   OBJECT_SCHEMA_NAME([C_Count].[object_id]) + '.'
                       
+ OBJECT_NAME([C_Count].[object_id]) AS [TableName] ,
                       
[C_Count].[Column Count] ,
                       
[I_Count].[Index_Count] ,
                       
CONVERT(DECIMAL(4, 2), ( [I_Count].[Index_Count] * 1.0
                                                
/ [C_Count].[Column Count]
                                                
* 1.0 )) AS [I/C ratio]
              
FROM     [C_Count]
                       
INNER JOIN [I_Count]
ON [C_Count].[object_id] = [I_Count].[object_id]
            
)
   
SELECT  [m].[TableName] AS [Schema/Table name] ,
           
[m].[Column Count] AS [Column count] ,
           
[m].[Index_Count] AS [Index count] ,
           
[m].[I/C ratio] AS [Index:Column ratio] ,
           
CASE WHEN [m].[I/C ratio] < 0.1
                
THEN '1 - Low. Having fewer indexes on a table allows Insert,
Update and Delete performance to remain high but be sure to check for missing
indexes.'
-- 1 in 10 indexes to columns
                
WHEN [m].[I/C ratio] < 0.25
                
THEN '2 - Moderate. There are plenty of indexes, in relation
to the number of columns, on the table. Consider altering existing indexes rather
than creating new ones if you need to index data to improve query
performance.'
-- 1 in 4 indexes to columns
                
WHEN [m].[I/C ratio] < 0.5
                
THEN '3 - High. Having lots of indexes will make everything
except Select queries, run more slowly. Consider removing/merging indexes that
are similar or get used very little.'
-- 1 in 2 indexes to columns
                
WHEN [m].[I/C ratio] BETWEEN 0.5 AND 10 -- 1 to 1 indexes to columns
                     
THEN CASE WHEN [column count] < 4
                               
THEN '3 - High. Many people recommend having a
clustered index in every table and then creating a 2nd index is not unrealistic.
You should be concerned though if there are more than
this.'
-- in a table with < 3 columns it may not be too big an issue
                               
ELSE '7 - V High. Having a high ratio of indexes
to columns could mean you are slowing down an awful lot of your OLTP transactions
while the indexes and statistics are updated every time the base data is
altered.'
-- You have an average of 1 index per two columns.
                          
END
                 ELSE
'9 - Really ? ! ' -- Really? more than 10 indexes to columns?!?!?
           
END AS [Concern]
   
INTO    [#IndexStats]
   
FROM    merged AS m
   
ORDER BY concern DESC ,
           
[m].[I/C ratio] DESC ,
           
[m].[Column Count] DESC
   
   
-- analysis
DECLARE @sum INT

SELECT 
@sum = COUNT(*)
FROM    [#IndexStats]

-- Summary of index:column ratio stats, by % of tables affected
SELECT  [is].[Concern] ,
       
COUNT([Concern]) [Table count],
       
CONVERT(DECIMAL(3, 2),
COUNT([Concern]) * 1.0 / @sum * 1.0) * 100 AS [Pct_Split]
FROM    [#IndexStats] AS [is]
GROUP BY [is].[Concern]
ORDER BY pct_split DESC
       
-- All index:column details by concern
SELECT  *
FROM    [#IndexStats] AS [is]

So this query collects data regarding column counts per table and then counts indexes per table and then merges (JOINS) them together to compare the details. When I am querying data like this and want to analyse it in a variety of ways I prefer to insert it into a temporary table. A personal preference.

The first extract from the temporary table is purely a summary of the data – how many tables are there in each category of concern? The last query lists the whole data set.

Running this against my (somewhat battered) copy of AdventureWorks we get the results as below

Blog_TooManyIndexes

In the summary section we can see that 21 tables (29% of the tables in the database) have an index count that is more than half of the column count. In the detail in the second grid we can see that one table has 5 indexes on a table with 4 columns.

Now I am not promoting any particular ratio of indexes to columns, all I am suggesting is that keeping an eye on this might give you a pointer that you are adding too many indexes and possibly altering existing ones would be a better solution for the performance of your server. Do you have any thoughts on whether this is a good way, or a worthwhile way to review your indexes?

Feel free to alter the percentages in the TSQL to something that you feel more appropriate.

Usual conditions apply. Don’t trust this code on your production servers without reading and understanding how it works. You will not get any support if this code does bad things.

4 Responses to “How many indexes is the right amount?”

  1. Anonymous says:

    Interesting Finds: December 15, 2011

  2. Anonymous says:

    Good one Jack, definately this will help.

    To add to this:-
    you can’t always count on this ration, i’ve seen the tables over 400 columns n i am sure this will give a breeze to most of the Performance tunning experts.
    though tht was a bad design….we found later…. but it will not be always.
    Since SQL allowes 1024 columns which is way too much to go with this ratio.

    Thanks for sharing Jack.

  3. Anonymous says:

    I think you should have a “0 – Normal” category for two or three column tables with one index (1 primary key).
    Also, a better analysis would be made if Foreign Keys were taken into account.

  4. fatherjack says:

    Joel, thanks for reading my blog. I avoided having a “Normal” category as I dont think there necessarily think there is a normal. All I wanted to do was group indexes in relation to the number of columns in the table so that they might show as a concern or point of interest to a DBA who may be very focussed on just following the Missing Index recommendations. Yes, there is no account taken of Foreign Keys. Feel free to take this code and add this function into it. Let me know how you get on.

Leave a Reply