Are There Any Indexes that are Not Being Used in Queries?
This is another obvious piece of advice, but is also a common problem, especially if the initial indexes created for the database were “guessed at” by DBAs or developers before the database went into production. Just looking at a table’s indexes won’t tell you if they index is being used or not, so identifying unused indexes is not always easy.
One of the best ways to identify unused indexes is to use the Index Tuning Wizard, which was previously discussed.
Unnecessary indexes, just like duplicate indexes, wastes disk space and contribute to less than optimal data access and modification performance.
Are the Indexes too Wide?
The wider an index, the bigger the index becomes physically, and the more work SQL Server has to perform when accessing or modifying data. Because of this, you should avoid adding indexes to very wide columns. The narrower the index, the faster it will perform.
In addition, composite indexes, that include two or more columns, also present the same potential problem. Generally, composite indexes should be avoided, if at all possible. Often, the heavy use of composite indexes in a database means that the database design is flawed.
You can’t always avoid indexing wide columns or using composite indexes, but if you think you have to use one, be sure you have carefully evaluated your choice and are confident you don’t have other options that may offer better performance.
Are Tables That are JOINed Have the Appropriate Indexes on the JOINed Columns?
In essence, the column (or columns) used in tables being JOINed should be indexed for best performance. This is straight-forward advice and fairly obvious, but auditing your indexes for optimal JOIN performance is not easy, as you must be familiar with all the JOINs being performed in your database in order to fully perform the audit.
Many people, when creating primary key/foreign key relationships (which are often used in JOINs) forget that while an index is automatically created on the primary key column(s) when it is established, an index for a foreign key is not automatically created, and must be created manually if there is to be one.
Because this is often forgotten, as part of your audit, you may want to identify all primary key/foreign key relationships in your tables and then verify that each foreign key column has an appropriate index.
Besides this, you can also use the Index Tuning Wizard can help identifying missing JOIN indexes, but I have found that the Wizard doesn’t always identify missing indexes for JOINed tables. When it comes right down to it, unless you know the types of common JOINs being run against your database, it is tough to identify all the columns that could benefit from an appropriate index.
Are the Indexes Unique Enough to be Useful?
Just because a table has one or more indexes doesn’t mean that the SQL Server Query Analyzer will use them. Before they are used, the Query Optimizer has to consider them useful. If a column in a table is not at least 95% unique, then most likely the query optimizer will not use an available non-clustered index based on that column. Because of this, don’t add non-clustered indexes to columns that aren’t at least 95% unique. For example, a column with “yes” or “no” as the data won’t be at least 95% unique, and creating an index on that column would in essence create an index that would never be used, which we have already learned puts a drag on performance.
For more information on selectivity, see this article.
As part of your audit, consider “eye-balling” the data in your tables. In other words, take a look at the data residing in your tables, and take an extra look at the columns that are indexed. Generally, it is very obvious if the data in a column is selective or not. If you notice that the data is all “male or female,” “y” or “n,” and so on, then this data is not selective and any indexes created on them will be a waste of space and a hindrance to performance.
Are You Taking Advantage of Covering Indexes?
A covering index, which is a form of a composite index, includes all of the columns referenced in the SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn’t have to look up the actual data in the table, reducing logical and/or physical I/O, thus boosting performance. While non-covering composite indexes can hinder performance, covering composite indexes can be very useful, and in many cases, really boost the performance of a query.
The hard part is to identify where covering indexes might be best used. While the Index Tuning Wizard will help, it will still miss a lot of opportunities where covering indexes can be useful. Otherwise, the only way to identify if they will be useful is to carefully examine all the common queries that run are across your database, which of course is near impossible, unless you are really, really bored and have nothing better to do.
At this point, in your audit, the goal should not be to identify new covering indexes as such, but to be aware of them so you can take advantage of them when you run across a situation where they will be helpful.