TechRepublic|Forums|Networks

Networks

General discussion

  • Creator
    Topic
  • June 7, 2000 at 8:00 am #2073637

    SQL 7 Procedure Cache

    Locked

    by kstroh · about 21 years, 11 months ago

    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?

    Topic is locked This conversation is currently closed to new comments.
  • Creator
    Topic

All Comments

  • Author
    Replies
    • June 8, 2000 at 3:06 am #3894711

      SQL 7 Procedure Cache

      by Anonymous · about 21 years, 11 months ago

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

    • June 13, 2000 at 9:35 am #3778267

      SQL 7 Procedure Cache

      by rsutton · about 21 years, 11 months ago

      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

  • Author
    Replies
Viewing 1 reply thread