How to get Query Plan Reuse in MS SQL Server

Question

I inherited a database application that has a table with about 450 queries. There's a calling procedures takes the @QueryId and @TheId as input parameters. The only way these queries are executed is via this procedure. The queries are like this:

@sql = replace('insert into #temp select col1, col2, col3, col4
from SomeTable st join OtherTable ot on matching_column
where st.TheID = ##TheId##', '##TheId##', @TheId);

exec sp_executesql @sql;

I want to get plan reuse, so I replace ##TheId## with @TheId and then execute the query like this:

exec sp_executesql @sql, N'@TheId int', @TheId;

However, I'm still seeing the same behavior where each plan is a unique plan, even though the @sql string is already compiled and in the procedure cache.

Now the string is like this

...where where st.TheID = @TheId

Question: how can I get plan reuse as desired on a parameterized query?

That is NOT a parameterized query. You have completely defeated the parameters here by building up a string. You should pass your parameter to the dynamic sql, or even better just avoid dynamic sql. From what you posted there is no need for dynamic sql here at all. — Sean Lange, Jul 19 '17 at 15:09
*Don't* use dynamic SQL. This isn't a parameterized query, it's just a plain-old string, vulnerable to SQL injection just like any other SQL string — Panagiotis Kanavos, Jul 19 '17 at 15:09
This is a good approach as it (*even more importantly*) addresses the SQL Injection issues. — RBarryYoung, Jul 19 '17 at 15:09
I'm trying to fix procedure cache bloat. There are 450 different rules, but they're executed thousands of times per day for various records. I looked in production last night and saw 50,000+ distinct plans. I want to reduce this to ~450. Then I can tune the bad performers, see which are most expensive, etc. I cannot do that with 50,000 queries — SqlNightOwl, Jul 19 '17 at 15:16
Is the sql text exactly the same for each of those different plans? — James Z, Jul 19 '17 at 15:17
Is #temp the same table between the calls, or does it get dropped? — James Z, Jul 19 '17 at 16:39

score 1 · Answer 1 · answered Jul 19 '17 at 15:01

1

Did you try creating stored procedure without making use of the dynamic query?

Try something along the following lines:

CREATE PROCEDURE insertdata 
(
    @TheId INT -- or whatever data type is being used
)   
AS
BEGIN 
    INSERT INTO #temp 
    SELECT 
        col1
        , col2
        , col3
        , col4
    FROM SomeTable st 
    JOIN OtherTable ot ON matching_column
    WHERE st.TheID = @Theid;
END

When you want to execute it, you'll simply do:

EXEC insertdata 123;

answered Jul 19 '17 at 15:01

Eli

2,538
1
25
36

There's no need to use stored procedures. A simple parameterized query is enough – Panagiotis Kanavos Jul 19 '17 at 15:09
Thanks, I though about that... but creating 450+ procedures is a tougher road than I wanted to tackle. I'd love to rewrite this, but there's no appetite to fix what really isn't broken. – SqlNightOwl Jul 19 '17 at 15:12

score 1 · Accepted Answer · answered Jul 19 '17 at 15:37

1

Well if you modify it to the following you should get plan reuse as this will make it a parameterized query:

@sql = replace('insert into #temp select col1, col2, col3, col4
from SomeTable st join OtherTable ot on matching_column
where st.TheID = ##TheId##', '##TheId##', '@TheId');

exec sp_executesql @sql, N'@TheID INT', @TheID;

https://technet.microsoft.com/en-us/library/ms175580(v=sql.105).aspx

answered Jul 19 '17 at 15:37

JMabee

2,230
2
9
13

Yikes, so it is. I would imagine the problem may be around that #temp table. You may want to look and see if you are getting a new recompile when you run that query by capturing the XE for sql_statement_recompile. I believe the insert into is causing a recompile. – JMabee Jul 19 '17 at 15:55
THANK YOU!!! I should have figured out that it was the #temp table messing me up! The solution is to remove the "insert into #temp" from the Query as well. Then I reworked the execution to this: `insert #temp exec sp_executesql @sql, N'@id int', @id'` – SqlNightOwl Jul 19 '17 at 18:43

How to get Query Plan Reuse in MS SQL Server

2 Answers2