Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script to check SQL Server connection pooling


By:   |   Last Updated: 2009-09-04   |   Comments (7)   |   Related Tips: More > Triggers

Problem
Our applications are using a high number of connections and the server is very busy. How do I check whether the application is using connection pooling correctly when connecting to SQL Server? Is there an easy way to see which connections are using connection pooling and which ones are not?  In this tip, I will go over how to capture this information for each logon, so you can see which connections are taking advantage of connection polling.

Solution
SQL Server 2005 introduced DDL Triggers and this solution takes advantage of the DDL Logon trigger along with the EVENTDATA() function to determine if the connections are using connection pool or NOT.

Identifying this fact is very important to every DBA as the cost of opening a connection every time data is requested is expensive. The cost of each user connection is approximately (3 * network_packet_size + 94 KB) where the default network packet size is 4 KB. That means each user connection cost is approximately 130 KB. In addition, the time it takes to make the connection can also slow down processing.

Here is the script to create a table to store the data and a DDL Logon trigger to capture this data.  This should be created in the master database.

--Create the dbo.ServerLogonHistory Table

CREATE TABLE dbo.ServerLogonHistory

    (

                EventType   VARCHAR(512),

                PostTime    DATETIME,

                SPID        INT,

                ServerName  VARCHAR(512),

                LoginName   VARCHAR(512),

                LoginType   VARCHAR(512),

                SID         VARCHAR(512),

                ClientHost  VARCHAR(512),

                IsPooled    BIT

    )

GO

    --Create the Logon Trigger Trigger_ServerLogon

CREATE TRIGGER Trigger_ServerLogon

            ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON

            AS

    BEGIN

    DECLARE @data XML

        SET @data = EVENTDATA()

    INSERT INTO dbo.ServerLogonHistory

        SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)')

                , @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')

                , @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(4)')

                , @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)')

                , @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)')

                , @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)')

                , @data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)')

                , @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)')

                , @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'nvarchar(1)')

    END

GO
            

In order for users to write to this table, that do not have rights to this table, I am using the EXECUTE AS clause in the trigger to run the code using the equivalent of the "sa" account.   If you do not want to use the "sa" account another option is to create a new login, grant insert permissions to this table for this login and then use this login in the EXECUTE AS clause in the trigger.

Here is another way to do this by giving GRANT INSERT permissions to PUBLIC for table master.dbo.ServerLogonHistory.  

--Create the dbo.ServerLogonHistory Table

CREATE TABLE dbo.ServerLogonHistory

    (

                EventType   VARCHAR(512),

                PostTime    DATETIME,

                SPID        INT,

                ServerName  VARCHAR(512),

                LoginName   VARCHAR(512),

                LoginType   VARCHAR(512),

                SID         VARCHAR(512),

                ClientHost  VARCHAR(512),

                IsPooled    BIT

    )

GO



    --Grant insert rights to public for this table

GRANT INSERT ON dbo.ServerLogonHistory TO PUBLIC



    --Create the Logon Trigger Trigger_ServerLogon

CREATE TRIGGER Trigger_ServerLogon

            ON ALL SERVER FOR LOGON

            AS

    BEGIN

    DECLARE @data XML

        SET @data = EVENTDATA()

    INSERT INTO dbo.ServerLogonHistory

        SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)')

                , @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')

                , @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(4)')

                , @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)')

                , @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)')

                , @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)')

                , @data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)')

                , @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)')

                , @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'nvarchar(1)')

    END

GO

Here is a sample result from the data that was collected using the above DDL trigger.

 

You can look at the IsPooled column to check if the connections are pooled.
  • 1 = pooled and
  • 0 = non-pooled connections.

Next Steps:

  • Here is another approach for finding connection pool information by James Rowland-Jones
  • Take a look at these other DDL Triggers 
  • Keep on eye on this audit table, because this will track all connections to your database server and may get quite large.
  • If you have any issues with LOGON triggers and you can not connect to SQL Server review this tip


Last Updated: 2009-09-04


next webcast button


next tip button



About the author
MSSQLTips author Sankar Reddy Sankar Reddy is a Database Engineer/DBA and SQL Server MVP. He has been working with SQL Server since 2003 in a variety of roles.

View all my tips
Related Resources


Become a paid author


Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, March 04, 2016 - 6:02:39 PM - Rob Catalano Back To Top

Thanks for the script!

Use this script to delete the trigger when you have enough data.

I had to be logged in as 'SA' for the correct permissions.

DROP TRIGGER Trigger_ServerLogon ON ALL SERVER 

 

Tuesday, July 07, 2015 - 1:47:51 AM - Trupti Back To Top

Script to check SQL Server connection pooling

I read your connection pooling blog it helped a lot Thanks for sharing. But using same can I get SPID details as well.
I want to view which user is updating which database.In short one more column in output window with db name.

Wednesday, September 03, 2014 - 3:05:41 AM - sawsan Back To Top

Dear,

Really useful, i tried this solution but i found tha AuditLoginTable grew exponentially, and added more than one record for each login

and every time I executed select query, the returned data increased twice.

Whad do you suggest to avoid this issue???

 

Sunday, January 16, 2011 - 6:31:49 AM - Yaniv Etrogi Back To Top

Good article.

I would like to point out that another approach exists and that is via sql server trace. For a detailed explanation you can visit this link:

http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2009/02/23/detecting-none-pooled-logins.aspx 

 

-Yaniv

 

 

Monday, August 16, 2010 - 2:06:53 PM - admin Back To Top
Thanks Richard for pointing that out.  This has been updated to reflect the correct author.

Monday, August 16, 2010 - 11:21:45 AM - Vishal Gamji Back To Top
Nice tip. Really useful. Thanks.

Friday, August 13, 2010 - 7:27:31 AM - Richard Douglas Back To Top
In the next steps section it states there is another approach by Jamie Thompson, however the link is to James Rowland-Jones's blog :-)
You should change the text to give credit to the right person.

Rich

Learn more about SQL Server tools