How To: Optimize SQL Indexes

Improving .NET Application Performance and Scalability

J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman
Microsoft Corporation

May 2004

Related Links

Home Page for Improving .NET Application Performance and Scalability

Chapter 12, Improving ADO.NET Performance

Chapter 14, Improving SQL Server Performance

Checklist: ADO.NET Performance

Checklist: SQL Server Performance

Send feedback to Scale@microsoft.com

patterns & practices Library

Summary: This How To describes an approach for optimizing table indexes to increase query performance. During the process, you use Structured Query Language (SQL) Profiler, Index Tuning Wizard, and SQL Query Analyzer to identify problems and analyze solutions.

Contents

Overview
What You Must Know
Summary of Steps
Step 1. Use SQL Profiler to Capture Data
Step 2. Use the Index Tuning Wizard to Select the Correct Indexes to Build
Step 3. Use SQL Query Analyzer to Optimize Queries and Indexes
Step 4. Defragment Indexes
Additional Resources

Applies To

• Microsoft® SQL Server™ 2000

Overview

This How To helps you to optimize your queries by indexing your tables correctly. The purpose of an index in SQL Server is to allow the server to retrieve requested data, in as few I/O operations as possible, in order to improve performance. This How To shows you how to use SQL Profiler, the Index Tuning Wizard, and SQL Query Analyzer to identify areas that can be improved. The Index Tuning Wizard is supplied with SQL Server and is available from Enterprise Manager, SQL Profiler, and SQL Query Analyzer to identify the indexes and indexed views needed. You can test the application with appropriate data, use SQL Profiler to measure query times, and then use the Index Tuning Wizard with input from the profiler workload to identify appropriate indexes and indexed views. You can use SQL Query Analyzer to analyze the queries and validate the indexes suggested by the Index Tuning Wizard.

**Note   **This How To is not an exhaustive treatment of indexing, but it highlights many of the key issues involved.

What You Must Know

Before optimizing indexes, you need to understand index design best practices. You will need to apply these practices once you have identified queries that need optimizing.

Understand Effective Index Design

When designing indexes, follow these guidelines:

• Use indexes on tables with numerous rows, on columns that are used in the WHERE clause of queries or in table joins, and on columns used in ORDER BY and GROUP BY queries.
• Avoid infrequently used indexes on frequently updated columns. In addition, avoid having many indexes on a table that is frequently updated. Otherwise, you unnecessarily increase the insert and update times of your queries. To improve performance, minimize the total width of the indexed columns.
• Use clustered and nonclustered indexes appropriately. Understand the purpose of each and choose the correct type for your scenario.
• Use a covering index to reduce the query execution time of frequently used statements. A covering index is a nonclustered index that has all the columns that come in a WHERE clause and in the query column selection.

More Information

For more information about creating good indexes, see the following resources:

• "Creating and Managing Indexes" in Chapter 6 of SQL Server 2000 Administrator's Pocket Consultant on TechNet at https://technet.microsoft.com/en-us/library/cc917626.aspx#XSLTsection126121120120
• "Designing an Index" in the Microsoft SQL Server 2000 documentation on MSDN® at https://msdn.microsoft.com/en-us/library/aa933128(SQL.80).aspx
• "Designing an Indexed View" in the Microsoft SQL Server 2000 documentation on MSDN at https://msdn.microsoft.com/en-us/library/aa933145(SQL.80).aspx

Understand the Data Distribution of Your Tables

The query optimizer selects indexes based on statistical analysis of the data it collects about your query. A query will behave differently if there are 1,000 rows in a table versus 1 million rows in the table. A table scan will be acceptable for small tables but not for large tables. To identify problem areas and optimize indexes, you need to have a good idea of the table sizes.

Summary of Steps

The following approach can help optimize table indexes:

1. Use SQL Profiler to capture data.
2. Use the Index Tuning Wizard to select the correct indexes to build.
3. Use SQL Query Analyzer to optimize queries and indexes.
4. Defragment the indexes.

Step 1. Use SQL Profiler to Capture Data

