FIX: SQL-DMO Operations on SQL Server 7.0 Uses More Memory (225501)

SYMPTOMS

Installing a SQL Distributed Management Objects (SQL-DMO) based application on a SQL Server 7.0 can use a larger memory footprint. While watching the memory usage for the process using SQL-DMO, you can see private bytes increase faster than the SQL Server 6.5 SQL-DMO version.

CAUSE

The SQL-DMO collections (database, tables, indexes, and so on) are used to cache information retrieval from the SQL Server. SQL Server 7.0 extends the sysname from 30 characters to 128 UNICODE characters. This can cause the local caching used by SQL-DMO to use more memory to maintain the collection.

WORKAROUND

On regular intervals invoke the Refresh method of the specified collection, using the TRUE parameter value; for example, Tables.Refresh (TRUE).

The following section was taken from the SQL Server Books Online SQL-DMO documentation.

"Refreshing a Collection"

When you use the Refresh collection method, SQL-DMO refreshes objects with current information from SQL Server. The Refresh method takes a single Boolean parameter, indicating if you want to release all objects in the collection and "under" the collection in the SQL-DMO hierarchy.

If True, SQL-DMO releases all objects in the collection and all objects "under" the collection.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

This design is similar to OLE DB resource pooling. It attempts to cache the object locally and avoid round trips to the SQL Server for each access to the specified object.

NOTE: The Release method frees the specified object back into the cache. It does not Release the memory for the specific object. The Refresh method must be used to free the cached memory.

In the Refresh method, when the Release parameter is True, all references maintained on a collection member. Any collections or objects within the members tree are released by force by SQL-DMO. SQL-DMO objects used by the application are invalid. SQL-DMO retrieves member object property values and refreshes the member object collection on the next application access to the object.

Microsoft is aware of the footprint change and may alter the caching algorithm in future releases.