Home Dashboard Directory Help
Search

Integration Services Catalog (SSISDB Database) Indexes for SSIS Server Maintenance Job by Mike Honey - Manga Solutions


Status: 

Closed


1
0
Sign in
to vote
Type: Suggestion
ID: 803880
Opened: 9/29/2013 11:05:05 PM
Access Restriction: Public
0
Workaround(s)
view

Description

As installed, the Integration Services Catalog (SSISDB Database) Indexes do a poor job of assisting the SSIS Server Maintenance Job to purge old log records. This issue is described and some index recommendations are provided in this blog post:
http://www.ssistalk.com/2013/01/31/ssis-2012-catalog-indexing-recommendations/

My testing showed these indexes greatly improved the speed of the SSIS Server Maintenance Job. E.g. for a major purge (e.g. removing 30m rows from internal.event_messages table) the intial run took 4 hours to delete only 4m rows (I cancelled the job at that point). After creating the recommended indexes the remaining 26m rows were deleted in "only" 1 hour (still not great).

Environment is SQL 2012 SP1 Developer Edition, Windows Server 2008 R2 SP1 x64, 16GB RAM, 4 x Xeon 2.4GHz CPU, Virtualized on VM Ware.
Details
Sign in to post a comment.
Posted by Microsoft on 10/7/2013 at 1:20 PM
Hello, Mike.
This has already been addressed in a recent Cumulative Update (CU) for SQL 2012. The information given says you have SP1, so can find CUs here: http://support.microsoft.com/kb/2772858
For anyone else reading this post, however, while we always recommend updating to the newest service pack, we still support you if you don't. You can find this CU for SQL 2012 RTM here: http://support.microsoft.com/kb/2692828/en-us
For the record, while we still always suggest getting the latest and greatest, to address this particular issue, you'll need CU4 or higher with SP1 or CU7 and up for RTM.
-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Sign in to post a workaround.