SSIS 2012 – Catalog Indexing Recommendations

2013-11-26 – UPDATE – SQL Server 2012 CU 7 -or- SQL Server 2012 SP1 CU4 addresses the indexing recommendations. See KB 2829948 for more details.

I’ve been sitting on this content for about a year now, and due to external pressure (Thanks Matthew!) I’ve decided to put this into writing. One of the reasons I’ve been sitting on this content is that I didn’t want to share it publicly because the recommendations (after the break) can put you into a state of unsupportability with Microsoft. So with that, you take on the sole risk by implementing my recommendations. After you review them, you’ll see they are pretty basic, much needed, and well vetted. Jamie Thomson has written about these indexes (or rather mentions that they are available) as they have helped with his reporting pack Codeplex project. All-in-all, I now feel pretty safe documenting these, as others are starting to use them and talk about them publicly. With that, here goes.

Out of the box, the new SSIS 2012 catalog database (SSISDB) has some basic indexing applied, with referential integrity set to do cascade deletes between most tables. (You can see the schema here.)

The schema was setup and designed to allow for a maintenance job, SSIS Server Maintenance Job, to purge records out of the SSISDB database to keep it at a manageable size. The problem, as documented previously here, is that the job relies on cascade deletes to do the purge. If you are experiencing problems with this job taking a considerable amount of time, or you feel it may be causing other issues, please read that post.

Also out of the box, there are a few reports shipped with the product – Integration Services Dashboard, All Executions, and All Validations. (As an aside, if these reports are not enough for you, please check out MVP Jamie Thomson‘s Codeplex project – SSIS Reporting Pack) These reports obviously query the SSISDB contents, and as your catalog grows in size, you may find that some of these reports start to get slower and slower over time. Especially the unfiltered “All Executions” report.

One last thing that I experienced in my environment were deadlocks across numerous SSISDB tables.

The thing that ties these things together is this – slow performance. Many months ago, I dove head-first into the SSISDB catalog and began to troubleshoot these performance and deadlock problems I was encountering. After a bit of looking around, it became clear that some basic indexing strategies were not implemented.

First, if you are on SQL Server 2012 RTM, you need to install Service Pack 1. This is a must. It contains fixes for several bugs – a few that I raised and escalated appropriately. Which is actually a good point – all of this data I’m posting here has been shared with the SSIS dev team. I do not always post Connect items for you to vote on, and these indexing strategies are one of those cases.

Here are the recommendations:

Index #1 – ncidxOperation_Id on internal.event_messages

CREATE NONCLUSTERED INDEX [ncidxOperation_Id] ON [internal].[event_messages]
(
	[operation_id] ASC
)

This index facilitates the join back to internal.operations. Additionally, it helps with the foreign key relationship.

Index #2 – ncidxExecution_id on internal.executable_statistics

CREATE NONCLUSTERED INDEX [ncidxExecution_id] ON [internal].[executable_statistics]
(
	[execution_id] ASC
)

This index facilitates the join back to internal.executions. Additionally, it helps with the foreign key relationship.

Index #3 – ncidxExecution_idSequence_id on internal.execution_component_phases

CREATE NONCLUSTERED INDEX [ncidxExecution_idSequence_id] ON [internal].[execution_component_phases]
(
	[execution_id] ASC,
	[sequence_id] ASC
)

This index helps with the deadlocks I was encountering on internal.execution_component_phases. There are a few queries that retrieve values from this table, and this index was necessary in that regard (see stored procedure – internal.append_execution_component_phases as one such query).

Index #4 – ncidxOperation_id on internal.operation_messages

CREATE NONCLUSTERED INDEX [ncidxOperation_id] ON [internal].[operation_messages]
(
	[operation_id] ASC
)

This index facilitates the join back to internal.operations. Additionally, it helps with the foreign key relationship.

Index #5 – ncidxEvent_message_id on internal.event_message_context

CREATE NONCLUSTERED INDEX [ncidxEvent_message_id] ON [internal].[event_message_context]
(
	[event_message_id] ASC
)

This index facilitates the join back to internal.event_messages. Additionally, it helps with the foreign key relationship.

As a bonus, there was one change to a stored procedure that I had to make to also help with the deadlocks on internal.execution_component_phases. At the beginning of the procedure, internal.append_execution_component_phases, I added “SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;” to ensure that this procedure can only run single-file (one at a time).

Together with these changes, I’ve been able to increase the performance of reports, seen deadlocks reduced to nearly zero, and it helped the SSIS Maintenance Job perform its purges more efficiently.

If you’d like to take a look at my scripts, you can download them all in a singe .zip file here – http://www.ssistalk.com/SSIS_2012_Missing_Indexes.zip

14 thoughts on “SSIS 2012 – Catalog Indexing Recommendations

Comments are closed.