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




Array

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 |