{"id":459,"date":"2025-07-08T08:17:44","date_gmt":"2025-07-07T23:17:44","guid":{"rendered":"https:\/\/sejiwon.com\/?p=459"},"modified":"2025-07-09T08:34:16","modified_gmt":"2025-07-08T23:34:16","slug":"disk-i-o%ec%99%80-logical-i-o%eb%a5%bc-sqlid%eb%a1%9c-%ed%99%95%ec%9d%b8%ed%95%98%eb%8a%94-mssql-%ec%bf%bc%eb%a6%ac","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=459","title":{"rendered":"disk i\/o\uc640 logical i\/o\ub97c sqlid\ub85c \ud655\uc778\ud558\ub294 mssql \ucffc\ub9ac"},"content":{"rendered":"\n<p>SQL Server\uc5d0\uc11c\ub294 Oracle\uc758 <code>SQL_ID<\/code>\ucc98\ub7fc <strong>\ud558\ub098\uc758 SQL \ubb38\uc7a5\uc744 \uc2dd\ubcc4<\/strong>\ud574\uc11c \ud574\ub2f9 \ucffc\ub9ac\uc758 <strong>Disk I\/O (\ubb3c\ub9ac\uc801 I\/O)<\/strong> \ubc0f <strong>Logical I\/O (\ub17c\ub9ac\uc801 I\/O)<\/strong> \uc815\ubcf4\ub97c \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n<p>SQL Server\uc5d0\ub294 <code>SQL_ID<\/code>\uac00 \uc5c6\uc9c0\ub9cc, \uc720\uc0ac\ud558\uac8c <code>sql_handle<\/code> \ub610\ub294 <code>query_hash<\/code>\ub97c \uc0ac\uc6a9\ud558\uba74 \ub3d9\uc77c\ud55c \uae30\ub2a5\uc744 \uad6c\ud604\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 \ubaa9\ud45c: &#8220;\ud558\ub098\uc758 SQL \ubb38\uc7a5&#8221;\ubcc4 Disk I\/O + Logical I\/O \ud655\uc778<\/h2>\n\n\n\n<p>SQL Server\uc5d0\uc11c\ub294 \ub2e4\uc74c DMV\ub4e4\uc744 \uc870\ud569\ud574 \uc0ac\uc6a9\ud569\ub2c8\ub2e4:<\/p>\n\n\n\n<ul>\n<li><code>sys.dm_exec_query_stats<\/code> : \ucffc\ub9ac \uc2e4\ud589 \ud1b5\uacc4<\/li>\n\n\n\n<li><code>sys.dm_exec_sql_text<\/code> : SQL \ud14d\uc2a4\ud2b8<\/li>\n\n\n\n<li><code>sys.dm_exec_query_plan<\/code> : \uc2e4\ud589 \uacc4\ud68d<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 \ucffc\ub9ac \uc608\uc81c: SQL \ubb38\uc7a5\ubcc4 Disk I\/O &amp; Logical I\/O \ubcf4\uae30<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT\n    qs.execution_count,\n    qs.total_logical_reads AS total_logical_io,\n    qs.total_physical_reads AS total_physical_io,  -- Disk I\/O\n    qs.total_logical_writes,\n    qs.total_elapsed_time \/ 1000 AS total_elapsed_ms,\n    qs.total_worker_time \/ 1000 AS total_cpu_ms,\n    st.text AS sql_text,\n    qs.query_hash,\n    qs.query_plan_hash\nFROM\n    sys.dm_exec_query_stats qs\nCROSS APPLY\n    sys.dm_exec_sql_text(qs.sql_handle) st\nWHERE\n    st.text LIKE '%&lt;\uc5ec\uae30\uc5d0 \ucc3e\uace0\uc790 \ud558\ub294 SQL \ud0a4\uc6cc\ub4dc \ub610\ub294 \ud14c\uc774\ube14\uba85 \ub4f1 \uc785\ub825&gt;%'\nORDER BY\n    qs.total_physical_reads DESC;\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\ud83d\udd0d \uc774 \ucffc\ub9ac\ub294 \uc2e4\ud589\ub41c SQL \ubb38\ub4e4 \uc911\uc5d0\uc11c \uac00\uc7a5 \ub9ce\uc740 \ub514\uc2a4\ud06c I\/O\ub97c \ubc1c\uc0dd\uc2dc\ud0a8 \ucffc\ub9ac\ub97c \ubcf4\uc5ec\uc90d\ub2c8\ub2e4. \uc6d0\ud558\ub294 SQL\uc744 \ucc3e\uc744 \ub54c\ub294 <code>LIKE<\/code> \uc870\uac74\uc744 \uc218\uc815\ud558\uac70\ub098 <code>query_hash<\/code>\ub85c \ud544\ud130\ub9c1\ud558\uba74 \ub429\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 \ud2b9\uc815 <code>query_hash<\/code>\ub85c \ucd94\uc801 (Oracle SQL_ID\ucc98\ub7fc)<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>-- \uc608\uc2dc: \ud2b9\uc815 query_hash\ub85c \uc870\ud68c\nDECLARE @query_hash BINARY(8) = 0x1234567890ABCDEF;  -- \uc2e4\uc81c \ud574\uc2dc \uac12 \uc785\ub825\n\nSELECT\n    qs.execution_count,\n    qs.total_logical_reads,\n    qs.total_physical_reads,\n    qs.total_logical_writes,\n    st.text AS sql_text,\n    qs.query_hash\nFROM\n    sys.dm_exec_query_stats qs\nCROSS APPLY\n    sys.dm_exec_sql_text(qs.sql_handle) st\nWHERE\n    qs.query_hash = @query_hash;\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 \ucffc\ub9ac \uc2e4\ud589 \uc2dc \uc2e4\uc2dc\uac04 I\/O \ud655\uc778 (STATISTICS IO)<\/h2>\n\n\n\n<p>\uc2e4\uc81c \ucffc\ub9ac \uc131\ub2a5\uc744 \ubcf4\ub824\uba74 SSMS\uc5d0\uc11c \uc9c1\uc811 \uc2e4\ud589\ud558\uba74\uc11c \uc544\ub798 \uc635\uc158\uc744 \uc124\uc815:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SET STATISTICS IO ON;\nSET STATISTICS TIME ON;\n\n-- \ubd84\uc11d\ud560 SQL \ubb38\nSELECT * FROM YourTable WHERE SomeColumn = 'X';\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\uacb0\uacfc\ub294 SSMS &#8220;Messages&#8221; \ud0ed\uc5d0 \ud45c\uc2dc\ub418\uba70, Logical\/Physical Reads \ub4f1\uc744 \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 \uc8fc\uc694 \uc9c0\ud45c \uc124\uba85<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>\ud56d\ubaa9<\/th><th>\uc124\uba85<\/th><\/tr><\/thead><tbody><tr><td><code>total_logical_reads<\/code><\/td><td>Buffer pool\uc5d0\uc11c \uc77d\uc740 \ud398\uc774\uc9c0 \uc218 (\ub17c\ub9ac I\/O)<\/td><\/tr><tr><td><code>total_physical_reads<\/code><\/td><td>\ub514\uc2a4\ud06c\uc5d0\uc11c \uc77d\uc740 \ud398\uc774\uc9c0 \uc218 (\ubb3c\ub9ac I\/O)<\/td><\/tr><tr><td><code>total_logical_writes<\/code><\/td><td>\ubc84\ud37c\uc5d0\uc11c \ub514\uc2a4\ud06c\ub85c \uc4f4 \ud398\uc774\uc9c0 \uc218<\/td><\/tr><tr><td><code>execution_count<\/code><\/td><td>\uc2e4\ud589 \ud69f\uc218<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 \uc694\uc57d<\/h2>\n\n\n\n<p>Oracle\uc758 <code>SQL_ID<\/code>\ub85c \ud655\uc778\ud558\ub358 \uac83\ucc98\ub7fc SQL Server\uc5d0\uc11c\ub294 \ub2e4\uc74c \ubc29\uc2dd\uc73c\ub85c \ud655\uc778\ud569\ub2c8\ub2e4:<\/p>\n\n\n\n<ul>\n<li><strong>\uc2dd\ubcc4\uc790<\/strong>: <code>sql_handle<\/code> or <code>query_hash<\/code><\/li>\n\n\n\n<li><strong>I\/O \uc9c0\ud45c<\/strong>: <code>total_logical_reads<\/code>, <code>total_physical_reads<\/code>, <code>total_logical_writes<\/code><\/li>\n\n\n\n<li><strong>\ub300\uc0c1 \ubdf0<\/strong>: <code>sys.dm_exec_query_stats<\/code>, <code>sys.dm_exec_sql_text<\/code><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server\uc5d0\uc11c\ub294 Oracle\uc758 SQL_ID\ucc98\ub7fc \ud558\ub098\uc758 SQL \ubb38\uc7a5\uc744 \uc2dd\ubcc4\ud574\uc11c \ud574\ub2f9 \ucffc\ub9ac\uc758 Disk I\/O (\ubb3c\ub9ac\uc801 I\/O) \ubc0f Logical I\/O (\ub17c\ub9ac\uc801 I\/O) \uc815\ubcf4\ub97c \ud655\uc778\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. SQL Server\uc5d0\ub294 SQL_ID\uac00 \uc5c6\uc9c0\ub9cc, \uc720\uc0ac\ud558\uac8c sql_handle \ub610\ub294 query_hash\ub97c \uc0ac\uc6a9\ud558\uba74 \ub3d9\uc77c\ud55c \uae30\ub2a5\uc744 \uad6c\ud604\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \u2705 \ubaa9\ud45c: &#8220;\ud558\ub098\uc758 SQL \ubb38\uc7a5&#8221;\ubcc4 Disk I\/O + Logical I\/O \ud655\uc778 SQL Server\uc5d0\uc11c\ub294 \ub2e4\uc74c DMV\ub4e4\uc744 \uc870\ud569\ud574 \uc0ac\uc6a9\ud569\ub2c8\ub2e4: \u2705\u2026 <span class=\"read-more\"><a href=\"https:\/\/sejiwon.com\/?p=459\">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\/459"}],"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=459"}],"version-history":[{"count":3,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/459\/revisions"}],"predecessor-version":[{"id":482,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/459\/revisions\/482"}],"wp:attachment":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=459"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=459"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=459"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}