SQL Server provides a few system stored procedures that can be used to monitor SQL Server by getting details on current processes, sessions, requests, locking information, and so on. In this recipe, you will see how you can use some system stored procedures to monitor current SQL Server processes, sessions, requests, and blocking information.
If you suddenly experience that database requests coming from applications are not being served normally and applications have to wait normally for database responses, you may want to do a quick check to see whether the requests are blocked by other requests or whether the processes are getting suspended very frequently.
In this example, we will use following system stored procedures to get the status of current processes:
sp_whosp_who2
The following is the prerequisite for this recipe:
An instance of SQL Server 2012 Developer or Enterprise Evaluation edition.