In the world of performance monitoring, there a number of ways to identify problems. Each method is different and usually, neither way is better than another.

Over the years, I’ve started look at metrics around non-clustered indexes to help find problems.   Once while working at a client site, I was astounded by how they would implement changes so rapidly.  This quick rate of change usually had detrimental effects on the performance of the application. Of course as usual, once the change was in Production, it was difficult to fix things.

One day, I happened to be looking at their flagship application’s database.  The table I was focused on was about 60GB in size.  This, in itself, was not a cause for alarm.  However, the 120GB worth of non-clustered index space was.  Did this mean that we had a performance issue?  Not necessarily.  In my opinion, this meant that we had a potential of having a performance issue.

Here is the common problem that I see.

1. Ted writes a query for the application.
2. Ted knows he needs an index so creates a non-clustered index to cover his query.  Awesome!
3. Paul also writes a query for the application
4. Paul is just as smart as Ted and so he writes another non-clustered index for his query.
5. Rinse and Repeat

Shockingly, this occurs more often than you’d think.  Instead of checking to see what indexes are already in place that might cover their respective queries, they just blindly create a new one.

To help identify this potential issue, you could just compare the total number of non-clustered indexes on the table, however in my opinion that doesn’t tell you the whole story.  You don’t know how large those indexes are so your story is not complete.

The script below will give you a better picture.  It compares the heap/clustered index size against the aggregate size of the non-clustered indexes.   The script is configured to identify indexes that are three times larger than table itself.  Your mileage may vary and you can certainly adjust to your liking.

If you run the script against one of your databases and the script alerts you to a potential issue, you will have to further evaluate that particular table.

DISCLAIMER: Do not run code you find on the internet in your production environment without testing it very thoroughly.  Period.  

/***************************************************************
-- Author: John Morehouse
-- Date: April 2015
-- T: @SQLRUS
-- E: john@jmorehouse.com
-- B: http://sqlrus.com
--THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
--IN OTHER WORDS: USE AT YOUR OWN RISK.
--AUTHOR ASSUMES ZERO LIABILITY OR RESPONSIBILITY.
--You may alter this code for your own purposes.
--You may republish altered code as long as you give due credit.
***************************************************************/;WITH TableIndexes (tableName , datapages, datasize)
AS (
SELECT st.name, si.dpages, (CAST(si.dpages as BIGINT) * 8192)/ 1024 as 'dataSize'
FROM .sys.tables st
INNER JOIN sys.sysindexes si ON st.object_id = si.id
WHERE st.type = 'U' -- user tables
AND si.indid in (0,1)-- Heap or clustered index
)
SELECT
ss.name as 'Schema'
, st.name as 'Table Name'
, MAX(ti.datasize) AS 'Table Size(KB)'
, SUM((CAST(si.dpages as BIGINT)*8192)/1024) AS 'Total NC Index Size(KB)'
, SUM(si.rowcnt) as 'Total Row Count'
, COUNT(si.indid) as 'NonClustered Index Count'
, CASE
WHEN MAX(ti.datasize*2) < SUM((CAST(si.dpages as BIGINT)*8192 )/1024) THEN 'WARNING'
ELSE 'OK'
END AS 'Response'
FROM sys.tables st
INNER JOIN sys.sysindexes si on st.object_id = si.id
INNER JOIN TableIndexes ti ON st.name = ti.tableName
INNER JOIN sys.schemas ss on st.schema_id = ss.schema_id
WHERE st.type = 'U' -- user tables
AND si.indid > 1
GROUP BY ss.name
, st.name
ORDER BY [Schema],[Table Name]

This script can be useful to help identify potential issues.   It is just another tool for your toolbox.

Enjoy!