SQL Servers query time differences

  • 2019 Standard rtm-cu8-gdr and enterprise. A sql stored procedure is running differently(picking up different execution plans) when run across these 2 editions.How do we zero in the problem and make sure the query runs as good on standard as it currently is on enterprise.

     

     

    • This topic was modified 1 month, 2 weeks ago by  mtz676.
    • This topic was modified 1 month, 2 weeks ago by  mtz676.
    • This topic was modified 1 month, 1 week ago by  mtz676.
    • This topic was modified 1 month ago by  mtz676.
    • This topic was modified 1 month ago by  mtz676.
  • The fact that the 2 queries are the same doesn't guarantee that you'll have the same query plan.  The query plan depends on the data in the tables and the statistics that the tables have.  If the data and statistics in both databases are different, then you can have different plans.   If you are using stored procedures and not batches, then you have to remember that the stored procedures' query plan is set at the first time that you activate the stored procedure and there is no plan for it in the plan cache.  In that case the server will create a query plan that is depended also on the procedure's parameters, so if you activated both procedures in each server with a different set of parameters, they could also have different query plans even if the data and statistics are the same

    Adi

  • Why are using the RTM version instead of applying patches?  You'll have performance issues from that as well.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • A few things noted above, but also query store can help here, and force a particular plan for a query.

  • While just moving from Standard to Enterprise can result in differences in execution plans, you may also be seeing other things. Check the server settings. Stuff like: default ANSI connection settings, Degree of Parallelism, Cost Threshold for Parallelism, Compatibility Level on the database. That's not an exhaustive list, but all those can result in differences in execution plans, even if the query, the data, and the statistics are 100% identical. However, another one that's possible, what are the statistics maintenance routines like between the two servers? Just because the data is the same, same structures, same query, doesn't mean the statistics are the same. This can also lead to differences in execution plans.

    Query Store is a simple way to monitor for this kind of behavior. For a given database, you can enable Query Store, run a test, say on Standard edition. Backup that database. Query Store data goes with it. Restore the database to an Enterprise edition server. Run the test again. Query Store will show you if the plan changed, and allow you to then compare the plans to understand why the differences are occurring. And then, as Steve notes, you can force the plan to behave appropriately (assuming you're not trying to force Enterprise behavior within a Standard edition server, that will just quietly fail).

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

  • Thank you everybody.

    • This reply was modified 1 month, 2 weeks ago by  mtz676.
  • Thank you @Grant and everybody else.

    Can a Stored Procedure(has several parameters as input) run differently in standard or enterprise version when executed from SSMS when compared getting triggered from the application? Can it pick different execution plans in standard and enterprise.(assuming everything remains the same.)Same Db in both standard and enterprise.(backed up from enterprise aand restored to standard).Same tables,same volume of data,same indexes, stats etc but still the stored procedure runs longer in standard than in enterprise as it picks up different execution plans. Where do I start my troubleshooting. Both sql versions run on the same windows box one at a time and not together.So underlying hardware stays the same. Thanks

  • The plans should be the same, if all knobs/config settings are the same and the data/stats are the same. If you mean you're attaching the database separately to standard and ee and things are different, I'd like to see a repro. It's entirely possible there is some difference here I'm not thinking about, but I don't know what EE feature would cause this.

    I'm sure the QE/QP team at MS would be interested. If you have a (not too large) repro, I'll pass it along.

  • I'd have to see the plans to understand what is happening. Compile variables, ANSI connection settings, these kinds of things can result in differences. There's no way to pick one thing out and say "this is it" without having the plans to compare.

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

  • @Steve. Thanks

    What settings/config values should be checking in Standard and enterprise (fyi:both on same machine)to troubleshoot this.A back and restore has been done from enterprise to standard, so data stays the same.Is there a list /reference you can pass along.

    Thanks

    • This reply was modified 1 month, 2 weeks ago by  mtz676.
  • @steve-2 Jones, @Grant and all,

    Thank you.

    I have saved the execution plans from enterprise and standard edition. How can I force the stored procedure to use the enterprise plan when I run the stored procedure on standard version? This possibly is far from the answer.But I am curious if this can done. Please advise.

     

    FYI: The SP has many queries as part of the execution plan and only 1 seems to show the big difference as listed underneath.

    I do understand the data is pretty vague underneath but any insight will be appreciated.Thanks

    FYI: The enterprise uses

    index seek(nonclustered view),(Number of rows read 2500,estimated rows to be read 800)

    nested loop (actual number of rows for all execution 2500,estimated number of executions 1, estimated number of rows per execution 2400)

    table scan(number of rows read - 5;estimated rows to be read - 5)

    and the

    standard uses

    table scan - 5

    Hash Match(Inner join),(actual rows 5000 estimated rows 150000)

    Clustered Index scan,(actual rows read and estimated rows read - 1800000)

    Non clustered index scan - 350000(number of rows read, estimated rows read)

    Merge(Inner) Join(actual rows 200000, estimated 350000)

    for the same stored procedure when the same set of parameters are passed.

    • This reply was modified 1 month, 1 week ago by  mtz676.
    • This reply was modified 1 month, 1 week ago by  mtz676.
    • This reply was modified 1 month, 1 week ago by  mtz676.
  • If you could post the plans (note, please, not a picture of the plans, but the xml files), we might be able to provide insight. With just a description, I've got nothing.

    A place to start though, look at the properties of the first operator in each plan. Using SSMS, you can compare the two plans. Look for differences in those properties (like I do here). That's likely going to point out the difference.

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

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

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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

    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

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

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