SQL Server Unused Index Monitoring and Reporting Tool

Monitoring your SQL Server index usage and taking the time to find unused indexes in SQL Server can enable you to take charge of your SQL Server performance, organization, and resource allocation, which ultimately helps the business run more effectively. When you analyze SQL Server index usage stats, you can more easily take appropriate action against bottlenecks, latency, and space-hogging indexes.

Monitoring and analysis can also let you more easily evaluate which indexes are performing best based on value density, seeks and scans, lookups, sorting ability, and query coverage. By identifying unused indexes in SQL Server, you can more easily maintain performance by running a SQL Server unused indexes reset and running an unused SQL Server missing index report to further understand your SQL Server index usage status.

With easy-to-understand visualizations, SQL Sentry is designed to help you make smarter, more informed decisions about your SQL Server index usage and performance. These choices can help reduce data storage footprint, limit query overhead, and counter long-running SQL queries and deadlock issues to help you optimize overall SQL Server performance.

The best place to start identifying unused indexes in SQL Server is to pick a SQL Server, database, and date range to analyze.

For example, you can explore and compare the index usage metrics total read operations to the total update operations to identify if any read operations are null (reads=0). If there have been updates to the index, the index is most likely not in use. From here, you can reference the general seeks, scans, and lookups of your indexes. Again, if any of these metrics equal zero or have low volumes, there’s a strong chance the given index is unused.

For even more information, you can use an automated monitoring and reporting tool to see the index name, description, keys, included columns, and filter definition for SQL Server indexes. This can offer further insights into total read and update operations in addition to seeks, scans, and lookups.

After identifying unused indexes, you may not want to delete them right away. Instead, flag and keep watch over them for some additional time to make sure there are no periodic processes making use of these indexes. Deleting an in-use index can be detrimental to your SQL Server, but removing unused indexes should be an essential task when optimizing your SQL Server index usage monitoring.

While you may want to use dynamic management views (DMVs) on SQL Server to identify duplicate indexes, Missing Index DMVs may overlook some crucial information.

Some downsides to utilizing Missing Index DMVs include the following:

• Limited scope
• Limited capacity
• Less accurate
• Column order isn’t accurate

Unordered columns can make it difficult to discover duplicate indexes, so ensuring the columns are ordered can be crucial. You can do this by running a script to list indexes on a table. The script should include index ids, included columns, filtered index columns, leaf/tree details for rowstore indexes, and columns defined for columnstore indexes.

Modify sp_helpindex to include the above info, and order by index_keys and included_columns, NOT index_name. Ordering by index keys can enable you to more easily see overlapping indexes and similar index keys with overlapping included columns.

While some duplicate indexes will be redundant, others will be overlapping. Redundant indexes, which are completely identical, should be consolidated so there’s one index for a wide variety of queries. Overlapping indexes are a little trickier; if they have the same qualities in the same order, they’re overlapping, but if they have the same qualities in a different order, they aren’t overlapping and can’t be combined. By viewing columns_in_tree and columns_in_leaf, you can more easily determine whether two or more indexes are redundant duplicates or overlapping duplicate indexes. Having a SQL Server missing index report can be critical when checking these parameters.

SQL Sentry is designed to show SQL Server indexes in different styles under the Indexes tab. The information displayed on these indexes can help you understand SQL Server index usage, identify unused indexes in SQL Server, and gain insight into the impact of an unused index in SQL Server.

There are a few different ways SQL Sentry can list SQL Server indexes:

• Overview Charts
  
  The Overview Chart section is built to show a handful of charts displaying statistics on indexes analyzed by SQL Sentry. This cumulative SQL Server index usage report can allow you to gain a general understanding of your SQL Server index usage, including Total Fragmentation and Total Buffer Usage.


• Index Grid View
  
  The Index Grid view section can help reveal various index usage statistics about individual indexes on SQL Server. You can also apply filters and order data by various columns in Index Grid view to customize how you view SQL Server index usage stats. When you select an index here, the Detail Index Chart is designed to update with information unique to the given index.


• Index Tree View
  
  The Index Tree view section can display individual index statistics in a hierarchical tree format. Like the Index Grid view, you can also apply filters and edit the number of rows shown using the collapsed Filter tab. However, unlike the Index Grid view, the Detail Index Chart won’t update index information according to filters chosen on the Index Tree view.


• Detail Chart
  
  The Detail Chart section contains a set of charts highlighting individual SQL Server index usage information. Some specific charts include the Index Space Usage Chart, which can tell you how much an index is on the disk and in the buffer, and the Index Activity Chart, which uses scan, seek, and lookup operations to help you identify unused indexes on SQL Server.


• Filter
  
  This tab, collapsed by default, can enable you to apply, remove, and edit filters to quickly view important SQL Server index usage stats. You can filter individual indexes by SQL Server and database name, start and end time of defragmentation operations, and total number of index pages. You can also control the number of rows shown in Grid view or Tree view through the Filter section.

With these index performance views, SQL Sentry is designed to help you more easily explore individual SQL Server index usage stats and run SQL Server index usage reports to gain a better understanding of the various SQL Server indexes present in your network. These SQL Server index visualizations can also help you make more informed decisions on index strategies and how to boost SQL Server performance.