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]
where [strBxOrderNo] = @pkc1
if @@rowcount = 0
exec sp_MSreplraiserror 20598
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?