Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    Manchester, UK
    Posts
    15

    Unanswered: URGENT - how to determine procedure cache size

    Hi folks -hope you can help me. I need to determine the size of the procedure cache to set on a new server that I've recently set up. The default is 3271 2K memory pages. The performance and tuning manual suggests that it should be the max. no of concurrent users * size of the largest plan * 1.25. How do I find out the size of the largest plan? I don't want to allocate too much memory to the procedure cache only for it to cause problems elsewhere.

    Thanks in advance!

  2. #2
    Join Date
    Jan 2005
    Posts
    10
    select sysobjects.name,
    sysprocs_entries = count(sysprocedures.id),
    size_in_pages = (count(sysprocedures.id) / 8) + 1,
    size_in_bytes = ((count(sysprocedures.id) / 8) + 1) * 2048
    from sysobjects,
    sysprocedures
    where sysobjects.type = "P"
    and sysobjects.id = sysprocedures.id
    group By sysobjects.name
    Order By count(sysprocedures.id)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •