Troubleshooting Database Slowness and Blocking with sp_who, sp_who2 and sp_WhoIsActive
When users are reporting slowness in applications then as a database administrator what is your first step, you will try to find some blocking from current active processes. Most of the DBA’s first step is to fire the query sp_who or sp_who2 and check the details.
Today we will discuss how we can work with sp_who, sp_who2 and overview on sp_WhoIsActive as well. SPIDs from 1 to 50 are reserved for system processes and normally do not create issues and above SPID 50 are user processes which normally creates problem and you have to troubleshoot and resolve these issues.
sp_who
You can use sp_who system stored procedure whenever you want to check the details for blocking, active users, sessions or processes. This stored procedure is officially provided by Microsoft and very useful for day to day activities. If you add a parameter ‘Active’ with sp_who you will get current active users, sessions, processes and other details as shown above.
You can use and execute sp_who as:
sp_who
sp_who ‘active’
sp_who ‘loginname’ –put your login name from sessions
sp_who 57 –where 57 is spid
sp_who ‘active’
As stated above you will get current active users, sessions, processes details with this procedures.
sp_who2
Though this system stored procedure is undocumented by Microsoft but it is very useful and widely used by DBAs and database developers across the globe. sp_who2 helps in finding all sessions which are available and established in database instance. It helps in finding blocking, high CPU, high disk IO as shown in figure above.
You can find the blocking SPID in BlkBy column and kill the same after analysing it with DBCC inputbuffer(spid).
You can use and execute sp_who2 as:
sp_who2
sp_who2 ‘active’
sp_who2 ‘loginname’ –put your login name from sessions
sp_who2 57 –where 57 is spid
sp_who2 ‘active’
sp_who2 ‘active’ is an extension to sp_who and similar to sp_who2 in output, it is very helpful in analysing current active sessions details and has more columns and useful details as compared to sp_who. It also helps in analysing parallel processes running with same SPIDs.
sp_WhoIsActive
sp_WhoIsActive is an external stored procedure provided by Adam Machanic from blog and is very useful and more advanced to sp_who and sp_who2. It gives you details about total duration for blocking, sql query involved in blocked by and blocking, query execution plan, xml file, blocking session id and wait time and wait type etc.
Though it takes much time for execution as compared to sp_who and sp_who2 but results provided by it is quite interesting and saves your lots of time for troubleshooting.
By adding extra parameters you will find execution plan details and xml details as given below.
@get_additional_info = 1
@get_plans =1
@get_locks = 1
You can use this procedure as:
sp_WhoIActive
sp_WhoIActive @get_additional_info = 1, @get_plans =1 –To get extra columns
Once you identified your blocking spid, high cpu time or high disk IO then you can kill the query with KILL command as:
KILL 57 –where 57 is blocking spid
You can download the script for sp_WhoIsActive from here.
CLICK HERE to watch live practical.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]
You may also like
