General discussion

  • Creator
  • #2073637

    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?

All Comments

  • Author
    • #3894711

      SQL 7 Procedure Cache

      by Anonymous ·

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

    • #3778267

      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!


Viewing 1 reply thread