Networks

General discussion

Gravatar
0 Votes
Locked

SQL 7 Procedure Cache

By kstroh ·
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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

SQL 7 Procedure Cache

by -Q-240248 In reply to SQL 7 Procedure Cache

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

gravatar
Collapse -

SQL 7 Procedure Cache

by kstroh In reply to SQL 7 Procedure Cache
gravatar
Collapse -

SQL 7 Procedure Cache

by rsutton In reply to SQL 7 Procedure Cache

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

gravatar
Collapse -

SQL 7 Procedure Cache

by kstroh In reply to SQL 7 Procedure Cache
Back to Networks Forum

Start or search

Related Discussions

Related Forums