{"id":6490,"date":"2019-03-12T08:27:04","date_gmt":"2019-03-12T08:27:04","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6490"},"modified":"2019-11-28T07:00:53","modified_gmt":"2019-11-28T07:00:53","slug":"sql-server-blocking-query","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-blocking-query\/","title":{"rendered":"SQL Server Blocking Query"},"content":{"rendered":"

Hi,<\/p>\n

While you are using your program which is connected to SQL Server database, you can feel\u00a0slowness on application related with SQL Server database. This problem is probably related with blocking session and database lock.<\/p>\n

\"\"<\/p>\n

<\/p>\n\n

To find blocking sessions or queries, execute following script. If blocking query are not ended in a short time, you can kill it after asking customer.<\/p>\n

 <\/p>\n

SELECT\r\ndb.name DBName,\r\ntl.request_session_id,\r\nwt.blocking_session_id,\r\nOBJECT_NAME(p.OBJECT_ID) BlockedObjectName,\r\ntl.resource_type,\r\nh1.TEXT AS RequestingText,\r\nh2.TEXT AS BlockingTest,\r\ntl.request_mode\r\nFROM sys.dm_tran_locks AS tl\r\nINNER JOIN sys.databases db ON db.database_id = tl.resource_database_id\r\nINNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address\r\nINNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id\r\nINNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id\r\nINNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id\r\nCROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1\r\nCROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2\r\nGO<\/pre>\n
 <\/p>\n\n
 <\/p>\n
Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.<\/span><\/h4>\n
https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Hi, While you are using your program which is connected to SQL Server database, you can feel\u00a0slowness on application related with SQL Server database. This problem is probably related with blocking session and database lock.<\/p>\n","protected":false},"author":1,"featured_media":6106,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3828,2227,3827],"tags":[2420,1028,1031,1600,1610,2231,2417,2418,2422,2421,2243,2238,2419,2237,2240,2239,2241,2242,2230,2229],"class_list":["post-6490","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-scripts","category-sql-server-2","category-sql-server-dba-scripts","tag-blocking-query-sql-server","tag-mehmet-deveci","tag-mehmet-salih-deveci","tag-sql-server-2012","tag-sql-server-2014","tag-sql-server-2019","tag-sql-server-blocking-query","tag-sql-server-blocking-query-find","tag-sql-server-blocking-session","tag-sql-server-lock","tag-sql-server-performance-scripts","tag-sql-server-performance-top-cpu-query","tag-sql-server-search-blocking-query","tag-sql-server-select-inventory","tag-sql-server-top-cpu","tag-sql-server-top-cpu-query","tag-sql-server-top-queries","tag-sql-server-top-query","tag-sql-server-useful-query","tag-sql-server-useful-script"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/doag-oracle-database-locking-mechanism-demystified-44-638.jpg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6490","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=6490"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6490\/revisions"}],"predecessor-version":[{"id":12952,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6490\/revisions\/12952"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/6106"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6490"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6490"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6490"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}