{"id":455,"date":"2025-07-08T08:11:32","date_gmt":"2025-07-07T23:11:32","guid":{"rendered":"https:\/\/sejiwon.com\/?p=455"},"modified":"2025-07-09T08:33:19","modified_gmt":"2025-07-08T23:33:19","slug":"mssql-microsoft-sql-server%ec%97%90%ec%84%9c-oracle%ec%9d%98-vsql-%eb%b7%b0%ec%99%80-%ec%9c%a0%ec%82%ac","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=455","title":{"rendered":"MSSQL (Microsoft SQL Server)\uc5d0\uc11c Oracle\uc758 v$sql \ubdf0\uc640 \uc720\uc0ac"},"content":{"rendered":"\n<p>MSSQL (Microsoft SQL Server)\uc5d0\uc11c Oracle\uc758 <code>v$sql<\/code> \ubdf0\uc640 \uc720\uc0ac\ud558\uac8c <strong>\uc2e4\ud589 \uc911\uc774\uac70\ub098 \uce90\uc2dc\ub41c SQL \ubb38\uc7a5\uc5d0 \ub300\ud55c \uc815\ubcf4<\/strong>\ub97c \uc81c\uacf5\ud558\ub294 \uc2dc\uc2a4\ud15c \ubdf0\ub098 DMV(Dynamic Management View)\ub294 \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<h2 class=\"wp-block-heading\">\u2705 Oracle\uc758 <code>v$sql<\/code>\uc774\ub780?<\/h2>\n\n\n\n<p>Oracle\uc5d0\uc11c <code>v$sql<\/code>\uc740 <strong>\ub77c\uc774\ube0c SQL \ubb38, \uce90\uc2dc\ub41c SQL \uc2e4\ud589 \uacc4\ud68d, \uc2e4\ud589 \ud1b5\uacc4 \ub4f1<\/strong>\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub294 \ubdf0\uc785\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 SQL Server\uc5d0\uc11c \uc720\uc0ac\ud55c \uc2dc\uc2a4\ud15c \ubdf0<\/h2>\n\n\n\n<p>SQL Server\uc5d0\uc11c\ub294 \ub2e4\uc74c DMV\ub4e4\uc744 \uc870\ud569\ud574\uc11c \uc0ac\uc6a9\ud569\ub2c8\ub2e4:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">1. <code>sys.dm_exec_query_stats<\/code><\/h3>\n\n\n\n<ul>\n<li>\uc2e4\ud589\ub41c \ucffc\ub9ac\ub4e4\uc758 \uc2e4\ud589 \ud1b5\uacc4 \uc815\ubcf4\ub97c \ub2f4\uace0 \uc788\uc74c<\/li>\n\n\n\n<li>\uc2e4\ud589 \ud69f\uc218, CPU \uc2dc\uac04, \ub17c\ub9ac\uc801 \uc77d\uae30, \uc2e4\ud589 \uc2dc\uac04 \ub4f1<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">2. <code>sys.dm_exec_sql_text(sql_handle)<\/code><\/h3>\n\n\n\n<ul>\n<li>SQL \ud14d\uc2a4\ud2b8\ub97c \ubc18\ud658<\/li>\n\n\n\n<li><code>sql_handle<\/code>\uc744 \uc774\uc6a9\ud558\uc5ec <code>dm_exec_query_stats<\/code>\uc640 \uc870\uc778<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">3. <code>sys.dm_exec_query_plan(plan_handle)<\/code><\/h3>\n\n\n\n<ul>\n<li>\uc2e4\ud589 \uacc4\ud68d XML\uc744 \ubc18\ud658<\/li>\n\n\n\n<li><code>plan_handle<\/code>\uc744 \uc774\uc6a9<\/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 \uc608\uc81c \ucffc\ub9ac<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT \n    qs.execution_count,\n    qs.total_worker_time AS total_cpu_time,\n    qs.total_elapsed_time,\n    qs.total_logical_reads,\n    qs.creation_time,\n    st.text AS sql_text,\n    qp.query_plan\nFROM \n    sys.dm_exec_query_stats qs\nCROSS APPLY \n    sys.dm_exec_sql_text(qs.sql_handle) st\nCROSS APPLY \n    sys.dm_exec_query_plan(qs.plan_handle) qp\nORDER BY \n    qs.total_worker_time DESC;\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\uc774 \ucffc\ub9ac\ub294 \uce90\uc2dc\uc5d0 \uc800\uc7a5\ub41c \ucffc\ub9ac\ub4e4 \uc911 CPU \uc0ac\uc6a9\ub7c9\uc774 \ub192\uc740 \uc21c\uc11c\ub85c SQL \ubb38\uacfc \uc2e4\ud589 \uacc4\ud68d\uc744 \ubcf4\uc5ec\uc90d\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 \uad00\ub828 DMV \uc694\uc57d<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>DMV \uc774\ub984<\/th><th>\uc124\uba85<\/th><\/tr><\/thead><tbody><tr><td><code>sys.dm_exec_query_stats<\/code><\/td><td>\uc2e4\ud589 \ud1b5\uacc4<\/td><\/tr><tr><td><code>sys.dm_exec_sql_text<\/code><\/td><td>SQL \ud14d\uc2a4\ud2b8<\/td><\/tr><tr><td><code>sys.dm_exec_query_plan<\/code><\/td><td>\uc2e4\ud589 \uacc4\ud68d (XML \ud615\ud0dc)<\/td><\/tr><tr><td><code>sys.dm_exec_requests<\/code><\/td><td>\ud604\uc7ac \uc2e4\ud589 \uc911\uc778 \uc694\uccad \uc815\ubcf4<\/td><\/tr><tr><td><code>sys.dm_exec_sessions<\/code><\/td><td>\uc138\uc158\ubcc4 \uc815\ubcf4<\/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 \ud604\uc7ac \uc2e4\ud589 \uc911\uc778 SQL \ud655\uc778 \uc608\uc2dc<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT \n    r.session_id,\n    r.status,\n    r.start_time,\n    st.text AS sql_text,\n    r.cpu_time,\n    r.total_elapsed_time\nFROM \n    sys.dm_exec_requests r\nCROSS APPLY \n    sys.dm_exec_sql_text(r.sql_handle) st\nORDER BY \n    r.cpu_time DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>\ud544\uc694 \uc2dc, SSMS\uc5d0\uc11c &#8220;Activity Monitor&#8221;\ub97c \uc2dc\uac01\uc801\uc73c\ub85c \uc774\uc6a9\ud574\ub3c4 \uc77c\ubd80 \uc720\uc0ac \uc815\ubcf4\ub97c \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n<p>\uc6d0\ud558\uc2dc\ub294 \ub370\uc774\ud130\ub098 \ubdf0\uac00 \ud2b9\uc815 \uc0c1\ud669\uc5d0 \uad6d\ud55c\ub418\uc5c8\ub2e4\uba74 \uc54c\ub824\uc8fc\uc138\uc694 \u2014 \uc608: \ucffc\ub9ac \uc131\ub2a5 \ubd84\uc11d, \ud604\uc7ac \uc2e4\ud589 \uc911\uc778 \uac83\ub9cc, \uce90\uc2dc\ub41c \uc804\uccb4 \ub4f1.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\">\ub098\uc758 \ub9d0:<\/h5>\n\n\n\n<p>\ub514\uc2a4\ud06ci\/o\uccb4\ud06c\ubc29\ubc95<\/p>\n\n\n\n<h6 class=\"wp-block-heading\">ChatGPT\uc758 \ub9d0:<\/h6>\n\n\n\n<p>SQL Server\uc5d0\uc11c <strong>\ub514\uc2a4\ud06c I\/O<\/strong> \uc131\ub2a5\uc744 \ud655\uc778\ud558\ub294 \ubc29\ubc95\uc740 \uc5ec\ub7ec \uac00\uc9c0\uac00 \uc788\uc73c\uba70, \uc0c1\ud669\uc5d0 \ub530\ub77c <strong>SQL Server DMV, PerfMon(\uc131\ub2a5 \ubaa8\ub2c8\ud130), DMF, \uc2dc\uc2a4\ud15c \ubdf0 \ub4f1<\/strong>\uc744 \ud65c\uc6a9\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uc544\ub798\ub294 \ub300\ud45c\uc801\uc778 \ubc29\ubc95\ub4e4\uc785\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 1. DMV\ub85c \ub370\uc774\ud130\ubca0\uc774\uc2a4\ubcc4 I\/O \ud1b5\uacc4 \ud655\uc778<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><code>sys.dm_io_virtual_file_stats<\/code><\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT \n    DB_NAME(vfs.database_id) AS database_name,\n    mf.physical_name,\n    vfs.num_of_reads,\n    vfs.num_of_writes,\n    vfs.io_stall_read_ms,\n    vfs.io_stall_write_ms,\n    vfs.size_on_disk_bytes \/ 1024 \/ 1024 AS size_mb\nFROM \n    sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs\nJOIN \n    sys.master_files AS mf\n    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id\nORDER BY \n    vfs.io_stall_read_ms + vfs.io_stall_write_ms DESC;\n<\/code><\/pre>\n\n\n\n<p>\ud83d\udd0e <strong>\uc124\uba85<\/strong>:<\/p>\n\n\n\n<ul>\n<li><code>num_of_reads\/writes<\/code>: \uc77d\uae30\/\uc4f0\uae30 \ud69f\uc218<\/li>\n\n\n\n<li><code>io_stall_read_ms\/write_ms<\/code>: \uc77d\uae30\/\uc4f0\uae30 \ub300\uae30 \uc2dc\uac04 (\ubc00\ub9ac\ucd08)<\/li>\n\n\n\n<li><strong>I\/O \ubcd1\ubaa9\uc774 \uc788\ub294 \ud30c\uc77c \uc2dd\ubcc4\uc5d0 \uc720\uc6a9<\/strong><\/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 2. \ud604\uc7ac I\/O\uac00 \ub9ce\uc740 \ucffc\ub9ac \ud655\uc778<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT \n    r.session_id,\n    r.status,\n    r.command,\n    r.cpu_time,\n    r.total_elapsed_time,\n    r.reads,\n    r.writes,\n    st.text AS sql_text\nFROM \n    sys.dm_exec_requests r\nJOIN \n    sys.dm_exec_sessions s ON r.session_id = s.session_id\nCROSS APPLY \n    sys.dm_exec_sql_text(r.sql_handle) st\nORDER BY \n    r.reads + r.writes DESC;\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 3. PerfMon (\uc131\ub2a5 \ubaa8\ub2c8\ud130)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Windows Performance Monitor\ub97c \ud1b5\ud574 \uccb4\ud06c\ud560 \uc218 \uc788\ub294 \uc8fc\uc694 I\/O \uad00\ub828 \uce74\uc6b4\ud130:<\/h3>\n\n\n\n<ul>\n<li><strong>PhysicalDisk\\Avg. Disk sec\/Read<\/strong><\/li>\n\n\n\n<li><strong>PhysicalDisk\\Avg. Disk sec\/Write<\/strong><\/li>\n\n\n\n<li><strong>PhysicalDisk\\Disk Reads\/sec<\/strong><\/li>\n\n\n\n<li><strong>PhysicalDisk\\Disk Writes\/sec<\/strong><\/li>\n\n\n\n<li><strong>LogicalDisk% Disk Time<\/strong><\/li>\n\n\n\n<li><strong>SQLServer:Buffer Manager\\Page reads\/sec<\/strong><\/li>\n\n\n\n<li><strong>SQLServer:Buffer Manager\\Page writes\/sec<\/strong><\/li>\n<\/ul>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u26a0\ufe0f \ucc38\uace0: <code>Avg. Disk sec\/Read<\/code> \ub610\ub294 <code>Write<\/code>\uac00 <strong>20ms(0.02\ucd08)<\/strong> \uc774\uc0c1\uc774\uba74 \ub290\ub9b0 \ub514\uc2a4\ud06c\ub85c \uac04\uc8fc<\/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 4. \ucffc\ub9ac\ubcc4 \ub17c\ub9ac\/\ubb3c\ub9ac \uc77d\uae30 \ud655\uc778 (\uc2e4\ud589\uacc4\ud68d \ud3ec\ud568)<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SET STATISTICS IO ON;\nSET STATISTICS TIME ON;\n\n-- \uc5ec\uae30\uc5d0 \ubd84\uc11d\ud560 \ucffc\ub9ac \uc791\uc131\nSELECT * FROM YourTable WHERE YourCondition;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><strong>STATISTICS IO<\/strong>: \ub17c\ub9ac\/\ubb3c\ub9ac \uc77d\uae30 \uc218<\/li>\n\n\n\n<li><strong>STATISTICS TIME<\/strong>: CPU \uc2dc\uac04\uacfc \uc804\uccb4 \uc2e4\ud589 \uc2dc\uac04<\/li>\n<\/ul>\n\n\n\n<p>SSMS\uc5d0\uc11c \uc2e4\ud589\ud558\uba74 \uba54\uc2dc\uc9c0 \ud0ed\uc5d0 \uacb0\uacfc \ud45c\uc2dc\ub428.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 5. TempDB I\/O \ud655\uc778<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SELECT \n    vfs.database_id,\n    DB_NAME(vfs.database_id) AS database_name,\n    mf.physical_name,\n    vfs.io_stall_read_ms,\n    vfs.io_stall_write_ms,\n    vfs.num_of_reads,\n    vfs.num_of_writes\nFROM \n    sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs\nJOIN \n    sys.master_files AS mf\n    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id\nWHERE \n    mf.database_id = 2 -- TempDB\nORDER BY \n    vfs.io_stall_write_ms DESC;\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 \uacb0\ub860<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>\ubaa9\uc801<\/th><th>\uc0ac\uc6a9 \ub3c4\uad6c<\/th><th>\uc124\uba85<\/th><\/tr><\/thead><tbody><tr><td>\uc804\uccb4 \ub514\uc2a4\ud06c I\/O \ud655\uc778<\/td><td><code>sys.dm_io_virtual_file_stats<\/code><\/td><td>DB\/\ud30c\uc77c\ubcc4 \uc77d\uae30\/\uc4f0\uae30\/\ub300\uae30 \uc2dc\uac04<\/td><\/tr><tr><td>\ucffc\ub9ac\ubcc4 I\/O<\/td><td><code>sys.dm_exec_requests<\/code> + <code>dm_exec_sql_text<\/code><\/td><td>\ud604\uc7ac \uc2e4\ud589 \uc911 \ucffc\ub9ac \uae30\uc900 I\/O<\/td><\/tr><tr><td>\uc2e4\uc2dc\uac04 OS \ub808\ubca8 I\/O<\/td><td>Performance Monitor<\/td><td>\ub514\uc2a4\ud06c \uc9c0\uc5f0, \uc77d\uae30\/\uc4f0\uae30 \uc18d\ub3c4 \ub4f1<\/td><\/tr><tr><td>\ud2b9\uc815 \ucffc\ub9ac \uc131\ub2a5 \ud655\uc778<\/td><td><code>SET STATISTICS IO\/TIME<\/code><\/td><td>SQL \ub0b4\ubd80 I\/O \uce21\uc815<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>MSSQL (Microsoft SQL Server)\uc5d0\uc11c Oracle\uc758 v$sql \ubdf0\uc640 \uc720\uc0ac\ud558\uac8c \uc2e4\ud589 \uc911\uc774\uac70\ub098 \uce90\uc2dc\ub41c SQL \ubb38\uc7a5\uc5d0 \ub300\ud55c \uc815\ubcf4\ub97c \uc81c\uacf5\ud558\ub294 \uc2dc\uc2a4\ud15c \ubdf0\ub098 DMV(Dynamic Management View)\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4: \u2705 Oracle\uc758 v$sql\uc774\ub780? Oracle\uc5d0\uc11c v$sql\uc740 \ub77c\uc774\ube0c SQL \ubb38, \uce90\uc2dc\ub41c SQL \uc2e4\ud589 \uacc4\ud68d, \uc2e4\ud589 \ud1b5\uacc4 \ub4f1\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub294 \ubdf0\uc785\ub2c8\ub2e4. \u2705 SQL Server\uc5d0\uc11c \uc720\uc0ac\ud55c \uc2dc\uc2a4\ud15c \ubdf0 SQL Server\uc5d0\uc11c\ub294 \ub2e4\uc74c DMV\ub4e4\uc744 \uc870\ud569\ud574\uc11c \uc0ac\uc6a9\ud569\ub2c8\ub2e4: 1.\u2026 <span class=\"read-more\"><a href=\"https:\/\/sejiwon.com\/?p=455\">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\/455"}],"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=455"}],"version-history":[{"count":2,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/455\/revisions"}],"predecessor-version":[{"id":480,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/455\/revisions\/480"}],"wp:attachment":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=455"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=455"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=455"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}