SET options causing multiple plans for a SP

Question

SET options causing multiple plans for a SP

Anoop Agarwal

SSC Enthusiast

Points: 141
More actions
August 10, 2023 at 10:00 am

#4257643

hello,
I have a stored procedure, which has multiple plans as per Database Performance Analyzer. It is called from the application as follows
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
EXEC Sp_mySp
I believe these set parameters are causing multiple plans. I need to provide data to my development team so that I can ask them to change the code.
What is the process I can follow to prove that SET options are causing problem.
Thanks,
Anoop
August 10, 2023 at 1:16 pm

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

Funny enough, the ChatGPT answer is pretty good, if not at all helpful. It's correct, the ANSI settings do affect plan generation. What it didn't bother to tell you was how to find the ANSI settings inside execution plans.

The good news is, because ANSI settings absolutely affect the plan generated, those settings are stored with the plan. If you right click the first logical operator (the one all the way to the left of the screen and at the top) and select "Properties" from the context menu, you will see all sorts of data about how the plan was generated. Down near the bottom are the ANSI settings. So, get a copy of each of the plans. Go to the ANSI settings. See if they are different. If they are, ta-da!!

ASSUMING!!!

Assuming the same parameters within the same database are used to generate two different plans. If the parameters are different, data is different, statistics have been updated, any of the other things that affect plan generation are different, you aren't comparing apples to apples. You have to get to the place where you can see that the one thing that changed, ANSI settings, is the ONLY thing that changed.

Now, if CHATGPT gets updated, it can use this to provide a better answer next time.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Anoop Agarwal SSC Enthusiast Points: 141 More actions · Answer 2

Thanks Grant,

I will check the plans for the query and go from there.

Regards,

Anoop

Anoop Agarwal SSC Enthusiast Points: 141 More actions · Answer 3

I checked all the 4 plans generated for the SP and they all have the same SET options. So, now I will look for parameters that SP is running with, to find out what could be causing Mutiple plans for it.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

Compile time values are also stored with the plan in the same place. When you look at plans with runtime values (aka, Actual Plans), you can also see the runtime value for comparisons (handy with bad parameter sniffing issues).

All on the same database?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Anoop Agarwal SSC Enthusiast Points: 141 More actions · Answer 5

I will check the execution times as well.

Everything is one database.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

Times... meh. Especially a single run can be affected by so many things.

If you're getting different execution plans for the same query, it really is either parameter values, implying you may have a parameter sniffing issue, or ANSI settings. Same database on the same server with the same data in the same state, those are about the only things that are going to result in plan differences.

Now, if statistics got updated between query runs, you may see different plans. Compare the row estimates with the actuals in the database, and/or look at the statistics to see where the estimates came from. If there's a data load that changed the data between query runs, again, statistics that leads this, but you could be seeing that. Changes to the underlying structures between runs, you could be seeing changes in plans. Code changes between runs. Tons of stuff that could be different.

But if everything is the same, it's statistics or ANSI settings.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

Check to see if forced parameterization is on. If it is, first take a baseline to see what performance is on the box then turn it off and check again. You may have to leave it turned off (which is the default, BTW).

Also, does your "EXEC Sp_mySp" pass any parameters?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anoop Agarwal SSC Enthusiast Points: 141 More actions · Answer 8

Hi Jeff,

I do not have force parameterization turned on. The sp passes two parameters as input. One is productcode varchar and locationid int. I checked the data and there is no massive change in data for SQL to use a new plan.

Regards,

Anoop

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

Anoop Agarwal wrote:

Hi Jeff,
I do not have force parameterization turned on. The sp passes two parameters as input. One is productcode varchar and locationid int. I checked the data and there is no massive change in data for SQL to use a new plan.
Regards,
Anoop

2019 is capable if storing more than one plan. It's actually not a fault. According to MS, it's a feature. I'm seriously thinking that this isn't a problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anoop Agarwal SSC Enthusiast Points: 141 More actions · Answer 10

Hi Jeff,

Thanks for the information,

The problem we are facing is when SQL decides to change the plan it was using, then the query run really slow for at least a day before it returns to normal execution times.

Let me know if you need more information.

Regards,

Anoop