table partition in order to solve frequent deadlock issues - how would I know if it will work?

Question

I use sql server 2005 enterprise edition and have a subscriber DB - part of a transactional replication - that is used for Business Intelligence purposes - they run reports out of it, and SSIS packages read from it and import data into SSAS.

This DB has over 800 GB, some tables have hundreds of millions of records.

All these reports and packages need to read data using READ COMMITTED isolation level.

As this DB is a subscriber DB and the publisher is very busy, replication keeps updating it all the time - whenever the publisher DB is updated.

Sometimes the replication procedures require exclusive locks on tables (or pages or records), and it might be that the reports or packages are running from those tables (or pages or records) and as a result DEADLOCKS are more frequent than what I would like to admit.

Example of a replication procedure involved in a deadlock today:

create procedure [sp_MSdel_dbotblBOrder]
        @pkc1 varchar(20)
as
begin  
    delete [dbo].[tblBOrder]
where [strBxOrderNo] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end  

Just for illustration, I will post below the process that was deadlocked against the procedure above: (it is a monster - and it should be at least a procedure but it is an AD HOC query at the moment)

I could not post the process here, because it is over 30,000 lines.

Anyway, I have been thinking about PARTITION these tables, one by one, starting with the ORDERS table, which is the one that causes most of the DEADLOCKS.

Other than create missing indexes, I just cannot tune all the processes because there are too many.

I believe that my partitioning the orders table (and all of its indexes) I can get a better management of LOCKS, and quicker updates from the replication agents.

I would partition the order table by DAY OF THE WEEK. I would have to create another 6 different filegroups. I have one single drive where I can put the data.

What can I monitor in order to guarantee that this project would work? By work I mean - eliminate or at least reduce a lot of the deadlocks.

How would I know that partition by day of the week is the best way to partition the order table? I could partition by month, but I believe the chunks would still be too big.

I could partition the previous years in an Archive partition, and the current year partition by day of the week.

How would I know that this is the best choice over enabling SNAPSHOT ISOLATION level for this database?

Answer 1

Since your problem is a conflict between constant updates and being able to run reports without facing deadlocks, I would recommend you consider using READ_COMMITTED_SNAPSHOT isolation set ON.

This also has overhead, but usually prevents most deadlocking situations. (Nothing is magic, of course.) You should read the details at:

http://msdn.microsoft.com/en-us/library/ms173763.aspx

Note that there is overhead in tempdb required in order to hold a consistent view of the data, which is needed to avoid the deadlocks. There is another SNAPSHOT isolation level that can be turned on per transaction.

Since this changes the blocking behavior, you may run into some transactions that need to be tuned or given a new index in order to avoid problems.

Therefore, if you use the READ_COMMITTED_SNAPSHOT isolation level, which works fine for me, you will need to monitor it until you are sure that all is well in your instance.