Post reply

Having to run this dbcc command to free up system ?

  • May 11, 2009 at 5:39 pm

    #72222

    Hi All,

    I sometimes tend to get slow degrading performance on billing prod server, but on running dbcc freeproccache everything then seems to start ticking along again, but prior to this, every/most processes tend to freeze and take a ridiculous amount of time to run.

    Any explanation/pointers/solutions

  • May 13, 2009 at 11:16 am

    #993928

    BOL:

    Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.

    Looks like you are running billing process after a long time where the plan will removed from cache... generally this command is not recommandation on prod. as this will clear all plans... so all queries might be regenerating plans...

    HTH.

    Vinay

    Thanx.
    Vinay

    http://rdbmsexperts.com/Blogs/
    http://vinay-thakur.spaces.live.com/
    http://twitter.com/ThakurVinay

  • May 13, 2009 at 2:50 pm

    #994110

    If running freeproccache gives you a performance boost, you might be experiencing stale query plans. How's your fragmentation?

    _____________________________________________________________________
    - Nate

    @nate_hughes

  • May 14, 2009 at 11:32 am

    #994755

    Also maybe try running Update stats would also help.

    -Roy

  • May 14, 2009 at 2:25 pm

    #994889

    Updates Statistics yup ... don't forget sp_recompile as well !

    Just because you have good statistics does not mean the optimizer will use them ...

    You have to 'force' plan recompilations !

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • May 14, 2009 at 6:13 pm

    #994985

    rudy komacsar (5/14/2009)

    Updates Statistics yup ... don't forget sp_recompile as well !

    Just because you have good statistics does not mean the optimizer will use them ...

    You have to 'force' plan recompilations !

    Thanks guys

    just wondering, are there any cons to running sp_recompile on a live system, i also assume that if i run this from a top level, then all objects inside the top level Sp which contains these objects will be recompiled as well.

    You have to 'force' plan recompilations !, will this be done by the recompile or the update statistics.

    There

  • May 15, 2009 at 6:34 am

    #995250

    sp_recompile uses quite a bit of resource. It does a cache lock when it has to a recompile. So be careful when you are doing a recompile on a server that si very very busy lots of transactions. Especially dont have any Stored procs with the option WITH RECOMPILE in it.

    -Roy

  • May 15, 2009 at 11:52 am

    #995557

    thanks for the reply, here is the dilemna.

    Scenario 1

    ==========

    SP executed from Management studio, takex about 400 milli seconds. resukts just outputed raw data.

    Scenario 2

    ==========

    Sp executed from the billing web server, same SP but results displayed in XML. raw data with XML wrapped around it.

    Problem is that when the cache is freed, then everything works again. Any explanation for such a behaviour ?

    Thanks.

  • January 26, 2010 at 12:11 pm

    #1108871

    I have exactly same issue on a few different servers, all with completely different hardware/configuration/workloads. The only thing they have in common is SQL 2005 sp2 v3042 or sp2+ v3152. The problem occurs very sporadically, but when it does it causes headaches. We've setup SQLAgent jobs that run this exact command every few hours.

    We too were nervous about negative impact this DBCC might cause, but so far it has been negligible. And this scheduled job has almost put an end to the problem (but it just feels "wrong").

    I just wish we either a) knew what the cause was (my guess is a SQL Server bug); or b) could be reasonably confident it would be gone once we applied sp3, or sp3 + cumulative update 6--something like that (we're are implementing sp3 + cu6 soon).

    If anyone can weigh in on this I would appreciate it?

  • January 27, 2010 at 2:42 pm

    #1109601

    Look at parameter sniffing, thats normally the cause of these problems. look at the procedure cache for invalid plans which have been cached.

  • January 27, 2010 at 2:47 pm

    #1109605

    Now I'm confused. You're the one who originally posted this. Are you now able to resolve your problem w/out resorting to DBCC?

  • January 28, 2010 at 1:59 am

    #1109799

    Please post new questions in a new thread in future.

    How much memory do these servers have? x86 or x64?

    What are the symptoms? If you query sys.dm_exec_requests, what are the queries waiting for?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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