How can I determine whether or not a stored procedure is recompiling every time?

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

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

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.

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


The following article explains how to find out if your stored procedure is recompiling:

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.

I would filter by the name of your stored procedure.

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

to this:

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

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

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

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.

Need Your Help

Check for HTML tags

javascript regex

I'm trying to write a regular expression to find out if there are any HTML tags, so far I have:

Type safety: Unchecked cast from Object to ArrayList<MyVariable>

java arraylist casting warnings unchecked-cast

Here is a part of a program that sends an ArrayList from a server to a client. I want to remove the warning about the last line in this code: