The Database Design Resource Center

SQL Server indexes - Boosting performance in the database

SQL Server indexes:

A couple of years ago your IT department made the investment to purchase SQL Server in order to manage and maintain your data and information.

This was an investment in our future and the future of our organization. We had evaluated the database options available to us, and decided to go with SQL Server.

After a while, we were wondering why the database that promised such robust performance for our dollar was not working properly and was taking way too long to return the records that we needed from it.

Every operation you do involving the databases that you have seem to take longer and longer with each passing day.

You know all of the data is there and you need to access it, however, with the delays you experience you become frustrated and give up.

Why not evaluate the normalization of your database design, gather statistics on your databases and have a look at what indexes are present on your databases and what SQL Server indexes may need to be added in order to get the performance that you require and deserve?

SQL Server indexes are used for many reasons.Some of those reasons are listed below:

  • To ensure data integrity
  • To ensure data accuracy
  • To speed up data storage and retrieval
  • To help the database engine manage the data that it is responsible for

Indexes are a good practice to use when working with databases because they can definitely have an impact on the performance of some operations that take place in the database.

For example, I have seen a query take over twenty-five minutes until proper indexes were added to the table based on usage and needs.

After the indexes were added the same query took under 10 seconds to run and return the data from the database.

While indexes are a good thing to utilize in a database architecture and design, they can be overdone just like any good thing.

Each index added will require some additional resources to be used by the server when an operation involving the table or tables in question takes place.

Therefore, the more indexes you add to a table, the more checks and balances the database engine has to maintain before the operation can be completed and the results returned to the user.

How many indexes are enough? That is up to the designer and relies on the situation at hand. If a table already has a couple of indexes associated with it and another needs to be added, the index may or may not make performance any better.

Adding more indexes to a table can increase performance and reduce the time it takes to perform an operation, however, they should be used with discretion.

SQL Server allows the user to view statistics and the performance plan associated with statements that are going to be executed against the database. These aids can help diagnose problems that may be helped with the addition of new indexes or the subtraction of existing ones.

After the decision has been made to work with the SQL Server indexes used with a particular table in a database, Enterprise Manager can be used to manage the indexes of that table.

In order to work with the indexes assigned to a table, expand the database that contains the table you want to work with, expand the tables in that database and right click on the table in question.

Select the All Tasks menu option, which will bring up the submenu options desired. The following illustration shows the menu options described above.

SQL Server indexes

After these menu options become available, select the Manage Indexes… menu option in order to work with the indexes associated with that table. The index management is accomplished using the following interface:

SQL Server indexes

The first option shows the database that you are currently working with and can be changed from within the interface to switch to another database. The next option shown is the selection of the table that you want to manage the indexes for. The lower half of the screen shows the existing SQL Server indexes that the table selected has.

There are also command buttons on the bottom portion of the screen that allow you add an index, edit an existing index or delete an existing index. In order to add a new index, click the New button and the following dialog will be presented to the user.

SQL Server indexes

The first step that is needed when creating a new SQL Server index is to assign the index a unique name within the database so that it can be distinguished from other indexes.

The next portion of the index creation screen is a table that allows the selection of the fields that make up the index.

There can be more than one field used to make up an index. For example, if you were to have fields called firstname, middlename and lastname, they could be indexed as one field to allow the searching for the full name to perform better on the server.

The order of the columns in the index can be sorted based on how the index needs to be arranged within the database with the Up and Down command buttons on the screen.

After the fields that make up an index are selected, the options associated with that index must be selected in order to help describe the way that index will function within the database management system.

Some of these options can be set whether or not the index will contain unique values, whether the index will ignore duplicate values or whether or not the statistics will be recomputed based on the index.

Unique values dictate whether or not the index can contain duplicate values.

Ignoring duplicate values will instruct the database engine to allow duplicate values in the SQL Server indexes selected.

A very important option is the computation of statistics. Statistics allow the database to compile information about the SQL Server indexes and data in the database to speed up performance when the database is used. Once the index is complete, click the OK button to save the changes.

Return to SQL Server DBA

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © /
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.