0

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?

2 Answers 2

4

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: NUTS

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: NUTS

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.

4
  • While this technique looks excellent, and I will in fact be using it in some situations, this particular SP is way too complex to rewrite in this fashion.
    – CB_Ron
    Sep 25, 2023 at 21:27
  • 1
    @CB_Ron take a look at the video I posted. There’s a solution at the end that uses OPTIMIZE FOR dynamically with the parameter value passed in. Sep 25, 2023 at 22:48
  • 1
    @CB_Ron FWIW, you don't necessarily always need to parameterize the entire procedure, rather only the specific query or two that are having statistics issues. It may be a good opportunity to refactor the other parts of this complex procedure too, since procedures shouldn't be coded to be overly complex.
    – J.D.
    Sep 26, 2023 at 0:02
  • @ErikDarling I saved that video to watch later. Thanks, I appreciated you sharing it.
    – CB_Ron
    Sep 26, 2023 at 16:49
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.

1
  • Thank you David. I've tried that approach, and the SP performance dies in that scenario.
    – CB_Ron
    Sep 25, 2023 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.