Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Unanswered: weird problem: SP executes slowly, but drop and recreate speeds it up!

    I have found an (encrypted) SP which takes ~20 seconds to run on one of our client DBs.

    If I drop and recreate (or alter) the SP giving it the same contents it will run in ~1 second.

    I need to know
    1. why the sp started running so slowly
    2. how to stop it happening again
    3. how to fix it without resorting to drop/recreate (I can't exactly write a batch script to recreate all our sps every so often)

    I have tried sp_updatestats and sp_recompile before running the sp to no avail...

    anyone heard of a problem like this before?

    cheers
    H

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, sounds like it is using a bad plan...

    sp_recompile should work...that's what happens when you recreate the sproc anyway...

    It's a big mess of a sproc, isn't it.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    7
    its just a couple of selects with 8 or so joins on each. and the recompile doesn't do a thing

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Its probably not the joins that would cause a bad execution plan. Do you have conditional statements in your WHERE clause?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If you can, get a showplan of the procedure when it is running fast, then compare that to a showplan of the query when it is running slow.

    Are you doing a full scan for the update statistics? By default, SQL Server samples 30%, I believe.

    Are any of the tables in the query deleted and repopulated? This tends to play heck with statistics.

  6. #6
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51
    did you try:
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    ??

  7. #7
    Join Date
    Mar 2004
    Posts
    7
    thanks for all the replies so far folks.

    I have now tried FREEPROCCACHE and DROPCLEANBUFFERS - still the same result. The only way to get it to work is to drop and recreate it seems...

    Using showplan I obtained these results

    Code:
    --PLAN BEFORE DROP AND RECREATE
           |--Compute Scalar(DEFINE:())
                |--Compute Scalar(DEFINE:())
                     |--Stream Aggregate(GROUP BY:(, , , , , , ))
                          |--Sort(ORDER BY:( ASC,  ASC,  ASC,  ASC,  ASC,  ASC,  ASC))
                               |--Bookmark Lookup(BOOKMARK:(), OBJECT:([xxx].[dbo].[Issuer]))
                                    |--Nested Loops(Left Outer Join, OUTER REFERENCES:() WITH PREFETCH)
                                         |--Filter(WHERE:())
                                         |    |--Hash Match(Left Outer Join, HASH:()=())
                                         |         |--Clustered Index Scan(OBJECT:([xxx].[dbo].[Security].[IssuerSecurity]))
                                         |         |--Hash Match(Inner Join, HASH:()=())
                                         |              |--Table Scan(OBJECT:([xxx].[dbo].[TransactionType]))
                                         |              |--Clustered Index Scan(OBJECT:([xxx].[dbo].[ClientTransaction].[PK_ClientTransaction]))
                                         |--Index Seek(OBJECT:([xxx].[dbo].[Issuer].[Issuer_PK]), SEEK:() ORDERED FORWARD)
    
    --PLAN AFTER DROP AND RECREATE
    	|--Compute Scalar(DEFINE:())
                |--Sort(ORDER BY:( ASC,  ASC,  ASC))
                     |--Stream Aggregate(GROUP BY:(, ))
                          |--Sort(ORDER BY:( ASC,  ASC))
                               |--Hash Match(Left Outer Join, HASH:()=())
                                    |--Hash Match(Inner Join, HASH:()=())
                                    |    |--Hash Match(Inner Join, HASH:()=())
                                    |    |    |--Table Scan(OBJECT:([xxx].[dbo].[TransactionType]))
                                    |    |    |--Bookmark Lookup(BOOKMARK:(), OBJECT:([xxx].[dbo].[ClientTransaction]))
                                    |    |         |--Index Seek(OBJECT:([xxx].[dbo].[ClientTransaction].[IX_ClientTransaction]), SEEK:() ORDERED FORWARD)
                                    |    |--Clustered Index Scan(OBJECT:([xxx].[dbo].[Security].[IssuerSecurity]))
                                    |--Table Scan(OBJECT:([xxx].[dbo].[Issuer]))
    It does look like a nasty plan ('nested loops' is ominous) but any ideas why it won't recompile without a drop?
    thanks again
    H

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You could add "with recompile" to the definition of the stored procedure, but would prevent query re-use. You mentioned the stored procedure was encrypted. Do you have access to the code, even if you can not post it here? It could be caused by an if statement in the procedure switching between two different queries. I suggest this, because the lines:
    Code:
    |--Sort(ORDER BY:( ASC,  ASC,  ASC))
    and
    Code:
    |--Sort(ORDER BY:( ASC,  ASC,  ASC,  ASC,  ASC,  ASC,  ASC))
    look too oddly diferent to me, but that could be caused by the hash joins.

    Nested Loops is a good plan for small data sets. Hash joins tend to be chosen for medium range sets (I don't have a ball park for how big medium is). Merge joins are a sign of tons of data coming through.

  9. #9
    Join Date
    Mar 2004
    Posts
    7
    The query contains a case statement in the select list, but no if...

    The problems with adding WITH RECOMPILE (aside from the lack of a compiled plan) are
    1. to do that i would have to alter the proc which would fix the problem anyway
    2. recompiling doesn't generate the new plan anyway if i use sp_recompile (or any of the other methods listed above)
    3. it doesn't explain what happened and how to stop it happening again.

    I have been holding out on you a litte; there are two selects in the procedure and it's just the second one that I have posted (the first remained the same after recreation). I have been speaking to the pages developer and it looks like the second dodgy one is not actually used so I can just drop it in the next version... not really a fix but hey - the client might stop complaining!

    thanks for all the help

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    mcintyre, this is definitely high-level sql, but it is an excellent article and may be what you need:

    http://www.sqljunkies.com/WebLog/ama...reCaching.aspx
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Mar 2004
    Posts
    7

    Talking

    thankyou all for your help! i have found the problem. and you aren't going to like it :P

    when you script the command out from QA it adds these lines
    Code:
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS OFF 
    GO
    It looks like the SET ANSI_NULLS OFF was making the select queries run really really slowly, joining nulls onto nulls. BEWARE OF THIS!

    cheers again froods.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Interesting. Thanks for posting your resolution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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