SQL Server Magazine

By using simple tuning techniques, you can make a big difference in the performance of certain queries. For example, the technique I demonstrated in "Are You in Tune?" July 2001, involved expressing in the form of a SARG any conditions in a query's WHERE clause that referenced indexed columns. A SARG is a search condition, expressed in a particular format; for details, see last month's column. Let me demonstrate how you can verify what I told you about SARGs and indexed columns. Then, let's explore ways that you can tell whether SQL Server is using an index and whether using the index is useful.

Northwind's Strange Index Design
To begin, let's look at some examples from the Northwind database, which automatically installs with SQL Server 7.0 and later. (If you've experimented with the Northwind database, you might want to rebuild it so that you're working with the same data and table structures that I am.) You might have noticed that Northwind has some problems with its index design. If you use the sp_helpindex procedure to examine the existing indexes on the existing tables, you'll see that in some cases, SQL Server has created multiple indexes of the same type on the same columns. Nothing in SQL Server prevents this duplication; the only restrictions are that you can have only one clustered index and that a table can't have two indexes with the same name. But you can create two indexes with different names on the same column. For example, look at the indexes on the orders table by executing the following statement:

EXEC sp_helpindex orders

In the output, note that both the EmployeeID column and the CustomerID column have two nonunique nonclustered indexes. Although SQL Server allows this duplication, you get no benefit from multiple indexes on the same set of columns. In fact, if you're modifying data in the table, you actually incur a penalty because SQL Server needs to maintain all the indexes to reflect the data modifications. I have no idea why the Northwind database has this strange set of indexes on its tables, but I want you to be aware of it.

Now let's look at the following query:

-- Query 1
USE Northwind
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT * FROM orders
WHERE customerID = 'CENTC'

This query uses a SARG on the CustomerID column, which has a nonclustered index. You can use either of two SET options to find information about the use and benefit of an index. If you use SET SHOWPLAN_TEXT ON before you run the query, SQL Server reports the execution plan it used, but it doesn't actually execute the query. To execute the query and see the results, you have to use SET SHOWPLAN_TEXT OFF. You can use SET STATISTICS_IO ON to see the I/O cost of running the query, but you won't get any information about statistics if SHOWPLAN_TEXT is on, because the query won't run. Therefore, SHOWPLAN_TEXT and STATISTICS IO are mutually exclusive.

Now, turn on another option called STATISTICS PROFILE. This option lets the query execute and shows the execution plan—and you can use it in conjunction with STATISTICS IO.

   Prev. page   [1] 2 3 4     next page