How SQL Server Determines an Execution Plan Using Available Indexes and Statistics

Because SQL Server expects 144 rows, the effort of filtering the records for the second parameter is predicted to be faster than to use two indexes and cross referencing these. However, the actual row count is much higher, causing this plan to be far from ideal.

Even though the number of occurrences for 'Smits' is approximately half of the number for 'Smit', this query takes three and a half minutes to execute while the query for Smit only takes about 1 second.

Work Around

So how do you solve this problem? Statistics can not be expanded. They are designed to be kept small, so that the process of determining an execution plan is kept small. Even if the statistics could be doubled in size, this problem will still arise.

The option of using an index hint will only be helpful if used in all the queries with a search parameter in this field. Adding an index hint is a manual job, requiring knowledge of the dataset. A human will encounter the same difficulties in determining a best use of indexes as SQL Server itself. Also, automating the use of index hints in a production environment is time-consuming and far from efficient.

In these situations, a work around is the best option. By changing the specific index into a clustered index, a bookmark lookup is no longer necessary. This saves considerable time and effort. If the statistics are off and the number of hits exceeds the expected number, all relevant records are clustered together and require little extra time.

In the above examples, if the index on gnaam is a clustered index, the execution plan is as follows:

Figure 6: Execution Plan - Search for Smits and Kerkstraat with Clustered Index

This query takes 7 seconds to execute. A significant improvement.

Index Planning

The effect of changing currently existing clustered index into a non-clustered index should be examined. There can only be one clustered index on a table. In general, the difference in performance for a field containing unique values is minimal between a clustered and a non-clustered index. If, however, this field is used in a one to many relationships, the general performance for the overall queries to the database might decline.

It is wise to take all these aspects into consideration when designing the index structure for your databases. It is often worth the effort to try different configurations and go with the one best suitable. Of course using the Index Tuning Wizard will help in this process.

Nils Bevaart has been a DBA for 5 years. He works for a Dutch government agency specializing in civilian registration. He has experience with SQL Server 2000 administration, performance tuning, and security. He is married and has a daughter.

Copyright 2005 by the author.