Index Analysis: Analyze and Optimize SQL Server Indexes

What is SentryOne Index Analysis?

SentryOne Index Analysis is a capability that presents actionable information to help you make smart decisions about SQL Server index management, which can help speed performance of your databases. 

You'll find this feature in:

SQL Sentry | Download Free Trial

Plan Explorer | Download Free Trial

 

Analyzing SQL Server indexes can be tricky

Successfully analyzing and managing SQL Server indexes is difficult—but critical, because of the negative impact poor index management can have on database performance. Some of the index analysis decisions you might struggle with include:

  • Should I reorganize or rebuild a fragmented index?
  • Does my chosen index cover a particular query?
  • Is my chosen index the best, most optimal, one for a particular operation?

These are the kinds of questions you need to answer before you can overcome your performance bottlenecks in SQL Server. If you don’t get the answers right, you can end up with some serious problems with your SQL Server performance.

Yes, it’s true that SQL Server Management Studio, as well as other tools, let you create, modify, and delete indexes, rebuild clustered and nonclustered indexes, and more. But when it comes to analyzing and optimizing SQL Server indexes, you need a solution that quickly points you to the best index plan for your situation. That's where SentryOne Index Analysis and Optimization capabilities come in.

 

 

Analyzing and optimizing your SQL Server indexes with SentryOne

 

SentryOne offers a powerful Index Analysis capability as part of SentryOne Plan Explorer, which is included in the SentryOne Monitoring Platform products.

Index Analysis provides a sandbox environment you can use to analyze an indexing strategy for a specific operation in a query, as long as the query is executed from within the Plan Explorer session.

Index Analysis in Plan Explorer guides you based on tried-and-true indexing methods to create the best index to cover a query. And when you run the query after applying the index, you’ll get an overall score for your index based on the SentryOne index scoring algorithm.

You can watch the score change in real time as you work toward the elusive 100% score—before executing a single line of code against SQL Server.

 

SentryOne_Index_Analysis

 SentryOne Index Analysis capability helps you analyze indexing strategies.

 

Indexes tab

 

The Indexes tab in the Performance Analysis Dashboard displays information about your tables and indexes that are collected by the SentryOne Fragmentation Manager.

This tab provides the stats you need to make informed decisions about index management in your SQL Server environment as you determine whether an index should be rebuilt or defragmented with the Fragmentation Manager.

The Indexes tab also displays a set of charts that contain aggregated statistics about the indexes that have been analyzed. You can also see index-related statistics such as the average percentage of fragmentation.

SentryOne-indexes-tab-184

The SentryOne Indexes tab helps you make good decisions about index management in your SQL Server environment, including when and how to perform defragmentation operations, when to adjust fill factors, or when an index definition should be changed.

 

You can also look at a tree view of indexes and related metrics as well as view detailed charts for a selected index to look at fragmentation history and more. 

Want to proactively manage defragmentation? With the SentryOne Fragmentation Manager, you can set an automated scheduled for index defragmentation.

The Indexes tab in SentryOne gives you all the relevant metrics you need to make informed decisions about index management—and boost SQL Server performance.

SentryOne-indexes-tab-total-fragmentation-overview-chart-184

 The SentryOne Total Fragmentation chart displays a once a day aggregation of the fragmentation levels for all indexes that have been analyzed by Fragmentation Manager.

What You Can Do with Index Analysis

  • Evaluate the best index based on query coverage, value density, sorting effectiveness, and seek ability
  • Make intelligent decisions about when and how to perform defragmentation operations, when to adjust fill factors, or when an index definition should be changed
  • Automatically collect table and index information, analyze the data, take the appropriate reorganization or rebuild operations, and then perform post-defragmentation analysis

“We have used SQL Sentry for analyzing and researching long-running SQL queries and deadlock issues, which have become major bottlenecks in our application development processes. The software has helped us address missing index issues and optimize query performances.”

—Jun Tang, DBA, J. Spargo & Associates

 

Read more independent SentryOne reviews on TrustRadius.