INF: Explanation of Open Objects in SQL Server

  - Microsoft SQL Server, versions 4.2 and 6.0
  - Microsoft SQL Server version 4.2 for OS/2

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

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.