SQL Server 估計錯誤

我每天有超過一百萬次插入。此表是一個“臨時表”，其中接收消息並將其發送到由應用程序處理的不同隊列。

一個名為的儲存過程在此表上dbo.leg_msgs執行UPDATE（顯示計劃https://www.brentozar.com/pastetheplan/?id=ryw9SDGoD），使用覆蓋索引，其列從最具選擇性到最不選擇性。

CREATE TABLE [dbo].[leg](
   [guid_operacao] [uniqueidentifier] NOT NULL,
   [dt_hr_entrada] [datetime] NOT NULL,
   [dt_hr_envio] [datetime] NULL,
   [id_fila] [int] NULL,
   [str_protocolo] [text] NOT NULL,
   [flg_enviar] [char](1) NOT NULL,
   [nr_ctrl_if] [char](20) NULL,
   [id_legado] [int] NULL,
   [tp_mensagem] [int] NOT NULL,
   [flag_sentido] [char](1) NULL,
   [flg_proc_util] [char](1) NOT NULL,
   [guid_protocolo] [uniqueidentifier] NOT NULL,
   [str_protocolo_enviada] [text] NULL,
   [fl_montou_protocolo] [char](1) NULL,
   [dt_hr_lock] [datetime] NOT NULL,
   [guid_lock] [uniqueidentifier] NOT NULL,
   [dt_hr_ok_leg] [datetime] NULL,
   [flg_ret_legado] [bit] NOT NULL,
   [flg_enviada] [bit] NOT NULL,
   [dt_hr_legado] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

儲存過程：

CREATE PROCEDURE [dbo].[leg_msgs] (  
@pTipoMensagem     INT     = NULL,   
@pIntegracaoViaSQL CHAR(1) = 'N',  
@pIdFila           INT     = NULL  
)  
AS  
BEGIN  
     
   SET NOCOUNT ON  
     
   DECLARE @guid_operacao   char(36),  
   @guid_protocolo   char(36),  
   @dt_hr_entrada   datetime,  
   @dt_ini    datetime,  
   @dt_fim    datetime,  
   @ds_strong_id   varchar(max),  
   @str_protocolo   varchar(max),  
   @cd_msg    varchar(10),  
   @nm_fila   varchar(100),  
   @id_tag_numctrl   varchar(20),  
   @id_status_matera  varchar(5),  
   @evento_id   varchar(10),  
   @dt_hr_entrada_str  varchar(50),  
   @nm_tag_sit_lanc  varchar(200),  
   @nm_proc_montagem_protocolo varchar(100),  
   @strSQL    nvarchar(1000),  
   @dt_movto   varchar(10),  
   @tempo_espera   numeric(20, 10),  
   @dt_hr_lock   datetime,  
   @guid_lock   char(36),  
   @montou_prot   char(1),  
   @id_fila_rep   int,  
   @qtd_regs   int,  
   @bol_delete   bit  
      
   -- Valores para @tempo_espera  
   --    0.00069445 (00:02:00)  
   --    0.00104167 (00:01:30)  
   --    0.00069445 (00:01:00)  
   --    0.00034724 (00:00:30)  
   SET @tempo_espera = 0.00069445  
     
   SET @dt_movto   = CONVERT(varchar(10), getdate(), 112)  
   SET @dt_hr_lock = getdate()  
   SET @guid_lock  = newid()  
   SET @dt_ini     = CAST(@dt_movto + ' 00:00' AS datetime)  
   SET @dt_fim     = CAST(@dt_movto + ' 23:59' AS datetime)  
   
     
   SELECT @qtd_regs = 1  
   FROM dba.dbo.leg WITH (NOLOCK)   
   WHERE tp_mensagem    = @pTipoMensagem  
   AND dt_hr_entrada BETWEEN @dt_ini AND @dt_fim  
   AND flg_enviar     = 'S'  
   AND flg_proc_util  = 'N'  
   AND id_fila       = @pIdFila  
   AND dt_hr_lock    <= CONVERT(varchar(23), getdate() - @tempo_espera, 121)  
   OPTION (RECOMPILE)  
     
   IF @@ROWCOUNT <> 0 BEGIN  
   BEGIN TRANSACTION  
       UPDATE dba.dbo.leg WITH (ROWLOCK READPAST)  
           SET dt_hr_lock = '19000101',  
           guid_lock  = '00000000-0000-0000-0000-000000000000'  
           WHERE tp_mensagem    = @pTipoMensagem  
           AND dt_hr_entrada BETWEEN @dt_ini AND @dt_fim  
           AND flg_enviar     = 'S'  
           AND flg_proc_util  = 'N'  
           AND id_fila       = @pIdFila  
           AND dt_hr_lock    <= CONVERT(varchar(23), getdate() - @tempo_espera, 121)  
     
       IF @@ERROR = 0 BEGIN  
           COMMIT TRANSACTION  
       END  
       ELSE BEGIN  
           ROLLBACK TRANSACTION  
           RETURN  
       END   
   END  
           
   SET NOCOUNT OFF  
     
END  

通常它執行很快（大約 30-50ms），但如果返回的行數超過 5000，它開始執行在 100-200ms 以上。

我的第一種方法是重建表和索引，但 SQL Server 仍然缺少估計。下面的截圖是重建後的。

CPU 時間 = 0 毫秒，經過的時間 = 0 毫秒。
表“腿”。掃描計數 1，邏輯讀取 16321，物理讀取 0，預讀讀取 0，lob 邏輯讀取 0，lob 物理讀取 0，lob 預讀讀取 0。
表“工作台”。掃描計數 1，邏輯讀取 32676，物理讀取 0，預讀讀取 0，lob 邏輯讀取 0，lob 物理讀取 0，lob 預讀讀取 0。

有什麼想法嗎？

編輯：

SQL Server 2017，但我目前的 COMPATIBILITY_LEVEL = 110

作為測試，我創建了一個聚集索引，重建了所有內容，但仍然得到錯誤的估計。

alter table leg add id bigint identity(1,1)
   
create clustered index ix_id on leg (id)
   
alter index all on dbo.leg rebuild

CPU 時間 = 0 毫秒，經過的時間 = 5 毫秒。
表“腿”。掃描計數 1，邏輯讀取 49703，物理讀取 0，預讀讀取 30，lob 邏輯讀取 0，lob 物理讀取 0，lob 預讀讀取 0。
表“工作台”。掃描計數 1，邏輯讀取 32724，物理讀取 0，預讀讀取 0，lob 邏輯讀取 0，lob 物理讀取 0，lob 預讀讀取 0。

新計劃：https ://www.brentozar.com/pastetheplan/?id=rkZ6mtzoP

**有用 **@NikitaSerbskiy 建議的相關連結

為什麼當我內聯變數時 SQL Server 使用更好的執行計劃？ 為什麼你調錯儲存過程（局部變數的問題）

通常它執行得很快，但如果返回的 os 行數大於 5000，它就會開始變得遲緩。

您是否沒有因為以下原因而面臨鎖定升級

單個 Transact-SQL 語句在單個非分區表或索引上獲取至少 5,000 個鎖。

?

https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15

雖然看起來確實存在一些基數估計問題，但我的猜測是對於堆表來說並不太牽強。

此外，在您的執行計劃中（在 PasteThePlan.com 上）顯示您的大部分瓶頸是在UPDATE腿表期間。我認為這可能是因為它是一個堆表。我還看到您在查詢之前從 Leg 表中進行選擇UPDATE。

clustered index如果您確實在 Leg 表上使用 a 而不是堆，這兩個操作可能會更高效。雖然沒有測試，但我不能肯定地說，但我建議嘗試一下。（如果你正在重建你的表並且它仍然是索引，那麼它實際上與clustered index在之前創建表SELECT並UPDATE在之後刪除它沒有太大區別。）

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