Jan 312013
 

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

  11 Responses to “SSIS 2012 – Catalog Indexing Recommendations”

  1. This is great information. I am putting together a presentation on the new SSIS Catalog and with your permission I’d like to reference these items.

  2. Sounds good Mike. Let me know if you have any questions.

  3. Great stuff, thanks. One question though: what happens if any of these tabels/procedures get changed in some CU or SP?

  4. [...] writing this article I ran across this excellent article on SSIS 2012 Catalog Indexing, and time permitting I will implement his suggestions and report back. Are any of you having [...]

  5. [...] deadlocking issues. Ultimately, this led Phil Brammer (blog|twitter) to post recommendations for optimizing the SSIS Catalog by adding neglected indexes. On our team, we implemented Phil’s recommendations to great [...]

  6. This article presents clear idea designed for the new people of blogging, that truly how to do
    blogging and site-building.

  7. Thank you for the good writeup. It in fact was once a enjoyment account
    it. Look complicated to more introduced agreeable from you!
    However, how could we keep in touch?

  8. [...] have pointed out the problem of missing indexes on SSISDB. Phil Brammer shared a missing index creation script via SSIS 2012 – Catalog Indexing Recommendations. I was [...]

  9. Phil, thanks for this. However, I’ve applied all of the indexes and have now altered the stored procedure, in hopes of it helping. We’ll see. It helps, but not enough.

  10. Thanks Phil for this very helpful post. My testing showed these indexes greatly improved the speed of the SSIS Server Maintenance Job. E.g. for a fairly 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).

    I’ve taken the liberty of raising this on Connect with a reference to your post:
    https://connect.microsoft.com/SQLServer/feedback/details/803880/integration-services-catalog-ssisdb-database-indexes-for-ssis-server-maintenance-job

    Anyone who strikes this issue and finds Phil’s indexes useful, please upvote – thanks.
    Mike

  11. Looks like all of these index recommendations have been incorporated into one of the cumulative updates. I’m currently running SQL 2012 SP1 CU4.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>