Performance Tuning: Implementing Indexes

ne of the most important knobs and levers you have in tuning a system from the database perspective is in your database's indexes. The indexes you apply (or don’t apply) can create performance gains far greater than any hardware changes you can make. Because the effect of the indexes is so great, it’s imperative that you understand what they are and how they are used.

In this tutorial I'll cover the commands you can use to create and alter your indexes. In other tutorials I'll cover the tools and processes you can use to monitor your indexes to see how well they are used, and when they need maintenance.

I'm not going to spend a lot of time here on the hardware side of the speed equation. I'll assume that you're using multi-processors, that the L2-cache is at least one megabyte per processor, that you're using highly optimized network cards, and that you've separated the drives that store the databases from the transaction logs. I'll also assume that you're using SCSI disks with some sort of hardware-based RAID system. 

The hardware part of a SQL Server application design can buy you more speed. But a recent statistic that I saw on a performance tuning Web site stated that, as long as SQL Server has adequate resources, changing out various pieces of hardware will gain you an average of only a 10% speed increase. Considering the cost of high-end hardware, that's not a great return on investment.

Most often, it's not the SQL Server platform settings or your hardware that is running slowly. It's your application or index design that is the problem. SQL Server normally works really fast — but it needs help to locate data. The first thing to do is take a good look at your indexes.

You can place an index on a table in SQL Server versions 2000 and lower, and from 2005 onward you can also even index views. I'm going to cover the process for creating indexes for view in another tutorial. You can also create Full-Text indexes for large text or binary data, and I've covered that in this tutorial.

When you place an index on a table, the server only scans the pages and records the page number where those groups of items are. For instance, if the data being indexed is alphabetical, then it might store the page numbers where all the "A through Cs" are, and so forth. It can then use the index on the table to quickly locate the page the data is on, just as you would in a book. Indexes effectively limit the data being searched, reducing the time spent on the search.

You can place many indexes on a table. In SQL Server 2000/2005 you can have one clustered index and 248 non-clustered indexes, or 249 non-clustered indexes. Each index can have 16 columns, which I'll explain further in a moment.

Designing Indexes

There are quite a few questions to ask before you put an index on a table. I'll cover some of the larger ones here, and we'll explore more questions in other performance tuning articles.

How Big Is The Data?

This question is the first to ask, because if the data isn't very large, SQL Server will always scan the table anyway. If SQL Server is bound and determined to scan the data, why create the overhead of an index?

What is the size limit? Well, there are few caveats, but by and large if the table is composed of fewer than 1,000 rows SQL Server will opt to scan it rather than use an index. It just doesn't make sense to do several I/O (Input/Output) operations to get at the data when the whole thing can be read quickly.

You can use Query Analyzer to find out if you're doing a table scan even when you've created an index. Type in the query in question, and then select the Query menu. Select the Show Execution Plan item, and you'll get a tab to see the path your query takes. You can hover over an icon to see more info about it.

How Often Is The Data Updated?

If your data is written more often than its read, you'll benefit less from an index. That's a bit of a generalization, because even a write can use an index. That's possible because some writes may look up data before they write data. These are the kinds of issues you must think through before you create an index.

What Fields Are Normally Used in Queries?

This is one of the most important questions to ask, and finding the answer will involve the DBA, the development team, and the users. You can use the SQL Profiler, Query Analyzer, and the Index Tuning Wizard to see what indexes might benefit your queries.

How Is The Data Shaped?

What this question is asking is the type of data, the ranges of data, and the size of the data that the table stores. If, for instance, the data is updated frequently, you'll want to create your indexes with a fill-factor that leaves room on each page to add more data, without having to create more pages. If the data is read more often, the fill-factor can be left at a more "full" level.

Creating Indexes

There are several ways to create and modify your indexes. The methods fall into two camps:Automated and Manual.

If you're just starting out, you can certainly benefit from the automated methods of index creation. The first of these in SQL Server 2000 is the Index Wizard. This tool can either watch or generate activity on your database to determine which tables need indexes. It will also create the indexes for you. It's often a good place to start — assuming that you'll come back in later and make sure that the indexes that were created are still valid for production.

In SQL Server 2005, this tool has been replaced with the Database Tuning Advisor, which can not only evaluate indexes, but can actually look at the entire design of your database. 

Another tool is found in Query Analyzer for SQL Server 2000 or the SQL Server Management Studio for SQL Server 2005. To use this indexing feature, open one of those tools, type a query, and then select the Query menu. Select the Show Execution Plan item, and you'll get a tab to see the path your query takes. Once you've run the query, right-click in the Estimated Execution Tab, and then select Manage Indexes.

