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.

Delay

25

50

Perfmon Graph

Delay

100

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

304

10

650

25

730

50

727

100

723

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.

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 |