{"id":826,"date":"2015-12-17T10:22:58","date_gmt":"2015-12-17T04:52:58","guid":{"rendered":"http:\/\/pramodsingla.com\/?p=826"},"modified":"2015-12-17T10:22:58","modified_gmt":"2015-12-17T04:52:58","slug":"sql-server-find-blocking","status":"publish","type":"post","link":"https:\/\/pramodsingla.com\/?p=826","title":{"rendered":"SQL Server -Find Blocking"},"content":{"rendered":"

Problem:<\/strong><\/span> Many a times DBA\/Developer needs to identify the blocking in SQL server. Following queries will help you out.<\/p>\n

Solution:<\/strong><\/span><\/p>\n

SELECT<\/span>\u00a0[owt]<\/span>.<\/span>[session_id]<\/span>,<\/span>
\n[owt]<\/span>.<\/span>[exec_context_id]<\/span>,<\/span>
\n[owt]<\/span>.<\/span>[wait_duration_ms]<\/span>,<\/span>
\n[owt]<\/span>.<\/span>[wait_type]<\/span>,<\/span>
\n[owt]<\/span>.<\/span>[blocking_session_id]<\/span>,<\/span>
\nowt<\/span>.<\/span>blocking_exec_context_id<\/span>,<\/span>
\n[owt]<\/span>.<\/span>[resource_description]<\/span>,<\/span>
\nCASE<\/span>\u00a0[owt]<\/span>.<\/span>[wait_type]<\/span>
\nWHEN<\/span>\u00a0N’CXPACKET’<\/span>\u00a0THEN<\/span>\u00a0RIGHT<\/i><\/span>\u00a0(<\/span>[owt]<\/span>.<\/span>[resource_description]<\/span>,<\/span>
\nCharindex<\/i><\/span>\u00a0(<\/span>N’=’<\/span>,<\/span>
\nReverse<\/i><\/span>\u00a0(<\/span>[owt]<\/span>.<\/span>[resource_description]<\/span>)<\/span>)<\/span>\u00a0–<\/span>\u00a01<\/span>)<\/span>
\nELSE<\/span>\u00a0NULL<\/span>
\nEND<\/span>\u00a0AS<\/span>\u00a0[Node\u00a0ID]<\/span>,<\/span>
\n[es]<\/span>.<\/span>[program_name]<\/span>,<\/span>
\n[est]<\/span>.<\/span>text<\/span>,<\/span>
\n[er]<\/span>.<\/span>[database_id]<\/span>,<\/span>
\n[eqp]<\/span>.<\/span>[query_plan]<\/span>,<\/span>
\n[er]<\/span>.<\/span>[cpu_time]<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>dm_os_waiting_tasks<\/span>\u00a0[owt]<\/span>
\nINNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>dm_exec_sessions<\/span>\u00a0[es]<\/span>
\nON<\/span>\u00a0[owt]<\/span>.<\/span>[session_id]<\/span>\u00a0=<\/span>\u00a0[es]<\/span>.<\/span>[session_id]<\/span>
\nINNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>dm_exec_requests<\/span>\u00a0[er]<\/span>
\nON<\/span>\u00a0[es]<\/span>.<\/span>[session_id]<\/span>\u00a0=<\/span>\u00a0[er]<\/span>.<\/span>[session_id]<\/span>
\nOUTER<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>Dm_exec_sql_text<\/b><\/span>\u00a0(<\/span>[er]<\/span>.<\/span>[sql_handle]<\/span>)<\/span>\u00a0[est]<\/span>
\nOUTER<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>Dm_exec_query_plan<\/b><\/span>\u00a0(<\/span>[er]<\/span>.<\/span>[plan_handle]<\/span>)<\/span>\u00a0[eqp]<\/span>
\nWHERE<\/span>\u00a0\u00a0[es]<\/span>.<\/span>[is_user_process]<\/span>\u00a0=<\/span>\u00a01<\/span>
\nAND<\/span>\u00a0es<\/span>.<\/span>session_id<\/span>\u00a0>=<\/span>\u00a050<\/span>
\n–and\u00a0\u00a0\u00a0[owt].[blocking_session_id]\u00a0is\u00a0null<\/i><\/span>
\n–and\u00a0owt.blocking_exec_context_id\u00a0is\u00a0null<\/i><\/span>
\nORDER<\/span>\u00a0\u00a0BY<\/span>\u00a0[owt]<\/span>.<\/span>[session_id]<\/span>,<\/span>
\n[owt]<\/span>.<\/span>[exec_context_id]<\/span>;<\/span><\/p>\n

go<\/span><\/p>\n

SELECT<\/span>\u00a0TOP<\/span>\u00a010<\/span>\u00a0Object_name<\/i><\/span>(<\/span>qt<\/span>.<\/span>objectid<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0‘SP\u00a0Name’<\/span>,<\/span>
\nSubstring<\/i><\/span>(<\/span>qt<\/span>.<\/span>text<\/span>,<\/span>\u00a0(<\/span>\u00a0qs<\/span>.<\/span>statement_start_offset<\/span>\u00a0\/<\/span>\u00a02<\/span>\u00a0)<\/span>\u00a0+<\/span>\u00a01<\/span>,<\/span>
\n(<\/span>
\n(<\/span>\u00a0CASE<\/span>\u00a0qs<\/span>.<\/span>statement_end_offset<\/span>
\nWHEN<\/span>\u00a0–<\/span>1<\/span>\u00a0THEN<\/span>\u00a0Datalength<\/i><\/span>(<\/span>qt<\/span>.<\/span>text<\/span>)<\/span>
\nELSE<\/span>\u00a0qs<\/span>.<\/span>statement_end_offset<\/span>
\nEND<\/span>
\n–<\/span>
\nqs<\/span>.<\/span>statement_start_offset<\/span>\u00a0)<\/span>\u00a0\/<\/span>\u00a02<\/span>\u00a0)<\/span>\u00a0+<\/span>\u00a01<\/span>)<\/span>\u00a0AS<\/span>\u00a0statement_text<\/span>,<\/span>
\nqp<\/span>.<\/span>query_plan<\/span>,<\/span>
\nqs<\/span>.<\/span>percent_complete<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>dm_exec_requests<\/span>\u00a0AS<\/span>\u00a0qs<\/span>
\nCROSS<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>Dm_exec_sql_text<\/b><\/span>(<\/span>qs<\/span>.<\/span>sql_handle<\/span>)<\/span>\u00a0AS<\/span>\u00a0qt<\/span>
\nOUTER<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>Dm_exec_query_plan<\/b><\/span>(<\/span>qs<\/span>.<\/span>plan_handle<\/span>)<\/span>\u00a0qp<\/span>
\nWHERE<\/span>\u00a0\u00a0qt<\/span>.<\/span>dbid<\/span>\u00a0=<\/span>\u00a0Db_id<\/i><\/span>(<\/span>)<\/span>\u00a0—\u00a0Filter\u00a0by\u00a0current\u00a0database<\/i><\/span><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Problem: Many a times DBA\/Developer needs to identify the blocking in SQL server. Following queries will help you out. Solution:[…]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/826"}],"collection":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=826"}],"version-history":[{"count":0,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/826\/revisions"}],"wp:attachment":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=826"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=826"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=826"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}