Sql Server Blocking Issue.

Question

When run sql server simple query it is taking more time. I do not know that somthing blocking in sql server,. How do find it?

Answer 1

Use below query to find out which process id (spid) is blocked 

 

select * from sys.sysprocesses where blocked<>0

 

and to find out what query is blocking sql server use below query.

 

select * from sys.dm_exec_sql_text (0x0100080046D0CE3A602752773B00000000000000)

 

Here yellow part is sql_handle column from  sys.sysprocesses tables.

 

Answer 2

SQL Server has inbuild query to check the status of currently running queries on the server.

Simply execute sp_who2  command to check which queries taking time to execute.