In SQL Server, if I have two otherwise identical stored procedures with different names, will they use the same execution plan?

Question

Azure SQL Managed Instance. Here is the scenario. I have a stored procedure that produces a count of rows that match some criteria. But, depending on the Customer that is chosen, the statistics can be wildly different. For example, one customer might only have a couple hundred rows returned, while another might have a couple hundred thousand rows returned. Seemingly randomly, SQL Server creates a new plan for one scenario or the other and the performance of the SP craters. Since this data is used on a website in a major part of the application, the SP is called thousands of times a day. Dropping and recreating the SP immediately cures the problem, but if it happens outside of normal business hours, it can be a while before the problem is resolved. A coworker came up with the idea of making an identical copy of the SP and naming it "countBig", then calling that one for the huge customers, and calling the regular one for the rest. I read elsewhere that it's actually the SQL statements in the SP that get the plans built, so I'm wondering if this potential solution will actually work?

Answer 1

yeah, but...

This will work, but now you have two pieces of code to maintain.

It may be easier to use parametrized dynamic SQL within one stored procedure to do this.

Using the Stack Overflow database as an example, here's what we have for counts of different types of posts:

+------------+----------+
| PostTypeId |  total   |
+------------+----------+
|          2 | 11091349 |
|          1 |  6000223 |
|          4 |    25129 |
|          5 |    25129 |
|          3 |      167 |
|          6 |      166 |
|          7 |        4 |
|          8 |        2 |
+------------+----------+

Sharing plans between these distributions is often not-fun for very parameter-sniffy reasons.

procedure

Doing this will get you what you're after without having to worry about maintaining two separate procedures and remembering to change one if you change the other.

CREATE OR ALTER PROCEDURE
    dbo.sniff_fix
(
    @PostTypeId integer
)
AS 
BEGIN
SET NOCOUNT ON;

DECLARE
    @s nvarchar(MAX) = N'';

SELECT
    @s = N'
SELECT TOP (100)
    p.OwnerUserId,
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE p.PostTypeId = @PostTypeId
';

IF @PostTypeId IN (1, 2)
BEGIN
    SELECT
        @s += N'AND 1 = (SELECT 1)';
END;
ELSE
BEGIN
    SELECT 
        @s += N'AND 2 = (SELECT 2)';
END;

SELECT
    @s += N'
GROUP BY
    p.OwnerUserId
ORDER BY
    c DESC;';

RAISERROR(@s, 0, 1) WITH NOWAIT;

EXEC sys.sp_executesql
    @s,
    N'@PostTypeId integer',
    @PostTypeId;
END;
GO 

Since there is redundant logic with different literal values added to the string depending on which parameter value is passed in, you'll get separate execution plans.

EXEC dbo.sniff_fix 
    @PostTypeId = 2;

EXEC dbo.sniff_fix 
    @PostTypeId = 4;

Plan for 2:

Query for 2:

SELECT TOP (100)
    p.OwnerUserId,
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE p.PostTypeId = @PostTypeId
AND 1 = (SELECT 1)
GROUP BY
    p.OwnerUserId
ORDER BY
    c DESC;

Plan for 4:

Query for 4:

SELECT TOP (100)
    p.OwnerUserId,
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE p.PostTypeId = @PostTypeId
AND 2 = (SELECT 2)
GROUP BY
    p.OwnerUserId
ORDER BY
    c DESC;

You may want to watch this video for other pointers and ideas.

Answer 2

coworker came up with the idea of making an identical copy of the SP and naming it "countBig", then calling that one for the huge customers,

Yes. That would work. But a better approach might be to just to use OPTION RECOMPILE on the query.