Slow running sql query on production server with sql server 2005
We got a request from users saying that sql query is taking too much time to execute. Normally it returns the result set less than 15 minutes now it taking more than 30 minutes to generate output.
We checked the active sessions on the production server by using sp_who2 command in sql server 2005.
It listed 20 active sessions on server out of which SPID 56 is taking most of the CPU time and it was running from 1 hr due to which other 2 sessions are blocked.
Using by Session process id 56, we executed the DBCC command on database to know the running query with that SPID.
Select * from emp_details where emp_sal > 25000
To find the active the transaction on database we executed the below command
Transaction information for database ‘Empdata’.
Transaction Information: Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (99785:86:20)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
It returned the select command which took almost more than 1 hr for its execution and still executing. We informed user that your query is taking too must time to execute and asked whether this is important to execute on peak time, Can we schedule this to late evening after business hours user agreed to this idea. Also we asked user confirmation for killing the session.
After the confirmation we killed session using below command using query analyzer.
It took another 10 minutes for killing the session on sql server. After that lock released and blockage removed from the table.
Now everything was working fine. We updated the request and changed the status to resolve.