如何确保分区修剪在运行时有效?

Question

假设我有一个非常大的表，按日期划分，使用月为范围。

我注意到指定硬值的查询将在执行计划中执行适当的分区修剪。( WHERE DATE_KEY = '1/1/2011')。这将只扫描当月的分区。

但是我注意到，当我在存储过程中使用变量(WHERE DATE_KEY = @DATE_KEY)时，SQL Server将扫描所有分区。

所以这就像SQL Server在缓存执行计划时做分区修剪，而不是在运行时。这并不理想。

我发现的解决方案是使用动态SQL代替EXEC('...WHERE DATE_KEY=''' + @DATE_KEY+ '''')。这是有效的，但不是很优雅。

所以我想知道是否有一个开关或一些参数在某个地方，我可以用它来使这个工作正常，而不需要通过动态SQL。假设一些"SET COMPILE_PLAN_AT_RUNTIME ON"或其他东西....

Answer 1

1. 使用RECOMPILE查询提示:
   OPTION(RECOMPILE)

2. 确保被比较的参数和列具有相同的数据类型:
   (WHERE DATE_KEY = @DATE_KEY)

Answer 2

要重新编译查询，请使用recompile查询提示。

检查是否重新编译存储过程。您可以:

• 在存储过程定义中标记为WITH RECOMPILE - SQL Server将在每次执行之前重新编译存储过程;如果经常调用存储过程，请注意对性能的影响;检查CREATE PROCEDURE
EXEC sp_my_procedure WITH RECOMPILE 在每个EXEC语句的末尾添加WITH RECOMPILE
• 使用sp_recompile在下次执行时强制重新编译

此外，您可以尝试在存储过程中使用RECOMPILE查询提示。

不要忘记测试性能，因为重新编译的成本很高。