儲存過程同時執行多次時，臨時表過濾索引創建被阻止

SQL Server 2014 中是否存在特定於在初始表創建後在臨時表上創建一個或多個篩選索引的任何已知阻塞問題？（即不是內聯索引創建）

我不允許發布實際程式碼；所以，我更改了表/列名以保護無辜者。下面的代表性但非常粗略的邏輯包含在儲存過程的開頭。

我相信，當儲存過程同時執行多次時，就會發生阻塞。根據我的 DMV 查詢，儲存過程在第一個非聚集索引創建語句中被阻塞。

CREATE TABLE #temp_table_name_goes_here (
   [the_first_col] BIGINT NULL
   ,[the_second_col] INT NULL
   ,[another_col] VARCHAR(20) NULL
   );

CREATE INDEX tmp_indx_temp_table_name_goes_here_1 ON #temp_table_name_goes_here (the_first_col)
WHERE the_first_col IS NOT NULL;

CREATE INDEX tmp_indx_temp_table_name_goes_here_2 ON #temp_table_name_goes_here (the_second_col)
WHERE the_second_col IS NOT NULL; 

說話！

正如 Paul 所建議的，我能夠使用SQL Query Stress重現您的問題，既使用您的範例，又更改內容以創建內聯索引：

CREATE TABLE #temp_table_name_goes_here
    (
        the_first_col BIGINT NULL,
        the_second_col INT NULL,
        another_col VARCHAR(20) NULL,
        INDEX tmp_indx_temp_table_name_goes_here ( the_first_col ) 
              WHERE the_first_col IS NOT NULL,
        INDEX tmp_indx_outstanding_inventory ( the_second_col ) 
              WHERE the_second_col IS NOT NULL
    );

當然，SQS 在 20 次迭代中執行 200 個並發會話，因此需要一些工作才能到達那裡。

我在 SQL Server 2017 上，因此啟用Trace Flag 3427並沒有改變這種情況。

如果您的系統上沒有以最佳方式配置 tempdb，我將從那裡開始：

• 每個核心一個數據文件，最多 8 個核心
• 啟用跟踪標誌 1117 和 1118

如果您仍有問題，我會向 Microsoft 提出支持案例。像這樣的可擴展性問題應該是最重要的。

跟進

在偶然發現這個問題之後，我決定使用表變數重新嘗試測試。在與上述相同的情況下，觀察到相同的爭論。

DECLARE @temp_table_name_goes_here TABLE
    (
        the_first_col BIGINT NULL,
        the_second_col INT NULL,
        another_col VARCHAR(20) NULL,
        INDEX tmp_indx_temp_table_name_goes_here ( the_first_col ) 
              WHERE the_first_col IS NOT NULL,
        INDEX tmp_indx_outstanding_inventory ( the_second_col ) 
              WHERE the_second_col IS NOT NULL
    );

根據Jonathan Fite 的評論，我還在開始時使用顯式 DROP IF EXISTS（或舊樣式檢查 NOT NULL object_id）重新執行了臨時表測試：

我以前遇到過這個錯誤。我的解決方案是在創建它之前添加顯式刪除索引和臨時表（DROP IF EXISTS）。只有在同一個會話多次執行儲存過程時才會發生這種情況。

這確實有幫助。爭用要麼不存在，要麼已最小化。

在末尾添加顯式 DROP TABLE 或 DROP IF EXISTS 會產生奇怪的效果：奇怪的是，爭用變成了刪除臨時表：

即不是內聯索引創建

主要缺點是臨時表不再符合記憶體條件，因為 DDL 是在創建後執行的。

這假設臨時表在沒有過濾索引的情況下符合條件，並且記憶體結構將對工作負載有益。這個問題對細節有點輕，所以提到這個有點猜測。

第二個考慮因素，幾乎可以肯定與問題無關，即單獨的索引創建語句會導致SNAPSHOT隔離級別顯式事務失敗，因為 DDL 沒有版本控制。

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