主页 / dba / 问题 / 326926
Accepted
lifeisajourney
Asked: 2023-05-10 06:48:41 +0800 CST

SQL Server 中为不同参数生成多个计划的存储过程

  • 772

我有一个存储过程，它为不同的参数生成略有不同的计划。我希望它使用第一次运行时生成的相同计划。

CREATE TABLE myTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Column1 VARCHAR(50),
    Column2 VARCHAR(50),
    Column3 VARCHAR(50),
    Column4 VARCHAR(50),
    Column5 VARCHAR(50),
    Column6 VARCHAR(50),
    Column7 VARCHAR(50),
    Column8 VARCHAR(50),
    Column9 VARCHAR(50),
    Column10 VARCHAR(50)
)

DECLARE @i INT = 1
DECLARE @j INT = 1
DECLARE @distinct_value_count INT = 20
DECLARE @distinct_value_count_with_more_rows INT = 3
DECLARE @rows_per_distinct_value INT = (20000 - (@distinct_value_count_with_more_rows * 2000)) / (@distinct_value_count - @distinct_value_count_with_more_rows)

WHILE @i <= @distinct_value_count
BEGIN
    DECLARE @current_rows_per_value INT = @rows_per_distinct_value
    IF @i <= @distinct_value_count_with_more_rows
    BEGIN
        SET @current_rows_per_value = @rows_per_distinct_value + 2000
    END
    
    SET @j = 1
    WHILE @j <= @current_rows_per_value
    BEGIN
        INSERT INTO myTable (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10)
        VALUES ('Value' + CAST(@i AS VARCHAR(2)),
                'Value' + CAST(@j AS VARCHAR(5)),
                'Value' + CAST(@j + 1 AS VARCHAR(5)),
                'Value' + CAST(@j + 2 AS VARCHAR(5)),
                'Value' + CAST(@j + 3 AS VARCHAR(5)),
                'Value' + CAST(@j + 4 AS VARCHAR(5)),
                'Value' + CAST(@j + 5 AS VARCHAR(5)),
                'Value' + CAST(@j + 6 AS VARCHAR(5)),
                'Value' + CAST(@j + 7 AS VARCHAR(5)),
                'Value' + CAST(@j + 8 AS VARCHAR(5)))
        SET @j = @j + 1
    END
    
    SET @i = @i + 1
END

Create NonClustered Index Idx_col on MyTable(Column1)
sp_recompile 'dbo.MyTable'

Create or Alter Procedure dbo.tmp_testProc(
@inValue VarChar(50)
)
As
Begin

Set NoCount On

Select Id 
Into #tmpCol
From MyTable
Where Column1 = @inValue

Select t.*
From MyTable t
Join #tmpCol tmp
On t.Id = tmp.Id
Order by t.Id

End

--Scenario 1
exec dbo.tmp_testProc @inValue = 'Value1' 

--Scenario 2
exec dbo.tmp_testProc @inValue = 'Value5'

我'Value1'在第一次执行时使用参数值 = 执行存储过程，然后使用'Value5'. 以下是两种场景的执行计划：

场景 1 执行计划：https://www.brentozar.com/pastetheplan/? id=r1H-3HONh

场景 2 执行计划：https://www.brentozar.com/pastetheplan/? id=r1p42SOE3

我预计第二种情况会采用与第一种情况相同的计划，但我发现计划有所不同。我的印象是优化器会为所有后续执行重用为初始参数集生成的相同计划。因此，我不确定为什么优化器会在这种情况下创建不同的计划。如果对此事有任何澄清，我将不胜感激。谢谢您的帮助。

sql-server

1 个回答

  1. Best Answer

    我的第一个想法是你正在运行 SQL Server 2022 并点击了一个名为Parameter Sensitive Plan Optimization的新功能，其中每个查询最多可以有三个计划（基于选择性，每个计划本身对嗅探敏感）。但是我用较低的数据库兼容性级别测试了您的代码，但我们仍然有不同的计划。

    因此，下一个嫌疑人是为每次执行重新编译。我连接了以下痕迹：

    CREATE EVENT SESSION [Recompiles] ON SERVER 
ADD EVENT sqlserver.sql_statement_recompile

    你有它。一个重新编译事件，recompile_cause 是“ Statistics changed ”。

    即，因为您用足够的数据填充了一个表，然后执行 SELECT，SQL Server 将为您更新统计信息，这反过来会生成重新编译。

    • 4

相关问题