logo

Thông báo

Icon
Error

Chia sẻ
Tùy chọn
Xem
Xem bài viết cuối
Offline admin  
#1 Đã gửi : 01/06/2018 lúc 10:03:28(UTC)
admin

Danh hiệu: Administration

Chức danh:

Nhóm: Administrators
Gia nhập: 23-07-2013(UTC)
Bài viết: 5,754
Man
Viet Nam
Đến từ: Vietnam

Cảm ơn: 8 lần
Được cảm ơn: 2 lần trong 2 bài viết

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.

Mã:
--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.

Mã:
--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.

connection pool results

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

By: Sankar Reddy

User is suspended until 02-08-2023 lúc 11:21:00(UTC) Vũ minh Định  
#2 Đã gửi : 04/07/2018 lúc 10:23:02(UTC)

Danh hiệu: Newbie

Nhóm: Registered

Bài viết được xoá bởi quản trị viên. | Lý do: Chưa rõ
Ai đang xem chủ đề này?
OceanSpiders 2.0
Di chuyển  
Bạn không thể tạo chủ đề mới trong diễn đàn này.
Bạn không thể trả lời chủ đề trong diễn đàn này.
Bạn không thể xóa bài của bạn trong diễn đàn này.
Bạn không thể sửa bài của bạn trong diễn đàn này.
Bạn không thể tạo bình chọn trong diễn đàn này.
Bạn không thể bỏ phiếu bình chọn trong diễn đàn này.

Powered by YAF.NET 2.2.3 | YAF.NET © 2003-2018, Yet Another Forum.NET
Thời gian xử lý trang này hết 0.252 giây.