Configuring OLE-DB Connection Pooling


Controlling the way in which the OLE-DB connection pooling service components work, is somewhat under-documented in my opinion. Contrast that to the reams of documentation available which describe how connection pooling within .NET managed data providers can be configured.

Until I stumbled across some bits of documentation, I believed that connection pooling in OLE-DB could only be either, enabled or disabled. Happily it turns out that OLE-DB connection pooling is almost as configurable as with managed providers. Sadly though, one has to resort to changing values in the registry, rather than the nice way in which it’s done with managed providers, using connection string parameters.

Controlling the Idle Connection Timeout

[HKEY_CLASSES_ROOT\CLSID\{clsid of oledb provider}\[SPTimeout:0x00]}]

Specific to the OLEDB provider, this DWORD value specifies the length of time (in seconds), that idle connections are held in the connection pool. If not specified, the default value is 60 seconds (for SQLServer). Each time a connection is returned to the pool, it’s timeout period is reset.

Maximum Connection Lifetime

[HKEY_CLASSES_ROOT\CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829\[MaxResLifeTime:0x00]}]

Specific to the MSDAINITIALIZE class, this DWORD value specifies the lifetime limit in seconds, of pooled connection objects. This value effectively controls a connections maximum lifetime, regardless of the connection moving into and out of the pool. When a connection reaches its maximum lifetime, the pool closes the connection, returning it to the Database server. If not specified, the default value is 600 seconds (10 minutes). In a load balanced environment, this setting allows connections on one server to balance out to other servers as old connections expire.

Connection Pooling, Failed Connection Retry Behaviour

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\Session Pooling\[Retry Wait:0x00]}]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\Session Pooling\[ExpBackOff:0x00]}]

These settings control how connection pooling behaves when a Database connection attempt fails.

The “Retry Wait” setting determines the amount of time (in seconds) that the service components wait until attempting to contact the server again. This is a DWORD value and and defaults to 60.

The “ExpBackOff” setting determines the factor by which the service components will increase the wait time between attempts to contact the server. This is a DWORD value and and defaults to 2.

Requirements:

SPTimeout and “Retry Wait” requires MDAC 2.5 or above
MaxResLifeTime requires MDAC 2.7 or above

Published by

Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s