SQL Server Performance

application lock up clear by "DBCC FLUSHPROCINDB"

Discussion in 'General DBA Questions' started by jethrojaw, Jun 28, 2006.

  1. jethrojaw New Member

    We have a vended package. Every once in a great while, portions of it would lock up and it will stop working until a DBCC FLUSHPROCINDB for that database is issued. I don't understand how flushing the procedure cache for that database would unfreeze portions of the application. I am keeping track of statistics and can post whatever anyone might want to see.

    Has anyone experienced this kind of behavior? Anyone have any idea why flushing the procedure cache for that database would possibly unfreeze the application? Any recommendation on how to track down what might be causing this (what statistics to review)? I don't want to put a trace on the system because of the overhead. The developers cannot provide the section of code that might be causing the lockup even though they have a fairly good idea of what function is causing it. It also cannot be replicated in a development environment because of various setup restriction as I was told.

    Thanks,
    Jethro
  2. SQLDBcontrol New Member

    Hi Jethro,<br /><br />Interestingly enough, I'm working on exactly the same problem this week.<br /><br />If flushing the procedure cache is resolving the problem then it sounds like there might potentially be a bad execution plan in cache - either for a specific procedure or several.<br /><br />You mentioned that the developers had a fairly good idea of what might be causing the problem. Next time you see this problem, rather than emptying the procedure cache target just one or two procedures (i.e. the ones that are suspected by the devs). Use sp_recompile &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />roc name&gt; to force that procedure to recompile the next time it's executed.<br /><br />This will at least tell you what procedure(s) are the cause of the problem and you can dig into those a little deeper to see why the bad plan might is being generated.<br /><br />Unfortunately you might need to run a trace if the above doesn't help. At some stage the overhead on the system becomes secondary because if the application becomes unuseable you need to analyse the system in order to indentify the nature of the problem. Emptying the cache isn't solving the problem, it's just delaying it until next time. So run a trace, explain the it will cause an overhead but that it will help analyse the problem.<br /><br />Hope that helps,<br /><br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by jethrojaw</i><br /><br />We have a vended package. Every once in a great while, portions of it would lock up and it will stop working until a DBCC FLUSHPROCINDB for that database is issued. I don't understand how flushing the procedure cache for that database would unfreeze portions of the application. I am keeping track of statistics and can post whatever anyone might want to see.<br /><br />Has anyone experienced this kind of behavior? Anyone have any idea why flushing the procedure cache for that database would possibly unfreeze the application? Any recommendation on how to track down what might be causing this (what statistics to review)? I don't want to put a trace on the system because of the overhead. The developers cannot provide the section of code that might be causing the lockup even though they have a fairly good idea of what function is causing it. It also cannot be replicated in a development environment because of various setup restriction as I was told.<br /><br />Thanks,<br />Jethro<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
  3. jethrojaw New Member

    Karl,

    Thanks for the information and suggestion. The problem is the issue doesn't occur that often. I cannot have the trace running for months in hope of catching the lockup. Trying to convince the users that the trace itself won't cause an outage is another thing (I'm sure you have the same issue). Also when it locks up, the users want it up right now. They don't want to spend the time finding out which procedure purged from the cache will solve the problem. They're typical users.

    Enough about users, on to more useful discussion. How does an execution plan go bad (or get corrupted)? Is this a sympton of something else? Could I be short on memory? I would think I would see memory swapping in and out instead of things locking up but I don't.

    The developers think that it is a particular set of functions that is causing the outage. Great, I can have the process run on a development server so I can stress test it, monitor resource utilization, trace it, purge it from cache at will, etc. to narrow down the issue. However, they say they can't replicate the setup due to various constraints (vended package and all). When it gets bad enough, the developers will suddenly be able to replicate the process in the development environment. Until then, I'm fishing in the dark.

    Thanks,
    Jethro
  4. SQLDBcontrol New Member

    Jethro,<br /><br />Are you sure you're not working in the same place as me.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />] You've described my situation perfectly.<br /><br />Our problem occurs quite randomly too (although it is more often than every month) and same problem with the users. When it goes bad they want it back up ASAP. Although I have convinced management now that clearing the cache isn't a solution - it just hides the problem. So we've got approval to let the users suffer for a while next we see the problem just so that we can run a trace and get some info.<br /><br />As for the execution plan, this can go bad over time due to various reasons. If the data in the underlying tables changes a great deal then a plan that was created today might not be ideal in a months time.<br /><br />Also, fragmentation can affect things here. If a plan used a particular index today, it might actual perform badly next week if that index is heavily fragmented.<br /><br />Also, if a stored procedure has lots of conditional logic, the first plan that is generated won't necessarily be the best plan if different conditions are applied to the procedure. This is where the problem probably lies in our case - a really badly written stored procedure without about 15 different paths that can be followed depending on various parameters. In reality it's probably a combination of all of these things (and probably some other things I haven't taken into account).<br /><br />Until the problem occurs again though you're just going to have to prepare yourself to quickly jump on it when it happens. <br /><br />Have a look at the suspect functions and see if there are any obvious things that may indicate where the problem lies.<br /><br />Prepare a trace so that you can start it the moment you hear about this problem. If possible, get a baseline trace now, whilst things are running smoothly so that you can compare it later on.<br /><br />When the problem comes, run the trace. Monitor locking/blocking as well just in case that is part of the issue.<br /><br />It's frustrating, I know but it's bit like trying to find a the hole in a leaky roof. It would take too much time to search the roof, inch by inch, trying to find the hole. So you're better off waiting till it rains so you can see the dripping water.<br /><br />By the way, I've been using a third-party tool to run my traces. It's called coefficient (Quest Software). It runs a standard trace in the background but the useful bit is that it aggregates all the data and analyses it. This makes it really easy to spot where things are going wrong and you can drill down to get more detail. It certainly beats trying to go through thousands of lines of trace data and I swear by it. I downloaded a trial version and we're going to be buying a copy soon. Trust me, it will make your life a lot easier <img src='/community/emoticons/emotion-1.gif' alt=':)' />.<br /><br />Anyway, good luck with your problem. I'll keep you posted in case I uncover anything about my problem as it seems our problems are quite similar.<br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
  5. jethrojaw New Member

    Karl,

    Thanks for all your assistance. I'll just keep on the clients until they keep me time to look at the problem. When it hurts bad enough, they'll come around.

    Good luck with your issue.

    Jethro.
  6. Michael Kansky New Member

    Hello Jethro,
    Having the same issue, about once a week.
    Did you ever solve yours, was it a poorly written procedure or config setting?
  7. Luis Martin Moderator

    Welcome to the forums!.
    Michael, this thread is 5 years old.
  8. Michael Kansky New Member

    :) didn't notice it, will repost
  9. satya Moderator

    As long as you explain the situation and relates to the old question its good to followup for a lead.

Share This Page