SQL Server indexes made handy

How can I identify the needed indexes for my application?

Once the ...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Microsoft SQL Server Performance Monitoring and Tuning SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Optimizing SQL Server indexes –- even when they're not your indexes Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Determining the source of full transaction logs in SQL Server SQL Server Database Modeling and Design Optimizing SQL Server indexes –- even when they're not your indexes Top tips and tricks for SQL Server database development Managing the development lifecycle with Visual Studio Team System 2008 A first look at Visual Studio Team System 2008 Database Edition Testing transaction log autogrowth behavior in SQL Server Top 10 SQL Server Tips of 2008 Tutorial: SQL Server indexing tips to improve performance Tutorial: Learn SQL Server basics from A-Z SQL Server database design disasters: How it all starts Can you shrink your SQL Server database to death? Database Management and Administration Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V How to create SQL Server virtual appliances for Hyper-V Push vs. pull: Configuring SQL Server replication

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

generally recommended indexes are identified, then it is time to determine the best indexes for your application. Below is a list of the SQL Server tools that will help in the identification process.

ID Tool Purpose SQL Version Additional Resources
1 Profiler Identify poorly performing queries as a means to identify potential indexes SQL Server 2000

SQL Server 2005 Tracking query execution with SQL Server 2005 Profiler

SQL Profiler: Features, functions and setup in SQL Server 2005
2 Database Engine Tuning Advisor Analyze data from Profiler or in real time to offer beneficial indexes or partitions based SQL Server 2005 Database Engine Tuning Adviser: How to tune your new SQL Server 2005
3 Index Tuning Wizard Analyze data from Profiler or in real time to offer beneficial indexes SQL Server 2000 Tricks for using the Index Tuning Wizard
4 sys.dm_db_missing _index_columns (Dynamic Management View) Identifies columns that are missing indexes SQL Server 2005 sys.dm_db_missing _index_columns

How do I build my indexes?

Once the generally recommended indexes are identified, then it is time to determine the best indexes for your application. Below is a list of the SQL Server tools that will help in the identification process.

ID Command\Directions Example
1 CREATE INDEX T-SQL command (SQL Server 2000) Create a non-clustered index

2 CREATE INDEX T-SQL command (SQL Server 2005) [IMAGE]
3 Enterprise Manager (SQL Server 2000) [IMAGE]
4 Management Studio (SQL Server 2005) [IMAGE]

How can I validate the indexes are beneficial?

It is great to build indexes, but it is not worth the time to build and maintain indexes without determining if you are benefiting from all of that work. One common scenario you do not want to fall victim to is building indexes that benefit one portion of the application and hinder another portion of the application. One approach to prevent this problem is to build a baseline and as changes are made to the database, run the data collection scripts and compare the results to the baseline. For additional information about baselines, check out this tip on Developing a performance baseline.

Adding some handy indexes to your environment should be a significant performance boost. The challenge is making sure all generally recommended indexes are covered and then to move on to more advanced ways to determine which indexes to select. If you have taken the time to analyze and build the necessary indexes, be sure to validate the performance improvements with a quantifiable measure.

About the author: Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.

More information on SearchSQLServer.com