Having to run this dbcc command to free up system ?

Question

Having to run this dbcc command to free up system ?

Dean Jones-454305

SSCarpal Tunnel

Points: 4804
More actions
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

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

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

Vinay Thankur SSCarpal Tunnel Points: 4178 More actions · Answer 1

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

RP_DBA SSCertifiable Points: 5116 More actions · Answer 2

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

_____________________________________________________________________
- Nate

@nate_hughes

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Also maybe try running Update stats would also help.

-Roy

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

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."

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 5

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

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

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 7

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.

Mike Good SSCertifiable Points: 7415 More actions · Answer 8

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?

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 9

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

Mike Good SSCertifiable Points: 7415 More actions · Answer 10

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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