Identical Query, Different Execution Stats

Question

Identical Query, Different Execution Stats

mg_dba

SSCrazy

Points: 2679
More actions
July 19, 2016 at 9:01 am

#315421

Hi All,
I have restored a copy of a production database to my local machine to troubleshoot a problematic query. When I execute the query on production I'm seeing ~3,000,000 logical reads, whereas when I execute the exact same query locally I'm only seeing ~500,000 logical reads, resulting in a much faster execution time (also zero physical reads in both cases).
As far as I can see, other than the physical environment, there are no differences whatsoever between the two. The indexes, data, execution plans, SET options etc. are all absolutely identical.
Are there any other factors that could influence the number of reads in this way? What am I missing?! :ermm:
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Have you checked fragmentation?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 2

And then check for compression on the one with fewer reads.

"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: 1003865 More actions · Answer 3

And stats. Small change in stats can make for wildly different executions.

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

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

Jeff Moden (7/19/2016)
And stats. Small change in stats can make for wildly different executions.

Absolutely!

Also, plans can look the same but still be very different on the inside, estimated versus actual is one of the bigger issues that could evidence itself like this. Be sure "the same" is really true.

"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

mg_dba SSCrazy Points: 2679 More actions · Answer 5

Hi All,

Many thanks for all your responses. I have restored a fresh copy of the database to try and ensure there are no differences and all these things appear to be identical as far as I can tell. The fragmentation of the indexes being used is the same to within a couple of percent and there is no compression on any of the tables. As far as I can see the stats are the same too (as you would expect from an exact copy of the database I assume?)

Both the plans I'm comparing are actual execution plans rather than estimated. These are identical right down to the join methods used (nested loops, hash match etc.).

Not sure what else there is to consider? Interestingly, the query uses two table variables and IO STATS shows the bulk of the additional reads on the production server seems to be against those, even though they appear identically in the execution plan. Could this be tempdb related somehow? Any other ideas?

Thanks

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

matt.gyton (7/20/2016)
Hi All,
Many thanks for all your responses. I have restored a fresh copy of the database to try and ensure there are no differences and all these things appear to be identical as far as I can tell. The fragmentation of the indexes being used is the same to within a couple of percent and there is no compression on any of the tables. As far as I can see the stats are the same too (as you would expect from an exact copy of the database I assume?)
Both the plans I'm comparing are actual execution plans rather than estimated. These are identical right down to the join methods used (nested loops, hash match etc.).
Not sure what else there is to consider? Interestingly, the query uses two table variables and IO STATS shows the bulk of the additional reads on the production server seems to be against those, even though they appear identically in the execution plan. Could this be tempdb related somehow? Any other ideas?
Thanks

did you compare all the properties of the first operator between the two plans? Compatibility level of the databases after the restore? Hmmm... there has to be a difference somewhere. It's about identifying it.

"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

mg_dba SSCrazy Points: 2679 More actions · Answer 7

Think I may have a solution!

I experimented with the plans by comparing the two XML versions on a diff checker and they came up almost identical with the exception of a few minor differences in estimated costs etc., so just to make sure I forced the same plan using OPTION(USE PLAN...) and still no improvement.

