Post reply

slow query

  • August 8, 2020 at 3:10 am

    #3778351

    Hi All,

    I have some issue with a slow running query and im quite new to tuning and have no idea how to proceed with this. For some reason I cant attach the plan file with this post and hence sharing the drive link to the plan

    https://drive.google.com/drive/folders/1zx-vhscuW1DcPNyIzJqak7hy0ufPwI9N?usp=sharing

    After referring the internet, tried creating some nonclustered index and updated statistics but it didn't help out. Please help

    Thanks in advance

    NB

     

    • This topic was modified 4 years, 3 months ago by  thenewbee.

  • August 8, 2020 at 9:03 am

    #3778377

    There isn't a simple answer to this one, but the first thing that sticks out is the excessive cardinality in thee scan and seek operators, hundreds of millions of rows. Best guess is to try to limit those first before looking at anything else.

    😎

    A query such as this one, with roughly 20 objects, can easily take days of work to optimise, my advice is to go for the greatest costs first and avoid being sidetracked on issues that may not be important in the grand scheme of things.

  • August 8, 2020 at 7:28 pm

    #3778420

    Hi Erikur,

    The view is actually a datasource for an SSRS report and it has the date from and to filters. So the view is supposed to bring back all the records and then filtered before rendering SSRS report.

    The greatest cost I can see is for a sort operation( please correct me if I'm wrong), can you please advise how to proceed.

    Thanks

  • August 9, 2020 at 5:18 pm

    #3778488

    For me, the link provided by the OP doesn't point to an SQLPlan file.  It points to a file but SQL Server doesn't recognize it as an SQLPlan.

    Eirikur, what did you use to interrogate the file?

     

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

  • August 9, 2020 at 11:28 pm

    #3778500

    Hi Jeff,

    It’s a SQL Sentry plan explorer file.

    Thanks

    NB

  • August 10, 2020 at 5:29 am

    #3778525

    Jeff Moden wrote:

    For me, the link provided by the OP doesn't point to an SQLPlan file.  It points to a file but SQL Server doesn't recognize it as an SQLPlan.

    Eirikur, what did you use to interrogate the file?

    The file is from SentryOne Plan Explorer, a nice free tool for examining execution plans.

    😎

     

  • August 10, 2020 at 5:36 am

    #3778526

    thenewbee wrote:

    Hi Erikur,

    The view is actually a datasource for an SSRS report and it has the date from and to filters. So the view is supposed to bring back all the records and then filtered before rendering SSRS report.

    The greatest cost I can see is for a sort operation( please correct me if I'm wrong), can you please advise how to proceed.

    Thanks

    The cost of the sort operators is directly related to the number of rows (cardinality) they have to process.

    😎

    I've seen several examples like this one, managed once to get a query from 17 hours down to a few seconds by limiting the cardinality and preventing it from parallel execution.

  • August 10, 2020 at 5:40 am

    #3778527

    thenewbee wrote:

    Hi Jeff,

    It’s a SQL Sentry plan explorer file.

    Thanks

    NB

    This is the reason for you not being able to attach the file, recommend that you post SQLPlan files in the future (SSMS)

    😎

  • August 10, 2020 at 7:08 am

    #3778533

    Hi Eric,

    Can you advise on this please,

    say as in this case the date filter is only passed from the SSRS report, how can I redesign the whole thing. consider that the source for the report is a single query.

     

    And sorry for not the actual plan as sometimes the business feel insecure when posting the actual tables and one good thing with plan explorer is we can obfuscate the objects. Hope you understand,

    Thanks

    NB

  • August 10, 2020 at 7:31 am

    #3778535

    thenewbee wrote:

    Hi Eric,

    Can you advise on this please,

    say as in this case the date filter is only passed from the SSRS report, how can I redesign the whole thing. consider that the source for the report is a single query.

    And sorry for not the actual plan as sometimes the business feel insecure when posting the actual tables and one good thing with plan explorer is we can obfuscate the objects. Hope you understand,

    Thanks

    NB

    Understood but the problem is that I can neither retrieve the full SQL query nor the code for underlying objects (view, functions etc.)

    😎

    The problem with obfuscated plans is that it severely limits ones ability to assist with a problem. Suggest you consult the business on whether you can either post the full query or the actual execution plan.

  • August 10, 2020 at 7:49 am

    #3778538

    having a sqlplan won't give you the sql query (the one that matters) on this case as a view is being used.

    main query is a view - I would bet it is a view with selects from other (multiple) views.

    a few UDF, a few KeyLookup, 2 Xpath/Xquery (slow in nature) and a few table/index scans, including one index scan with 750Million rows.

    without the full definition of the underlying views and functions it will be very hard to give any help at all. I know some people are afraid to show their code but with very few exceptions no code like this is really something that needs to be kept confidential.

     

    this is one of those cases where I would try and rewrite this to be a stored proc returning a recordset which SSRS would happily use.

    sql plan attached as a zip file - can someone on admin change forum definition to allow .txt/.sqlplan files

    Attachments:
    You must be logged in to view attached files.

  • August 10, 2020 at 1:13 pm

    #3778618

    Eirikur Eiriksson wrote:

    Jeff Moden wrote:

    For me, the link provided by the OP doesn't point to an SQLPlan file.  It points to a file but SQL Server doesn't recognize it as an SQLPlan.

    Eirikur, what did you use to interrogate the file?

    The file is from SentryOne Plan Explorer, a nice free tool for examining execution plans.

    😎

    Thanks, Eirikur.  My ignorance there is from the fact that I don't use the tool at all because I don't want to get used to something that a lot of places I occasionally do work for don't have and won't allow the installation of.

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

  • August 10, 2020 at 1:47 pm

    #3778625

    frederico_fonseca wrote:

    having a sqlplan won't give you the sql query (the one that matters) on this case as a view is being used.

    main query is a view - I would bet it is a view with selects from other (multiple) views.

    a few UDF, a few KeyLookup, 2 Xpath/Xquery (slow in nature) and a few table/index scans, including one index scan with 750Million rows.

    without the full definition of the underlying views and functions it will be very hard to give any help at all. I know some people are afraid to show their code but with very few exceptions no code like this is really something that needs to be kept confidential.

    this is one of those cases where I would try and rewrite this to be a stored proc returning a recordset which SSRS would happily use.

    sql plan attached as a zip file - can someone on admin change forum definition to allow .txt/.sqlplan files

    Thanks for the conversion to a native SQLPlan.  There's also a huge number of single row estimates (Object 16 has a 1.4 million row seek joined with Object 17, which has a 73 million row scan for an output of a little of 100K rows) and another set of joins that return 0 rows, etc, etc.  I agree that someone needs to take this bad boy apart and do a rewrite, possibly using "Divide'n'Conquer" methods like storing an interim step or two in a Temp Table or two to simplify the joins.

    Because of the really poor estimates, I also wonder when the last time they rebuilt stats was.

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

  • August 10, 2020 at 7:44 pm

    #3778740

    Could you post the plan here: https://www.brentozar.com/pastetheplan/

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • August 10, 2020 at 7:58 pm

    #3778742

    I see a bunch of table scans - which seems to indicate based on the estimates - a table variable or a heap (which should still have some statistics).  The table scan thinks there is 1 row to be returned...

    I also noticed that Object11.Index5 is referenced multiple times with a clustered index scan - 149786 rows.

    Object15.Index11 is referenced twice in the same portion, once for all 189423 rows and again in the FOR XML part.  There are other large clustered index scans and index scans and at least one more FOR XML process (guessing these are returning multiple values delimited by some character in a single column).

    I don't think we can get any better analysis without seeing the actual query.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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