none
Performance Spike RRS feed

  • Question

  • We believe something related to stored procedures is corrupted or something of the sort.

    We base this becuase we can take the exact code in the PROC and run it as a regular query and get a response back in less than a min. where when we run the PROC we never get a result set back. Now, if we removed anything that was added to the PROC today and for the most part make it the same as it was last week we get great performance again.

    My theory is excution plan corruption, cache and/or field signatures which other than "excution plan" are phrases I am making up in this context becuase I really dont venture outside writing queries.

    So with that what the?...

    we are calling the company that manages our database tomorrow but it would be nice to have education before asking them these questions.

    Wednesday, January 21, 2009 11:27 PM

All replies

  •  I can not do either.
    That said my question here is 100% general I want to avoid specifics becuase I am more trying to get an understanding of how SQL caches Sprocs.

    Does that premis make sense?

    plus we (and in all the developers at work) have been around and around the script question for 3 solid days now. We know 100% for sure that its sprocs in general compared to query in general. I am just trying to get a SQL Server understanding of what might go wrong machine wise to cause cache to maybe get corrupted
    • Edited by SEAN_MCAD Thursday, January 22, 2009 12:37 AM more info
    Thursday, January 22, 2009 12:33 AM
  • Sean,

    Try adding the WITH RECOMPILE procedure_option to the Stored Procedure definition.  This will force the stored procedure to recompile every time it executes and prevent you from having a bad plan from one set of input parameters affect other executions that would result in a different execution plan.

    http://msdn.microsoft.com/en-us/library/ms187926.aspx
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Thursday, January 22, 2009 3:45 AM
    Moderator