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


Odd behavior -- execution plan different when I execute in the target database vs not in the target...


Odd behavior -- execution plan different when I execute in the target database vs not in the target database

Author
Message
llevity
llevity
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1599 Visits: 385
So I have a query that's basically SELECT column FROM DB1.Schema.View. The view is a bit of a mess, does some crazy unions on other tables, etc, but... it works, and has a cost of 300.

However, if I execute it in the context of a database that's not the one I'm selecting from, IE, the dropdown at the top left of SSMS has DB2 selected, the cost jumps up to like 3billion, and it never finshes. It's spending most of it's cost on a tempDB sort.

Further more, running it in the context of DB3 or DB4 also works fine. It just doesn't like DB2, and I'm not sure why.
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)

Group: General Forum Members
Points: 132671 Visits: 8996
Maybe DB2 has the same objects and something different (like 10000X more data, missing indexes, different data types, etc)?

The best way to help you is to post the query plans.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jason A. Long
Jason A. Long
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22944 Visits: 8463
TheSQLGuru - Monday, May 7, 2018 6:05 PM
Maybe DB2 has the same objects and something different (like 10000X more data, missing indexes, different data types, etc)?

The best way to help you is to post the query plans.

Good to see you back Kevin! Hope all is well.

In adition to Kevins comments, different server settings and available resources. That said, If you're getting a spill to tempdb in one but not the other, the usual suspects are massive desparities in row counrs (already mentioned by Keni) and/or out of date stastics on one instance but not the other. Also, check to make sure both versions are set to use the same compatibility level and the same version of the cardinality estimator.

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)

Group: General Forum Members
Points: 391448 Visits: 34705
Also could be that the optimizer fixes are enabled/disabled on one of the databases and not the others. That would certainly lead to differences, especially if the query is already a bit on the edge as you describe.

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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)

Group: General Forum Members
Points: 132671 Visits: 8996
Jason A. Long - Monday, May 7, 2018 9:23 PM
TheSQLGuru - Monday, May 7, 2018 6:05 PM
Maybe DB2 has the same objects and something different (like 10000X more data, missing indexes, different data types, etc)?

The best way to help you is to post the query plans.

Good to see you back Kevin! Hope all is well.


Thanks for asking Jason, but sadly no. Sad But I miss helping the SQL Community out so I have decided to pick one to respond to now and again.


Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jason A. Long
Jason A. Long
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22944 Visits: 8463
TheSQLGuru - Tuesday, May 8, 2018 11:04 AM
Jason A. Long - Monday, May 7, 2018 9:23 PM
TheSQLGuru - Monday, May 7, 2018 6:05 PM
Maybe DB2 has the same objects and something different (like 10000X more data, missing indexes, different data types, etc)?

The best way to help you is to post the query plans.

Good to see you back Kevin! Hope all is well.


Thanks for asking Jason, but sadly no. Sad But I miss helping the SQL Community out so I have decided to pick one to respond to now and again.

I'm not one to pry, so I won't... but if there's anything I can do, shoot me a PM.
In any case, whatever it is, I'm rooting for you. Smile

llevity
llevity
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1599 Visits: 385
To clarify, I'm running the exact same query, against the exact same database target. It's just the explain plan differs significantly in cost whether or not I have the correct database selected in the context dropdown in the upper left in SSMS.

Attached are the two plans. Good.sqlplan is when I'm in DataMart in the drop down DB context (and the query points to data in the DataMart database), Bad.sqlplan is when I'm in a different database context, but it's still the same query as Good.sqlplan.

I'm baffled still. I've tried updating stats on DataMart, no luck.
Attachments
Good.sqlplan (37 views, 3.00 MB)
Bad.sqlplan (51 views, 3.00 MB)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)

Group: General Forum Members
Points: 391448 Visits: 34705
llevity - Tuesday, May 8, 2018 11:29 AM
To clarify, I'm running the exact same query, against the exact same database target. It's just the explain plan differs significantly in cost whether or not I have the correct database selected in the context dropdown in the upper left in SSMS.

Attached are the two plans. Good.sqlplan is when I'm in DataMart in the drop down DB context (and the query points to data in the DataMart database), Bad.sqlplan is when I'm in a different database context, but it's still the same query as Good.sqlplan.

I'm baffled still. I've tried updating stats on DataMart, no luck.


But that context change thing means you are changing the database, so you're not running it in one, but different databases. Give me a bit and I'll look at the exec plans.

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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
llevity
llevity
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1599 Visits: 385
Ah ok, I see but I'm fully qualifying the DB in the query. Here's the query in question that I used above. Whatever I have selected in the context, it should query DataMart, right? What does the context thing even effect when fully qualifying in the query?

SELECT count(*)
FROM [Datamart].[VersaReg].[DMV_ESE_FACILITIES]


Sue_H
Sue_H
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87927 Visits: 17603
llevity - Tuesday, May 8, 2018 11:29 AM
To clarify, I'm running the exact same query, against the exact same database target. It's just the explain plan differs significantly in cost whether or not I have the correct database selected in the context dropdown in the upper left in SSMS.

Attached are the two plans. Good.sqlplan is when I'm in DataMart in the drop down DB context (and the query points to data in the DataMart database), Bad.sqlplan is when I'm in a different database context, but it's still the same query as Good.sqlplan.

I'm baffled still. I've tried updating stats on DataMart, no luck.

You are changing databases and the databases aren't the same - they are using two different cardinality estimator versions. Check the compatibility level of the two databases.

Sue




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