open objects Option

Use the open objects option to set the maximum number of database objects that can be open at one time on Microsoft® SQL Server™. Database objects are those objects defined in the sysobjects table: tables, views, rules, stored procedures, defaults, and triggers.

open objects is dynamic, growing as needed (self-configuring option). In other words, SQL Server sets this value depending on the current needs of the system. In most cases, you should not need to change this value.

Consider increasing the value set in open objects if SQL Server displays a message that you have exceeded the number of open objects. Because open objects consume memory, increasing this value takes memory from other SQL Server uses and makes it necessary to increase the amount of memory dedicated to the server. The default is to allow SQL Server to set and increase open objects as needed.

At server startup, SQL Server builds a pool of descriptor data structures in memory that are used to describe database objects as they are referenced. The number of descriptors built is equal to the number set in open objects. The first time a database object is referenced, SQL Server takes one of the descriptors from the free pool of descriptor data and allocates it to the specific object. If multiple tasks reference the same object at the same time, it is still considered one open object.

For example, two tasks issue the following command at the same time:

UPDATE table_a SET cola = @variable

There is only one descriptor allocated to table_a, which is considered one open object. However, if table_a has an update trigger, then a second descriptor is allocated to the trigger, counting as a second open object.

Each allocated descriptor has a use counter that indicates how many concurrent queries are referencing the object it defines. The use count is increased by one at the start of a query, and decreased by one by the end of the query. In the earlier example, the table_a descriptor would have a use count of 2 until the two queries finish; it then decreases to 0.

After the free pool of descriptors has been used, SQL Server starts reusing inactive descriptors when it needs to allocate a new descriptor. An inactive descriptor is one whose use count is 1. The first time SQL Server has to reuse a descriptor, it issues this message in the error log:

Warning: OPEN OBJECTS parameter may be too low;

attempt was made to free up descriptors in localdes().

Run sp_configure to increase parameter value.

SQL Server repeats this message after each 1,000 times it has to reuse a descriptor. If you notice that these messages are being issued frequently in the error log, set open objects to a higher value.

open objects is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change open objects only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.

To set the open objects option

See Also