Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    104

    Unanswered: ...and suddenly a stored procedure takes too much time

    Hi, I have a sp that was taking very little time (about 34 sec). But suddenly is stacked. It is running and running and running but not LOCKED neither SUSPENDED. It is always RUNNABLE. I have made Index and statistics optimization but nothing. I looked into execution plan but everything seems ok. All the time is in 3 indexes that are Index Seek and not Table Scan!!! So why is stacked... I do not know how much time it takes because I have to stop it. (SQL SERVER 2008 R2, the database was migrated from SQL SERVER 2000)

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    have you checked for blocking maybe by using sp_who2 and blby column?

    have you looked at the fragmentation on the tables and indices?

    is there some other process dragging the server down like backups or restores or file copies or database file growth occurring?

    have you looked at the task manager or resource monitor? is there anything pegged out to the maximum?

    have you recreated the issue in more than one environment? (dev, qa, uat(?)) ?

    Aside from scans in the execution plan, are there any expensive bookmark or key lookups?

    Is there any looping (cursor or WHILE loops) in your SQL that could be on infinite repeat due to a coding bug and/change in data?

    have you looked at the currently executing SQL with the dynamic management views to see what SQL is currently executing?

    Other than that, please post or DDL complete with indices, stored procedure, execution plan, and maybe the output from SET STATISTICS IO.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not all index seeks are good. If you have no where clause on the query, then an index seek would be bad. What is the wait_type (or even last_wait_type) for the query in sys.dm_exec_requests?

  4. #4
    Join Date
    Feb 2004
    Posts
    104
    Quote Originally Posted by Thrasymachus View Post
    have you checked for blocking maybe by using sp_who2 and blby column?

    have you looked at the fragmentation on the tables and indices? Yes

    is there some other process dragging the server down like backups or restores or file copies or database file growth occurring? No

    have you looked at the task manager or resource monitor? is there anything pegged out to the maximum? No only the process of this sp

    have you recreated the issue in more than one environment? (dev, qa, uat(?)) ?

    Aside from scans in the execution plan, are there any expensive bookmark or key lookups? No

    Is there any looping (cursor or WHILE loops) in your SQL that could be on infinite repeat due to a coding bug and/change in data? No there is not cursors or while loops... EXCEPT that one column calculated based on values in a table (O) that participate in the query. The column is:CAST((select min (s1.day ) from table s1 where DATEPART(wk, s1.day ) = DATEPART(wk, O.day ) and year(s1.day) = year(O.day)) AS SMALLDATETIME) AS FROM_DW
    I have found that this is the problem. But why the problem appeared now... after many years!!!

    have you looked at the currently executing SQL with the dynamic management views to see what SQL is currently executing? No... What exactly do you mean?

    Other than that, please post or DDL complete with indices, stored procedure, execution plan, and maybe the output from SET STATISTICS IO.
    I have done index maintenance but not statistics

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    No there is not cursors or while loops... EXCEPT that one column calculated based on values in a table (O) that participate in the query. The column is:CAST((select min (s1.day ) from table s1 where DATEPART(wk, s1.day ) = DATEPART(wk, O.day ) and year(s1.day) = year(O.day)) AS SMALLDATETIME) AS FROM_DW
    I have found that this is the problem. But why the problem appeared now... after many years
    How did you determine that this was the problem?

    Sometimes, code that is not properly optimized will limp along well enough for a while and then it will reach a tipping point where the size of the database is just too large for what that code is doing. But you said the execution plan was clean, so without more facts I am left to ponder my belly button lint.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    CAST((select min (s1.day ) 
       from table s1 
       where DATEPART(wk, s1.day ) = DATEPART(wk, O.day ) 
         and year(s1.day) = year(O.day)) AS SMALLDATETIME) AS FROM_DW
    This looks entirely un-SARGeable, and appears to be embedded in the SELECT clause of your query. This means that this SELECT statement will be run once for every row of the result set. See if you can move this call to the FROM clause, rather than the SELECT clause.

  7. #7
    Join Date
    Feb 2004
    Posts
    104
    Quote Originally Posted by MCrowley View Post
    Code:
    CAST((select min (s1.day ) 
       from table s1 
       where DATEPART(wk, s1.day ) = DATEPART(wk, O.day ) 
         and year(s1.day) = year(O.day)) AS SMALLDATETIME) AS FROM_DW
    This looks entirely un-SARGeable, and appears to be embedded in the SELECT clause of your query. This means that this SELECT statement will be run once for every row of the result set. See if you can move this call to the FROM clause, rather than the SELECT clause.

    I issued the command EXEC sp_updatestats 'RESAMPLE' and is ok. It was the statistics!
    However I think that you have right... When I remove this column (before update statistics) then stopped to run indefinitely making 3-4 min.
    So even if the query run again on a reasonable time must optimize this column anyway!

    Thanks to all

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •