sql - with - 如何建索引




優化SQL Server上的刪除 (10)

在sql server上Delete s有時很慢,我經常需要優化它們以減少所需的時間。 我一直在尋找有關如何做到這一點的提示,我發現了各種各樣的建議。 我想知道你最喜歡和最有效的技術,以馴服刪除野獸,以及它們的工作方式和原因。

到現在:

  • 確保外鍵有索引

  • 確保索引的條件

  • 使用WITH ROWLOCK

  • 銷毀未使用的索引,刪除,重建索引

該你了。


(如果索引是“未使用”的,為什麼它們一直存在?)

我過去使用的一個選項是批量完成工作。 粗略的方法是使用SET ROWCOUNT 20000 (或其他)和循環(可能帶有WAITFOR DELAY )直到你全部擺脫(@@ ROWCOUNT = 0)。

這可能有助於減少對其他系統的影響。


2014-11-05答案摘要

這個答案被標記為社區維基,因為這是一個不斷變化的主題,有很多細微差別,但整體上答案很少。

第一個問題是你必須問自己你正在優化的場景是什麼? 這通常是在db上具有單個用戶的性能,或者在db上具有許多用戶的擴展。 有時答案恰恰相反。

對於單用戶優化

  • 提示一個TABLELOCK
  • 刪除刪除中未使用的索引,然後重建它們
  • 批量使用SET ROWCOUNT 20000 (或其他任何東西,取決於日誌空間)和循環(可能帶有WAITFOR DELAY ),直到你全部刪除( @@ROWCOUNT = 0
  • 如果刪除一大部分錶,只需創建一個新表並刪除舊表
  • 對要刪除的行進行分區,然後刪除該分區。 [閱讀更多...]

用於多用戶優化

  • 提示行鎖
  • 使用聚集索引
  • 如果刪除了大塊,則設計聚簇索引以最小化頁面重組
  • 更新“is_deleted”列,然後在維護窗口期間稍後進行實際刪除

用於一般優化

  • 確保FK的源表上有索引
  • 確保WHERE子句具有索引
  • 使用視圖或派生表標識要在WHERE子句中刪除的行,而不是直接引用該表。 [閱讀更多...]

在非常大的表上,您有一組非常具體的刪除標準,您還可以對錶進行分區,切換分區,然後處理刪除。

SQLCAT團隊一直在非常大量的數據上使用這種技術。 我在here找到了一些引用,但我會嘗試找到更明確的東西。


如果UPDATES比DELETES更快,您可以添加一個名為DELETED的狀態列,並在您的選擇中對其進行過濾。 然後在晚上運行一個執行實際刪除的proc。


您可能會對以下文章“ 快速訂購刪除操作 ”感興趣。

執行快速SQL Server刪除操作

該解決方案側重於利用視圖以簡化為批量刪除操作生成的執行計劃。 這是通過引用給定的表一次而不是兩次來實現的,這反過來減少了所需的I / O量。


您是否啟用了引用完整性的外鍵? 你有觸發器有效嗎?


我對Oracle有更多的經驗,但很可能同樣適用於SQL Server:

  • 刪除大量行時,發出表鎖,因此數據庫不必執行大量行鎖
  • 如果您刪除的表被其他表引用,請確保其他表在外鍵列上有索引(否則數據庫將對另一個表上的每個已刪除行執行全表掃描以確保刪除行不違反外鍵約束)

我想知道是不是垃圾收集數據庫的時候了? 您將行標記為刪除,服務器稍後在掃描期間將其刪除。 你不希望每次刪除都有這個 - 因為有時一行必須現在去 - 但它偶爾會很方便。


有刪除然後刪除。 如果要將數據作為修剪作業的一部分老化,則希望能夠通過聚簇鍵刪除連續的行塊。 如果你必須使來自不連續的高容量表的數據老化,那將非常非常痛苦。


簡化WHERE子句中函數的任何使用! 例:

DELETE FROM Claims
WHERE dbo.YearMonthGet(DataFileYearMonth) = dbo.YearMonthGet(@DataFileYearMonth)

這種形式的WHERE子句需要8分鐘才能刪除125,837條記錄。

YearMonthGet函數組成一個日期,其中包含輸入日期的年份和月份,並設置day = 1 。 這是為了確保我們根據年月而不是月中刪除記錄。

我將WHERE子句重寫為:

WHERE YEAR(DataFileYearMonth) = YEAR(@DataFileYearMonth)
AND MONTH(DataFileYearMonth) = MONTH(@DataFileYearMonth)

結果:刪除大約需要38-44秒才能刪除這些125,837條記錄!





sql-server