SQL includes a simple stored procedure, sp_who that allows for fast monitoring of SQL server resources by examining active and inactive SQL processes. Even with an efficient facade and coding style, sp_who can be used to assess performance in several different ways:

1. Detecting connection leaks - Even a well-developed .NET application can be brought to its knees with a single connection leak, causing the application’s connection pool to fill and prevent further SQL queries.
2. Assessing application load - How much of a resource hog is your application in a live environment? If you can identify SQL-based bottlenecks in your application, you’ll be able to optimize them.

Read the rest of this entry »