Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Unanswered: How to increase configuration parameter procedure cache size?

    Hi everyone,

    Who can tell me how to increase the configuration parameter procedure cache size to make more procedure cache available?
    I searched it on the internet and few info returned, who can give me more info about that? I'm a new one......

    Your help will be very appreciated:-)

    Thanks^_^
    Winnie

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    sp_configure "procedure cache size" <value>
    This is basisc info that can be found Sybase' website > Support & Downloads > Search for the appropriate product (in your case ASE I presume).
    Look fr the System Administration Guide and search it for 'procedure cache size'.

    The online documentatino of Sybase is very handy for all kinds of information you need.
    Last edited by Martijnvs; 12-07-07 at 04:31.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Apr 2007
    Posts
    63
    I see, thank you
    But why I always be asked the password when I want to enter isql?
    cmd line like this: isql -S localhost -U sa

    the username should be sa,and no password, so I don't know what I should input......

    Thanks^_^
    Winnie

  4. #4
    Join Date
    Apr 2007
    Posts
    63
    Seems that use isql -U sa can enter the isql
    But why they tell me "No matching configuration options", Does it work on my machine?

    1> exec sp_configure 'procedure cache percent', 30
    2> go
    Msg 18124, Level 16, State 1:
    Server '###', Procedure 'sp_configure', Line 917:
    No matching configuration options. Here is a listing of groups:

  5. #5
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by winniewang
    Seems that use isql -U sa can enter the isql
    But why they tell me "No matching configuration options", Does it work on my machine?

    1> exec sp_configure 'procedure cache percent', 30
    2> go
    Msg 18124, Level 16, State 1:
    Server '###', Procedure 'sp_configure', Line 917:
    No matching configuration options. Here is a listing of groups:
    You used the wrong parameter name percent instead of size:
    Code:
    1> sp_configure "procedure cache size"
    2> go
     Parameter Name                 Default     Memory Used Config Value Run Value   Unit                 Type
     ------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
     procedure cache size                  3271        6902        3271         3271 memory pages(2k)     dynamic
    
    (1 row affected)
    (return status = 0)
    1> sp_configure "procedure cache size", 5000
    2> go
    The first statement displays the current value of the procedure cache size.
    You should increase that value, with the second statement.
    I'm not crazy, I'm an aeroplane!

  6. #6
    Join Date
    Apr 2007
    Posts
    63
    Could you tell me what the max value should be? 10000? I still get the error below: OLE DB error: OLE DB or ODBC error: There is not enough procedure cache to run this

    procedure, trigger, or SQL batch. Retry later, or ask your SA to reconfigure SQL Server with more procedure cache.
    ; Sort failed because there is insufficient procedure cache for the configured number of sort buffers. Please retry the query after configuring lesser number of sort buffers.

    Thanks^_^
    Winnie

  7. #7
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by winniewang
    Could you tell me what the max value should be? 10000? I still get the error below: OLE DB error: OLE DB or ODBC error: There is not enough procedure cache to run this

    procedure, trigger, or SQL batch. Retry later, or ask your SA to reconfigure SQL Server with more procedure cache.
    ; Sort failed because there is insufficient procedure cache for the configured number of sort buffers. Please retry the query after configuring lesser number of sort buffers.

    Thanks^_^
    Winnie
    The max value dpends on how much memory your server has.
    I have servers with a procedure cache of 70.000 or 80.000, maybe more. Just keep increasing the value until your procedure runs properly.
    I'm not crazy, I'm an aeroplane!

  8. #8
    Join Date
    Apr 2007
    Posts
    63
    But if I increase the parameter to 10000, the machine is too slow....
    Is there other method can increase procedure cache? if so, could you give me some detail info?

    Thanks
    Winnie

  9. #9
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by winniewang
    But if I increase the parameter to 10000, the machine is too slow....
    Is there other method can increase procedure cache? if so, could you give me some detail info?

    Thanks
    Winnie
    If you get the erro ryou mentioned, you need to increase the parameter, or rewrite your procedure so that it's more efficient with it's memory. I'm not too good with that.

    How much physical memory does your server have, and what is the value of the parameter 'max memory'? Procedure cache size cannot exceed max memory.
    I'm not crazy, I'm an aeroplane!

  10. #10
    Join Date
    Apr 2007
    Posts
    63
    Total logical memory is "27562" and Total physical memory is "25508". seems that "procedure cache size" didn't exceed them.

  11. #11
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by winniewang
    Total logical memory is "27562" and Total physical memory is "25508". seems that "procedure cache size" didn't exceed them.
    How much memory is there in your server? 2, 3, 4Gb?? And what is the value of the parameter 'max memory' (not total logical/physical memory)?

    If you increase procedure cache size and the server becomes slow, it may well be that the procedure just demands much resources. After the procedure ends, things should return to normal.
    I'm not crazy, I'm an aeroplane!

  12. #12
    Join Date
    Apr 2007
    Posts
    63
    Martijnvs,
    Sorry for the delay reply, I think it is the server configuration. So I think I cannot increase procedure cache only via procedure cache. Is there any other way to increase procedure cache?

    Thanks^_^
    Winnie

  13. #13
    Join Date
    Apr 2007
    Posts
    63
    Seems that this way can solve my problem, even though it took me much more time to process my mining structure.

    Thanks a lot^_^
    Winnie

  14. #14
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    You're welcome. Sometimes problems can't be solved by changing parameters, the physical limitations will always be there. As long as the increased processing time isn't too much of a problem, you should just be a little more patient.

    Good luck with the rest .
    I'm not crazy, I'm an aeroplane!

Posting Permissions

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