Systems Engineering and RDBMS

Pooled Connections

Posted by decipherinfosys on May 31, 2010

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:

  1. Logon Triggers and EventData() function – here, here and here.
  2. MSDN article on connection pooling for the .Net Framework Data Provider for SQL Server – here.
  3. Another good article on ADO.Net connection pooling – here.
  4. Article at Pythian – excellent article discussing connection pooling – here.
  5. MSDN post on SQL Server connection Pooling (ADO.Net) – here.
About these ads

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

 
Follow

Get every new post delivered to your Inbox.

Join 82 other followers

%d bloggers like this: