What is connection pooling and why is it necessary? A connection resource on the database side is essentially the database server process, the session and the associated memory. In order to utilize the database resources effectively and to provide end user concurrency, applications use various resource pooling solutions. In a typical web based application, connection pooling is typically done by a middle tier like an application server which gets x number of connections to a database and stores them in a pool (like an array). Those are then marked as not being in use. When a request comes in from the end user that goes through the application server, it would get a connection from the pool and use it as well as mark it as “in use”. Once the response is given to the request and the session has been fulfilled, it goes back to the pool so that it can be used by another request.
The benefits of connection pooling are obvious – no connect/disconnect of the connections which is an overhead and using pooled connections, one can service the needs to many users. In this post, we will look into pooled connections from a DBA’s perspective i.e. how a DBA can find out whether the connections hitting their instance are pooled or not.
As per MSDN, the memory usage per connection is roughly: (3 * network_packet_size + 94) KB. So, for 4096 byte network packet size which is the default, we are talking about 106KB per connection. So, you can see how it can add up pretty fast. So, from a DB side, how can we track whether our applications are using pooled connections? We can either trace the sessions (sql trace/profiler provides all the pertinent information) – using the same SQL that we had used in our Profiler basics post, we can change the filter condition to see which event classes and which event name and data columns & subclassnames are to be used:
select
v2.name as Event_Class
, v1.name as event_name
, v4.name as data_column
, case v4.is_filterable when 0 then 'No' else 'Yes' end as Is_Column_Filterable
, v5.subclass_name
, v5.subclass_value
from
sys.trace_events as v1
inner join sys.trace_categories as v2
on v1.category_id = v2.category_id
inner join sys.trace_event_bindings as v3
on v1.trace_event_id = v3.trace_event_id
inner join sys.trace_columns as v4
on v3.trace_column_id = v4.trace_column_id
inner join sys.trace_subclass_values as v5
on v3.trace_event_id = v5.trace_event_id
and v3.trace_column_id = v5.trace_column_id
where v5.subclass_name like '%pooled%'
order by v2.name, v1.name
Output:
Event_Class event_name data_column Is_Column_Filterable subclass_name subclass_value
Security Audit Audit Login EventSubClass Yes Nonpooled 1
Security Audit Audit Login EventSubClass Yes Pooled 2
Security Audit Audit Login Failed EventSubClass Yes Nonpooled 1
Security Audit Audit Login Failed EventSubClass Yes Pooled 2
Security Audit Audit Logout EventSubClass Yes Nonpooled 1
Security Audit Audit Logout EventSubClass Yes Pooled 2
Using these, one can trace it out either by using SQL Trace or an active Profiler session. However, a better solution would be to make use of the EVENTDATA() function along with a logon trigger since that would put less load on the server and also provide us an easy way to do trend analysis – we had discussed their usage once before when answering a question from a reader on how to prevent connections from certain IP addressess – you can read that post here. As you know, in order to capture the XML data about LOGON events, we will make use of the EVENTDATA() function. And as per BOL, the LOGON event returns this event data schema – and it has the IsPooled information that we are interested in:
<EVENT_INSTANCE>
<EventType>event_type</EventType>
<PostTime>post_time</PostTime>
<SPID>spid</SPID>
<ServerName>server_name</ServerName>
<LoginName>login_name</LoginName>
<LoginType>login_type</LoginType>
<SID>sid</SID>
<ClientHost>client_host</ClientHost>
<IsPooled>is_pooled</IsPooled>
</EVENT_INSTANCE>
Unlike the Audit logon trigger that we had created in that post, over here we would be interested in doing some trend analysis since we need to capture the data over a period of time to see what is going on and whether we have pooled connections or not. So, let’s create a table for analysis:
CREATE TABLE dbo.AUDIT_POOLED_CONNECTION
(
AUDIT_POOLED_CONNECTION_ID INT IDENTITY NOT NULL
, EVENT_TYPE NVARCHAR(128) NOT NULL
, POST_TIME DATETIME NOT NULL
, SPID INT NOT NULL
, SERVER_NAME NVARCHAR(128) NOT NULL
, LOGIN_NAME NVARCHAR(128) NOT NULL
, CLIENT_HOST NVARCHAR(30) NOT NULL
, IS_POOLED BIT NOT NULL
, CREATE_DATE_TIME DATETIME NOT NULL DEFAULT GETDATE()
, CREATE_USER_ID INT NOT NULL DEFAULT 2 --ADMIN USER
,CONSTRAINT PK_AUDIT_POOLED_CONNECTION PRIMARY KEY (AUDIT_POOLED_CONNECTION_ID)
)
GO
And here is the simple logon trigger code to dump the data into this table:
create trigger AuditIsPooled_Demo
/* server means instance level*/
on all server
with execute as 'sa'
/* We specify the logon event at this stage
– Capture all the pertinent information for trend analysis
*/
for logon
as
begin
/*Insert the data that we are interested in for Pooled connections analysis*/
Insert into dbo.Audit_Pooled_Connection (Event_Type, Post_Time, SPID, Server_Name, Login_Name, Client_Host, Is_Pooled)
SELECT
EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(128)’) as Event_Type
, EVENTDATA().value(‘(/EVENT_INSTANCE/PostTime)[1]’, ‘datetime’) as Post_Time
, EVENTDATA().value(‘(/EVENT_INSTANCE/SPID)[1]’, ‘int’) as SPID
, EVENTDATA().value(‘(/EVENT_INSTANCE/ServerName)[1]’, ‘nvarchar(128)’) as Server_Name
, EVENTDATA().value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘nvarchar(128)’) as Login_Name
, EVENTDATA().value(‘(/EVENT_INSTANCE/ClientHost)[1]’, ‘nvarchar(30)’) as Client_Host
, EVENTDATA().value(‘(/EVENT_INSTANCE/IsPooled)[1]’, ‘bit’) as Is_Pooled
end
go
Now, upon several executions, we can see the output in that table (abridged):
AUDIT_POOLED_CONNECTION_ID EVENT_TYPE POST_TIME SPID SERVER_NAME LOGIN_NAME CLIENT_HOST IS_POOLED CREATE_DATE_TIME CREATE_USER_ID
118 LOGON 2010-05-31 12:00:04.023 56 SVDB-03 sysdba 192.168.104.130 1 2010-05-31 12:00:04.037 2
119 LOGON 2010-05-31 12:00:04.037 58 SVDB-03 sysdba 192.168.104.130 0 2010-05-31 12:00:04.037 2
So, now we have seen how a DBA can monitor pooled connections from the instance side. And for more understanding of the connection pools, their configuration and controlling connection pool fragmentation, look at the resources section below.
We will look into connection pooling in Oracle from a DBA perspective perspective in one of our future posts. Oracle has also introduced DRCP (Database Resident Connection Pooling) in Oracle 11g which is also something we will look into that post.
Resources:
- Logon Triggers and EventData() function – here, here and here.
- MSDN article on connection pooling for the .Net Framework Data Provider for SQL Server – here.
- Another good article on ADO.Net connection pooling – here.
- Article at Pythian – excellent article discussing connection pooling – here.
- MSDN post on SQL Server connection Pooling (ADO.Net) – here.
