Indexes are the backbone of SQL Server performance, but poorly designed or maintained indexes can lead to slow queries and high I/O. Enter sp_BlitzIndex, a powerful tool from the SQL Server First Responder Kit designed to analyze and optimize indexes. Whether you are dealing with unused indexes, or missing indexes, sp_BlitzIndex provides the insights you need to keep your database running smoothly. In this article, we will explore how to use sp_BlitzIndex to master index optimization and improve query performance.
sp_BlitzIndex
It analyzes design issues with indexes and provides a rough list that could draw your attention. Then you need to add your interpretation on this result set for index tuning. As indexing is as much an art as a science. There are black, white and grey area in index tuning. Though there are some guidelines for tuning, however, sometimes you need to break those guidelines to get better performance. This tool does not give any quick fix answers. It requires your intervention to parse and combine the result set.
Running sp_BlitzIndex Default Mode
Run the sp_BlitzIndex when you want to tune the index and you want to spend couple of hours on it. There are couple of parameters here:
@GetAllDatabases = 1 - Watches all the databases of your SQL Server and shows the combined the result. Put 0, in case you want to work on only one database where you execute the stored procedure.
@Mode = 0 (default): returns prioritized findings based on the D.E.A.T.H. Method in my index classes.
@Mode = 2: inventory of existing indexes, good for copy/pasting into Excel to work offline. Has a @SortOrder parameter for things like rows, size, reads, writes, lock time.
@Mode = 3: inventory of missing indexes.
@Mode = 4: like Mode 0, but returns more findings (that tend to be harder to fix.)
EXEC sp_BlitzIndex @GetAllDatabases=1, @Mode = 0
Figure-1: Output of sp_BlitzIndex
Analyzing the Output
sp_BlitzIndex gives prioritize output sorted by D.E.A.T.H. method:
- Deduplicate – remove fully or nearly identical indexes
- Eliminate unused indexes if any
- Add desperately needed indexes to enhance query performance
- Tune query plans to make it faster
- Heaps usually need clustered indexes
Deduplicate
In Figure-1, there is no duplicate index hints. However, as per the D.E.A.T.H. method, before adding any new index, first remove the existing fully or nearly identical one. Because, index puts extra load on the server. Before, putting any extra burden let's remove duplicate one. Otherwise, things will be worsen more. Let's assume there are below two indexes on the TestTable.
CREATE NONCLUSTERED INDEX [IX_COL1_COL2_COL3] ON [dbo].[TestTable]
([COL1] ASC,
[COL2] ASC,
[COL3] ASC
)
CREATE NONCLUSTERED INDEX [IX_COL1_COL2_COL3_COL4_COL5] ON [dbo].[TestTable]
([COL1] ASC,
[COL2] ASC,
[COL3] ASC,
[COL4] ASC,
[COL5] ASC
)
The indexes IX_COL1_COL2_COL3 and IX_COL1_COL2_COL3_COL4_COL5 are nearly identical. In this scenario, you can drop the first index. Though the second index has some additional columns. Reading a larger index will take more time. However, dropping the first index will be better for inserts/updates/deletes, because it has one less index to manage. It also makes the database smaller and database maintenance tasks become smaller/faster. Remember, if you have
- two indexes with the exact same fields in the same order, and
- one has 1-2 extra fields, and
- there are no include fields, or the include fields are the same
- then you can drop the smaller index
Notes:
- Check indexing options like partitioning and sorting in tempdb to make sure they are consistent from index to index.
- If two indexes have the same fields but in different order, that does not mean you can drop one of them.
- After making index changes, restart the SQL Server instance to reset the DMV counters that monitor index usage. If it is not feasible, then you need to log the changes to somewhere to understand what the changes have been.
Eliminate Unused Indexes
In this scenario (in Figure-1), there is no duplicate index suggestion from sp_BlitzIndex. In case there are hints, crafted that with your own discretion to drop the unused indexes.
Add Suggested Indexes
As depicted in Figure-1, sp_BlitzIndex suggests high value missing index. For the top suggested query, it says that
- it ran 4,653 times (check usage column)
- if suggested index is implemented, it will be 100% faster (check usage column)
- average query cost is 8299.8204 (check usage column)
- estimated benefit per day is 2,369,267,749 (check Details:schema.table.index(indexid) column)
Meaning to say that this query runs a lot and adding the suggested index will make it 100% faster. Per day benefit will be billions time. The second and third query ran once, adding index will make it 97%+ faster. However, the per day benefit is just 500K+. If a query runs several times and per day benefit is more than millions of time then consider to add the index. So for the first query, you can think of adding index. However, for later cases it is not suggested. Remember, SQL Server loves to add indexes if it sees a query without index.
In Figure-1, the fourth suggestion warns about heap table. If you really want to treat this as heap then leave it alone.
Figure-2: Output of sp_BlitzIndex
As shown in Figure-2, in Create TSQL column of sp_BlitzIndex output, you will get the required TSQL for creating the indexes. Before running it, ensure that the column order is correct. Otherwise, you will not get benefit from it. There is also url browse it for further clarification on it.
Running sp_BlitzIndex with Mode=2
Running the sp_BlitzIndex with mode=2 will give you the inventory of your indexes including database, schema, name, columns, size, read/write and many more. You can copy/paste it to excel and for offline analysis.
EXEC sp_BlitzIndex @GetAllDatabases=1, @Mode = 2
Figure-3: Output of sp_BlitzIndex with Mode=2
You can use @SortOrder parameter along with @Mode=2 to sort the output by 'rows', 'size' or 'writes'. It's really handy for finding out top indexes sorted by rows/size/writes.
Running sp_BlitzIndex with Mode=3
Mode=3 produces the inventory of missing indexes meaning SQL Server wishes to have them. including database, schema, table, benefits, query cost, estimated improvement, impact and many more.
EXEC sp_BlitzIndex @GetAllDatabases=1, @Mode = 3
Figure-4: Output of sp_BlitzIndex with Mode=3
Running sp_BlitzIndex with Mode=4
Mode=4 will give you same result as Mode=0. In addition, it will provide further findings up to 250.
EXEC sp_BlitzIndex @GetAllDatabases=1, @Mode = 4
Figure-5: Output of sp_BlitzIndex with Mode=4
Running sp_BlitzIndex for Individual Findings
sp_BlitzIndex can be run for individual findings of mode=0. Copy/paste the TSQL of More Info column as shown in Figure-2 and run it. For example, running below query will produce output of Figure-6.
EXEC dbo.sp_BlitzIndex @DatabaseName='StackOverflow', @SchemaName='dbo', @TableName='Posts';
Figure-6: Output of sp_BlitzIndex for individual findings
Figure-6, has below 6 output of the individual findings which are very comprehensive. You can do further analysis based on the existing indexes, missing indexes, table structure and statistics. Craft your discretion with the recommendation to take the final decision about the indexes.
- Existing indexes of the table
- Missing indexes that SQL server wishes to have
- Table structure
- Foreign keys
- Statistics info of the table
- Columnstore info
Important Notes
Look at the Usage Stats column of Figure-6. Let's explore this:
- Server Uptime - it's the uptime of the server in days. For index tuning exercise, uptime must align with business cycle. For example, for a Bank there is a month starts and month ends. Typically, from first day of the month to the last working day. For a stock exchange company, everyday is same busy day. It's important because, if a SQL server's uptime conforms with business cycle then it will not miss any queries and associated indexes.
- Reads - index makes a read faster, so if you have many reads, it might need to create it.
- Writes - index makes the write performances slower. If you have a query with many writes and zero read, probably you want to drop that index.
Conclusion
Index optimization is a cornerstone of SQL Server performance tuning, and sp_BlitzIndex is an indispensable tool for achieving it. By providing deep insights into index suggestion, benefits, usages, missing indexes, and more, sp_BlitzIndex empowers database administrators to make informed decisions that enhance query performance and reduce resource overhead. Whether you're identifying unused indexes, addressing fragmentation, or adding missing indexes, this tool simplifies the process and helps you maintain a healthy, high-performing database.