Indexes are crucial for SQL Server query performance. Everyone needs an Index. However, improper indexing strategies can degrade database performance, leading to slow queries and cause resource bottlenecks.

Effectively managing indexes involves not only identifying the missing ones that could improve performance, but also pinpointing unused indexes that unnecessarily consume system resources.

The following is shared in this post:
> Script 1: Check for Missing Indexes
> Script 2: Check for Un-used Indexes
> More Information

Script 1: Check for Missing Indexes

The script below returns missing index suggestions for the current database:

Use this script to identify and rank missing indexes based on SQL Server’s query analysis, prioritizing those with the highest impact. Refine suggestions based on your workload and performance goals. We should look at combining missing index suggestions with existing indexes to optimize performance.

For more information on this from Microsoft, have a look at this page: Tune nonclustered indexes with missing index suggestions

Script 2: Check for Unused Indexes

This script is for the indexes you don’t want. We’re able to check for un-used indexes indexes by analyzing index usage stats. Indexes with no recent seeks, scans, or lookups are considered candidates for removal.

To check for unused indexes in SQL Server, run the following:

Removing unused indexes can free up storage, reduce index maintenance overhead, and improve write performance. Before removing an index, ensure it is not indirectly supporting features such as indexed views or constraints.

More Information

To summarise, identifying missing indexes helps improve query performance, while removing unused indexes conserves resources and enhances system efficiency.

For more good information from myself, an experienced Microsoft SQL Database Admin, check out my post SQL Server Performance Tuning: Index Fragmentation Scripts, which is particularly useful to follow-on from this. The topic moves towards applying regular maintenance to the indexes you do want to keep around.

In this post I’m sharing more scripts to help with SQL Server performance tuning. This time, we’re looking at index and heap fragmentation which are useful for pinpointing inefficiencies on your database.

Fragmentation, whether in indexes or heaps, can lead to slower queries, inefficient storage, and increased maintenance times. Rebuilding fragmented indexes, converting heaps to clustered indexes, and maintaining good statistics can improve overall SQL Server health and performance.

The following scripts and information is included here:
> Script 1: Get Index Fragmentation Levels
> Script 2: Identifying Heap Fragmentation
> Addressing Fragmentation & Stale Stats
> More Performance Troubleshooting Tips

Script 1: Get Index Fragmentation Levels

This script helps retrieves key information about your SQL Server indexes and their fragmentation levels:

Script 2: Identifying Heap Fragmentation

Heaps, or tables without clustered indexes, are prone to fragmentation. Fragmentation in heaps can impact query performance, and potentially increase the time it takes for full backups to complete.

Use this script to identify fragmented heaps:

The ForwardRecordCount column is of particular interest, if you see a huge number on a heap then perhaps it’s the reason queries are running slow. If it’s a small table, I wouldn’t worry about this much.

Addressing Fragmentation & Stale Stats

After running the scripts above and identifying fragmentation, here’s how to address these issues:

Maintain Optimal Statistics

We don’t need to go too granular in explaining how to check if your statistics are good. We just need to make sure we keep them up to date, which can be done by having a Maintenance Plan for Updating Statistics.

For more information on updating stats, checkout this MS Docs Page: Update Statistics Task (Maintenance Plan). Stale statistics can seriously hurt query performance, be sure to refresh them periodically.

Index Maintenance

We should rebuild our indexes on tables to reduce fragmentation by using the ALTER INDEX REBUILD command. If on SQL Server Enterprise Edition you can perform this without impacting table availability using one of the available options. For indexes with lower fragmentation levels, consider using the ALTER INDEX REORGANIZE command. This is a more lightweight operation than rebuilding indexes but still helps to improve performance.

It’s a good idea to set up maintenance plans for addressing fragmentation, especially on large tables, and schedule them during off-peak hours.

For more information, these Microsoft Documentation Pages will help:
> Index Rebuild vs Reorganize
> ALTER INDEX Docs

Rebuild or Avoid Heaps

Rebuilding a heap can help to eliminate fragmentation and optimize the table’s data storage.

You can convert a heap to a clustered index to resolve fragmentation and enhance sequential data access:

-- Create clustered index on a table with no clustered index (heap)
CLUSTERED INDEX IX_ClusteredIndexName ON TableName(ColumnName);

More Performance Troubleshooting Tips

To provide you with some general follow-up material, I’ll recommend my other post, SQL Server Performance Troubleshooting Scripts. It covers identifying SQL worker thread issues, monitoring running queries, and optimizing stored procedures. It also contains links to check the health of High Availability (HA) environments, like Always On, replication, and Mirroring.

Feel free to leave a comment if there’s something I’ve missed or if you have any suggestions for improvement. Ty!