I really want to cover this topic because of a recent event in which reporting services would not render a report I created due to time outs, which were mainly caused by performance issues due to lack of any non-clustered (or any other kind) of indexes. Not many BI and data analysts truly understand when and why an index, or multiple for that matter, needs to be configured and set up properly. Most of the time the DBA would ideally have these put in place for us so we don’t pay as much attention. In this post I will show a couple different stored procedures you can use to determine which indexes need to be created. I will write another post over the next couple of days to dig down into the nitty-gritty details of how indexes work, how they impact performance, and why they need to be created.
Method #1: Using Dynamic Management Views (DMVs)
This method was shown to me by our VP of IT and it proved to be a very useful tool for identifying basic indexing needs. This stored procedure creates a weighted identifier (Index Advantage) that will show you which tables are being hit the most and have the most costly queries. The more costly the query, the more the server gets bogged down with load. So how do we fix this? By creating an index. When creating your databases and tables always remember that you want to make the system work as little as possible and let the tools handle most of the load, so when you need to constantly pull data back out of your database you can do so with ease.
CREATE PROCEDURE spIndexAnalysis (@Database VARCHAR(500))
AS
SELECT ( user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) ) AS [Index Advantage] ,
‘CREATE NONCLUSTERED INDEX IX_’ + OBJECT_NAME(c.object_id) + ‘_’
+ LEFT(CAST(NEWID() AS VARCHAR(500)), 5) + CHAR(10) + ‘ ON [‘
+ OBJECT_NAME(c.object_id) + ‘](‘
+ CASE WHEN c.equality_columns IS NOT NULL
AND c.inequality_columns IS NOT NULL
THEN c.equality_columns + ‘,’ + c.inequality_columns
WHEN c.equality_columns IS NOT NULL
AND c.inequality_columns IS NULL
THEN c.equality_columns
WHEN c.inequality_columns IS NOT NULL
THEN c.inequality_columns
END + ‘)’ + CHAR(10)
+ CASE WHEN c.included_columns IS NOT NULL
THEN ‘INCLUDE (‘ + c.included_columns
+ ‘) WITH ( FILLFACTOR = 90 )’
ELSE ”
END AS [T-SQL]
FROM sys.dm_db_missing_index_group_stats a
INNER JOIN sys.dm_db_missing_index_groups b ON a.group_handle = b.index_group_handle
INNER JOIN sys.dm_db_missing_index_details c ON c.index_handle = b.index_handle
WHERE DB_NAME(database_id) = @Database
AND equality_columns IS NOT NULL
ORDER BY [Index Advantage] DESC
Always remember that you shouldn’t replicate an index. If an index is currently referencing a specific column make sure you DON’T reference that same column in another index. This will cause you to write the indexed data to disk multiple times, causing space to be filled up much more quickly.
Method #2: Use BlitzIndex
Many of you may know about the stored procedure that is online and available to download for free called sp_BlitzIndex. I downloaded this tool and have been playing around with it for a couple of days and it is VERY useful. You can download it at the following link:
https://www.brentozar.com/blitzindex/
This stored procedure will loop through your tables at various levels and determine the status of each table, if there’s anything wrong with the indexes that currently exist, or if there is one that needs to be created. It’s a complicated stored procedure, but that’s because it’s packed with a TON of features.
That One SQL Blog 