Sql Server Database Indexes and Execution Plans

Sql Server Database Indexes

Most ASP.NET web application connect to a database, and you want to make sure the queries and stored procedures used to query the database run as optimal as possible.  If you are not a database administrator (DBA) nor have any formal training with Sql Server, I recommend you at least understand the concept of indexes.  Indexes help you tune your Sql Server database so queries and stored procedures run as efficiently as possible.  Indexes help Sql Server organize and find the data in your tables so that it doesn't have to scan or “manually“ sort every record in your table to give you the information in your request.

In general, you tend to create indexes based on fields used in your WHERE, GROUP BY, and ORDER BY Clauses to give Sql Server a heads-up on how you will want your data presented in the future.  I almost grimace in suggesting such a practice, because you don't want to go crazy with indexes.  They come with a price in terms of disk space and performance on inserts and updates.  However, as a rule of thumb, inspect your WHERE, GROUP BY, and ORDER BY clauses to give you some information to help you tune Sql Server and add your indexes.

 

Clustered Indexes

Many databases have one special index per table where all of the data from a row exists in the index. SQL Server calls this index a clustered index. Instead of an index at the back of a book, a clustered index is closer in similarity to a phone book because each index entry contains all the information you need, there are no references to follow to pick up additional data values.

As a general rule of thumb, every non-trivial table should have a clustered index. If you only create one index for a table, make the index a clustered index. In SQL Server, creating a primary key will automatically create a clustered index (if none exists) using the primary key column as the index key. Clustered indexes are the most effective indexes (when used, they always cover a query), and in many databases systems will help the database efficiently manage the space required to store the table.

When choosing the column or columns for a clustered index, be careful to choose a column with static data. If you modify a record and change the value of a column in a clustered index, the database might need to move the index entry (to keep the entries in sorted order). Remember, index entries for a clustered index contain all of the column values, so moving an entry is comparable to executing a DELETE statement followed by an INSERT, which can obviously cause performance problems if done often. For this reason, clustered indexes are often found on primary or foreign key columns. Key values will rarely, if ever, change.

 

Query Analyzer

Query Analyzer can help you understand what Sql Server is doing in terms of your queries and stored procedures.  It is free and comes with Sql Server.

In particular, it has an option called, Show Execution Plan, that gives you a graphical illustration of what Sql Server is doing to service your request.

 

 

You will want to run your most active queries through Query Analyzer and view the execution plans using a variety of indexes to tune performance.  Note than an index that helps the performance of one query could hurt the performance of another.  Typically you will want to keep a list of the most active queries and stored procedures and give them precedence in how you set indexes.

 

When to Add Indexes

In general, I do not add indexes until the end of a project when I have a good feel for how the application is using the data.  Don't add indexes willy nilly during the development of your application as adding the wrong or too many indexes can actually hurt performance.  Once you are fairly confident with your code base and the implementation of your user stories, finely tune your queries and stored procedures with indexes during the final iterations of your project safely and with confidence.

 

Lookup Table Example

Creating indexes is all about how you are using your database tables, so there are no real hard and fast rules.  However, lookup tables are a particular easy thing to discuss in terms of indexes as the data is inserted and updated quite infrequently and most often used in a read only manner.

Let's say you have two tables, called State and City.  Each State has a list of Cities, and the most often query in your application is asking for a list of Cities in alphabetical order for a particular State:

SELECT ID, Name from City Where StateID = @StateID ORDER BY Name ASC

Adding no additional indexes with ID as your PK, autonumbered and clustered, your Execution Plan for the following query would look like the following.  Sql Server is spending about 25% of its time doing a sort and the rest of the time using the PK to get the records.

 

 

Let's add a new index, IX_City, that indexes the cities by StateID ASC.  Here is the new execution plan.

 

 

Sql Server is now taking advantage of your index to get the data since it is faster, but we still have some overhead with the sorting, etc.  Let's change the IX_City index to StateID ASC and Name ASC.  Here is the new execution plan, which is serviced totally by your new and improved index.

 

 

Indexes - Get a Perspective

Sql Server is fast.  Very fast.  In the example above, there could be very few records in the State and City Tables and you won't be able to notice the difference in performance.  Also, you may be caching lookup tables since they change so little anyway.

Therefore, you have to get a perspective on indexes.  Indexes mainly provide a noticeable difference when you get thousands of records in your tables and when used to help your most active queries and stored procedures.  However, one needs to plan wisely for that day and get the “common sense” indexes in the database so your application doesn't hit a wall and you are to blame.

 

Constant Tuning

As you maintain your application and add new features based on new requirements, you will have to always go back and tune the database.  Queries will change and many more records will be added that force you to reindex your tables to make them run optimally given the new changes.

 

Conclusion

A basic understanding of Sql Server Indexes should be known by all to help you in your development.  Adding the proper indexes to your tables can help keep your application performing well long after hundreds and thousands of records have been added to the tables.  Use Query Analyzer to display the execution plans of your most active queries and stored procedures so you can tune Sql Server and create the indexes wisely.  Best to wait and add indexes toward the latter part of the project after you have a feel for the way the data is queried and consistently tune and reindex your application after you make new enhancements.