SQL Server indexes - Boosting performance in the database

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.

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:

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.

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