You can use SQL Profiler to identify queries and stored procedures that perform poorly. SQL Profiler traces provide information about the duration of the query execution, the number of read and write operations run to satisfy the query, and much more.

Choose the Correct Events to Monitor

Each event that you trace results in more overhead, so minimize the number of events to monitor. Make use of the SQL Profiler templates, which provide a number of predefined traces. For example, you can use the SQLProfilerSP_Count template to identify the most frequently used stored procedures.

For more information about using SQL Profiler, see "How To: Use SQL Profiler" in the "How To" section of this guide.

Run SQL Profiler from a Remote Computer

SQL Profiler consumes computer resources, so do not run it on the same computer as the SQL Server database you are profiling.

Identify Frequently Running Queries with Long Duration

Analyze the profiler log to understand the frequency with which queries are being invoked and their duration. Deal with high duration, frequently run queries first. Poorly performing SELECT statements may require that you add indexes, whereas queries executing frequent updates may require that you remove surplus indexes.

Step 2. Use the Index Tuning Wizard to Select the Correct Indexes to Build

Use the Index Tuning Wizard to identify indexes and indexed views that need to be created, and indexes that need to be dropped.

Generate a Workload

In order for the Index Tuning Wizard to recommend indexes and indexed views, you need to provide it with a workload. The following are some good practices for generating a workload.

Use Profiler Output as Workload

For existing systems and systems under development, SQL Profiler can be used to provide source information to the Index Tuning Wizard. Run the application, capture the SQL queries in a profiler trace, and include all default events and columns in the trace that will be used as a workload.

Use SQL Query Analyzer to Feed the Workload

If you have not yet developed the system and cannot use SQL Profiler, you can run the queries from SQL Query Analyzer and start the Index Tuning Wizard from it.

Limit the Workload Size

The Index Tuning Wizard can process a maximum of 32,000 queries. If you are capturing profiler results of a performance or system test or you are capturing data from an existing application in production, set up a filter to limit the amount of data captured. For example, you may want to exclude queries with a near zero duration, or focus on queries executing against a specific database.

Run the Index Tuning Wizard from a Remote Computer

If you want to optimize the indexes on an existing database in production, run the Index Tuning Wizard from a remote computer to minimize the overhead on the database production server.

Update Database Statistics Before You Run the Wizard

The Index Tuning Wizard will give better recommendations if the database statistics are up to date. If you have an existing database that is not too large, you can make a copy of the database, update the statistics, and run the workload and wizard on it.

Use Table Scaling on a Nonproduction Database

If you are running the Index Tuning Wizard on a nonproduction database and are aware that your production database has a different number of rows and you cannot make a copy of it, you can pass this information to the Index Tuning Wizard. In the Select Tables to Tune dialog box, you can specify the number of rows in the production server in the Projected Rows column.

Analyze Recommendations Using SQL Query Analyzer

The Index Tuning Wizard shows the top queries it has determined will benefit the most from the index suggestions. Analyze these queries with and without the index in SQL Query Analyzer. Evaluate the following questions to determine the effectiveness of your index:

• Have you lowered the cost allocation for the specified operation?
• Have you decreased the overall CPU utilization, query duration, and number of read and write operations?

You can monitor the execution plan and the server trace from SQL Query Analyzer itself by selecting the View option for these modes under the Query menu item. If you are using an existing application, do not try the evaluation on the production database; instead, use an equivalent development database.

Apply Validated Recommendations in Production

You can use the Index Tuning Wizard to generate SQL scripts to implement any recommended changes. These scripts can be run in production to implement the necessary changes.

More Information

For more information about using the Index Tuning Wizard, see "Index Tuning Wizard SQL Server 2000" on TechNet at https://technet.microsoft.com/en-us/library/cc966541.aspx.

Step 3. Use SQL Query Analyzer to Optimize Queries and Indexes

The Index Tuning Wizard cannot be used to optimize all types of queries. For example, the wizard will not optimize cross-database queries, queries that use temporary tables, or queries in triggers. You can manually optimize queries within triggers by running them within SQL Query Analyzer.

