The SQL Server Partition Management Utility (http://sqlpartitionmgmt.codeplex.com/) is one of the best tool used to manage the partition-switch operations. It is a command line tool and can be integrated in a SSIS package or used to generate the T-SQL scripts needed in a regular “sliding window” partition management scenario. A blog post that shows how to use this tool is this one.
In my case, I wanted to speed the loading of a big partitioned fact table through a SSIS package (that calls two child packages). So this package calls more instances of the tool in order to load more than one staging table in parallel. Each staging table is related to a fact table partition. After each staging table is loaded, the SSIS package loads the target fact table using the partition-switch operations against the staging table.
All seemed to work fine, but during the test phase, when I tried to increase the degree of parallelism (that is the number of executed instances of the tool), I got a deadlock error.
After spending some hours on this issue, I realized that the package was well developed and that the cause of the issue was the tool. The deadlock was occurring when an instance of the exe was trying to read from sys.tables and a different one was trying to alter the schema with an ALTER TABLE command. So I decided to fix the bug by myself setting the READ UNCOMMITTED transaction isolation level at the beginning of the main transaction. Now my SSIS package works like a charm.
Even though I had answered to a discussion detailing the fix here and a colleague of mine opened another issue about a missing throw of an exception, the project owner never took part to the discussion. So I supposed he abandoned the project and I decided to fork it on GitHub, including the fixes to the two above mentioned issues. You can find the repo here:
SQL Server Partition Management Utility
Enjoy the power of this tool and feel free to participate to the project as a contributor!
