number of open partitions

Summary information
Default value	500
Range of values	100 – 2147483647
Status	Dynamic
Display level	Basic
Required role	System administrator
Configuration groups	Memory Use, Meta-Data Caches

Specifies the number of partitions that Adaptive Server can access at one time. The default value is 500.

Optimizing the number of open partitions parameter for your system

The default run value is 500. If this number is insufficient, Adaptive Server displays a message after trying to reuse active partition descriptors. You must adjust this value.

To set the number of open partitions parameter optimally:

Use sp_countmetadata to find the total number of open partitions. For example:
```
sp_countmetadata "open partitions"
```
The best time to run sp_countmetadata is when there is little activity in the server. Running sp_countmetadata during a peak time can cause contention with other processes.

Suppose Adaptive Server reports the following information:
```
There are 42 user partitions in all database(s),
requiring 109 Kbytes of memory. The 'open 
partitions' configuration parameter is currently set 
to 110.
```
Configure number of open partitions to that value, as follows:
```
sp_configure "number of open partitions", 110
```

During a peak period, find the number of active metadata cache descriptors, for example:

sp_monitorconfig "open partitions"

Usage information at date and time: Jun 30 2008  3:15PM.

Name                          Num_free        Num_active       Pct_act 
Max_Used        Reuse_cnt
---------------------------   --------------   ---------       --------
-----------     ---------
number of open partitions     27               57              51.8
83               0

In this example, 83 is the maximum number of partition descriptors that have been used since the server was last started.

Configure the number of open partitions to 83, plus 10 percent (8), for a total of 91:
```
sp_configure "number of open partitions", 91
```

If there is a lot of activity on the server, for example, if tables are being added or dropped, run sp_monitorconfig periodically. You must reset the cache size as the number of active descriptors changes. See sp_monitorconfig in the Reference Manual for more information.