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....

Time to defrag those Vision Indexes

Below is a script that can help you determine if you need to defrag your vision indexes. Enjoy...

//As usual, I provide no guarantees and cannot be liable for the code listed below.
//FYI, DO NOT RUN THIS SCRIPT DURING PEAK HOURS

DECLARE @VisionDatabaseID int
SET @VisionDatabaseID = YOUR_VISION_DATABASE_ID_HERE

SELECT
obj.Name as ObjectName,
idx.name as IndexName,
idx.type_desc as IndexType,
idxStats.Avg_Fragmentation_In_Percent
FROM sys.dm_db_index_physical_stats (@VisionDatabaseID, NULL,NULL, NULL, NULL) AS idxStats
INNER JOIN sys.indexes idx WITH(NOLOCK) ON idxStats.Object_id = idx.Object_id
INNER JOIN sys.objects obj on idx.object_id = obj.object_ID AND idxStats.Index_id = idx.Index_id
WHERE Avg_Fragmentation_In_Percent >= 30
AND idx.[name] IS NOT NULL
order by idxStats.Avg_Fragmentation_In_Percent desc

Sizing up your large vision tables.

Below is a script that will sniff out how large your vision tables are. For simplicity sake, I've assumed that the largest tables in your vision database probably are the LD, TKDetail, Audittrail, ReportLog and ledger tables. You can compare the output produced by the sql below to the results of sp_spaceused to understand the magnitude of your largest tables. Enjoy...

SELECT
o.name,
o.Type,
s.name as SchemaName,
SUM(reserved_page_count) * 8/1024 as ReservedPagesMB,
SUM(used_page_count) * 8/1024 as UsedPagesMB,
SUM( CASE
     WHEN (index_id < 2) THEN (in_row_data_page_count +
                               lob_used_page_count +
                               row_overflow_used_page_count)
     ELSE lob_used_page_count + row_overflow_used_page_count
     END)*8/1024 as TotalPagesMB,
SUM ( CASE
      WHEN (index_id < 2) THEN row_count
      ELSE 0
      END) as recordCount,
SUM( CASE 
     WHEN used_page_count > (CASE
                             WHEN (index_id < 2)
                             THEN ( in_row_data_page_count +
                                    lob_used_page_count +
                                    row_overflow_used_page_count)
                             ELSE lob_used_page_count + 
                             row_overflow_used_page_count END)
THEN used_page_count - (CASE
                        WHEN (index_id < 2)
                        THEN ( in_row_data_page_count +
                               lob_used_page_count +
                               row_overflow_used_page_count)
                        ELSE lob_used_page_count + 
                        row_overflow_used_page_count
                        END) ELSE 0 END)*8/1024 as IndexSizeMB,
SUM(CASE 
    WHEN reserved_page_count > used_page_count
    THEN reserved_page_count - used_page_count
    ELSE 0 END) *8/1024 as UnusedMB
FROM sys.dm_db_partition_stats dps
inner join sys.objects o on dps.object_ID = o.object_id
inner join sys.schemas s on o.schema_id = s.schema_id
WHERE o.name IN('LD','TKDetail','LedgerAR','LedgerMISC','LedgerEX','LedgerAP','ReportLog','AuditTrail')
group by o.name,o.Type, s.name