You can use SQL Query Analyzer to examine the execution plan and cost distribution of queries. To enable the execution plan as well as the trace, use the Show Execution Plan and Show Server Trace menu options under the Query menu.

Analyze the Cost Distribution

Analyze the execution plan of the queries and identify costly operations and operators that have been marked in red and for table scans. Optimize the most costly query operations.

For more information about using SQL Query Analyzer, see "Graphically Displaying the Execution Plan Using SQL Query Analyzer" in the SQL Server Books online.

Identify Indexes to Reduce Cost

Check the feasibility of adding an index to fix any table scans or clustered index scans. Determine whether the execution plan uses existing indexes. Remove any optimization hints in queries or stored procedures.

**Note   **You cannot use indexes for some operations, for example if the table being queried contains too few data rows.

Apply the Index and Reevaluate the Query

Once you have applied the identified index, reevaluate the query in SQL Query Analyzer (as mentioned in the first step) and analyze the cost distribution. The index is useful if such parameters as cost, duration, and number of read operations are lower in the trace tab than they are in the original query.

More Information

For more information about using SQL Query Analyzer to optimize indexes, see "Tuning Indexes" on MSDN at https://msdn.microsoft.com/en-us/library/aa216973(SQL.80).aspx.

Step 4. Defragment Indexes

When initially built, an index has little or no fragmentation. The logical order of index data matches the physical layout of the data on the disk. As data changes, index pages of ordered data might be spread across the disk, resulting in more I/O activity and decreased performance.

Index defragmentation can particularly benefit databases and indexes that have numerous read-only operations. When defragmenting indexes, consider the following suggestions:

• Focus on the larger indexes first, because the computer running SQL Server is less likely to cache them.

• Identify the index defragmentation by using the DBCC SHOWCONTIG command. The Logical scan fragmentation value gives the best indication of defragmentation and should be as low as possible; a value between 0% and 10 % is acceptable. If the index does not span multiple files, the Extents switches value should be as close as possible to the Extents scanned value.

• Rectify the fragmentation by recreating the index or defragmenting it. If you can afford to have the index offline, drop and recreate the index for best performance. You can recreate an index by using DBCC REINDEX. Note that using DBCC REINDEX holds locks and can block running queries and updates.

  If having the index offline or potentially blocking queries is not acceptable, use DBCC INDEXDEFRAG to defragment the leaf-level index pages, which improves the performance of an index scan. Using INDEXDEFRAG is not as effective as recreating the index, but in many cases it is a quicker option. It is also an online option that does not hold long term locks that can block running queries or updates. The time it takes to run INDEXDEFRAG depends on the amount of defragmentation.

For large databases, it is a best practice to defragment the indexes at regular intervals.

More Information

For more information about defragmenting indexes, see "Microsoft SQL Server 2000 Index Defragmentation Best Practices" on TechNet at https://technet.microsoft.com/en-us/library/cc966523.aspx.

Also, see "DBCC SHOWCONTIG" and "DBCC INDEXDEFRAG" in SQL Server books online.

Additional Resources

For more information, see the following resources:

• Chapter 4, "Architecture and Design Review of a .NET Application for Performance and Scalability"
• Chapter 12, "Improving ADO.NET Performance"
• Chapter 13, "Code Review: .NET Application Performance"
• Chapter 14, "Improving SQL Server Performance"
• "Checklist: ADO.NET Performance" in the "Checklists" section of this guide
• "Checklist: SQL Server Performance" in the "Checklists" section of this guide
• "How To: Optimize SQL Queries"

For more information about optimizing SQL indexes, see the following resources:

• Knowledge Base article 311826, "INF: Index Tuning Wizard Best Practices" at https://support.microsoft.com/default.aspx?scid=kb;EN-US;Q311826
• "Improving Performance with SQL Server 2000 Indexed Views" on TechNet at https://technet.microsoft.com/en-us/library/cc917717.aspx
• "Creating and Managing Indexes" in Chapter 6 of SQL Server 2000 Administrator's Pocket Consultant on TechNet at https://technet.microsoft.com/en-us/library/cc917626.aspx#xsltsection126121120120

© Microsoft Corporation. All rights reserved.