SQL Server Query runs slow when placed inside of a stored procedure

Question

I have a query that I am putting in to a stored procedure. When I run the query with local variables the query takes ~1 second to run. When I put the same query inside of a stored procedure and call the SP, it takes around 2 minutes to run.

From previous questions in SO I think it may be related to parameter sniffing. When I've had this issue before I have declared local variables inside my SP and then used the local variables throughout. This has worked in the past, but does not seem to help me in this case.

I currently have

CREATE PROCEDURE dbo.ProcedureName

    @DIV VARCHAR(4),
    @STD VARCHAR(1), -- S or N
    @scen varchar(20)
AS
BEGIN

    DECLARE 
        @DIV_copy VARCHAR(4),
        @STD_copy VARCHAR(1),
        @scen_copy varchar(20);
    SELECT
        @DIV_copy = @DIV,
        @STD_copy = @STD,
        @scen_copy = @scen;

I have also tried adding WITH RECOMPILE like so

CREATE PROCEDURE dbo.ProcedureName

    @DIV VARCHAR(4),
    @STD VARCHAR(1), -- S or N
    @scen varchar(20)

WITH RECOMPILE
AS
BEGIN

    DECLARE 
        @DIV_copy VARCHAR(4),
        @STD_copy VARCHAR(1),
        @scen_copy varchar(20);
    SELECT
        @DIV_copy = @DIV,
        @STD_copy = @STD,
        @scen_copy = @scen;

Additionally, I have tried adding the OPTION(RECOMPILE) at the end of my SP like so:

SELECT *
    FROM #Output

OPTION(RECOMPILE)

END
GO

I also tried using:

OPTION(OPTIMIZE FOR UNKNOWN )

As well as:

OPTION(QUERYTRACEON 4136)

On this option, I do not have appropriate permissions for query tracing.

None of the 5 fixes above appear to fix the issue as the stored procedure still takes anywhere between 2 minutes and 2 minutes and 30 seconds for the same query that takes 1 or 2 seconds outside of the stored procedure.

These fixes all came from this article 'Different Approaches to Correct SQL Server Parameter Sniffing'

Has anyone had similar issues? Thank you for your time!

SQL Server 2008R2

Feinberg

It does sound like parameter sniffing, and I don't have any other idea what it could be. You could check this article to see if it offers any additional solutions that you haven't already tried: sommarskog.se/query-plan-mysteries.html – Mycenae

I have tried adding the OPTION(RECOMPILE) at the end of my SP - it should be placed after the offending query. But if with recompile didn't work I doubt this correction will. – Certifiable

I have had the exact same symptoms and creating local copies of params has always solved it. The only difference is that I have always used a SET statement instead of a SELECT statement to copy values into the local variables. – Nigrify

i have been in similar situation one, to resolve that i tweak the query thus change in query plan to resolve that, can you share the Actual query plan and query. – Gaven

Answer 1

Unfortunately I did not figure out why the same exact query had so much trouble when placed inside a stored procedure when compared to the sql statements alone.

As a "workaround" I spent some time optimizing my queries within the SP. I realized one table I was joining to was very large (millions and millions of rows) so what I did was grab the relevant data from the large table and created a temp table to hold it, then joined to the (much) smaller temp table and that seemed to do the trick. The SP is executing in 3-4 seconds now.

I am still somewhat new to SQL outside of the basics so I am learning a lot. Let this serve as a reminder to scrutinize your queries, there's often room for improvement. This feels somewhat like scotch tape and paperclips, but my problem was solved.

Thank you everyone for your input.

Feinberg

Rather than a temp table, you'll likely do better by adding an index to the large table that matches the join conditions. – Conidium

True but I do not have permissions to do that, though I could ask our DBA team perhaps. – Feinberg

Answer 2

In my experience, best way to run query in stored procedures without performance issues is to run them with

sp_executesql

statement.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver16 for refernces.

Sabin