articles >> performance tuning >> Using Index Intersection to Boost SQL Server ...

Using Index Intersection to Boost SQL Server Performance

By : Neil Boyle Feb 28, 2003

First introduced in SQL Server 7.0, and of course available with SQL Server 2000, Index Intersection gives you new options for creating indexes on tables in order to maximize performance.

To demonstrate how Index Intersection works, I am going to use the "authors" table from the Pubs database to first explain how the current indexes on that table work, then we will look at the options that Index Intersection gives us so we can improve the performance of queries that hit the "authors" table.

In a way, the "authors" table is both a good and a bad example for this article. It is great for demonstration purposes because the Pubs database predates SQL Server 7.0, and so we can see how indexes were chosen without the benefit of Index Intersection. Pubs is also well known and available to everyone. On the other hand, the performance gains from using Index Intersection (if any) for a table of this size and design are fairly negligible.


Without Index Intersection

Imagine you have a table with two columns that you search on regularly as a pair, for example:


USE PUBS
GO

SELECT *
FROM authors
WHERE au_fname = 'Akiko' AND au_lname = 'Yokomoto'


The Pubs database has a non-clustered compound index (i.e. an index with more than one column) on this table, which suits this query perfectly because the index is defined on the columns au_lname and au_fname, in that order. SQL Server will use this index to return results for this query.

The ordering of the indexes columns is important because to use a compound index, the leftmost column of the index must be considered in the WHERE clause (or the JOIN clause of a multi-table query) Because of this, SQL Server will handle the following two queries in different ways.


SELECT *
FROM  authors
WHERE au_lname = 'Yokomoto'

SELECT *
FROM authors
WHERE au_fname = 'Akiko'


The first query from this pair will use the same index to search the table as the first example, because au_lname is the first column defined in the index. However, SQL Server cannot use the same index for the second query, because au_fname is not leftmost in the index definition, and so the optimizer will pick another execution plan, or do a full table scan (au_fname is normally not indexed). You can prove this to yourself by running these queries in Query Analyzer and displaying an execution plan.