I work almost exclusively with third party applications. I don't get to do a lot of tuning except for reports and automating stuff. One of the apps at the law firm where I work is our document managment system (DMS). Over the years I have learned that the vendor did a great job designing the database and indexes. If there are any problems with this app, I know something unusual is happening.

We recently have been migrating our offices one at a time to a new workstation image among other changes. One thing that was added was a log off script that included querying the DMS to pull the most recently modified documents locally. Each office has its own database and things had been going well as far as I knew. That is, until our second largest office was migrated. The script was taking forever to run. I did not write the script, but got involved eventually to help figure out what to do. In fact, when they finally asked for my help, I didn't realize at first that the problem was confined to just one office, just one database.

Looking at the execution plan I saw that there was a very expensing Bookmark Lookup accounting for 100% of the cost of the plan. I checked the execution plan of the same query in one of the other databases and couldn't find the Bookmark Lookup at all. The most expensive part of the plan in the other database was a clustered index seek.

Since multiple databases were located on the same instance, I was able to check the execution plan of both queries in one batch. Doing so showed me that the "Query Cost (relative to the batch)" was 99% for the query running against the problmatic database. Yikes, there was definately a problem here.

I decided to use the Databse Engine Tuning Adviser. It recommended adding an additional index to the large activitylog table. I figured that this meant that one of the indexes was missing from the table and that the recommended index was probably in place on the other databases. When comparing the indexes on the table in the two different databases, I found that all the indexes were in place and that the columns were identical. The only difference was that the all of the indexes in the table in the problem database were non-clustered.

Since the index was not clustered, the app or, in this case the query in the script, used the index but had to go back to the large table to get additional columns. Why was this index non-clustered when it should have been clustered? We acquired this database 5 years ago when we merged with another law firm. Maybe someone there decided that clustered indexes were bad. In fact, as I looked at other tables, there were no clustered indexes on any of them.

So now, armed with additional information, I decided the best plan of action was to ignore what the tuning adviser had recommended and to rebuild the existing index as a clustered index. I scheduled an outage for the DMS application that night and fixed the index as well as the indexes in the other tables. It was easy to know which ones needed to be clustered as the vendor included a "C" at the end of each of the clustered index names.

After the index was rebuilt, I compared the estimated execution plan again in a batch with queries pointing to two databases. Now, the query in the problem database was performing better than the query in the database that performed well earlier that day.

There can only be one clustered index on a table and this determines how the table is organized. Often you will see the primary key column as the clustered index, but this is not a hard and fast rule.

This taught me a few things: First query and index tuning is really fun, always make sure the clustered index is in place, and sometimes people do stupid stuff to perfectly fine databases.