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

  • 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.

  • 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

  • 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.

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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. 🙁 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

  • 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. 🙁 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. 🙂

  • 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.

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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] 

  • 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

  • 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.

    Looks to me like the two databases either have radically different data sets or radically different statistics (possibly caused by radically different data sets). Since these are estimated plans, I can only see the row estimates, not the actual rows, but based on the row estimates, there are differences that are likely caused by stats or the data itself.

    However, one database has a cardinality of 130 and the other 70. That could also lead to the differences. One of the plans timed out (the 130 version, the bad plan). Looks like you're getting "good" behavior from the old cardinality estimator. Since this is a view that you're pulling from, I can't tell if the code within it is the same on both databases or not, but there could be differences there too. I suspect there might be because (assuming I counting correctly) the "good" plan has 23 different PlanAffectingConvert warnings and the "bad" plan has 22. That implies code differences.

    Between the code differences and the cardinality differences, that alone could explain everything. However, I still think some of the row estimate differences are caused by data or stats, not the cardinality estimation engine differences.

    "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

  • I'm having a moment where I question whether anything I know is true!  🙂  Is my basic understanding of what the context thing does wrong?  If I have a query that includes the database name, what does the context even do?  In my example, say I'm doing the below:

    Select * from Datamart.MySchema.MyTable;

    Whatever I have selected in the context, it still pulls the data from DataMart database, correct?  In my case, I have the context set to [NotDatamart], and I get the bad query plan.  I get the good query plan when the context is set to [DataMart].  And in any event, there's not even a MySchema.MyTable IN the [NotDatamart] database.

  • Just verified it to be sure, the calling databases cardinality estimation is used in cross-database queries. So, yeah, when you change the database to the one that has a different cardinality estimator, probably because of compatibility level, but possibly through database scoped configurations.

    "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

  • Yep, bingo, the databases are at different compatibility level. Good call on that Sue, and thanks for the help and verification, Grant.  Now to read on about cardinality estimators in the different compatibility levels.  It works better in the older comp version, unfortunately, so my next step is to experiment with the different solution approaches.

    Thanks all!

  • 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.

    What is the actual performance difference between the two plans when you run the queries against that view ([Datamart].[VersaReg].[DMV_ESE_FACILITIES]) for real?

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

Viewing 15 posts - 1 through 15 (of 15 total)

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