dbcc freeproccache

>>Can someone explain why this command would speed up an appliation

because you proc cache might be using up the bulk of the RAM leaving less RAM for data resulting in going to disk to get additional data
Do you use a lot of dynamic SQL?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

No. This is a new issue we have seen with this application after a newer build was sent to us. Is there a way to prevent the proc cache from using so much RAM?

No, you can not control how much memory the procedure cache will use.

What version of SQL, and what hardware platform.

When i've seen stuff like this is that a bad execution plan is getting into the cache and it works great in some instances, but poorly in others. Does the CPU spike when perfomance goes down?

Are your statistics up to date on all the indexes? Can you identify which procedure is causing the problem?

I blogged a bit about the procedure cache and the buffer cache here.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

We are running SQL 2000 Enterprise Edition in an active-passive cluster on Windows 2003 server. We have Quad Xeon processors, 4Gb of RAM and external SAN disks with over 250Gb of space available.

I had Microsoft looking at our server during the slowness and there were instances when some queries in this application caused the CPU to spike.

I run maintenance weekly: Updating stats, reindexing indexes. I have even manually run these and it did not fix the issue.

The only thing that can fix our issue when the application is running slow is to either reset services, switch nodes (which is just like resetting services) or running this dbcc command.

...either reset services, switch nodes ... or running this dbcc command.

Click to expand...

All three of these have the same effect. The buffer cache and procedure cache are flushed.

As you are using Windows 2000 you can have a maximum of 1 Gig of RAM for the procedure cache. How large is the database? What is the buffer cache hit ratio? What is the estimate length of time that data stays in the buffer cache? What is the procedure cache hit ratio? How much data is in the procedure cache?

The syscacheobjects table in the master database will provide you with some basic cache information. Take a look at Quest Software's Spotlight on SQL Server or Spotlight on SQL Server Enterprise. It's got some very useful metricks on the products main page reguarding the buffer and procedure cache.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

We are using Windows 2003 server.