Monitor User Connections in SQL Server

Applications typically make connections to the SQL Server databases in large numbers but what if those connections are not successfully closed? How you can monitor them? This short article describes the methods we can use to monitor unclosed connections.

Sample Application

Let us start with a simple C# WinForms application which has the following UI.

The Connect button will have the below coding behind it:

private void Connect_Click(object sender, EventArgs e)

        {

            int i = 0;

            while (1 == 1) // Loop indefinitely

            {

                //Connection is create and connectiong to database

                string conn = “Data Source=.;Initial Catalog=UserConnection;

Integrated Security=SSPI;enlist=false”;

                SqlConnection sqlConn = new SqlConnection(conn);

                sqlConn.Open();

                //Store procedure insert_connection_details is executed,

                //looping instance value  (i) will be passed

                SqlCommand com = new SqlCommand(“insert_connection_details”, sqlConn);

                com.CommandType = CommandType.StoredProcedure;

                com.Parameters.Add(new SqlParameter(“@connectionId”, SqlDbType.Int)).Value = i;

                com.ExecuteNonQuery();

                //if the Close check box is selected onlyConnection will be closed

                if (chkClose.Checked)

                    sqlConn.Close();

//if the dealy is not 0, there will be delayed of millisecond of given value in Delay box

                if (txtDelay.Text != “0″)

                {

                    Thread.Sleep(Convert.ToInt32(txtDelay.Text));

                }

                i = i + 1;

            }

Since this code is very simple and fully commented you should understand what is happening with the connect button.

From the SQL Server side, as seen from the above code, there should be a stored procedure as well as a table to store this information.

CREATE TABLE [dbo].[user_connection](

      [ConnectionID] [int] NOT NULL,

      [SPID] [int] NULL,

      [DateTime] [datetime] NULL,

 CONSTRAINT [PK_user_connection] PRIMARY KEY CLUSTERED

(

      [ConnectionID] ASC

)

)

So after the table is created, the next step is to create the stored procedure:

CREATE PROC [dbo].[insert_connection_details]

@connectionId int

AS

SET NOCOUNT ON

INSERT INTO user_connection

SELECT

@connectionId,

@@SPID,

GETDATE()

So now we are ready for the testing. For testing, we will use standard tools like perfmon and SQL Server Profiler. In perfmon we will be using the User Connections counter of the SQL Server General Statics object. Meanwhile, in the SQL Profiler, the Audit Login event will be captured.

So are you ready to go.

Scenario 1: Not closing the database connection.

When we executed the above code, the code will fail with the following error.

We will further analyze this error is at the end of this article.

After this error if we look at the perfmon output you may see something like this:

Before running the application, the count was 5 which is caused by system processes which are connected to SQL Server.   When the application starts, the number of connections starts to increase rapidly and after reaching 105 it stops.

If you check the profiler or the table, you will see there are more than 100 connections that have been made to the databases. To be exact, there are 304 records in the table.

Let’s execute few queries against this table.

If you simply run the SELECT * statement, you will see following result set.

Here you can see that SPID is not a unique number and it is repeating. For example, SPID  57 can be seen in two places. Similarly, other  SPIDs are also duplicated.

SELECT SPID

       ,COUNT(1) SPID_COUNT

FROM user_connection

GROUP BY SPID

ORDER BY 1

  

The above result shows that SPID is used multiple times. For example, SPID 56 is used for 6  times, 68 is used 4 times etc.

However, total count for rows here is exactly matches 100. There you are – the Application is failing when it reaches unique SPID equal 100 not the number of connections.

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |