Post reply

large number of databases

  • January 14, 2008 at 7:50 pm

    #767955

    I will start doing some testing once I will get the test server and see how many SPs are touched by users, maybe it would be indeed a small number and that will get me close to the benchmarking that MS done for 2000.

    The benchmark should be fairly close to my situation since the memory allocation for the procedure cache is similar in 2000 and 2005, as you guys pointed out.

    Thanks for your help guys, I will keep you posted in case any of you might encounter the same situation.

  • January 15, 2008 at 8:51 am

    #768199

    icata (1/14/2008)

    ...The benchmark should be fairly close to my situation since the memory allocation for the procedure cache is similar in 2000 and 2005, as you guys pointed out. ...

    Actually this is not true. Up until SP2, SQL 2005 could have a MASSIVELY larger procedure cache. SP2 made significant reductions, but on higher RAM boxes the procedure cache will still be significantly larger on a 2005 box.

    See here for lots of details: http://blogs.msdn.com/sqlprogrammability/archive/2007/01/22/3-0-changes-in-caching-behavior-between-sql-server-2000-sql-server-2005-rtm-and-sql-server-2005-sp2.aspx

    On a related topic, I would appreciate it if anyone interested in procedure cache configuration would go here and vote to have MS make the procedure cache a configurable setting by admins: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293188.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply