Reuse_cnt for number of open partitions Sybase_ASE

[DilipVoora]

Hi Experts,

We are frequently getting logged with the below message in SPA.log file for our system SPA installed with ASE 16.0 sp02 on SUSE LINUX.

00:0006:00000:00064:2016/07/04 08:00:01.83 server  Increase the config parameter 'number of open partitions' to avoid descriptor reuse. Reuse may result in performance degradation.

We have checked sp_monitorconfig 'number of open partitions' and found that the reuse_cnt is populating with positive value.

1> sp_monitorconfig 'number of open partitions'

2> go

Usage information at date and time: Jul  4 2016  4:07PM.

Name                      Num_free    Num_active  Pct_act Max_Used    Reuse_cnt   Instance_Name

------------------------- ----------- ----------- ------- ----------- ----------- ------------------------------

number of open partitions           3       49997  99.99        50000      167402 NULL

(1 row affected)

(return status = 0)

Can anyone advise me over the parameter with an example, this message and how to make the reuse_count value back to 0 to attain performance?

Regards,

Dilip Voora

[DilipVoora]

Hi Mark/Jeroen,

I could see the output of sp_monitorconfig 'number of open partitions' is different with the output of sp_countmetadata 'number of open partitions' with respect to used value and the configured value.

Can you please clarify me over the below?

1.Is there any relation between 'user partitions' in all databases and the 'number of open partitions'?

2.If we need increase the configuration parameter 'number of open partitions' will show any impact on the other two configuration parameters 'number of open objects, number of open indexes'?

Regards,

Dilip Voora

[Mark_A_Parsons]

Your attachments/images appear to require a login to a microsoft website.

I suggest you repost with attachments/images that do *NOT* require a separate login to another web site.

Better yet, since we're talking about a relatively small amount of data at this point ... either cut-n-paste the text directly into your post or cut-n-paste the text into a *txt file and attach said *txt file to your post.

---------------------

Yes, 'number of open partitions' relates to the number of partitions in the entire dataserver.  If 'number of open partitions' is lower than the results from sp_countmetadata then you're likely to see Reuse_cnt > 0.

The other 'number of open XXXX' settings are similarly related to the results of sp_countmetadata for said config settings.  While there is a relationship between actual objects/indexes/partitions ... the sp_configure settings are standalone/separate from each other.

[DilipVoora]

Thanks Mark. Below are the outputs of sp_countmetadata 'number of open partition's and 'sp_monitorconfig of number of open partitions,number of open indexes and number of open objects'.

So, here when I inquired sp_countmetadata 'number of open partitions' it is showed us that 207797 user partitions are being used in all the databases but we have configured the parameter with 50000 only.

Hence as per your advise I will increase the configuration parameter to atleast 210000 - 100% to bring back the reuse_cnt parameter 0 and to achieve performance upon checking the logical memory availability.

Regards,

Dilip Voora

[jayrijnbergen]

to increase the number of open partitions to 100.000 (just an example, increase depends on your env)

exec sp_configure "number of open partitions", 100000

you might want to check other config options as well:

exec sp_monitorconfig "all"

especially check parameters:

number of open objects

number of open indexes

if needed, increase in the same using sp_configure

increasing these config options is dynamic, no server restart needed

extra memory is required for these options, you might need to increase max memory as well

to see how much memory is still available:

exec sp_configure memory