Query taking more than 4 hrs. - Any recommendations/suggestions?

  • Feivel

    SSCertifiable

    Points: 7518

    One of our stored procs has been running for more than 4 hrs. and we have to finally kill it as it is taking quite and causing blocking...also this shouldn't take this long....we've come to a point and identified the batch of code that is causing the problem. Our indexes and stats are all tuned and up to date so we are looking at query modification. However, we aren't really sure what changes we could make to reduce the execution time. I won't be able to provide the actual execution plan as it is timing out. As per my findings when I looked at sys.dm_exec_requests and what it was waiting on I could note that it "select * into" was single threaded and the last_wait_type was "SOS_SCHEDULER_YIELD".

    Any suggestions or recommendations would be much appreciated.

    Thanks

    • This topic was modified 1 month, 1 week ago by  Feivel.
    • This topic was modified 1 month, 1 week ago by  Feivel.
    Attachments:
    You must be logged in to view attached files.
  • Jeff Moden

    SSC Guru

    Points: 996843

    See the 2nd link in my signature line below on the information you should provide on performance related problems.  Thanks.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jonathan AC Roberts

    SSCoach

    Points: 17321

    You have an OUTER APPLY (aliased c2) in the query but in the WHERE clause of the main query you have

    AND D.LiveTable = c2.TableName

    So why is it an OUTER APPLY not CROSS APPLY?

    There is no alias prefixed to column AuditControl, so how are we to know which table contains the column?

    Same for column ClientEditorFieldID (which table is it on?)

    How many rows are on each table?

    You have not indexed any of the temporary tables, maybe you should?

     

     

     

     

  • Feivel

    SSCertifiable

    Points: 7518

    Jonathan AC Roberts wrote:

    You have an OUTER APPLY (aliased c2) in the query but in the WHERE clause of the main query you have

    AND D.LiveTable = c2.TableName

    So why is it an OUTER APPLY not CROSS APPLY? No clue about that but I can recommend the devs to try and change that if it logically makes sense since I don't spend too much time in development and to admit I am not very well versed at it...unfortunately, cannot give a solid answer for that.

    There is no alias prefixed to column AuditControl, so how are we to know which table contains the column? It is coming from ImportDetail if we are referring the above query and if we are talking about the problematic query this is coming from #idopfield aliased as D

    Same for column ClientEditorFieldID (which table is it on?) this is from the table #crf or which is aliased as cf

    How many rows are on each table? I have provided all the row counts and commented them next to its relevant table except for temp table used in problematic query such as the #MissingRequiredFields. FieldMappings table is quite small roughly 28793 rows

    You have not indexed any of the temporary tables, maybe you should? Do you have any specific recommendation you can provide?

    Thank you so much for all the help and taking out time to even look into it. Appreciate your help.

    • This reply was modified 1 month, 1 week ago by  Feivel.
    • This reply was modified 1 month, 1 week ago by  Feivel.
    • This reply was modified 1 month, 1 week ago by  Feivel.
  • Feivel

    SSCertifiable

    Points: 7518

    Jeff Moden wrote:

    See the 2nd link in my signature line below on the information you should provide on performance related problems.  Thanks.

    Okay thanks I'll follow this guideline going forward. Could you provide some help on this if you possibly have time and if you don't mind doing it. As I mentioned in my question I don't have the execution plan that I can provide an XML output for so not sure if you can provide some general recommendation w/o it.

  • Mike01

    SSChampion

    Points: 11282

    I'm sure this isn't helping either

    REPLACE(REPLACE(fm.DestTable, 'Holding_', ''), 'HoldingTable_', '')

    and this

    AND

    (

    LTRIM(RTRIM(ISNULL(CAST(im.NewValue AS VARCHAR(MAX)), ''))) <> ''

    AND im.NewValue IS NOT NULL

    )

     

    Have you tried creating/populating temp tables and then running smaller parts of the problem query to see where the issue might lie?  How many records does FieldMappings have in it?  Also look for Implicit conversions.  Execution plans will help

    • This reply was modified 1 month, 1 week ago by  Mike01.

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Jonathan AC Roberts

    SSCoach

    Points: 17321

    Feivel wrote:

    Jonathan AC Roberts wrote:

    You have not indexed any of the temporary tables, maybe you should? Do you have any specific recommendation you can provide?

    Thank you so much for all the help and taking out time to even look into it. Appreciate your help.

    I would initially try :

    CREATE CLUSTERED INDEX IX_#icdetail_1 ON #icdetail(ImportControlID,ImportItemId,ClientID,ClientEditorFieldID);
    CREATE CLUSTERED INDEX IX_#crf_1 ON #crf(FieldName,TableName);
    CREATE CLUSTERED INDEX IX_#idopfield ON #idopfield(LiveField,LiveTable);

    But first, if I had the data, I would run the query with the execution plan and see if the optimiser thinks there are any missing indexes.

    PS: Create each index for each table immediately after the creation that table. It may help with other queries in the stored procedure.

  • Feivel

    SSCertifiable

    Points: 7518

    Mike01 wrote:

    I'm sure this isn't helping either

    REPLACE(REPLACE(fm.DestTable, 'Holding_', ''), 'HoldingTable_', '')

    and this

    AND

    (

    LTRIM(RTRIM(ISNULL(CAST(im.NewValue AS VARCHAR(MAX)), ''))) <> ''

    AND im.NewValue IS NOT NULL

    )

    Have you tried creating/populating temp tables and then running smaller parts of the problem query to see where the issue might lie?  How many records does FieldMappings have in it?  Also look for Implicit conversions.  Execution plans will help

    I tried the option of loading the temp tables and then going step by step and found the problem code but wasn't able to recommend or fix the code. FieldMappings have roughly 28973 rows.  Unfortunately, can't generate the plan for the problem query as I've been asked to kill the query as it is causing blocking and running quite long. I will see if I get a chance to run this query w/o getting blocked  or block any one else off hrs. and see if I could add in the execution plan in here.

    • This reply was modified 1 month, 1 week ago by  Feivel.
  • Feivel

    SSCertifiable

    Points: 7518

    Jonathan AC Roberts wrote:

    Feivel wrote:

    Jonathan AC Roberts wrote:

    You have not indexed any of the temporary tables, maybe you should? Do you have any specific recommendation you can provide?

    Thank you so much for all the help and taking out time to even look into it. Appreciate your help.

    I would initially try :

    CREATE CLUSTERED INDEX IX_#icdetail_1 ON #icdetail(ImportControlID,ImportItemId,ClientID,ClientEditorFieldID);
    CREATE CLUSTERED INDEX IX_#crf_1 ON #crf(FieldName,TableName);
    CREATE CLUSTERED INDEX IX_#idopfield ON #idopfield(LiveField,LiveTable);

    But first, if I had the data, I would run the query with the execution plan and see if the optimiser thinks there are any missing indexes.

    PS: Create each index for each table immediately after the creation that table. It may help with other queries in the stored procedure.

    Okay I will try creating these indexes and see if it benefits. Since, the query isn't completing in its entirety I have no clue of the optimizer suggesting an index for the problem query. I'll try and run the entire query off hrs. and hopefully let it run until completion w/o being blocked or causing excessive blocking.

  • Jeffrey Williams

    SSC Guru

    Points: 88603

    I would recommend starting with these two parts of the query:

    AND cf.TableName = REPLACE(REPLACE(fm.DestTable, 'Holding_', ''), 'HoldingTable_', '')

    LTRIM(RTRIM(ISNULL(CAST(im.NewValue AS VARCHAR(MAX)), ''))) <> ''

    The first one doesn't make sense - you are replacing the values in fm.DestTable that contain either Holding_ or HoldingTable_ with an empty string and comparing that to cf.TableName?  Are there really values in cf.TableName that are empty that should be matched to your 'Holding' tables?

    If that is the case - wouldn't it be better to modify the TableName in the #crf table where the entries are blank to either Holding_ or HoldingTable_ - and if you need to match on both entries in fm.DestTable for a single row in cf.TableName you can always add those additional rows to the temp table.

    The second one definitely doesn't need RTRIM and LTRIM...it looks like here you are trying to include any row that is not null and is not an empty string and I don't believe you need to convert to VARCHAR(MAX) - unless that is defined as a TEXT column.  Either way - move the convert into the generation of the temp table #idopfield and then you can just use im.NewValue > '' which will exclude all NULL values and empty strings (note: a string of spaces will be evaluated as an empty string - but if that is an issue, move that to the build of the temp table also).

    If possible - try to convert the NOT IN to an IN...if that isn't possible then try converting to a NOT EXIST.  You should be able to eliminate the reference to #crf in the NOT IN portion if you change to a NOT EXISTS.

    You also have that portion filtered by the client ID ( @clientid ) but are not filtering the outer query.  Thus, the OUTER APPLY is returning all rows for all clients where the passed in client does not have that field...this doesn't appear to be correct.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Jonathan AC Roberts

    SSCoach

    Points: 17321

    Feivel wrote:

    Okay I will try creating these indexes and see if it benefits. Since, the query isn't completing in its entirety I have no clue of the optimizer suggesting an index for the problem query. I'll try and run the entire query off hrs. and hopefully let it run until completion w/o being blocked or causing excessive blocking.

    You can take the code in the stored procedure and make it into a script to test each statement in the stored procedure then make adjustments for performance as you go through each step, make changes and retest that one statement. You will need an environment to test it on, ideally this won't be the production database.

  • Jeff Moden

    SSC Guru

    Points: 996843

    Feivel wrote:

    Jeff Moden wrote:

    See the 2nd link in my signature line below on the information you should provide on performance related problems.  Thanks.

    Okay thanks I'll follow this guideline going forward. Could you provide some help on this if you possibly have time and if you don't mind doing it. As I mentioned in my question I don't have the execution plan that I can provide an XML output for so not sure if you can provide some general recommendation w/o it.

    Yeah sorry.... I missed the part where you spoke of why you couldn't provide and actual execution plan. In that case, I'd do like some of the others have suggested...

    Run one section of the problematic part of the code at a time and find out where the performance issue actually is in section and then we can start to whittle on that.

    I will say that the use of DISTINCT is usually is an indication of duplicate rows being returned by the SELECT and that's an indication that someone needs  to figure out where that duplication is occurring and find better criteria to fix it.  Behind the scenes, it can create a shedload of miniature accidental many-to-many (CROSS) joins or several big ones that will take a month of Sundays to resolve due to all of the working rows created behind the scenes.

    There are a couple of oddities in the problematic section of the code but you probably can't even generate an estimated execution plan on it to help.  That being said, any idea how many rows are in the table including the Temp Tables that are created?

    And you said that you'd "try and run the entire query off hrs".  Unless you're babysitting the box while that happens and keep track of the size of TempDB, I wouldn't let this query run on it's own.

     

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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