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.

Optimizing the number of open partitions parameter for your system

If the default value of number of open partitions is insufficient, Adaptive Server displays a message after trying to reuse active partition descriptors. You must adjust this value.

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 110, as reported by sp_countmetadata:
```
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   Instance_Name
------------------------   ----------  ----------  -------  -----------
----------  --------------------
number of open partitions     27               57     51.8           83
          0                   NULL

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, periodically run sp_monitorconfig. Reset the cache size as the number of active descriptors changes. See sp_monitorconfig in the Reference Manual: Procedures.