SQL Server indexes made handy

Indexes should be reviewed to ensure prime performance. Weed out valueless indexes in SQL Server, while identifying the best indexes for your application. In this tip learn how to validate and build indexes for best performance.

When starting an application, you should be able to identify many of the indexes based on a reasonable set of rules.

As the application grows and changes, the indexes should be reviewed to ensure no good index candidates are overlooked. It should be based on how the application is used not based on theory. In the same light, make sure erroneous, duplicate or valueless indexes are removed. This is a precautionary measure to make certain your SQL Server does not have to manage unneeded indexes. In this tip we will identify index recommendations, index creation and index validation.

Commonly Indexed Columns

A common question for many DBAs and developers, relates to the traditional columns recommended for indexes. The general recommendations are:

  • Primary Key's
  • Foreign Key's
  • Columns supporting these clauses in SELECT, INSERT, UPDATE and DELETE commands:

    • INNER JOIN
    • RIGHT | LEFT OUTER JOIN
    • WHERE
    • ORDER BY
    • GROUP BY
    • HAVING

Additional index considerations should be:

  • The volume of data – With a small number of rows, a table scan may be as fast and cost less than traversing an index to access the data.
  • The selectivity of the data – When the data has low selectivity, i.e. the same data is stored in the column, the value of the index can be minimal.
  • The processing type – With a transaction processing system, the indexes should be minimized to support insertions as fast possible, compared to a reporting system where querying the data in new and different ways will benefit from extensive indexes.
  • SQL Server 2000 indexing dos and don'ts
  • Indexing Q&A: Answers to your common indexing questions

    How can I identify the needed indexes for my application?

    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 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

    CREATE INDEX [IDX_Job_Desc] ON [dbo].[jobs] ([job_desc])
    GO
    2 CREATE INDEX T-SQL command (SQL Server 2005)
    3 Enterprise Manager (SQL Server 2000)
    4 Management Studio (SQL Server 2005)

    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

  • Tip: Stored procedures: Create and delete constraints and indexes
  • Book Excerpt: Quick steps: 12 indexing tips in 12 minutes
  • Book Excerpt: How to apply indexes in the real world
  • This was first published in September 2006

    Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close