Friday, November 6, 2009

Find Blocking, Expensive Processes and Bad Processes In SQL Server

1.   SP_Who and SP_Who2 replace:-
sp_who, sp_who2, Enterprise Manager, and SSMS are turns out from the master.sysprocesses table And why not get good data analysis?. Of course you can pull the good report from dmv's as well but i am not discussing here. DMV's can be used from 2005 server onwards. You can write your own SQL queries to display the information you want, and create some informative reports. Suppose, for instance, you want to know which applications are connecting to your database. Sysprocesses contains a column called program_name, here i developed one query please find below for query and screenshot.

SELECT Program_name, count(*) 
FROM master..sysprocesses 
GROUP BY Program_name 
ORDER BY count(*) desc
2.   Blocking:-
We've found that when blocking occur that users are noticing slowdowns and complain about it. One of the other columns available in the sysprocesses table is "blocked". Normally, this column has a value of zero, If the value is zero then table is not blocked. However, if a row has a non-zero blocked value, that value is an integer which is the SQL Server process ID number of the process that this process is blocked by. 

SELECT count(*) from master..sysprocesses
WHERE blocked <> 0 

3.   Kill SPID:- 
SELECT spid 
From master..sysprocesses 
WHERE loginame = 'Domain\username' 

IF you want to kill all the SPIDs of this particular user then use below query. 

SELECT count(*) from master..sysprocesses
WHERE blocked <> 0 

4.   Expensive Processes:- 
Below script will give you the SPID, Name of the application, and an integer called SPIDduration, which represents the number of seconds that the SPID has been logged into the database. 

SELECT spid, program_name, datediff
(second,login_time, getdate()) as SPIDduration
FROM master..sysprocesses WHERE spid > 50

5.   Find Bad Processes:- 
The output of this below script will be top 50 processes, sorted by descending CPUTimeBYSeconds. (CPUTimeBYSeconds is the alias we've given to the computed column Cputime/connected scconds) 

Select top 50 spid, blocked,
db_name(dbid)) as DBName, cpu,
datediff(second,login_time, getdate()) as Secsonds,
convert(float, cpu / datediff(second,login_time, getdate())) as CPUTimeBYSeconds,
convert(varchar(16), hostname) as Host,
convert(varchar(50), program_name) as Program,
convert(varchar(20), loginame) as Login
FROM master..sysprocesses
datediff(second,login_time, getdate()) > 0 and spid > 50 

How to find blocking. how to know blocking. how to resolve blocking. how to get rid of blocking. how to set priority for blocking. how to resolve blocking. sql server 2005 blocking. sql server 2008 blocking. sql server 2008 r2 blocking. sql server 2012 blocking. sql blocks. deadlocks. how to resolve deadlocks. how to kill blocking process. how to kill process. how to avoid blocks. how to find bad process. how to find expensive process. how to know sql server expansive process. how to check sql server expensive process. how to find out blocking. identifying blocking. sp_who2 for finding the process. sql server 2012 kill process.


Wow its a wonderful article, i usually run sp_who2, thanks you so much for sharing valuable information to the community. I am a regular visitor of your blog

Post a Comment