June 23, 2009 at 3:59 pm
i was wondering, is there a way, i automatically kill the blocking sessions id? If this can not be done from SQL Server, is there a third party product wihch can do this?
Basically I dont want to have any blocking nor deadlock happening on my system.
June 23, 2009 at 7:18 pm
California (6/23/2009)
i was wondering, is there a way, i automatically kill the blocking sessions id? If this can not be done from SQL Server, is there a third party product wihch can do this?Basically I dont want to have any blocking nor deadlock happening on my system.
You can kill a blocking process using Kill statement
Refer BOL.
Also, killing processes isn't a solution. you need to check what's causing blockings. do you need to tune in some queries or create new indexes. There are other reasons for blocking as well. For example, you cannot perform schema change while backup is running. your query must wait for the backup to complete(an example of blocking). Since it's an integral part of sql server, you cannot entirely eliminate blockings.
This query will find out the blocking queries. You can put them into a job to automate.
[highlight=""]--================================
SELECT DB_NAME(tl.resource_database_id) AS DatabaseName, tl.resource_type, tl.request_mode, tl.resource_associated_entity_id ,
tl.request_session_id 'who_session',who_es.program_name 'who_program', who_es.nt_domain 'who_nt_domain', who_es.nt_user_name 'who_user_name', who_es.login_name 'who_login_name',
wt.blocking_session_id 'blk_session',bl_es.program_name 'blk_program', bl_es.nt_domain 'blk_nt_domain', bl_es.nt_user_name 'blk_user_name', bl_es.login_name 'blk_login_name',
request_owner_id 'transaction_id', wt.resource_description
FROM sys.dm_tran_locks as tl
INNER JOIN sys.dm_os_waiting_tasks as wt
ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_sessions bl_es on bl_es.session_id=tl.request_session_id
INNER JOIN sys.dm_exec_sessions who_es on who_es.session_id=wt.blocking_session_id
--================================[/highlight]
June 23, 2009 at 9:31 pm
Blocking is the way to synchronizing data access .
Just imagine what will happen if someone pulls the chair on which you are sitting .
As pradeep said ,killing is not going to help .
you can use sp_who2 '' or select * from sys.sysprocesses where blocked 0
Then , try to find out the SPID at the head of the blocker list .
Head of the blocker is the one due to which entire blocking chain is being created.There can be more than 1 head of blockig list .for EG SPID 121 is blocked by 123 is blocked by 155 and another list could be SPID 267 blocked by 280 blocked by 300 .In the above example 155 and 300 are the head blockers.
Then do a dbcc inputbuffer to find out what these SPIDs are doing .
And then you need to take appropriate action like creating indexes , updating stats/ rebuilding indexes,using hints in case a lot of locks are being created or higher level locks are being created , changing your application code the way it accesses the data or if you are in SQL Server 2005 using Snapshot isolation level in case your reads are being blocked .
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 24, 2009 at 4:30 pm
Thanks for the detailed answer.
my scenarios: If i even see the blocking SPID, i can not do anything. the application code is all dynamically generated and have no control. thats why i asked, if there's a way, i can monitor blocking and automatically kill the spid? possible?
Second option would be using snapshot isolation. i read few articles on it and it says, it wont block readers nor writers. is it true? if yes, how efficient is this? I am upgrading my environment to SQL 2008 and will it help to move to snapshot isolation to avoid blocking? any articles here on this new isolation level?
Thanks again for all the contribution.
June 24, 2009 at 9:41 pm
1) for how long will you do that ..its going to be a pain .You can create a SP using cursors to do that .Remember you cant do kill @spid_name .So you will have to generate a line of commands as output and execute each line serially .
2) You need to test it in your enviornment , how efficient it is .
Reason is when you want to read the data which is being modified , a snapshot is created and kept in tempdb .The users read that copy .
So writers wont block readers and readers wont block writers .
But still writers will block writers .2 updates and 2 inserts might get blocked if the lock is > row level .
3) you should concentrate of finding the root cause of blocking and eleminate it .make sure you have proper indexes , proper maintenance of those indexes is happening and stats are refreshed in a timely manner .
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 25, 2009 at 10:36 am
thanks for providing the detailed answer. indeed its very useful. i will follow the recommend solution.
thanks again,
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply