SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Same query different actual execution plan


Same query different actual execution plan

Author
Message
Jay@Work
Jay@Work
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2006 Visits: 135
I guess I could summarise my question by asking if a query plan is created based on available resources.

Here is the long winded version.
We have a database which we regularly restore to our development environment.
Recently a piece of code has become very sluggish in the development environment. The dev environment behaves well on the whole.
This database is identical in both environments in that the SQL version is the same, the indexes and statistics are maintained weekly.
The only difference is production is 4 CPUs and 32GB RAM where as the dev is 2 CPUs and 16 GB RAM.

Now if we were talking a few seconds difference I would put it down to specs but the difference is instant for production and around 1 minute for dev.
Little else is happening in Dev, there is no locking/blocking/stress.
I look at the actual execution plan for the same query on both servers and there is a stand out difference. I can send screenshots/XML if it helps?
Basically production uses a key lookup with a 100% cost where as dev is using a "hash mash (inner join)" for 53% cost and a few repartition(?) steps (query performance is not my strong point)!
On dev the EP tells me I should create an index to reduce the time by 89% whereas production just spits it our in under a second.
Both tables have the same number of rows, the same indexes, keys everything!

Would this change of execution plan be based on the available CPU/RAM at the time the query ran. I'm at a complete loss to understand the difference in EP and performance across the 2 servers.
I've even had the VM guy check under the hood to make sure there is no disk/VM reasons.
The perplexing thing is that all other queries/functions I've looked at in this DB/Application behave almost identically bar a few milliseconds
Attachments
dev.sqlplan (20 views, 112.00 KB)
production.sqlplan (14 views, 105.00 KB)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)

Group: General Forum Members
Points: 385280 Visits: 42514
Jay@Work - Monday, March 5, 2018 4:59 PM
I guess I could summarise my question by asking if a query plan is created based on available resources.

Here is the long winded version.
We have a database which we regularly restore to our development environment.
Recently a piece of code has become very sluggish in the development environment. The dev environment behaves well on the whole.
This database is identical in both environments in that the SQL version is the same, the indexes and statistics are maintained weekly.
The only difference is production is 4 CPUs and 32GB RAM where as the dev is 2 CPUs and 16 GB RAM.

Now if we were talking a few seconds difference I would put it down to specs but the difference is instant for production and around 1 minute for dev.
Little else is happening in Dev, there is no locking/blocking/stress.
I look at the actual execution plan for the same query on both servers and there is a stand out difference. I can send screenshots/XML if it helps?
Basically production uses a key lookup with a 100% cost where as dev is using a "hash mash (inner join)" for 53% cost and a few repartition(?) steps (query performance is not my strong point)!
On dev the EP tells me I should create an index to reduce the time by 89% whereas production just spits it our in under a second.
Both tables have the same number of rows, the same indexes, keys everything!

Would this change of execution plan be based on the available CPU/RAM at the time the query ran. I'm at a complete loss to understand the difference in EP and performance across the 2 servers.
I've even had the VM guy check under the hood to make sure there is no disk/VM reasons.
The perplexing thing is that all other queries/functions I've looked at in this DB/Application behave almost identically bar a few milliseconds


Would help if you posted the two execution plans (as *.sqlplan files). Hard to say anything without that information.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
RandomStream
RandomStream
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2895 Visits: 648

This article has been invaluable to me:
Slow in the Application, Fast in SSMS?


Jay@Work
Jay@Work
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2006 Visits: 135
RandomStream - Monday, March 5, 2018 5:33 PM

This article has been invaluable to me:
Slow in the Application, Fast in SSMS?


The query is slow both in the application and SSMS. But instant in both production areas
Jay@Work
Jay@Work
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2006 Visits: 135
Lynn Pettis - Monday, March 5, 2018 5:07 PM
Jay@Work - Monday, March 5, 2018 4:59 PM
I guess I could summarise my question by asking if a query plan is created based on available resources.

Here is the long winded version.
We have a database which we regularly restore to our development environment.
Recently a piece of code has become very sluggish in the development environment. The dev environment behaves well on the whole.
This database is identical in both environments in that the SQL version is the same, the indexes and statistics are maintained weekly.
The only difference is production is 4 CPUs and 32GB RAM where as the dev is 2 CPUs and 16 GB RAM.

Now if we were talking a few seconds difference I would put it down to specs but the difference is instant for production and around 1 minute for dev.
Little else is happening in Dev, there is no locking/blocking/stress.
I look at the actual execution plan for the same query on both servers and there is a stand out difference. I can send screenshots/XML if it helps?
Basically production uses a key lookup with a 100% cost where as dev is using a "hash mash (inner join)" for 53% cost and a few repartition(?) steps (query performance is not my strong point)!
On dev the EP tells me I should create an index to reduce the time by 89% whereas production just spits it our in under a second.
Both tables have the same number of rows, the same indexes, keys everything!

Would this change of execution plan be based on the available CPU/RAM at the time the query ran. I'm at a complete loss to understand the difference in EP and performance across the 2 servers.
I've even had the VM guy check under the hood to make sure there is no disk/VM reasons.
The perplexing thing is that all other queries/functions I've looked at in this DB/Application behave almost identically bar a few milliseconds


Would help if you posted the two execution plans (as *.sqlplan files). Hard to say anything without that information.


I have edited the post and added the 2 files
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (163K reputation)SSC Guru (163K reputation)SSC Guru (163K reputation)SSC Guru (163K reputation)SSC Guru (163K reputation)SSC Guru (163K reputation)SSC Guru (163K reputation)SSC Guru (163K reputation)

Group: General Forum Members
Points: 163998 Visits: 21465

The estimates are way out, but I’m guessingthat’s your data rather than stale stats.

It would be interesting to see the plan fromthe dev environment, with a nested loops join forced:

select t.charge_ctr,

t.transaction_date,

sum(d.AMOUNT),

t.status,

COUNT(*)

from nucChargeTransaction t

INNER loop JOINNUCCHARGETRANSACTION d

ON d.CHARGE_CTR= t.CHARGE_CTR

where t.transaction_ctr =50469380

and d.status in ('C','F')

group by t.CHARGE_CTR, t.TRANSACTION_DATE, t.STATUS

Check that the index [CHARGECTR_AK] is thesame in both DB’s.

Add column [Amount] to the INCLUDE part of thisindex in both DB’s to eliminate the key lookup.



“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
Exploring Recursive CTEs by Example Dwain Camps
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)

Group: General Forum Members
Points: 385280 Visits: 42514
Several things looking at the execution plans.
One, drop the (nolock) hint. This is not a "go fast button" and could potentially cause erroneous data to be returned.
Two are the tables nucChargeTransaction and NUCCHARGETRANSACTION the same tables? Is the join between them a self join? If so, why?
Three, the difference I see between dev and production is the volume of data being returned. In dev you are returning close to 45 million rows of data for status in 'C' or 'F'. In production, however, you are only returning 5 rows of data for status in 'C' or 'F'.

Please post the DDL (CREATE TABLE statement) for the table(s) involved including all indexes defined. Also, please post some sample data (no more than 10 rows of data) as INSERT statements using Table Value Constructor format, https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql.
Also, based on the sample data, show us what the expected results should be from the query.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jay@Work
Jay@Work
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2006 Visits: 135
Lynn Pettis - Tuesday, March 6, 2018 8:23 AM
Several things looking at the execution plans.
One, drop the (nolock) hint. This is not a "go fast button" and could potentially cause erroneous data to be returned.
Two are the tables nucChargeTransaction and NUCCHARGETRANSACTION the same tables? Is the join between them a self join? If so, why?
Three, the difference I see between dev and production is the volume of data being returned. In dev you are returning close to 45 million rows of data for status in 'C' or 'F'. In production, however, you are only returning 5 rows of data for status in 'C' or 'F'.

Please post the DDL (CREATE TABLE statement) for the table(s) involved including all indexes defined. Also, please post some sample data (no more than 10 rows of data) as INSERT statements using Table Value Constructor format, https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql.
Also, based on the sample data, show us what the expected results should be from the query.


Thanks I'll post the results of the above query - yes the tables are the same. The data is pretty much identical in both environments as we restore production over the top of dev.
This is a 3rd party application so I can't explain the why's. We could replace the underlying SP with one of our own if the performance becomes an issue (in production it takes a couple of milliseconds) but my query more relates to the difference in execution plan and would this be the result of the 2 vastly different execution times?
Jay@Work
Jay@Work
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2006 Visits: 135
Jay@Work - Tuesday, March 6, 2018 12:03 PM
Lynn Pettis - Tuesday, March 6, 2018 8:23 AM
Several things looking at the execution plans.
One, drop the (nolock) hint. This is not a "go fast button" and could potentially cause erroneous data to be returned.
Two are the tables nucChargeTransaction and NUCCHARGETRANSACTION the same tables? Is the join between them a self join? If so, why?
Three, the difference I see between dev and production is the volume of data being returned. In dev you are returning close to 45 million rows of data for status in 'C' or 'F'. In production, however, you are only returning 5 rows of data for status in 'C' or 'F'.

Please post the DDL (CREATE TABLE statement) for the table(s) involved including all indexes defined. Also, please post some sample data (no more than 10 rows of data) as INSERT statements using Table Value Constructor format, https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql.
Also, based on the sample data, show us what the expected results should be from the query.


Thanks I'll post the results of the above query - yes the tables are the same. The data is pretty much identical in both environments as we restore production over the top of dev.
This is a 3rd party application so I can't explain the why's. We could replace the underlying SP with one of our own if the performance becomes an issue (in production it takes a couple of milliseconds) but my query more relates to the difference in execution plan and would this be the result of the 2 vastly different execution times?


Sorry forgot to mention that ultimately both environments return the same number or rows (which is 1). Why there is such a huge difference in the rows being handled mid-code I don't know. I just (guessed) put it down to the different ways in which the EP was extracting it
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)

Group: General Forum Members
Points: 385280 Visits: 42514
Jay@Work - Tuesday, March 6, 2018 12:03 PM
Lynn Pettis - Tuesday, March 6, 2018 8:23 AM
Several things looking at the execution plans.
One, drop the (nolock) hint. This is not a "go fast button" and could potentially cause erroneous data to be returned.
Two are the tables nucChargeTransaction and NUCCHARGETRANSACTION the same tables? Is the join between them a self join? If so, why?
Three, the difference I see between dev and production is the volume of data being returned. In dev you are returning close to 45 million rows of data for status in 'C' or 'F'. In production, however, you are only returning 5 rows of data for status in 'C' or 'F'.

Please post the DDL (CREATE TABLE statement) for the table(s) involved including all indexes defined. Also, please post some sample data (no more than 10 rows of data) as INSERT statements using Table Value Constructor format, https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql.
Also, based on the sample data, show us what the expected results should be from the query.


Thanks I'll post the results of the above query - yes the tables are the same. The data is pretty much identical in both environments as we restore production over the top of dev.
This is a 3rd party application so I can't explain the why's. We could replace the underlying SP with one of our own if the performance becomes an issue (in production it takes a couple of milliseconds) but my query more relates to the difference in execution plan and would this be the result of the 2 vastly different execution times?


See point 3. In dev the query is pulling close to 45 million rows of data from nucChargeTransaction table and only 5 rows of data from the same table in production for the same criteria.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search