Some additional input for improving the health of the SD database, which will lend to performance improvements of the System Delivery Engine:
1) Cleanup and delete obsolete software packages from the DSM Software Library. There's a database tool within WinOffline that will help identify which software packages are "unused" and not installed on any agents.
2) Run CleanApps, to remove duplicate usd_applic records from the database. You can find the steps to do this here: Software Delivery Job Container Build Failed - CA Knowledge
3) While CAF is stopped, run these queries to cleanup orphaned records in the software delivery database tables. Be sure to terminate/delete all existing job containers first. If any fail to terminate on their own, this script will also fix that:
print 'Cleanup jobs in terminating state.'
declare @var_objectid varchar(50)
declare job_delete cursor for
select objectid from usd_job_cont where state=8 and objectid in (select jobcont from usd_jcview) and objectid in (select jcchild from usd_link_jc) and objectid in (select jcont from usd_link_jc_act)
open job_delete
fetch job_delete into @var_objectid
while (@@fetch_status=0)
begin
delete usd_jcview where jobcont=@var_objectid
delete usd_link_jc where jcchild=@var_objectid
delete usd_link_jc_act where jcont=@var_objectid
delete usd_cc where objectid=@var_objectid
delete usd_job_cont where objectid=@var_objectid
fetch job_delete into @var_objectid
end
close job_delete
deallocate job_delete
go
print 'Mark usd_applic records, referencing a non-existent usd_activity, for cleanup.'
update usd_applic
set activity=0x00000000000000000000000000000000
where activity <> 0x00000000000000000000000000000000
and activity not in (select objectid from usd_activity)
go
print 'Cleanup usd_applic records orphaned from any activation procedure.'
delete from usd_applic where actproc not in (select objectid from usd_actproc) and actproc <> 0x00000000000000000000000000000000
go
print 'Cleanup usd_applic records orphaned from any usd_target (i.e. non-existent target).'
delete from usd_applic where target not in (select objectid from usd_v_target)
go
print 'Cleanup orphaned usd_activity records.'
delete from usd_activity where objectid not in (select activity from usd_link_jc_act)
go
print 'Cleanup orphaned usd_actproc records.'
delete from usd_actproc where rsw not in (select objectid from usd_rsw)
go
print 'Cleanup orphaned usd_link_swg_sw records.'
delete from usd_link_swg_sw where sw not in (select objectid from usd_rsw)
go
The combination of all three of these actions should improve the overall performance of software delivery and the delivery engine.
-Brian