Clever Geek Handbook

How can I tell if a stored procedure is being overloaded every time?

I have a SQL Server stored procedure with the following T-SQL code contained inside:

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

This is what the "dynamic" WHERE clause worries me about. The user does not have to pass every parameter to this stored procedure. Just the ones they are interested in using as a filter for the output.

How can I use SQL Server Studio or Profiler to check if this store routine is recompiled every time?

0


source to share


3 answers


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

Here is a quote from the relevant section:



start SQL Profiler and start a new monitor, connect to our server and give an appropriate trace name, select the events tab and delete the already existing events in the "selected event" classes. Now select the "Stored Procedures" node in the "Available Event Classes" and add SPComplete , SPRecompile, SPStarting, SP: StmtStarting and SP: StmtCompleted Now select the Data Columns tab and select the desired number of events and data columns you want Add filters to reduce the number of events you collect.

I would filter out the name of your stored procedure.

+1


source


Just casually, you can simplify them:

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

:



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

As for recompilation - is it declared WITH RECOMPILE

?

+1


source


An insert into the temp table in your example that forces the SP to recompile every time because it cannot be precompiled.

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

A promising extract ...

The second main difference is that any procedure with a temporary table cannot be precompiled, and the execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling the script gives a big advantage to the speed of execution. This advantage can be dramatic for lengthy procedures where recompilation can be too costly.

+1


source






More articles:


All Articles