Anyway, what seems to have made an improvement was substituting the following code in the stored proc (yeah, this was a stored procedure...sorry, I probably should've mentioned that!)

I changed this...

NOT EXISTS(SELECT TOP 1 Id FROM @Table

...to this...

NOT EXISTS(SELECT 1 FROM @Table

This seems to have got rid of the additional reads and significantly improved the performance of the query on the production server. I can see why it would've reduced the reads, but the only thing I'm not sure about is why this didn't make any difference on my local machine?! I get the same performance with both versions there.

Anyway, thanks for all your suggestions...further testing will be done tomorrow, my brain is fried! :doze:

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 8

Shifting gears a bit, I've recently come across a similar post. Setting trace flag 1118 apparently worked a miracle. I forget about such things because I've had that set since I first read Paul Randal's article on the subject.

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

mg_dba SSCrazy Points: 2679 More actions · Answer 9

Thanks Jeff, I wasn't aware of that...just read the article - looks interesting! I don't think we suffer too heavily from TempDB contention at present, but it's certainly worth looking into.

Cheers

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

matt.gyton (7/20/2016)
Think I may have a solution!
I experimented with the plans by comparing the two XML versions on a diff checker and they came up almost identical with the exception of a few minor differences in estimated costs etc., so just to make sure I forced the same plan using OPTION(USE PLAN...) and still no improvement.
Anyway, what seems to have made an improvement was substituting the following code in the stored proc (yeah, this was a stored procedure...sorry, I probably should've mentioned that!)
I changed this...
NOT EXISTS(SELECT TOP 1 Id FROM @Table
...to this...
NOT EXISTS(SELECT 1 FROM @Table
This seems to have got rid of the additional reads and significantly improved the performance of the query on the production server. I can see why it would've reduced the reads, but the only thing I'm not sure about is why this didn't make any difference on my local machine?! I get the same performance with both versions there.
Anyway, thanks for all your suggestions...further testing will be done tomorrow, my brain is fried! :doze:

Whilst it doesn't address the reason for your post, it's important to know the differences between these two types of subquery.

NOT EXISTS(SELECT TOP 1 Id FROM @Table... will evaluate the subquery exactly as if it isn't in a conditional block using whatever operators are appropriate.

NOT EXISTS(SELECT 1 FROM @Table... is identified as a row-goal query by the optimiser and is evaluated quite differently; typically only 1 row is estimated which steers the optimiser to nested-loops joins. If there is a row to be found, then execution of the subquery immediately stops - there's no point in continuing. The estimate of 1 row (for a goal of 1 row) and tendency to accommodate it by using nested-loops joins can be disastrous if there are large tables with many matching rows involved in the subquery.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mg_dba SSCrazy Points: 2679 More actions · Answer 11

So it turns out my earlier optimism was slightly misplaced....the code change did not provide the fix 🙁

However, I noticed that it temporarily improved performance until the SP ran with a specific parameter, then it began performing poorly again. I was slightly surprised, because I had already added OPTION(RECOMPILE) to the main statement in the SP to try and prevent parameter sniffing, which seemed to have worked previously.

I have now recreated the SP using CREATE PROCEDURE...WITH RECOMPILE and it seems to give much more consistent performance results. Not sure why the OPTION(RECOMPILE) was not sufficient, but it looks promising none the less...!

mg_dba SSCrazy Points: 2679 More actions · Answer 12

ChrisM@Work (7/21/2016)
matt.gyton (7/20/2016)
Think I may have a solution!
I experimented with the plans by comparing the two XML versions on a diff checker and they came up almost identical with the exception of a few minor differences in estimated costs etc., so just to make sure I forced the same plan using OPTION(USE PLAN...) and still no improvement.
Anyway, what seems to have made an improvement was substituting the following code in the stored proc (yeah, this was a stored procedure...sorry, I probably should've mentioned that!)
I changed this...
NOT EXISTS(SELECT TOP 1 Id FROM @Table
...to this...
NOT EXISTS(SELECT 1 FROM @Table
This seems to have got rid of the additional reads and significantly improved the performance of the query on the production server. I can see why it would've reduced the reads, but the only thing I'm not sure about is why this didn't make any difference on my local machine?! I get the same performance with both versions there.
Anyway, thanks for all your suggestions...further testing will be done tomorrow, my brain is fried! :doze:
Whilst it doesn't address the reason for your post, it's important to know the differences between these two types of subquery.
NOT EXISTS(SELECT TOP 1 Id FROM @Table... will evaluate the subquery exactly as if it isn't in a conditional block using whatever operators are appropriate.
NOT EXISTS(SELECT 1 FROM @Table... is identified as a row-goal query by the optimiser and is evaluated quite differently; typically only 1 row is estimated which steers the optimiser to nested-loops joins. If there is a row to be found, then execution of the subquery immediately stops - there's no point in continuing. The estimate of 1 row (for a goal of 1 row) and tendency to accommodate it by using nested-loops joins can be disastrous if there are large tables with many matching rows involved in the subquery.

That's interesting - thanks. In this instance there is only the one table variable in the subquery, so I'm guessing it shouldn't matter too much...good to know though!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

matt.gyton (7/21/2016)
So it turns out my earlier optimism was slightly misplaced....the code change did not provide the fix 🙁
However, I noticed that it temporarily improved performance until the SP ran with a specific parameter, then it began performing poorly again. I was slightly surprised, because I had already added OPTION(RECOMPILE) to the main statement in the SP to try and prevent parameter sniffing, which seemed to have worked previously.
I have now recreated the SP using CREATE PROCEDURE...WITH RECOMPILE and it seems to give much more consistent performance results. Not sure why the OPTION(RECOMPILE) was not sufficient, but it looks promising none the less...!

This is interesting. Can you post an example actual plan for a slow and a fast execution?

How many rows in your table variables?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden