We have one SQL Server 2008 on which we run several ETL packages in parallel. This setup has always suffered stability issues.

Since I have taken over I am trying to find a way to increase stability so my team focus on development instead of maintenance.

At the moment the SSIS ETL packages are scheduled using the task scheduler and fine tuning the starting time is how they fix stability issues. When debugging using 1204 error flag we noticed a considerable amount of deadlocks, which are probably the cause of the issues. But the amount of queries to debug and eventually rewrite is a giangantic task.

The ETL duration is about 8h, with some packages sometime overlapping each other.

My question here is to know if using a broker such as Service Broker, or any message broker, instead of the task scheduler, would avoid overlapping, therefore avoid deadlocks, therefore increase stability ?

  • 2
    Not the approach you have in mind, but I think this can help. You can look at the indexes in your database with this free tool brentozar.com/blitzindex. You said you experience blocking\deadlocking, there is a chance that is because your indexing strategy is subpar. This tool might help you to surface easy fixes without spending much time. – Vladimir S. Aug 14 '17 at 6:47
  • 1
    If your blocking and deadlocks include SELECT queries taking S locks, you might try switching the database to READ COMMITTED SNAPSHOT mode. docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/… – David Browne - Microsoft Aug 14 '17 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.