{"id":510,"date":"2025-07-24T17:08:32","date_gmt":"2025-07-24T08:08:32","guid":{"rendered":"https:\/\/sejiwon.com\/?p=510"},"modified":"2025-07-25T09:39:10","modified_gmt":"2025-07-25T00:39:10","slug":"mssql-%eb%8d%b0%eb%93%9c%eb%9d%bd-%eb%b0%9c%ec%83%9d%ec%8b%9c-%eb%9d%bd%eb%b0%9c%ec%83%9d%ec%8b%9c%ed%82%a8-%ec%bf%bc%eb%a6%ac-%ec%b0%be%eb%8a%94-%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=510","title":{"rendered":"Mssql \ub370\ub4dc\ub77d \ubc1c\uc0dd\uc2dc \ub77d\ubc1c\uc0dd\uc2dc\ud0a8 \ucffc\ub9ac \ucc3e\ub294 \ubc29\ubc95"},"content":{"rendered":"\n<p>MSSQL\uc5d0\uc11c&nbsp;<strong>\ub370\ub4dc\ub77d(deadlock)<\/strong>&nbsp;\uc774 \ubc1c\uc0dd\ud588\uc744 \ub54c, \uc5b4\ub5a4 \ucffc\ub9ac\ub098 \ud504\ub85c\uc138\uc2a4\uac00 \ub370\ub4dc\ub77d\uc744 \uc720\ubc1c\ud588\ub294\uc9c0 \ucc3e\ub294 \ubc29\ubc95\uc740 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\ud83d\udd0d 1.&nbsp;<strong>SQL Server \ub370\ub4dc\ub77d \ud655\uc778 \ubc29\ubc95 \uc694\uc57d<\/strong><\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">\u2705 \ubc29\ubc95 1: SQL Server \uc5d0\uc774\uc804\ud2b8 \ub85c\uadf8 \ub610\ub294 \uc2dc\uc2a4\ud15c \ud5ec\uc2a4 \uc138\uc158(XE)<\/h4>\n\n\n\n<p>SQL Server 2008 \uc774\uc0c1\uc5d0\uc11c\ub294&nbsp;<strong>\uc2dc\uc2a4\ud15c \ud5ec\uc2a4 \uc138\uc158 (system_health extended event)<\/strong>&nbsp;\uc774 \uae30\ubcf8\uc801\uc73c\ub85c \ub370\ub4dc\ub77d \uc815\ubcf4\ub97c \uae30\ub85d\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">\ud83d\udca1 \ub370\ub4dc\ub77d XML \uc870\ud68c (\uac00\uc7a5 \ud754\ud55c \ubc29\ubc95)<\/h5>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>-- \ucd5c\uadfc \ub370\ub4dc\ub77d \uc774\ubca4\ud2b8 \ucd94\ucd9c\nSELECT \n    XEvent.query('(event\/data\/value\/deadlock)[1]') AS DeadlockGraph\nFROM \n    (SELECT XEvent.query('.') AS XEvent\n     FROM \n        (SELECT CAST(target_data AS XML) AS TargetData\n         FROM sys.dm_xe_session_targets st\n         JOIN sys.dm_xe_sessions s \n         ON s.address = st.event_session_address\n         WHERE s.name = 'system_health'\n           AND st.target_name = 'ring_buffer') AS Data\n     CROSS APPLY TargetData.nodes('\/\/RingBufferTarget\/event[@name=\"xml_deadlock_report\"]') AS XEventData(XEvent)\n    ) AS Deadlocks;\n<\/code><\/pre>\n\n\n\n<p>\uc774 \ucffc\ub9ac \uacb0\uacfc\ub85c \ub098\uc624\ub294&nbsp;<code>DeadlockGraph<\/code>&nbsp;\ub294 XML \ud615\uc2dd\uc758 \ub370\ub4dc\ub77d \uadf8\ub798\ud504\ub85c, SSMS\uc5d0\uc11c \ud074\ub9ad\ud558\uba74 \uc2dc\uac01\ud654\ub41c \ud615\ud0dc\ub85c \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u2705 \ubc29\ubc95 2: \ub370\ub4dc\ub77d \uc774\ubca4\ud2b8\uc5d0 \ub300\ud574 Extended Events \uc138\uc158 \uc9c1\uc811 \uc124\uc815<\/h4>\n\n\n\n<p>\ub354 \uad6c\uccb4\uc801\uc73c\ub85c \ucd94\uc801\ud558\uace0 \uc2f6\ub2e4\uba74, \uc0ac\uc6a9\uc790 \uc9c0\uc815 Extended Events \uc138\uc158\uc744 \ub9cc\ub4e4\uc5b4 \ubaa8\ub2c8\ud130\ub9c1 \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\ud83d\udd0e 2. \ub370\ub4dc\ub77d \ucffc\ub9ac XML \ubd84\uc11d \ubc29\ubc95<\/h3>\n\n\n\n<p><code>DeadlockGraph<\/code>\uc758 XML\uc744 \uc5f4\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uc815\ubcf4\uac00 \ub4e4\uc5b4 \uc788\uc2b5\ub2c8\ub2e4:<\/p>\n\n\n\n<ul>\n<li><code>process<\/code>&nbsp;\ud0dc\uadf8: \ud574\ub2f9 \uc138\uc158\uc758 \uc2e4\ud589 \ucffc\ub9ac (<code>inputbuf<\/code>)<\/li>\n\n\n\n<li><code>owner<\/code>\/<code>waiter<\/code>: \uc5b4\ub5a4 \ub9ac\uc18c\uc2a4\ub97c \uac00\uc9c0\uace0 \uc788\uace0, \uc5b4\ub5a4 \ub9ac\uc18c\uc2a4\ub97c \uae30\ub2e4\ub9ac\ub294\uc9c0<\/li>\n\n\n\n<li><code>resource<\/code>: \ub77d\uc774 \uac78\ub9b0 \ud14c\uc774\ube14\/\uc778\ub371\uc2a4\/\ud0a4 \ub4f1<\/li>\n\n\n\n<li><code>victim<\/code>: \ub370\ub4dc\ub77d \ud76c\uc0dd\uc790 (\uc790\ub3d9\uc73c\ub85c \ub864\ubc31\ub428)<\/li>\n<\/ul>\n\n\n\n<p>\uc608\uc2dc:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>&lt;process id=\"process123\" ...>\n    &lt;inputbuf>\n        UPDATE Orders SET status = 'Completed' WHERE OrderID = 123\n    &lt;\/inputbuf>\n&lt;\/process>\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\u2705 \ubc29\ubc95 3: SQL Server Error Log \ud655\uc778<\/h3>\n\n\n\n<p>\ub370\ub4dc\ub77d \ubc1c\uc0dd \uc2dc SQL Server\ub294&nbsp;<strong>\uc624\ub958 \ub85c\uadf8\uc5d0 \ub370\ub4dc\ub77d \uc694\uc57d<\/strong>\uc744 \ub0a8\uae41\ub2c8\ub2e4.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>EXEC xp_readerrorlog 0, 1, N'deadlock';\n<\/code><\/pre>\n\n\n\n<p>\ub2e4\ub9cc \uc774 \ub85c\uadf8\ub294 \uac04\ub7b5\ud558\uba70, \uc704\uc758 Extended Event\uc5d0 \ube44\ud574 \uc815\ubcf4\uac00 \ubd80\uc871\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\u2705 \ubc29\ubc95 4: Trace Flag 1222 \/ 1204<\/h3>\n\n\n\n<p>SQL Server\uc5d0 Trace Flag\ub97c \ucf1c\uba74 \ub370\ub4dc\ub77d \uc815\ubcf4\ub97c \ub85c\uadf8\ub85c \ub0a8\uae41\ub2c8\ub2e4.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>-- \uc11c\ubc84 \uc2dc\uc791 \uc2dc \uc801\uc6a9\ub418\ub3c4\ub85d \ud558\ub824\uba74 \uad6c\uc131 \uc635\uc158 \uc0ac\uc6a9\nDBCC TRACEON (1222, -1);\n<\/code><\/pre>\n\n\n\n<ul>\n<li>1222: XML \ud615\uc2dd \ub85c\uadf8<\/li>\n\n\n\n<li>1204: \ud2b8\ub9ac\ud615 \ub85c\uadf8<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\u2705 \ubc29\ubc95 5: SSMS Activity Monitor \ub610\ub294 DMVs<\/h3>\n\n\n\n<p><code>sys.dm_tran_locks<\/code>,&nbsp;<code>sys.dm_exec_requests<\/code>,&nbsp;<code>sys.dm_os_waiting_tasks<\/code>&nbsp;\ub4f1\uc744 \uc870\ud569\ud558\uc5ec \ud604\uc7ac \ub77d \ubcf4\uc720\/\ub300\uae30 \uc0c1\ud0dc\ub97c \ucd94\uc801\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT \n    r.session_id,\n    r.status,\n    r.blocking_session_id,\n    r.wait_type,\n    t.text AS query_text\nFROM sys.dm_exec_requests r\nJOIN sys.dm_exec_sessions s ON r.session_id = s.session_id\nCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t\nWHERE r.blocking_session_id &lt;> 0;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 \uc815\ub9ac<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>\ubc29\ubc95<\/th><th>\uc124\uba85<\/th><th>\uad8c\uc7a5 \uc5ec\ubd80<\/th><\/tr><\/thead><tbody><tr><td>system_health XE<\/td><td>\uae30\ubcf8 \uc124\uc815\ub418\uc5b4 \uc788\uc74c, XML \uadf8\ub798\ud504 \ud655\uc778 \uac00\ub2a5<\/td><td>\u2b50 \ub9e4\uc6b0 \uad8c\uc7a5<\/td><\/tr><tr><td>Extended Event \uc9c1\uc811 \uc0dd\uc131<\/td><td>\ub354 \uc0c1\uc138 \ucd94\uc801 \uac00\ub2a5<\/td><td>\uace0\uae09<\/td><\/tr><tr><td>Error Log<\/td><td>\uac04\ub7b5\ud55c \ud655\uc778\uc6a9<\/td><td>\ubcf4\uc870<\/td><\/tr><tr><td>Trace Flag (1222\/1204)<\/td><td>\ub85c\uadf8\uc5d0 \uc790\uc138\ud55c \ub0b4\uc6a9 \uae30\ub85d<\/td><td>\ubcf4\uc870<\/td><\/tr><tr><td>DMV \uc870\ud569<\/td><td>\uc2e4\uc2dc\uac04 \ubd84\uc11d \uac00\ub2a5<\/td><td>\ubcd1\ud589 \ud65c\uc6a9<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n","protected":false},"excerpt":{"rendered":"<p>MSSQL\uc5d0\uc11c&nbsp;\ub370\ub4dc\ub77d(deadlock)&nbsp;\uc774 \ubc1c\uc0dd\ud588\uc744 \ub54c, \uc5b4\ub5a4 \ucffc\ub9ac\ub098 \ud504\ub85c\uc138\uc2a4\uac00 \ub370\ub4dc\ub77d\uc744 \uc720\ubc1c\ud588\ub294\uc9c0 \ucc3e\ub294 \ubc29\ubc95\uc740 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4. \ud83d\udd0d 1.&nbsp;SQL Server \ub370\ub4dc\ub77d \ud655\uc778 \ubc29\ubc95 \uc694\uc57d \u2705 \ubc29\ubc95 1: SQL Server \uc5d0\uc774\uc804\ud2b8 \ub85c\uadf8 \ub610\ub294 \uc2dc\uc2a4\ud15c \ud5ec\uc2a4 \uc138\uc158(XE) SQL Server 2008 \uc774\uc0c1\uc5d0\uc11c\ub294&nbsp;\uc2dc\uc2a4\ud15c \ud5ec\uc2a4 \uc138\uc158 (system_health extended event)&nbsp;\uc774 \uae30\ubcf8\uc801\uc73c\ub85c \ub370\ub4dc\ub77d \uc815\ubcf4\ub97c \uae30\ub85d\ud569\ub2c8\ub2e4. \ud83d\udca1 \ub370\ub4dc\ub77d XML \uc870\ud68c (\uac00\uc7a5 \ud754\ud55c \ubc29\ubc95) &#8212; \ucd5c\uadfc \ub370\ub4dc\ub77d \uc774\ubca4\ud2b8 \ucd94\ucd9c\u2026 <span class=\"read-more\"><a href=\"https:\/\/sejiwon.com\/?p=510\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[11],"tags":[],"_links":{"self":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/510"}],"collection":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=510"}],"version-history":[{"count":2,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/510\/revisions"}],"predecessor-version":[{"id":512,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/510\/revisions\/512"}],"wp:attachment":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=510"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=510"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=510"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}