No Clustered Index for LD?

If you have ever looked into the performance of vision, more than likely you have at some point in time taken a look at the LD table.

I was looking at the indexes for the LD table the other day, and noticed that there was no clustered index defined for LD. At first I found this troubling, but then thought about how clustered indexes work.

To get you started, the query below will give you a list of indexes that exist on LD. Unless you have added a clustered index yourself, you will see that there is no clustered index defined.

select * from sys.indexes where object_id IN(select object_id from sys.objects where name = 'ld')

A clustered index actually contains the data in order at the leaf level. Imagine if a clustered index existed, and that the clustered index was based on the columns wbs1, wbs2 and wbs3. That's seems reasonable on a labor table. Now imagine that you have the following ranges of projects in your databases starting a project number 1 (0.000.001) through project number 10000. (0.010.000).

So let's say your Deltek Vision administrator posts some time keeper data and several of your employees worked on project 0.000.500. Because a clustered index stores data in order at the leaf level, SQL Server would have the task of page splitting and moving data to accommodate the insertion of data after project 0.000.499. This would cause alot of overhead for the SQL Server. And due to the fact that every single bucket on a timekeeper timesheet becomes an individual labor detail record, this would cause the posting of labor to take much longer. In addition, labor transfers and adjustments would also take much longer during interactive billing.

After talking with another fellow dba, he reminded that many times Deltek's service packs or upgrade database scripts contain the creation of a clustered index and then the dropping of the index immediately following. Consider the following from a recent upgrade SQL file from a Deltek vision upgrade.

Print ' LD'
create clustered index junk on ld(wbs1, wbs2, wbs3)
go
drop index ld.junk
go

The above script is taking the LD table, which is a heap, and is applying a clustered index to it which forces an ordered storage of the data. The creation process removes empty space filled by forward pointers. The clustered index is then dropped.

Deltek probably at one point had a clustered index on the LD table, removed it due to performance issues. Then they probably added the above script to upgrades to help keep the LD table in working order.


Enjoy....