The manual methods of creating indexes aren't used for designing indexes; they're used to implement designs. The command-based manual method is the CREATE INDEX T-SQL statement. You can read the full format of that command here, but we'll start with a simplified syntax for our example.

First, we need a database to work with. On your favorite test system, open Query Analyzer (SQL Server 2000) or SQL Server Management Studio (SQL Server 2005) and connect. Type in the following commands in a query window:

USE master;
GO
CREATE DATABASE IndexTest;
GO
USE IndexTest;
GO
CREATE TABLE MainTable
( IDNumber int
, Fname varchar(30)
, Lname varchar(30)
, HomeState varchar(30) );
GO

With this test database and one table in place, we now need to fill it with data. I've created a simple script that stuffs almost 200,000 records in the database. We need that many to test the indexes so just copy and paste this code:

DECLARE @IDNumber int
DECLARE @Fname varchar(30)
DECLARE @Lname varchar(30)
DECLARE @HomeState varchar(30)
SET @IDNumber = 1
SET @Fname = ’A’
SET @Lname = ’B’
SET @HomeState = ’C’
WHILE @IDNumber < 200000
BEGIN
INSERT INTO MainTable VALUES (@IDNumber, @Fname, @Lname, @HomeState)
SET @IDNumber = @IDNumber + 1
IF ASCII(@Fname) = 254
BEGIN
SET @Fname = ’A’
SET @Lname = ’B’
SET @HomeState = ’C’
END
SET @Fname = CHAR(ASCII(@Fname) + 1)
SET @Lname = CHAR(ASCII(@Lname) + 1)
SET @HomeState = CHAR(ASCII(@HomeState) + 1)
END
SELECT COUNT (*)
FROM MainTable
GO

When this completes (it took about 2 minutes on my system) you should see the number of rows inserted into the table. Now let's turn on a special output called SHOWPLAN_TEXT and then run a query. The SHOWPLAN_TEXT setting displays what the query processor in SQL Server does to satisfy a query:

SET SHOWPLAN_TEXT ON
GO
SELECT Fname, Lname
FROM MainTable
WHERE Fname <> ’A’ AND Lname = ’B’
GO

The output looks something like this:

 |--Table Scan(OBJECT:([IndexTest].[dbo].[MainTable]), WHERE:([IndexTest].[dbo].[MainTable].[Fname]<>[@1] AND [IndexTest].[dbo].[MainTable].[Lname]=[@2]))

What this shows us is that the query processor had to look through the entire 200K rows to return those rows that don't have 'A' as an Fname value but do have 'B' as an Lname value. Without an index, you'll have a slow query response. Now let's turn off the SHOWPLAN_TEXT option and create an index that covers the last three columns:

SET SHOWPLAN_TEXT OFF
GO
CREATE INDEX TextIndex ON MainTable (Fname, Lname, HomeState);
GO

You can see that I've used the CREATE INDEX statement, followed by the name of the index. The next part is the ON statement, which tells the command which table I'm interested in, followed by parentheses and then the name of the columns I want in the index, in the order I want them indexed. That will become important later.

Now let's turn on the SHOWPLAN_TEXT option again and check to see what the same query does this time:

SET SHOWPLAN_TEXT ON;
GO
SELECT Fname, Lname
FROM MainTable
WHERE Fname <> ’A’ AND Lname = ’B’
GO

Here are the results on my system:

 |--Index Seek(OBJECT:([IndexTest].[dbo].[MainTable].[TextIndex]), SEEK:([IndexTest].[dbo].[MainTable].[Fname] < ’A’ OR [IndexTest].[dbo].[MainTable].[Fname] > ’A’), WHERE:([IndexTest].[dbo].[MainTable].[Lname]=’B’) ORDERED FORWARD)

You can see that now instead of scanning the table, I'm using an Index Seek operation, which is more efficient than a physical table scan. I'll cover more of these return codes later.

You can leave this database on your test system if you wish, since I'll refer back to it in future tutorials. If you want to get rid of it, just enter these commands:

SET SHOWPLAN_TEXT OFF;
GO
USE master;
GO
DROP DATABASE IndexTest;
GO

Remember as data changes are made in your database, the index must be updated, or it becomes useless. If a table is read a great deal, then indexes are great. If they are written to a great deal, they aren't as great. The problem arises in that most tables are both heavily read and heavily written. Another construct, called statistics, stores how relevant the index is to the query. 

Online Resources

There’s a great reference on indexes here, which covers more information on clustered and non-clustered indexes.