{"id":462,"date":"2025-07-08T08:21:52","date_gmt":"2025-07-07T23:21:52","guid":{"rendered":"https:\/\/sejiwon.com\/?p=462"},"modified":"2025-07-09T08:31:28","modified_gmt":"2025-07-08T23:31:28","slug":"elapsetime%ea%b3%bc-disk-i-o%eb%a5%bc-%ed%95%9c%eb%b2%88%ec%97%90-%ed%99%95%ec%9d%b8%ed%95%98%eb%8a%94-%ec%bf%bc%eb%a6%ac","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=462","title":{"rendered":"elapsetime\uacfc disk i\/o\ub97c \ud55c\ubc88\uc5d0 \ud655\uc778\ud558\ub294 \ucffc\ub9ac"},"content":{"rendered":"\n<p>SQL Server\uc5d0\uc11c <strong>SQL \ubb38\uc7a5 \ub2e8\uc704\ub85c <code>Elapsed Time<\/code>(\uc804\uccb4 \uc18c\uc694 \uc2dc\uac04)\uacfc <code>Disk I\/O<\/code>(Physical Reads)\ub97c \ud568\uaed8 \ud655\uc778\ud558\ub294 \ucffc\ub9ac<\/strong>\ub294 \ub2e4\uc74c\uacfc \uac19\uc774 \uad6c\uc131\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\uc801: SQL \ubcc4 \uc18c\uc694 \uc2dc\uac04 + \ub514\uc2a4\ud06c I\/O \ud55c\ub208\uc5d0 \ubcf4\uae30<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT \n    TOP 50\n    st.text AS sql_text,\n    qs.execution_count,\n    qs.total_elapsed_time \/ 1000 AS total_elapsed_ms,\n    (qs.total_elapsed_time \/ qs.execution_count) \/ 1000 AS avg_elapsed_ms,\n    qs.total_worker_time \/ 1000 AS total_cpu_ms,\n    qs.total_logical_reads AS logical_reads,\n    qs.total_physical_reads AS physical_reads,\n    qs.total_logical_writes AS logical_writes,\n    qs.creation_time,\n    qs.last_execution_time\nFROM \n    sys.dm_exec_query_stats AS qs\nCROSS APPLY \n    sys.dm_exec_sql_text(qs.sql_handle) AS st\nORDER BY \n    qs.total_physical_reads DESC; -- \ub610\ub294 avg_elapsed_ms DESC\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 \uc124\uba85<\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>\uceec\ub7fc<\/th><th>\uc124\uba85<\/th><\/tr><\/thead><tbody><tr><td><code>sql_text<\/code><\/td><td>SQL \ubb38\uc7a5 (\ucd5c\ub300 4KB)<\/td><\/tr><tr><td><code>execution_count<\/code><\/td><td>\uc2e4\ud589 \ud69f\uc218<\/td><\/tr><tr><td><code>total_elapsed_ms<\/code><\/td><td>\ucd1d \uc2e4\ud589 \uc2dc\uac04 (ms)<\/td><\/tr><tr><td><code>avg_elapsed_ms<\/code><\/td><td>\ud3c9\uade0 \uc2e4\ud589 \uc2dc\uac04<\/td><\/tr><tr><td><code>total_cpu_ms<\/code><\/td><td>\ucd1d CPU \uc0ac\uc6a9 \uc2dc\uac04 (ms)<\/td><\/tr><tr><td><code>physical_reads<\/code><\/td><td>\ub514\uc2a4\ud06c\uc5d0\uc11c \uc77d\uc740 \ud69f\uc218 (Disk I\/O)<\/td><\/tr><tr><td><code>logical_reads<\/code><\/td><td>\uba54\ubaa8\ub9ac\uc5d0\uc11c \uc77d\uc740 \ud69f\uc218<\/td><\/tr><tr><td><code>logical_writes<\/code><\/td><td>\uba54\ubaa8\ub9ac \u2192 \ub514\uc2a4\ud06c\ub85c \uc4f4 \ud69f\uc218<\/td><\/tr><tr><td><code>creation_time<\/code><\/td><td>\ucc98\uc74c \uc2e4\ud589\ub41c \uc2dc\uac04<\/td><\/tr><tr><td><code>last_execution_time<\/code><\/td><td>\ub9c8\uc9c0\ub9c9\uc73c\ub85c \uc2e4\ud589\ub41c \uc2dc\uac04<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">\u2705 \ud301: \uc2e4\ud589 \uc2dc\uac04 \uae30\uc900 \uc815\ub82c \ubcc0\uacbd<\/h3>\n\n\n\n<ul>\n<li>\ub514\uc2a4\ud06c I\/O\uac00 \ub192\uc740 \ucffc\ub9ac: <code>ORDER BY qs.total_physical_reads DESC<\/code><\/li>\n\n\n\n<li>\uc2e4\ud589 \uc2dc\uac04\uc774 \uc624\ub798 \uac78\ub9ac\ub294 \ucffc\ub9ac: <code>ORDER BY avg_elapsed_ms DESC<\/code><\/li>\n\n\n\n<li>CPU\ub97c \ub9ce\uc774 \uc4f0\ub294 \ucffc\ub9ac: <code>ORDER BY total_cpu_ms DESC<\/code><\/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 \ud2b9\uc815 SQL \ucd94\uc801 (\uc6d0\ud558\uba74 \ucd94\uac00)<\/h3>\n\n\n\n<p><code>query_hash<\/code>, <code>sql_handle<\/code>, \ub610\ub294 SQL \ud14d\uc2a4\ud2b8 \uae30\uc900\uc73c\ub85c \ud2b9\uc815 \ucffc\ub9ac\ub9cc \ucd94\uc801\ud558\uace0 \uc2f6\ub2e4\uba74 WHERE \uc870\uac74\uc744 \ucd94\uac00\ud558\uc2dc\uba74 \ub429\ub2c8\ub2e4.<\/p>\n\n\n\n<p>\uc608:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>WHERE st.text LIKE '%your_table_name%'\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server\uc5d0\uc11c SQL \ubb38\uc7a5 \ub2e8\uc704\ub85c Elapsed Time(\uc804\uccb4 \uc18c\uc694 \uc2dc\uac04)\uacfc Disk I\/O(Physical Reads)\ub97c \ud568\uaed8 \ud655\uc778\ud558\ub294 \ucffc\ub9ac\ub294 \ub2e4\uc74c\uacfc \uac19\uc774 \uad6c\uc131\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \u2705 \ubaa9\uc801: SQL \ubcc4 \uc18c\uc694 \uc2dc\uac04 + \ub514\uc2a4\ud06c I\/O \ud55c\ub208\uc5d0 \ubcf4\uae30 SELECT TOP 50 st.text AS sql_text, qs.execution_count, qs.total_elapsed_time \/ 1000 AS total_elapsed_ms, (qs.total_elapsed_time \/ qs.execution_count) \/ 1000 AS avg_elapsed_ms, qs.total_worker_time \/ 1000 AS total_cpu_ms,\u2026 <span class=\"read-more\"><a href=\"https:\/\/sejiwon.com\/?p=462\">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\/462"}],"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=462"}],"version-history":[{"count":2,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/462\/revisions"}],"predecessor-version":[{"id":478,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/462\/revisions\/478"}],"wp:attachment":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=462"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=462"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=462"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}