[winniewang]

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

[Martijnvs]

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.

[winniewang]

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

[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:

[Martijnvs]

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:

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.

[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

[Martijnvs]

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.

[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

[Martijnvs]

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.

[winniewang]

Total logical memory is "27562" and Total physical memory is "25508". seems that "procedure cache size" didn't exceed them.

[Martijnvs]

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.

[winniewang]

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

[winniewang]

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

[Martijnvs]

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 .