INF: Explanation of Open Objects in SQL Server

ID: Q114843

The information in this article applies to:

SUMMARY

This article is an expansion of the description of the sp_configure 'open objects' parameter found in the Microsoft SQL Server Books Online and reference manuals.


MORE INFORMATION

The sp_configure 'open objects' parameter controls the number of database objects which can be open concurrently. Database objects are those objects defined in the sysobjects table: tables, views, rules, stored procedures, defaults, and triggers.



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 of sp_configure 'open objects.' Each time a database object is referenced for the first time, SQL Server takes one of the descriptors from this free pool 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, if 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 would be considered one open object. If, however, table_a had an update trigger, then a second descriptor would be allocated to the trigger which would count as a second open object.

Each allocated descriptor has a use counter which indicates how many concurrent queries are referencing the object it defines. The use count gets incremented by one at the start of a query, and decremented by one by the end of the query. In our example above, the table_a descriptor would have a use count of 2 until the two queries finished; it would then be decremented to 0.

Once the free pool of descriptors has been used up, SQL Server starts reusing inactive descriptors when it needs to allocate a new descriptor. An inactive descriptor is one whose use count is zero. The first time SQL Server has to reuse a descriptor it issues the following 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 a system administrator notices these messages being issued frequently in the error log, they should increase the sp_configure 'open objects' parameter.

Additional query words: sql6 Windows NT 


Keywords          : kbusage SSrvGen SSrvInst 
Version           : 4.2 6.0 | 4.2
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 19, 1999