Monitor User Connections in SQL Server

Scenario 2: Closing the connection

In this scenario, what we are trying is close the
connection by clicking the check box.

This time there are no failures since application can  use
the same connection. If we observe the perfmon and query we can see that one
SPID is used throughout the execution of the application.

When the application was started the number of connections are increased
by one but no more. Moreover, throughout the execution period the same SPID is
being used.

Scenario 3: Changing the Delay without closing the connection.

Now, lets try this with the delay option on. We want to
see whether there is a different behavior with the delay option. We will start
with delay of 10ms.

Let us look at the perfmon output:

Still the application is failing after reaching 100
unique SPIDs (you can verify this by running the T-SQL query which was executed
before), however, the increase in the number of connections is not as steep as before. Also,
now we can see no of actual connections has increased to 650 from 304.

Let us see this by increasing the delay.




Perfmon Graph





It is very clear that from the above graphs, whenever you
increase the delay, the time taken for the application to fail has also increased.

Delay (ms)

No of Connections

No Delay










Scenario 4: Multiple execution of application instance

In all the above scenarios we were looking at a single instance of the execution
of the application. What of if you execute the application using two instances?

The  below perfmon graph results from exectuing the app using two instances

Now you can see that when two applications are
running, you will see 200 unique SPIDs. This means allocation of 100 is per application instance.


Pages: 1 2 3


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 |