如何确定存储过程是否每次都重新编译？

I've got the a SQL Server stored procedure with the following T-SQL code contained within:

我有一个SQL Server存储过程,其中包含以下T-SQL代码:

insert into #results ([ID], [Action], [Success], [StartTime], [EndTime], [Process])
select
    'ID' = aa.[ActionID],
    'Action' = cast(aa.[Action] as int),
    'Success' = aa.[Success],
    'StartTime' = aa.[StartTime],
    'EndTime' = aa.[EndTime],
    'Process' = cast(aa.[Process] as int)
from
    [ApplicationActions] aa with(nolock)
where
    0 = case
            when (@loggingLevel = 0) then 0
            when (@loggingLevel = 1 and aa.[LoggingLevel] = 1) then 0
        end
    and
    1 = case
            when (@applicationID is null) then 1
            when (@applicationID is not null and aa.[ApplicationID] = @applicationID) then 1
        end
    and
    2 = case
            when (@startDate is null) then 2
            when (@startDate is not null and aa.[StartTime] >= @startDate) then 2
        end
    and
    3 = case
            when (@endDate is null) then 3
            when (@endDate is not null and aa.[StartTime] <= @endDate) then 3
        end
    and
    4 = case
            when (@success is null) then 4
            when (@success is not null and aa.[Success] = @success) then 4
        end
    and
    5 = case
            when (@process is null) then 5
            when (@process is not null and aa.[Process] = @process) then 5
        end

It's that "dynamic" WHERE clause that is bothering me. The user doesn't have to pass in every parameter to this stored procedure. Just the ones that they are interested in using as a filter for the output.

这是困扰我的“动态”WHERE子句。用户不必将每个参数传递给此存储过程。只是他们有兴趣用作输出过滤器的那些。

How would I go about using SQL Server Studio or Profiler to test whether or not this store procedure is recompiling every time?

我将如何使用SQL Server Studio或Profiler来测试此存储过程是否每次都重新编译?

3 个解决方案

#1

The following article explains how to find out if your stored procedure is recompiling: http://it.toolbox.com/blogs/programming-life/sql-performance-abnormal-stored-procedure-recompiles-8105

以下文章介绍了如何确定存储过程是否重新编译:http://it.toolbox.com/blogs/programming-life/sql-performance-abnormal-stored-procedure-recompiles-8105

Here's a quote from the appropriate section:

以下是相应部分的引用:

start SQL Profiler and start a new trace, connect to our server and give an appropriate trace name, select the events tab and remove the already existing events on the "Selected event classes" list box. Now choose the "Stored Procedures" node in the "Available event classes" and add SPComplete, SPRecompile, SPStarting, SP:StmtStarting and SP:StmtCompleted. Now choose the "data columns" tab and select just about the right amount of events and data columns that you need. Add filters to reduce the number of events you collect.

启动SQL事件探查器并启动新跟踪,连接到我们的服务器并提供适当的跟踪名称,选择事件选项卡并删除“选定事件类”列表框中已存在的事件。现在选择“可用事件类”中的“存储过程”节点,并添加SPComplete,SPRecompile,SPStarting,SP:StmtStarting和SP:StmtCompleted。现在选择“数据列”选项卡,然后选择您需要的适当数量的事件和数据列。添加过滤器以减少您收集的事件数。

I would filter by the name of your stored procedure.

我会按您的存储过程的名称进行过滤。

#2

Just offhand, you can simplify these:

只是随便,你可以简化这些:

    2 = case
                    when (@startDate is null) then 2
                    when (@startDate is not null and aa.[StartTime] >= @startDate) then 2
            end

to this:

    (@startDate is null OR aa.[StartTime] >= @startDate)

As far as the recompile - is it declared WITH RECOMPILE?

至于重新编译 - 是否声明了WITH RECOMPILE?

#3

Your inserting into a temp table in your example which causes the SP to be recompiled every time because it cannot be precompiled.

您插入示例中的临时表,导致每次都重新编译SP,因为它无法预编译。

This is one of the differences between using temp tables and table variables - a good article on the differences can be found here

这是使用临时表和表变量之间的差异之一 - 可以在这里找到关于差异的好文章

Pertinent extract...

The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table-variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

第二个主要区别是具有临时表的任何过程都不能预编译,而具有表变量的过程的执行计划可以预先静态编译。预编译脚本为其执行速度带来了重大优势。对于长程序而言,这种优势可能是戏剧性的,其中重新编译可能过于昂贵。