Index Analysis: Analyze and Optimize SQL Server Indexes

What is SolarWinds SQL Sentry Index Analysis?

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 SQL Sentry Index Analysis and Optimization capabilities come in.

 

 

Analyzing and optimizing your SQL Server indexes with SQL Sentry

 

SQL Sentry offers a powerful Index Analysis capability as part of SolarWinds Plan Explorer, which is included in SQL Sentry.

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 SQL Sentry 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.

 

sql-sentry-index-analysis

 SQL Sentry 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 SQL Sentry 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.

sql-sentry-index-analysis-indexes-tab

The SQL Sentry 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 SQL Sentry Fragmentation Manager, you can set an automated scheduled for index defragmentation.

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

sql-sentry-index-analysis-total-fragmentation

 The SQL Sentry 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 SQL Sentry reviews on TrustRadius.

Ready to end your database performance problems?

 

Download a SQL Sentry trial

See for yourself how SQL Sentry can help you achieve optimal database performance.

Download Free Trial

Schedule a SQL Sentry demo

Learn more about how SQL Sentry can help you troubleshoot and prevent database performance problems. Register to attend a public demo or schedule a one-on-one session.

Schedule Demo

Learn more about SQL Sentry

Discover how you can quickly find and fix SQL Server performance problems with SQL Sentry, which delivers proven scalability, easy-access dashboards, and visibility across your entire database environment.

Learn More