如何證明以下 T-SQL 對性能不利？

我最近繼承了一個包含大量儲存過程的程式碼庫。他們支持的系統遇到了我正在研究的許多性能問題。

許多儲存過程具有這樣的模式：

1. 創建臨時表
2. 建立動態 SQL 查詢以插入一堆記錄，例如

DECLARE @sql VARCHAR(MAX)
SET @sql = 'INSERT INTO @tempTable
SELECT SomeColumn, SomeColumn2, SomeColumn3, etc FROM MyTable'

IF @someParam = [SomeValue]
   SET @sql = @sql + 'WHERE SomeColumn = [SomeValue]';

IF @someOtherParam = [SomeOtherValue]
   SET @sql = @sql + 'WHERE SomeOtherColum = [SomeOtherValue]';

3. 執行這個動態sql

EXEC(@sql);

4. 從臨時表中選擇並帶入一堆附加資訊以返回給客戶端。

SELECT

  ...

FROM

  @tempTable
  INNER JOIN ...

我的直接想法是：

• 有動態 SQL，所以沒有記憶體計劃，意味著每次都會生成計劃。
• 有一種INSERT SELECT模式，因此表鎖定更可能是一個問題。

我以這種方式重寫了一些儲存過程：

   SELECT 

      ...

   FROM

       MyTable 
       INNER JOIN ...

   WHERE

   (
       @someParam != SomeValue
       OR
       SomeColumn = SomeValue
   )
   AND
   (
       @someOtherParam != SomeOtherValue
       OR
       SomeOtherColumn = SomeOtherValue
   )        

通過比較 SQL Management Studio 中的執行計劃和客戶端統計資訊，我沒有加快儲存過程的速度，因此我擔心建議對所有儲存過程進行大規模重寫。

我正在嘗試對實時客戶場景進行一些分析，但至今仍無法證明我的想法。

誰能對我的想法背後的理論提供任何證實，或者有什麼更好的方法來證明我的懷疑？

問題是我已經讀過動態 SQL 並不總是一個封閉的案例 - 即它取決於它的使用方式。我對鎖定的理解也下降了，因為我無法 100% 地確認這種類型INSERT SELECT將如何鎖定表。

有動態SQL，所以沒有記憶體計劃，意味著每次都會生成計劃

不一定是真的。動態 SQL 可以（並且確實）與靜態 SQL 一樣使用記憶體計劃。對於解析為動態 SQL 的動態搜尋條件通常是正確的答案。有關詳細資訊，請參閱T-SQL中的動態搜尋條件。

有一個 INSERT SELECT 模式，所以表鎖定更可能是一個問題。

不一定是真的，特別是@tempTable

以這種方式重寫了一些儲存過程

使用這樣的多個OR條件是一種反模式。您正在強制查詢優化器提出一個適用於所有這些參數的任何值的計劃。通常唯一的解決方案是掃描，忽略任何索引。原始程式碼更好。

誰能提供…任何更好的方法來證明我的懷疑？

是的。措施。使用Waits 和 Queues 之類的方法。不要依賴你的直覺。找到瓶頸並相應地解決它們。

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