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

Vision's Impact on TempDB

Below is a script that you can run throughout the day to understand your Deltek Vision environment's impact on the tempdb database.

//As usual, I provide no guarantees and cannot be liable for the code listed below.

select
tskUse.session_id,
tskUse.request_id,
ses.host_name,
ses.program_name,
req.cpu_time,
req.reads,
req.writes,
req.granted_query_memory*8/104 as granted_query_memoryMB,
tskUse.task_allocation_count*8/1024 as task_allocation_countMB,
tskUse.task_deallocation_count*8/1024 as task_deallocation_countMB,
(SELECT text FROM sys.dm_exec_sql_text(req.sql_handle)) AS TheQuery
from ( Select session_id, request_id,
sum( internal_objects_alloc_page_count +
user_objects_alloc_page_count) as task_allocation_count,
sum (internal_objects_dealloc_page_count +
user_objects_dealloc_page_count) as task_deallocation_count
from sys.dm_db_task_space_usage
where session_id > 50
group by session_id, request_id) as tskUse
inner join sys.dm_exec_requests as req on tskUse.session_id = req.session_id and tskUse.request_id = req.request_id
inner join sys.dm_exec_sessions as ses on req.session_id = ses.session_id
where req.database_id IN(select database_id from sys.databases where name = 'Vision')
order by tskUse.task_allocation_count DESC

10IT014: Senior Database Engineer

Looks like Deltek is looking for a Senior Database Engineer.

Returning values from mulitple records as a single column

There are times where you want to rollup single column data from mulitple records and present as a single column as part of a result set. Sure, Pivot does essentially the same thing, but you must know the values of the data and the pivot returns multiple columns, not mutliple values as one column.

The solution below comes in very handy for reporting purposes where you really want 1 line of data with associated with mulitple columns.

Enjoy..

DECLARE @StateList VARCHAR(MAX), @ClientID varchar(32)
SET @ClientID = 'YOUR_CLIENT_ID_HERE'
SELECT  @StateList = COALESCE(@StateList + ' , ','') + ClientStateList.state 
from
 (select distinct state 
  from claddress 
     where clientid = @ClientID) as ClientStateList

select name, @StateList as USLocations from cl where ClientID = @ClientID

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

Deltek Consulting Companies

Below is a list of Deltek Consulting Companies that I have come across while looking for external resources.

Incortech
http://deltekvisionsoftware.com/

Central Consulting Group
http://www.centralconsultinggroup.com/

Innovative Solutions Group
http://www.isgltd.com/

KPI Insight Consulting
http://www.kpi-insight.com/

ITB Consulting
http://www.itbconsultinginc.com/

Avanced Enterprise Systems
http://www.aesystems.net/

Strategies Group
http://www.strategiesgroup.com/

Loren Saunders
http://saundersconsultinggroup.com/deltek.html

Hirsch Consulting
http://www.hirschtech.com/DeltekVision.asp

Silver Edge
http://www.silveredge.com/

Ideal Solutions Group
http://www.idealsolutionsgroup.com/deltek.html

Computer Analytics
http://www.cacorp.com/IT%20Consulting_deltek.html

Keystone Business Solutions
http://keystonebusinessconsultants.com/service01a.html