{"id":471,"date":"2025-07-09T08:27:14","date_gmt":"2025-07-08T23:27:14","guid":{"rendered":"https:\/\/sejiwon.com\/?p=471"},"modified":"2025-07-09T08:29:44","modified_gmt":"2025-07-08T23:29:44","slug":"%ec%8b%a4%ec%8b%9c%ea%b0%84-%eb%8c%80%ea%b8%b0-%ec%9d%b4%eb%b2%a4%ed%8a%b8-%ed%8a%b9%ec%a0%95-%ec%84%b8%ec%85%98%ec%9d%98-i-o-%ec%a0%95%eb%b3%b4-%eb%b3%91%eb%aa%a9-%ed%98%84%ec%83%81-%eb%b6%84","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=471","title":{"rendered":"\uc2e4\uc2dc\uac04 \ub300\uae30 \uc774\ubca4\ud2b8, \ud2b9\uc815 \uc138\uc158\uc758 I\/O \uc815\ubcf4, \ubcd1\ubaa9 \ud604\uc0c1 \ubd84\uc11d \ubaa8\ub2c8\ud130\ub9c1 \ucffc\ub9ac"},"content":{"rendered":"\n

\u2705 1. \uc2e4\uc2dc\uac04 \uc138\uc158 \ub300\uae30 \ubc0f \ubcd1\ubaa9 \ud655\uc778 (\ud604\uc7ac \uc2e4\ud589 \uc911\uc778 \uc694\uccad)<\/strong><\/h2>\n\n\n\n
SELECT\n    r.session_id,\n    r.status,\n    r.command,\n    r.wait_type,\n    r.wait_time,\n    r.wait_resource,\n    r.cpu_time,\n    r.total_elapsed_time,\n    r.logical_reads,\n    r.reads AS physical_reads,\n    r.writes,\n    r.blocking_session_id,\n    s.login_name,\n    s.host_name,\n    s.program_name,\n    st.text AS sql_text\nFROM sys.dm_exec_requests r\nJOIN sys.dm_exec_sessions s ON r.session_id = s.session_id\nOUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st\nWHERE r.session_id <> @@SPID\nORDER BY r.total_elapsed_time DESC;\n<\/code><\/pre>\n\n\n\n
\n
\ud83d\udca1 \uc774 \ucffc\ub9ac\ub294 \ud604\uc7ac \uc2e4\ud589 \uc911\uc778 \uc694\uccad \uc911 \ub300\uae30 \uc911\uc778 \uc694\uccad<\/strong>, \uc2e4\ud589 \uc2dc\uac04 \uc624\ub798 \uac78\ub9ac\ub294 \ucffc\ub9ac<\/strong>, I\/O\uac00 \ub9ce\uc740 \ucffc\ub9ac<\/strong>, \ube14\ub85c\ud0b9<\/strong> \uc5ec\ubd80\ub97c \ud30c\uc545\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
\u2705 2. \ub300\uae30 \uc720\ud615\ubcc4 \uc2e4\uc2dc\uac04 \ud1b5\uacc4 (Waits \ubd84\uc11d)<\/strong><\/h2>\n\n\n\n
SELECT\n    wait_type,\n    wait_time_ms \/ 1000.0 AS wait_time_sec,\n    waiting_tasks_count,\n    wait_time_ms \/ NULLIF(waiting_tasks_count, 0) AS avg_wait_time_ms\nFROM sys.dm_os_wait_stats\nWHERE wait_type NOT IN (\n    'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',\n    'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE',\n    'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT',\n    'BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT',\n    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT',\n    'XE_DISPATCHER_JOIN','WAIT_XACT_OWN_TRANSACTION'\n)\nORDER BY wait_time_ms DESC;\n<\/code><\/pre>\n\n\n\n
\n
\ud83d\udd0d \ubcd1\ubaa9 \uc720\ud615(\uc608: PAGEIOLATCH<\/code>, CXPACKET<\/code>, WRITELOG<\/code>)\uc744 \ud30c\uc545\ud558\uc5ec \uc131\ub2a5 \uc800\ud558 \uc6d0\uc778 \ubd84\uc11d\uc5d0 \uc0ac\uc6a9\ub429\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
\u2705 3. I\/O\uac00 \ub9ce\uc740 \uc138\uc158 Top 10<\/strong><\/h2>\n\n\n\n
SELECT TOP 10\n    r.session_id,\n    r.status,\n    r.cpu_time,\n    r.reads AS physical_reads,\n    r.writes,\n    r.logical_reads,\n    r.total_elapsed_time,\n    s.login_name,\n    st.text AS sql_text\nFROM sys.dm_exec_requests r\nJOIN sys.dm_exec_sessions s ON r.session_id = s.session_id\nOUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st\nWHERE r.session_id <> @@SPID\nORDER BY r.logical_reads DESC;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u2705 4. Blocking \uc138\uc158 \ubaa8\ub2c8\ud130\ub9c1<\/strong><\/h2>\n\n\n\n
SELECT\n    blocking_session_id AS blocker,\n    session_id AS blocked,\n    wait_type,\n    wait_time,\n    wait_resource,\n    st.text AS blocked_query\nFROM sys.dm_exec_requests\nOUTER APPLY sys.dm_exec_sql_text(sql_handle) AS st\nWHERE blocking_session_id <> 0\nORDER BY wait_time DESC;\n<\/code><\/pre>\n\n\n\n
\n
\ud83e\uddf1 \ube14\ub85c\ud0b9 \ud2b8\ub9ac \ubd84\uc11d \uc2dc \uc720\uc6a9.<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
\u2705 5. \ub514\uc2a4\ud06c \uad00\ub828 \ubcd1\ubaa9: PAGEIOLATCH \ub300\uae30 \ud655\uc778<\/strong><\/h2>\n\n\n\n
SELECT\n    wait_type,\n    waiting_tasks_count,\n    wait_time_ms,\n    wait_time_ms \/ NULLIF(waiting_tasks_count, 0) AS avg_wait_time_ms\nFROM sys.dm_os_wait_stats\nWHERE wait_type LIKE 'PAGEIOLATCH%'\nORDER BY wait_time_ms DESC;\n<\/code><\/pre>\n\n\n\n
\n
\ud83e\udde0 PAGEIOLATCH_SH<\/code>, PAGEIOLATCH_EX<\/code>\ub294 \ub514\uc2a4\ud06c I\/O \ubcd1\ubaa9<\/strong>\uc744 \ub098\ud0c0\ub0c5\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
\u2705 6. \uc2e4\uc2dc\uac04 \uc2a4\ud540\ub77d\/\uc7a0\uae08 \ubcd1\ubaa9 \uc815\ubcf4 (\uace0\uae09)<\/strong><\/h2>\n\n\n\n
SELECT *\nFROM sys.dm_os_spinlock_stats\nWHERE spins > 0\nORDER BY spins DESC;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u2705 7. \ud65c\uc131 \ud2b8\ub79c\uc7ad\uc158 \ubc0f \uc7a0\uae08 \ud655\uc778<\/strong><\/h2>\n\n\n\n
SELECT\n    request_session_id AS session_id,\n    resource_type,\n    resource_database_id,\n    resource_associated_entity_id,\n    request_mode,\n    request_status\nFROM sys.dm_tran_locks\nWHERE request_status = 'WAIT';\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
\ud83d\udd1a \uc815\ub9ac: \ubaa9\uc801\ubcc4 \ucd94\ucc9c<\/h2>\n\n\n\n
\ubaa9\uc801<\/th>\uc0ac\uc6a9 \ucffc\ub9ac<\/th><\/tr><\/thead>
\uc2e4\uc2dc\uac04 \uc694\uccad, \uc138\uc158 \ubd84\uc11d<\/td>1\ubc88 \ucffc\ub9ac<\/td><\/tr>
\uc804\uccb4 \ub300\uae30 \uc2dc\uac04 \ubd84\uc11d<\/td>2\ubc88 \ucffc\ub9ac<\/td><\/tr>
\ube14\ub85c\ud0b9, \uc7a0\uae08<\/td>4\ubc88, 7\ubc88<\/td><\/tr>
I\/O \uc131\ub2a5 \ubb38\uc81c \ubd84\uc11d<\/td>3\ubc88, 5\ubc88<\/td><\/tr>
\uc2dc\uc2a4\ud15c \ubcd1\ubaa9 (\ub300\uae30 vs CPU \ub4f1)<\/td>2\ubc88, 6\ubc88<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"
\u2705 1. \uc2e4\uc2dc\uac04 \uc138\uc158 \ub300\uae30 \ubc0f \ubcd1\ubaa9 \ud655\uc778 (\ud604\uc7ac \uc2e4\ud589 \uc911\uc778 \uc694\uccad) SELECT r.session_id, r.status, r.command, r.wait_type, r.wait_time, r.wait_resource, r.cpu_time, r.total_elapsed_time, r.logical_reads, r.reads AS physical_reads, r.writes, r.blocking_session_id, s.login_name, s.host_name, s.program_name, st.text AS sql_text FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st WHERE r.session_id <> @@SPID ORDER BY r.total_elapsed_time DESC;\u2026 Read More »<\/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\/471"}],"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=471"}],"version-history":[{"count":3,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/471\/revisions"}],"predecessor-version":[{"id":475,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/471\/revisions\/475"}],"wp:attachment":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=471"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=471"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=471"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}