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])
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.

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

Answers


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

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
            end

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

Why does IE11 give this warning: HTML1406: Invalid tag start: “<?”

html internet-explorer xhtml doctype internet-explorer-11

My pages are written and declared as XHTML 1.0 Strict. The first lines goes like this:

How to create an Object with all attributes of a html tag?

javascript html object dom

Here is my test code, extracted from a form I'm building:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.