June 14, 2021 at 9:14 am
2019 Standard rtm-cu8-gdr and enterprise. A sql stored procedure is running differently(picking up different execution plans) when run across these 2 editions.How do we zero in the problem and make sure the query runs as good on standard as it currently is on enterprise.
June 14, 2021 at 1:30 pm
The fact that the 2 queries are the same doesn't guarantee that you'll have the same query plan. The query plan depends on the data in the tables and the statistics that the tables have. If the data and statistics in both databases are different, then you can have different plans. If you are using stored procedures and not batches, then you have to remember that the stored procedures' query plan is set at the first time that you activate the stored procedure and there is no plan for it in the plan cache. In that case the server will create a query plan that is depended also on the procedure's parameters, so if you activated both procedures in each server with a different set of parameters, they could also have different query plans even if the data and statistics are the same
Adi
June 14, 2021 at 3:05 pm
Why are using the RTM version instead of applying patches? You'll have performance issues from that as well.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 14, 2021 at 7:13 pm
A few things noted above, but also query store can help here, and force a particular plan for a query.
June 15, 2021 at 11:47 am
While just moving from Standard to Enterprise can result in differences in execution plans, you may also be seeing other things. Check the server settings. Stuff like: default ANSI connection settings, Degree of Parallelism, Cost Threshold for Parallelism, Compatibility Level on the database. That's not an exhaustive list, but all those can result in differences in execution plans, even if the query, the data, and the statistics are 100% identical. However, another one that's possible, what are the statistics maintenance routines like between the two servers? Just because the data is the same, same structures, same query, doesn't mean the statistics are the same. This can also lead to differences in execution plans.
Query Store is a simple way to monitor for this kind of behavior. For a given database, you can enable Query Store, run a test, say on Standard edition. Backup that database. Query Store data goes with it. Restore the database to an Enterprise edition server. Run the test again. Query Store will show you if the plan changed, and allow you to then compare the plans to understand why the differences are occurring. And then, as Steve notes, you can force the plan to behave appropriately (assuming you're not trying to force Enterprise behavior within a Standard edition server, that will just quietly fail).
"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
June 18, 2021 at 4:36 pm
Thank you @Grant and everybody else.
Can a Stored Procedure(has several parameters as input) run differently in standard or enterprise version when executed from SSMS when compared getting triggered from the application? Can it pick different execution plans in standard and enterprise.(assuming everything remains the same.)Same Db in both standard and enterprise.(backed up from enterprise aand restored to standard).Same tables,same volume of data,same indexes, stats etc but still the stored procedure runs longer in standard than in enterprise as it picks up different execution plans. Where do I start my troubleshooting. Both sql versions run on the same windows box one at a time and not together.So underlying hardware stays the same. Thanks
June 18, 2021 at 5:04 pm
The plans should be the same, if all knobs/config settings are the same and the data/stats are the same. If you mean you're attaching the database separately to standard and ee and things are different, I'd like to see a repro. It's entirely possible there is some difference here I'm not thinking about, but I don't know what EE feature would cause this.
I'm sure the QE/QP team at MS would be interested. If you have a (not too large) repro, I'll pass it along.
June 18, 2021 at 5:39 pm
I'd have to see the plans to understand what is happening. Compile variables, ANSI connection settings, these kinds of things can result in differences. There's no way to pick one thing out and say "this is it" without having the plans to compare.
"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
June 18, 2021 at 7:21 pm
@Steve. Thanks
What settings/config values should be checking in Standard and enterprise (fyi:both on same machine)to troubleshoot this.A back and restore has been done from enterprise to standard, so data stays the same.Is there a list /reference you can pass along.
Thanks
June 18, 2021 at 7:57 pm
There's far to many. From sp_config to all the database settings. MS has not made this easy.
A few people have tried:
June 21, 2021 at 10:05 am
@steve-2 Jones, @Grant and all,
Thank you.
I have saved the execution plans from enterprise and standard edition. How can I force the stored procedure to use the enterprise plan when I run the stored procedure on standard version? This possibly is far from the answer.But I am curious if this can done. Please advise.
FYI: The SP has many queries as part of the execution plan and only 1 seems to show the big difference as listed underneath.
I do understand the data is pretty vague underneath but any insight will be appreciated.Thanks
FYI: The enterprise uses
index seek(nonclustered view),(Number of rows read 2500,estimated rows to be read 800)
nested loop (actual number of rows for all execution 2500,estimated number of executions 1, estimated number of rows per execution 2400)
table scan(number of rows read - 5;estimated rows to be read - 5)
and the
standard uses
table scan - 5
Hash Match(Inner join),(actual rows 5000 estimated rows 150000)
Clustered Index scan,(actual rows read and estimated rows read - 1800000)
Non clustered index scan - 350000(number of rows read, estimated rows read)
Merge(Inner) Join(actual rows 200000, estimated 350000)
for the same stored procedure when the same set of parameters are passed.
June 21, 2021 at 11:53 am
If you could post the plans (note, please, not a picture of the plans, but the xml files), we might be able to provide insight. With just a description, I've got nothing.
A place to start though, look at the properties of the first operator in each plan. Using SSMS, you can compare the two plans. Look for differences in those properties (like I do here). That's likely going to point out the difference.
"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
June 21, 2021 at 3:09 pm
We need to see the index definitions as well. It seems to me that the index definitions would need to be different to produce plans and stats that drastically different.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 21, 2021 at 3:18 pm
We need to see the index definitions as well. It seems to me that the index definitions would need to be different to produce plans and stats that drastically different.
They've said it's a straight restore between the two, so I suspect it's something other than data, structures or stats, assuming nothing was done beyond the restore operation.
"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
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply