表分區以解決頻繁的死鎖問題 - 我怎麼知道它是否會起作用？

我使用 sql server 2005 企業版，並有一個訂閱者數據庫——事務複製的一部分——用於商業智能目的——他們從中執行報告，SSIS 包從中讀取數據並將數據導入 SSAS。

這個數據庫有超過 800 GB，一些表有數億條記錄。

所有這些報告和包都需要使用 READ COMMITTED 隔離級別讀取數據。

由於此數據庫是訂閱者數據庫並且發布者非常忙，因此複製會一直更新它 - 每當更新發布者數據庫時。

有時復製過程需要對錶（或頁面或記錄）進行排他鎖，並且可能是報告或包是從這些表（或頁面或記錄）執行的，因此死鎖比我想要的更頻繁承認。

今天涉及死鎖的複製過程範例：

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  

只是為了說明，我將在與上述過程陷入僵局的過程下方發布：（它是一個怪物 - 它應該至少是一個過程，但它目前是一個 AD HOC 查詢）

我無法在此處發布該過程，因為它超過 30,000 行。

無論如何，我一直在考慮對這些表進行分區，從 ORDERS 表開始，這是導致大多數 DEADLOCKS 的表。

除了創建失去的索引之外，我只是無法調整所有程序，因為太多了。

我相信通過對訂單表（及其所有索引）進行分區，我可以更好地管理 LOCK，並更快地從複製代理進行更新。

我會按星期幾對訂單表進行分區。我將不得不創建另外 6 個不同的文件組。我有一個可以放置數據的驅動器。

我可以監控什麼以確保該項目能夠正常執行？我的意思是工作——消除或至少減少很多死鎖。

我怎麼知道按星期幾分區是對訂單表進行分區的最佳方式？我可以按月分區，但我相信這些塊仍然太大。

我可以在存檔分區中對前幾年進行分區，並將目前年份按星期幾進行分區。

我怎麼知道這是為該數據庫啟用 SNAPSHOT ISOLATION 級別的最佳選擇？

由於您的問題是不斷更新和能夠執行報告而不會遇到死鎖之間的衝突，因此我建議您考慮使用READ_COMMITTED_SNAPSHOT隔離集ON。

這也有成本，但通常可以防止大多數死鎖情況。（當然，沒有什麼是神奇的。）您應該在以下位置閱讀詳細資訊：

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

請注意，tempdb為了保持數據的一致視圖需要成本，這是避免死鎖所必需的。SNAPSHOT每個事務可以打開另一個隔離級別。

由於這會改變阻塞行為，您可能會遇到一些需要調整或賦予新索引以避免問題的事務。

因此，如果您使用READ_COMMITTED_SNAPSHOT對我來說很好的隔離級別，您將需要對其進行監控，直到您確定您的實例中一切正常。

引用自：https://dba.stackexchange.com/questions/89156