Databases Reference
In-Depth Information
Thinking Through the Issue
Let's think about what we know:
•
The application does not retrieve or update LOB data.
•
Statement pooling is not being used.
•
Let's assume that the database driver is not causing the problem.
•
The application does not retrieve large amounts of data, so scrollable cursors
are not being used.
•
We know the application is using connection pooling. Let's look into this
more closely.
As discussed earlier in this topic, ODBC connection pooling as implemented
in Microsoft's Driver Manager does not provide a way to define a maximum pool
size. Therefore, the pool size grows dynamically as the application uses the pool
to get a connection. This can result in memory issues because the connection,
even when not in use, holds on to resources. How can you determine if this is the
issue?
One tool on Windows that you can use to monitor the pool is Performance
Monitor (PerfMon). The following URL contains a Microsoft document
1
that
explains how to use PerfMon to monitor connection pools:
Let's assume that after monitoring the ODBC connection pool, we did not
see an issue with it. What next? We also know that we are using ADO. With ADO,
resource pooling is turned on by default. Is the application using both ODBC
connection pooling and resource pooling? Yes.
Using two implementations of pooling would definitely use more memory
on the database server.
The Resolution
To limit the memory use on the database server associated with connections, turn
off ODBC connection pooling. Microsoft's documentation recommends that you
do not use these two types of pooling together—that you choose which imple-
mentation you want to use and use it exclusively within a given application.
1
The database application in this case study serves a large insurance company
with many users. The company has many applications. This particular applica-
1
Ahlbeck, Leland, Don Willits, and Acey J. Bunch. “Pooling in the Microsoft Data Access Components.”
May 1999 (updated August 2004). Microsoft Corporation. 2 February 2009
<http://msdn.microsoft.
