Discussion Locked

SQL 7 Procedure Cache

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I upgraded my SQL 6.5 database to a SQL 7 database. I am now getting EXTREMELY poor performance. The application that accesses the server runs mostly stored procedures. I know that memory is handled much differently in 7.0, but SP's that used to take milliseconds are now taking up to 30 seconds. The SP's are called over and over and don't seem to be staying in the procedure cache long enough. Any suggestions other then just adding more memory?

4 replies

by Former member

In order to check to see if it is a memory shortage run PerfMon and use the SQL counters, such as the procedure cache counter, and check the memory utilization from there. Otherwise use task manager to check your memory useage...

Your SQL servershould not serve any other purpose and should be in 'application server' mode.

You can also change the amount of memory dedicated to the Procedure Cache (set at 30% of avail. memory). the remainder 70% is reserved for the data cache. If you have sufficient memory, maybe you can up the procedure cache to 40%...

by rsutton

Did you do an upgrade or Full install? If you did an upgrade the upgrade leaves your db's in 6.5 Compatablity mode. Syntax:
sp_dbcmptlevel [[@dbname =] name] [, [@new_cmptlevel =] version] look it up in BOL. Had the same problem.

Good Luck!

Rob

This discussion is closed to new replies.