SQL Servers query time differences

Question

SQL Servers query time differences

Viewing 14 posts - 16 through 28 (of 28 total)

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

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

ScottPletcher wrote:

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

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 2

Stats were never updated on both the versions (standard and enterprise).Both stand 2 years old.Checked some tables which are referred in the stored proc.They stats are the same.

Looks like statistics update helped here(old sql versions):

https://www.sqlservercentral.com/forums/topic/update-statistics-is-part-of-a-restore#:~:text=If%20the%20statistics%20were%20out,t%20modify%20any%20of%20it.

Should I try the same?

This reply was modified 3 years, 5 months ago by mtz676.
This reply was modified 3 years, 5 months ago by mtz676.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

mtz676 wrote:

I have saved the execution plans from enterprise and standard edition.

I've probably missed them. Which post did you attach them to?

--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)

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 4

@scott,@Grant,@Steve, All

We have again done a straight back and restore across enterprise to standard.Under what circumstances can the execution plans be different.Both instances run on same windows and do not run at the same time to test performance.

Thanks

This reply was modified 3 years, 5 months ago by mtz676.
This reply was modified 3 years, 5 months ago by mtz676.
This reply was modified 3 years, 5 months ago by mtz676.
This reply was modified 3 years, 5 months ago by mtz676.
This reply was modified 3 years, 5 months ago by mtz676.
This reply was modified 3 years, 5 months ago by mtz676.
This reply was modified 3 years, 5 months ago by mtz676.
This reply was modified 3 years, 5 months ago by mtz676.
This reply was modified 3 years, 5 months ago by mtz676.

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

mtz676 wrote:

I do understand I am not posting the plans making this cumbersome for all.

Yes.

Does this mean it needs only 3200 rows but is ending up reading 1700000 rows to make that decision.

Yeah, it's filtering down to 3200 rows from 1.7 million

Also found out that some of the same tables(~5 tables in the stored proc) in standard version have 0.03 to 0.15 percent more number of rows than enterprise.Can this make a difference in execution plans?

OK. Here we have a problem. Are we working from perfectly identical databases (other than their location). If not, this is an exercise in futility. Yes, data differences can result in different plans. Yes, statistics differences can result in different plans. If we're not looking at the exact same database, restored from the same backup, with the same data, the same statistics, the same everything, so that the ONLY difference is standard versus enterprise, then it could be differences elsewhere. In order to proceed, this has to be 100% perfectly clear.

I'm not trying to be in any way mean, but it feels like we're bouncing around on this topic too much. I'm trying to be emphatic here, not angry. We're trying to help out, but keeping us in the dark on the plans makes this really hard.

"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

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 6

My bad ,I understand. Thanks again @Grant.

This reply was modified 3 years, 5 months ago by mtz676.

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

We are trying to help.

You said the rows were different. Do you mean the row estimates are different? That's different and could be an indication of the difference between enterprise & standard, maybe.

Generally speaking, and I'm really talking broad strokes, you should expect the same, or better, performance from enterprise.

"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

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 8

@Grant/All,absolutely, the advise I have received from this community has been unparalleled.Learnt a lot from here and I rely more here than the web.

Initially the DB was not a true copy between enterprise and standard. That has been rectified and now it is a true copy but it still is picking up different execution plans. On same windows box,2 instances(enterprise and standard) running 1 at a time to test performance,same SP returns same number of output rows,both enterprise and standard have stale statistics but neverthless still the same as it it is a back/restore so same database copies but still different execution plans. Does enterprise prefer to use indexed views ?Can a stats update help standard perform better/equal enterprise here and this is probably a too generic question to ask but as you mentioned if enterprise sql is built to pick up equal or better quicker/less resource intensive execution plans than standard when everything else stays the same,What is it that helps enterprise do so and how do we identify that, that makes it better? Please ignore my question if there is no straight answer and as I have not shared the plans. Thanks again for the responses and for being patient.

This reply was modified 3 years, 5 months ago by mtz676.
This reply was modified 3 years, 5 months ago by mtz676.
This reply was modified 3 years, 5 months ago by mtz676.
This reply was modified 3 years, 5 months ago by mtz676.

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

So, go to this Microsoft web page. Scroll down to the section titled "RDBMS scalability and performance". Look at stuff like, row mode memory grant feedback and others. A whole bunch of performance stuff is Enterprise only. Yes, you will see differences in execution plans between the two because of all this. Now, which of these are you seeing? No idea (can't tell w/o the plan), but yeah, it's very likely to see differences. It's very likely to see better performance from Enterprise. And yeah, that's on purpose. Microsoft would like you to spend more money. So, in lots of circumstances, Standard = Enterprise. However, not in all circumstances. It's likely, this is what you're seeing.