If you’ve been a DBA for a while, you likely have
encountered developers who are extremely proficient
in front-end application design but have a rather
limited understanding of the database technology
they’re using as the back end. In these situations, it
seems that indexes are only applied as an afterthought,
if they are even applied at all.
Once these applications start to process a serious
amount of data, performance problems begin to surface.
This typically happens a few days after the application
is released to production. A DBA at this point might be
asked to take a look and see what can be done.
After some preliminary investigations and performing
a few traces, the DBA might decide to perform
a full index analysis. The Database Engine Tuning
Advisor might be useful here, but I prefer getting to
know my indexes personally rather than relying on a
wizard’s best educated guess. So, I
wrote two T-SQL stored procedures
to aid in this task: sp_GenerateIndexes
and sp_ListAllIndexes, which
work on SQL Server 2005 and SQL Server 2000.
The sp_GenerateIndexes stored procedure generates
SQL statements that can drop and create indexes for a
specified table. By having the SQL code drop and create
indexes, you can do some experimenting by adding or
removing columns as you see fit. If you’re unhappy
with the results, you can simply use sp_GenerateIndexes
again to drop
the new indexes
and re-create the
original indexes
by running the
SQL code that was generated previously. You would, of
course, do this experimentation only in a test environment,
not on your live production server.
The sp_GenerateIndexes stored procedure also creates
an extremely useful report that details the various
properties of each index, such as whether the index is
clustered, the sort order, and the fill factor. Figure 1
contains an example of a report that’s been edited for
space. I tried to keep the column names short so that
I could include as much information as possible in the
report. Here is a brief description of what each column
in the report contains:
- TableName—Specifies the name of the table.
- IndexName—Specifies name of the index.
- ID—Specifies the index ID as retrieved from the
sysindex table. (Note that 0 and 255 aren’t present
because they have special meanings.)
- Clust—Specifies whether the index is clustered.
- Uniq—Specifies whether the index is unique.
- PK—Specifies whether the index is a primary key
constraint.
- IgDup—Specifies whether the index ignores duplicate
entries.
- Stat—Specifies whether the index is really an autogenerated
statistic and not a true index.
- Hyp—Specifies whether the index is really a hypothetical
index created by the Index Tuning Wizard
and not a true index.
- Fill—Notes the original fill factor that was specified
when the index was created.
- PadIdx—Specifies whether index padding is enabled
for the index.
- ColumnNames—Provides a concatenated list of all
the columns in the index.
As you can see from the sample report in Figure
1, the MyTable table has a clustered primary key, a
unique index, and a nonclustered compound index
with a descending sort order on its second column. The
original fill factor was specified as 100 for each index.
What stands out in this report is that the third index is a
compound index that includes the same column as the
clustered index (i.e., TheID). This is wasteful because
each index row in the nonclustered index already contains
a row-locater, which in this case, is the clustered
index key (i.e., TheID).
The second stored procedure that I created—
sp_ListAllIndexes—iterates through all the tables in
the current database, calling sp_GenerateIndexes for
each table found. The output from sp_GenerateIndexes
is captured and stored in a worktable, then displayed at
the end as one big master report.
After running sp_ListAllIndexes, you’ll have a
master report that lists all the indexes on all the tables
in the current database. A quick glance at this report
is often all you need to make a few strategic decisions,
such as whether to add clustered indexes to heap tables
or whether to remove obviously useless indexes. If there
are no indexes to report on, you can bring the empty
report to the development team and politely explain to
them that a database without indexes is about as useful
as a mouse without buttons.
End of Article
Register
