Freeproccache

Question

Freeproccache

Brad Griffin

SSC-Addicted

Points: 479
More actions
March 5, 2012 at 2:54 am

#254177

I have read with interest all the items about FREEPROCCACHE, but have found that this is the only thing that corrects and issue we have with 2 database's on our system. What I am wondering is what is the underlying issues that may cause the need for DBCC FREEPROCCACHE to be run?????

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Just about none. It's a brute-force instrument, wipes every plan from the cache and forces SQL to recompile them all. It can fix parameter sniffing problems or other cases of erratic execution plans, but there are usually better fixes once the problems have been narrowed down.

This might be worth a watch, session 10 from here (free login required) http://www.sqlpass.org/24hours/spring2011/

Edit: I've only ever had to regularly use FreeProcCache once on a production system and that was due to a 'bug' in the memory management in SQL 2005 RTM and SP1 that let the procedure cache grow far bigger than it should.

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 2

It should be sufficent to identify which proc is the reason and then specifically set it to recompile . FreeProccache is gonna wipe out all plans and that could hurt overall performance

Jayanth Kurup[/url]

Brad Griffin SSC-Addicted Points: 479 More actions · Answer 3

Damn....My problem is that we have a 3rd party application that access 2 databases. Structurally they are identical, but access to one is fine where access to the other is poor. The OIT_SW database is over 400Gb in size and has no problems accessing it from the GUI (The same GUI manages both database's), the second database, OIT_SWMG is only around 80Gb in size, but the performance on this is poor.

The 2 database files reside on different network drives, but those 2 network drives reside on the same SAN partition......

Over the last day or so after I have run the sp_updatestats and Freeproccache performance has improved.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 4

Have u checked for any blocking issues?

Are you using any hints in the procedures ?

Jayanth Kurup[/url]

Brad Griffin SSC-Addicted Points: 479 More actions · Answer 5

Its a 3rd party application, so we have no access to the queries, etc that are being generated...

Thanks anyway.......

Have loaded a Trial Version of SQL Index Manager and found a number of indexes that need attention, have run the "fix" from SQL Index Manager on those. So I guess I will have to keep an eye on it

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Overall performance (everything's slow) or erratic performance (usually fine, but occasionally not)?

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

Brad Griffin SSC-Addicted Points: 479 More actions · Answer 7

Overall things seem to be running OK, but it has been on the smaller of the 2 database's that performance has been poor.

Installed a copy of RedGates SQL Index Manager yesterday and ran that to correct a number of indexes and things seem to be reasonable since then....

Fingers crossed

Brad

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

If a database has general poor performance, then maybe have a read through the following and see if you can use that technique to identify the problematic queries

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

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

Brad Griffin SSC-Addicted Points: 479 More actions · Answer 9

Brad Griffin

SSC-Addicted

Points: 479

March 6, 2012 at 3:56 pm

#1455655

will do 🙂

Thanks

Brad