Courseware Development Tools

Transcription

1 SQL Server 2005 성능문제해결 본문서는다음위치에있는 Microsoft TechNet 문서의기사로부터작성되었습니다 : Troubleshooting Performance Problems in SQL Server 요약 : SQL Server 데이터베이스에서일시적인성능저하를경험하기란좀처럼드문일입니다. 작업부하를고려하지않은서투른데이터베이스설계나부적절하게구성된시스템은이러한성능문제를유발하는여러가지잠재적인원인들입니다. 관리자는이러한문제를예방하거나최소화해야하며, 문제가발생할때는, 그원인을진단한뒤문제해결을위한적절한조치를취해야합니다. 이문서는 SQL Server 프로파일러, 시스템모니터, 그리고 SQL Server 2005 의새로운동적관리뷰등과같은공개된도구들을사용해서공통적인성능문제를진단및해결하기위한단계적인지침을제공합니다. 역자 : 김정선 (MVP) - Microsoft SQL Server MVP - Microsoft TechNet Honor - 현 ) 필라넷 DB 사업부수석컨설턴트 - 현 ) 삼성 SDS 멀티캠퍼스전임교수 목차 SQL Server 2005 성능문제해결... 1 소개... 2 목적... 2 방법론... 3 리소스병목... 3 리소스병목해결을위한도구... 4 CPU 병목... 4 과도한컴파일과재컴파일 (Recompile)... 5 비효율적인쿼리계획 쿼리병렬처리 서투른커서사용 메모리병목 배경 메모리부족발견 메모리오류에대한일반적인문제해결단계 메모리오류 I/O 병목... 33

2 2 SQL Server 2005 성능문제해결 문제해결 Tempdb tempdb 디스크공간모니터링 디스크공간문제해결 과도한 DDL 및할당작업 느린쿼리 차단 인덱스사용량모니터링 결론 부록 A: DBCC MEMORYSTATUS 설명 부록 B: 차단스크립트 (Blocking Scripts) 인덱스사용정보분석 Wait states 소개 목적 운영중인 SQL Server 데이터베이스에대해일시적인성능문제를경험할수있습니다. 그이유에는서투른데이터베이스설계에서부터작업부하를고려하지않은부적절한시스템이포함될수있습니다. 관리자로서이러한문제를예방하거나최소화하길원할것입니다. 문제가발생할때는그원인을진단한뒤문제해결을위한적절한조치를취해야합니다. 이백서에서다루는문제범위는 Microsoft 고객지원서비스 (CSS 혹은 PSS) 에서잠재적인문제들에대한포괄적인분석결과공통적으로드러난문제들로제한합니다. 이문서는 SQL Server 프로파일러, 시스템모니터, 그리고 SQL Server 2005 의새로운동적관리뷰등과같은공개된도구들을사용해서공통적인성능문제를진단및해결하기위한단계적인지침을제공합니다. 이문서의주된목적은공통적인고객시나리오로공개된도구들을사용해서 SQL Server 성능문제의진단및해결을위한일반적인방법론을제공하는것입니다. SQL Server 2005 는지원성에있어서장족의발전을했습니다. 커널계층 (SQL-OS) 이재설계되었으며내부구조및통계적데이터를동적관리뷰 (DMV) 를통해서관계형행집합으로노출합니다. SQL Server 2000 에서는이러한정보를 sysprocesses 같은시스템테이블을통해서노출했지만, 때론내부구조로부터관련정보를얻기위해 SQL Server 프로세스메모리에대한물리적인덤프를생성하길원할때가있습니다. 그러나여기에는두가지주된쟁점이있습니다. 첫번째, 덤프의크기및생성시간으로인해서고객이항상물리적덤프를제공하기란어렵다는것이고, 두번째, 해당파일을분석하기위해 Microsoft 로보내는것이그문제를진단하는것보다더오래걸릴수있다는것입니다. 이것이이문서의두번째목적입니다. 바로 DMV 를소개하는것입니다. DMV 는대부분의경우에물리적덤프를생성하고분석하는절차를생략함으로써보다신속하게진단할수있도록도와줍니다. 이문서에서는 SQL Server 2000 과 SQL Server 2005 에서동일문제를어떻게해결하는지단계적으로비교합니다. DMV 는단순하면서도보다친숙한관계형인터페이스를통해서중요한시스템정보를얻을수있도록지원합니다. 이러한정보들은잠재적인문제상황발생시관리자에게

3 SQL Server 2005 성능문제해결 3 경고를주기위한목적의모니터링에도사용될수있습니다. 또한, 이정보들은이후의전문적인분석을위해서정기적으로수집될수있습니다. 방법론 SQL Server 를느리게만드는원인들은많이있습니다만, 다음 3 가지핵심증상을통해서문제의진단을시작합니다. 리소스병목 : 이문서에서는 CPU, 메모리, I/O 병목을다루며네트워크문제는고려하지않습니다. 각리소스병목에대한문제식별방법을기술하고그잠재적인원인들을차례로반복합니다. Tempdb 병목 : SQL Server 인스턴트별오직하나의 tempdb 가존재하므로, 이것이성능과디스크공간의병목이될수있습니다. 잘못된응용프로그램은과도한 DDL/DML 작업과용량소비라는두가지측면에있어서 tempdb 의과부하를유발합니다. 이것은동일서버에서실행중인다른응용프로그램의성능저하혹은작업실패를유발할수있습니다. 악성쿼리 : 기존쿼리의성능이느려지거나새로운쿼리가예상보다더느릴수있습니다. 여기엔많은원인들이있습니다. 예를들어 : 통계정보의변경으로인해기존쿼리가잘못된쿼리계획을산출. 인덱스부재로인해테이블스캔을유발하고쿼리가느려짐. 리소스사용량이많지않음에도불구하고차단으로인해응용프로그램이느려짐 잘못개발된응용프로그램이나스키마설계혹은트랜잭션에대해부적절한격리수준의선택이과도한차단의원인이될수있습니다. 이런증상들의원인이반드시독립적인것은아닙니다. 잘못선택된쿼리계획은시스템리소스를과도하게소비하고전반적인성능저하의원인이될수있습니다. 따라서대용량테이블에필요한인덱스가없거나혹은쿼리최적화프로그램 (Optimizer) 이그인덱스를사용하지않도록결정한다면이것이악성쿼리를유발할뿐만아니라, SQL Server 가접근이필요한페이지들을캐시에저장하기위해서결국불필요한데이터페이지들까지읽어들이게되고이를메모리 (Buffer Pool) 에둠으로써 I/O 하위시스템의막대한부하를주게됩니다. 마찬가지로, 자주실행되는쿼리에과도한재컴파일도 CPU 에부하를줍니다. 리소스병목 다음절에서는 CPU, 메모리, I/O 하위시스템리소스들이어떻게병목이되는지에대해서논의할것입니다. ( 네트워크문제는제외합니다.) 각리소스병목에대해서그문제를식별하는방법과발생가능한원인들에대해서차례로반복합니다. 예를들어, 메모리병목은과도한페이징을유발해서결국성능에영향을미치게됩니다. 리소스병목이있는지를판단하기전에, 정상적인상황에서리소스가어떻게사용되고있는지알아야합니다. 이문서에서개략적으로소개하는방법들을사용해서리소스사용에대한기준정보를수집할수있습니다. 결국은용량을거의다소비하고있는리소스가문제이며현재구성으로는그모든작업부하를지원할수없다는것을알게됩니다. 이러한문제를처리하기위해서, 더강력한프로세스, 메모리, 혹은 I/O 나

4 4 SQL Server 2005 성능문제해결 네트워크채널의대역폭을증가시킬필요가있을것입니다. 그러나, 이러한절차를밟기전에리소스병목에대한몇가지공통적인원인들을이해하는것이도움이됩니다. 여기엔서버구성정보변경과같이부가적인리소스를추가할필요가없는해결방안들이있습니다. 리소스병목해결을위한도구 특정리소스병목을해결하기위해다음과같은도구들을사용합니다. 시스템모니터 (PerfMon): Windows 구성요소로제공되는도구입니다. 보다자세한정보는시스템모니터문서를참조하십시오. SQL Server 프로파일러 : SQL Server 2005 프로그램그룹내의성능도구그룹의 SQL Server Profiler 를참조하십시오. DBCC 명령 : 자세한내용은 SQL Server 온라인설명서와본문서의부록 A 를참조하십시오. DMV: 자세한내용은본문서와함께 SQL Server 온라인설명서를참조하십시오. CPU 병목 CPU 병목은서버의다른부하가없는상황에서, 불시에발생할수있습니다, 공통적인원인들로는최적화되지않은쿼리계획, 잘못된서버구성, 설계문제, 그리고부족한하드웨어리소스등을들수있습니다. 보다빠른혹은더많은프로세스를성급하게구매하기보다는, 먼저 CPU 대역폭을가장많이소비하는주범이누구인지를식별하고튜닝이가능한지를확인해야합니다. 서버가 CPU 집중적인지를판단하는가장좋은방법은시스템모니터입니다. Processor: %Processor Time 카운터가높은지를살펴봐야합니다. CPU 당프로세스시간이 80% 를초과하는경우일반적으로병목이라고간주합니다. 또는 sys.dm_os_schedulers 뷰를사용해서실행가능한작업수 (runnable_tasks_count) 가일반적으로 0 이아닌지를살펴봄으로써 SQL Server 스케줄러를모니터할수있습니다. 0 이아닌값은해당작업이실행하기위해서대기함을나타냅니다 ; 이카운터가높은값이면 CPU 병목을나타내는증상입니다. 다음쿼리를사용해서모든스케쥴러를나열하고실행가능한작업수를볼수있습니다. select scheduler_id, current_tasks_count, runnable_tasks_count from sys.dm_os_schedulers where scheduler_id < 255 다음쿼리는캐시된일괄처리 (batch) 나프로시저중에서 CPU 를가장많이소비하는대상을알수있습니다. 이쿼리는동일 plan_hanlde( 동일일괄처리나프로시저의일부임을의미 ) 을기준으로해당구문에서소비한전체 CPU 사용량을집계합니다. 만일 plan_handle 을기준으로하나이상의구문을포함한다면, CPU 를가장많이소비하는문제쿼리를식별하기위해서보다상세하게접근하게될것입니다.

5 SQL Server 2005 성능문제해결 5 select top 50 sum(qs.total_worker_time) as total_cpu_time, sum(qs.execution_count) as total_execution_count, count(*) as number_of_statements, qs.plan_handle from sys.dm_exec_query_stats qs group by qs.plan_handle order by sum(qs.total_worker_time) desc 이절의나머지부분에서는공통적인 CPU- 집약적작업들의내용과, 그러한문제를찾아서해결하는효율적인방법들에대해서논의합니다. 과도한컴파일과재컴파일 (Recompile) 일괄처리 (Batch) 나원격프로시저호출 (RPC) 이 SQL Server 로전송되면서버는그것을실행하기전에먼저해당쿼리계획에대한적합성과정확성을검사합니다. 그중하나가실패하면, 일괄처리는다른쿼리계획을산출하기위해서다시컴파일해야만합니다. 그러한컴파일을재컴파일 (recompilation) 이라고합니다. 재컴파일은일반적으로정확성을보장하기위해서필요하며데이터변경으로인해서서버가보다최적화된쿼리계획을산출할수있다고판단하는경우에수행됩니다. 컴파일은본래 CPU 집약적이므로과도한재컴파일은결과적으로 CPU- 집중형성능문제의원인이됩니다. SQL Server 2000 에서는, 저장프로시저를재컴파일할때재컴파일을유발시킨해당구문만이아니라저장프로시저전체를재컴파일합니다. SQL Server 2005 는구문단위재컴파일을도입했습니다. SQL Server 2005 가저장프로시저를재컴파일하게되면, 프로시저전체가아니라해당구문만을컴파일합니다. 이러한동작은 CPU 대역폭을보다적게사용함으로써 COMPILE 잠금과같은리소스상에충돌을줄여줍니다. 재컴파일의원인은다음과같습니다 : 스키마변경 정보변경 지연된컴파일 SET 옵션변경 임시테이블변경 OPTION(RECOMPILE) 쿼리힌트사용 1 발견과도한컴파일과재컴파일을찾아내기위해서시스템모니터 (PerfMon) 혹은 SQL Trace(SQL Server 프로파일러 ) 를사용할수있습니다. 시스템모니터 (Perfmon) 1 원문에서는 RECOMPILE 쿼리힌트를추가로언급하고있으나온라인설명서, 프로파일러등을참조해보면 OPTION(RECOMPILE) 과같은것으로판단됩니다.

6 6 SQL Server 2005 성능문제해결 SQL Statistics 개체가 SQL Server 인스턴스에보낸요청의유형과컴파일을모니터하기위한카운터를제공합니다. 컴파일이높은 CPU 사용의원인인지를알아내기위해서는수신된일괄처리수와함께관련된쿼리컴파일및재컴파일수를모니터합니다. 이상적으로는, 사용자가임시 (ad hoc) 쿼리를전송하지않는한 Batch Requests/sec 대비 SQL Recompilations/sec 비율은아주낮아야합니다. 핵심카운터는다음과같습니다. SQL Server: SQL Statistics: Batch Requests/sec SQL Server: SQL Statistics: SQL Compilations/sec SQL Server: SQL Statistics: SQL Recompilations/sec 보다자세한정보는, SQL Server 온라인설명서의 SQL Statistics 개체 를참조하십시오. SQL Trace PerfMon 카운터가높은재컴파일수치를나타낸다면, 재컴파일이 SQL Server 로하여금많은 CPU 소비하게만드는원인이될수있습니다. 이제프로파일러추적을이용해서재컴파일이발생하는해당저장프로시저를찾아야합니다. SQL Server 프로파일러추적은재컴파일원인을포함한추가정보를제공합니다. 이러한정보를얻기위해서다음이벤트를사용합니다. SP: Recompile / SQL: StmtRecompile. SP: Recompile 과 SQL: StmtRecompile 이벤트클래스는재컴파일된해당저장프로시저및문장 (Statement) 을나타냅니다. 저장프로시저를컴파일할때는, 저장프로시저에대해하나의이벤트가발생하고컴파일된각문장에대해하나가발생합니다. 그러나, 저장프로시저를재컴파일할때는, 재컴파일을유발하는해당문장만이재컴파일됩니다 (SQL Server 2000 에서와같이저장프로시저가전체가아니라 ). SP: Recompile 이벤트클래스에대한몇가지중요한데이터열은아래목록에있습니다. 특히 EventSubClass 데이터열은재컴파일하는이유를파악하는데있어서중요합니다. SP: Recompile 은재컴파일되는프로시저나트리거에대해한번발생되며재컴파일이될수도있는임시일괄처리에대해서는발생되지않습니다. SQL Server 2005 에서는 SQL: StmtRecompile 를모니터하는것이보다유용합니다. 이이벤트클래스는모든유형의일괄처리, 임시쿼리, 저장프로시저, 트리거가재컴파일될때발생합니다. 핵심데이터열은다음과같습니다. EventClass EventSubClass ObjectID ( 해당문장을포함한저장프로시저를의미 ) SPID StartTime SqlHandle TextData 보다자세한정보는, SQL Server 온라인설명서의 SQL: StmtRecompile 이벤트클래스 를참조하십시오.

7 SQL Server 2005 성능문제해결 7 만일저장해둔추적파일이있다면, 다음쿼리를사용해서캡처된재컴파일이벤트를모두볼수있습니다. select spid, StartTime, Textdata, EventSubclass, ObjectID, DatabaseID, SQLHandle from fn_trace_gettable ( 'e:\recompiletrace.trc', 1) where EventClass in (37,75,166) 참고 2. EventClass 값과해당이벤트 37 = Sp:Recompile, 75 = CursorRecompile, 166=SQL:StmtRecompile SqlHandle 과 ObjectID 열혹은다른열을기준으로쿼리결과를그룹화하면, 재컴파일의주범이어떤프로시저인지알아내거나혹은다른이유 ( 예를들어 SET 옵션변경 ) 때문인지알수있습니다. Showplan XML For Query Compile. Showplan XML For Query Compile 이벤트클래스는 Microsoft SQL Server 가 SQL 문장을컴파일또는재컴파일할때발생합니다. 이이벤트에는컴파일또는재컴파일되는해당문장에대한정보가있습니다. 이정보에는해당쿼리계획과프로시저의개체 ID 를포함합니다. 이이벤트는각각의컴파일혹은재컴파일에대해서캡처가되므로상당한성능부하를가집니다. 시스템모니터에서 SQL Compilations/sec 카운터가높은값을보인다면, 이이벤트를모니터합니다. 이정보를통해서, 어떤문장이자주재컴파일되는지알수있으며해당문장의파라미터를변경하는데사용할수있으므로재컴파일수를줄일수있습니다. DMV. sys.dm_exec_query_optimizer_info DMV 를사용하면, SQL Server 가최적화에소비하는시간에대한좋은아이디어를얻을수있습니다. 이 DMV 를일정간격을두고두번실행해서그결과를비교하면, 해당기간동안최적화에소비한시간에대한좋은정보를얻을수있습니다. select * from sys.dm_exec_query_optimizer_info counter occurrence value optimizations elapsed time E-2 특히 elapsed time 3 값은최적화로인해서경과된시간을나타냅니다. 2 원문에별다른언급이없어서, 역자임의로추가합니다.

8 8 SQL Server 2005 성능문제해결 일반적으로최적화동안의 elapsed time 은최적화를위해서사용된 CPU 시간에가깝습니다 ( 최적화절차가매우 CPU 집중적이므로 ), 따라서컴파일시간이 CPU 사용에미치는정도에대한좋은측정방법이됩니다. 이러한정보를캡처하는데유용한다른 DMV 로는 sys.dm_exec_query_stats 가있습니다. 관심있게볼데이터열은다음과같습니다 : Sql_handle Total worker time Plan generation number Statement Start Offset 자세한정보는, SQL Server 온라인설명서에서 sys.dm_exec_query_stats 항목을참조하십시오. 특히, plan_generation_num 은쿼리재컴파일횟수를나타냅니다. 다음예제쿼리는재컴파일횟수를기준으로상위 25 건의저장프로시저를보여줍니다. select * from sys.dm_exec_query_optimizer_info select top 25 sql_text.text, sql_handle, plan_generation_num, execution_count, dbid, objectid from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num >1 order by plan_generation_num desc 추가정보를위해서, Microsoft TechNet 에 Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 ( 를참조하십시오. 해결방안과도한컴파일및재컴파일이문제임을확인했다면, 다음옵션들을고려할수있습니다. 재컴파일의원인이 SET 옵션변경에있다면, SQL Server 프로파일러를사용해서어떤 SET 옵션이변경되는지확인하십시오. 저장프로시저내에서 SET 옵션의변경은피하셔야합니다. 연결시의설정하는것이 3 sys.dm_exec_query_optimizer_info DMV 에는원문에언급된두가지카운터이외에상당히많은카운터항목들이제공됩니다. 오히려더유용한정보들이많이있습니다. 반드시살펴보시길권장합니다.

9 SQL Server 2005 성능문제해결 9 더좋습니다. SET 옵션은서버에연결하고있는동안에는변경되지않도록하십시오. 임시테이블에대한재컴파일임계값은일반테이블보다더낮습니다. 임시테이블에재컴파일원인이통계정보변경때문이라면, 임시테이블대신에테이블변수를사용할수있습니다. 테이블변수의카디널리티 4 변경은재컴파일을유발하지않습니다. 이접근방법의문제점은테이블변수의경우통계정보를만들거나보존하지않기때문에최적화프로그램이테이블변수의카디널리티를지속적으로추적하지않는다는것입니다. 이것이결국은최적화되지못한쿼리계획을만들게됩니다. 다른옵션들을시험해보고최선의방법을선택합니다. 또다른옵션은 KEEP PLAN 쿼리힌트를사용하는것입니다. 이옵션은임시테이블에대한낮은재컴파일임계값을일반테이블과동일하게설정합니다. EventSubclass 열이 Statistics changed 를나타내는것을볼수있습니다. 통계정보변경으로인한재컴파일을피하려면 ( 예를들어, 통계정보변경으로인한계획이최적이아닌경우 ), KEEPFIXED PLAN 쿼리힌트를지정합니다. 실제로이옵션을사용하면, 재컴파일이정확성 - 관련원인 ( 예를들어, 테이블의구조가변경되어서더이상해당계획을적용하지않는경우 ) 인경우에만발생하고통계정보로인해서는발생하지않도록합니다. 문장에서참조중인테이블의구조가변경되거나, 혹은테이블이 sp_recompile 저장프로시저로표시되어서재컴파일이발생하는경우가그예입니다. 테이블혹은인덱싱된뷰에정의된인덱스와통계정보에대해 통계자동업데이트 옵션을해제하면해당개체의통계정보변경으로인한재컴파일을차단하게됩니다. 주의할것은, 통계정보자동화 기능을해제하는것은일반적으로좋은아이디어가아닙니다. 이는쿼리최적화프로그램이해당개체의데이터변경을더이상반영하지않게되므로, 최적화되지못한쿼리계획을만들게됩니다. 이방법은다른모든대체방법들을철저하게검증한후에최후의방안으로만사용하기바랍니다. 일괄처리내에서는반드시한정된개체이름 ( 예, db.table1) 을사용해야개체이름해석시의모호함을제거하고재컴파일을피할수있습니다. 지연된컴파일로인한재컴파일을피하기위해서는, DML 과 DDL 을교차하지않습니다. 또한 IF 문장과같은조건절에서 DDL 을구성하지않습니다. 데이터베이스엔진튜닝관리자 (DTA) 를수행해서쿼리실행시간및컴파일시간향상을위해어떠한인덱스변경이필요한지알수있습니다. 저장프로시저가 WITH RECOMPILE 옵션혹은 RECOMPILE 쿼리힌트를사용해서만들어졌는지점검합니다. 저장프로시저가 WITH RECOMPILE 옵션으로만들어진경우 SQL Server 2005 에서는저장프로시저내의특정구문만재컴파일하는문장단위 RECOMPILE 힌트의이득을취할수있습니다. 이는실행될때마다전체프로시저가매번재컴파일되는것을피하면서개별문장에대한컴파일을허용합니다. RECOMPILE 힌트에대한자세한정보는, SQL Server 4 카디널리티 (Cardinality) 는수학에서집합의원소의개수를의미합니다. 여기서는테이블의행수를의미합니다.

10 10 SQL Server 2005 성능문제해결 비효율적인쿼리계획 온라인설명서를참조하십시오. 쿼리에대한실행계획을산출할때, 최적화프로그램은해당쿼리에대해가장빠른응답속도를제공하는계획을선택하려고합니다. 가장빠른응답시간이라는의미가반드시 I/O 양을최소화하거나, CPU 를가장적게사용한다는것은아닙니다 - 여러가지리소스에대한균형을맞추는것입니다. 특정유형의연산자는다른것보다더많은 CPU 를사용합니다. 근본적으로해시 (Hash) 연산자와정렬 (Sort) 연산자는입력데이터전체를스캔합니다. 스캔작업에미리읽기 (read ahead) 가적용됩니다, 이는해당연산자에의해서페이지가요청되기전에대부분의페이지들이이미버퍼캐시에적재된상태로만들어주는것입니다. 따라서, 물리적 I/O 에대한대기를최소화하거나제거할수있습니다. 이러한유형의연산자가물리적 I/O 에더이상제약을받지않으면높은 CPU 소비를보여주게됩니다. 그에반해서, 중첩반복조인 (Nested Loop Join) 은많은인덱스검색연산을수행하며해당페이지들이버퍼캐시에모두존재하지않는다면, 테이블의서로다른부분을돌아다니며인덱스검색을하게되므로결국 I/O 집중적인연산이됩니다. 최적화프로그램의가장중요한입력은 Showplan(EstimateRows 와 EstimateExecutions 속성 ) 에서볼수있는각연산자별카디널리티예측입니다. 이는여러가지대체쿼리계획에대한비용을평가하는데사용됩니다. 정확한카디널리티예측이없다면, 이는최적화프로그램에서사용되는가장중요한입력에결함이생기는것이며, 비효율적인계획이최종계획으로선택되는문제를내포하는것입니다. 다음자료, Statistics Used by the Query Optimizer in Microsoft SQL Server 2005 ( 는 SQL Server 최적화프로그램이통계정보를사용하는방법을자세히설명하는훌륭한백서입니다. 이백서는최적화프로그램이통계정보를사용하는방법, 최신통계정보로유지하기위한최적화사례, 그리고정확한카디널리티예측을방해함으로써비효율적인쿼리계획의원인이되는몇가지공통적인쿼리설계문제를논의합니다. 발견보통비효율적인쿼리계획들은꽤많이발견됩니다. 비효율적인쿼리계획은 CPU 소비를증가시킵니다. sys.dm_exec_query_stats 를쿼리하면 CPU 를소비하는대상쿼리를효율적으로찾을수있습니다.

11 SQL Server 2005 성능문제해결 11 select highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time, q.dbid, q.objectid, q.number, q.encrypted, q.[text] from (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc 또는, sys.dm_exec_cached_plans 을쿼리해서 CPU 집약적인여러가지연산자들예를들어, %Hash Match%, %Sort% 같은조건으로필터를사용함으로써해당주범을찾을수있습니다. 해결방안비효율적인쿼리계획을발견했다면다음옵션들을고려합니다. 데이터베이스엔진튜닝관리자로인덱스권장구성을산출합니다. 카디널리티예측오류가있는지검사합니다. 결과집합의크기를최대한제한하도록 WHERE 절을구성한쿼리입니까? 그렇지않은쿼리는근본적으로리소스를많이소비합니다. 쿼리에관련된테이블에 UPDATE STATISTICS 를실행하고같은문제가계속되는지확인합니다. 해당쿼리가최적화프로그램으로하여금정확한카디널리티예측을할수없도록작성되어있습니까? 구문상의문제가있다면쿼리재작성을고려하십시오. 해당스키마나쿼리를변경할수없다면, SQL Server 2005 의새로운기능인계획지침 (Plan Guide) 을사용해서특정텍스트와일치하는쿼리에쿼리힌트를지정할수있습니다. 이방법은저장프로시저내부를비롯한임시 (ad hoc) 쿼리등에적용할수있습니다. OPTION (OPTIMIZE FOR) 와같은힌트는최적화프로그램이특정계획을선택하는데있어서카디널리티예측에영향을줍니다. OPTION (FORCE ORDER) 나 OPTION (USE PLAN) 과같은힌트는전반적인쿼리계획조정에범위를변경할수있습니다. 쿼리병렬처리 쿼리실행계획생성시, SQL Server 쿼리최적화프로그램은가장빠른응답시간을제공하는계획을선택하려고합니다. 그런데만일쿼리

12 12 SQL Server 2005 성능문제해결 비용이병렬처리에대한비용임계값 (cost threshold for parallelism) 옵션에설정된값을초과하게되면, 최적화프로그램은병렬로실행되는계획을생성합니다. 병렬쿼리계획은다중쓰레드로쿼리를처리합니다, 각쓰레드는가용한 CPU 에분산되고각프로세서로부터 CPU 시간을사용합니다. 병렬처리용프로세서의최대개수는최대병렬처리수준 (max degree of parallelism) 서버옵션을사용한서버범위에서혹은 OPTION(MAXDOP) 힌트를사용한개별쿼리수준에서제한할수가있습니다. 주어진연산을병렬로수행할쓰레드수를평가하는실제병렬처리수 (degree of parallelism, DOP) 결정은실행시점까지연기됩니다. SQL Server 2005 는쿼리를실행하기전에, 몇개의스케쥴러가사용중인지를판단하고나머지스케쥴러를충분히사용하도록쿼리의 DOP 을선택합니다. DOP 이선택되면, 해당병렬처리수준으로완료때까지실행됩니다. 병렬쿼리는보통직렬실행계획과비교해서약간더높은 5 CPU 시간을사용하지만, 경과시간은더단축됩니다. 다른병목예를들어, 물리적 I/O 를위한대기등이없다고가정한다면일반적으로병렬계획은전체프로세서에걸쳐서 100% 의 CPU 를사용할것입니다. 병렬계획실행을이끌어내는핵심요소 ( 시스템유휴상태 ) 는쿼리가실행된후에변경될수가있습니다. 예를들어, 유휴상태에서쿼리가요청된다면서버는병렬계획을선택하고네개의 DOP 을사용해서 4 개의서로다른프로세서에쓰레드를만들수있습니다. 해당쓰레드가실행될때기존연결에서많은 CPU 를요구하는또다른쿼리가전송될수있습니다. 그시점에서는, 그모든쓰레드들이현재가용 CPU 시간을나누어사용해야하므로아주짧은시간동안만 CPU 를사용할수있게되고, 이것이결국은높은쿼리실행시간을유발하게됩니다. 병렬쿼리가본질적으로나쁜것은아니며쿼리에대한빠른응답시간을제공합니다. 그러나쿼리에대한해당응답시간을위해서시스템의전체작업량과다른쿼리의응답에부담을줍니다. 병령쿼리는일반적으로일괄처리작업과의사결정지원용작업에적합하며트랜잭션처리환경에서는바람직하지않습니다. 발견쿼리병렬처리문제를찾아내기위해서다음방법들을사용합니다. 시스템모니터 (Perfmon) SQL Server: SQL Statistics Batch Requests/sec 카운터를조사합니다. SQL Server 온라인설명서에서 SQL Statistics Object 에대한보다자세한정보를참조하십시오. 병렬계획으로결정되기위해서는쿼리예측비용이 병렬처리에대한비용임계값 구성설정 ( 기본값 5) 을초과할정도로커야하므로, 서버가초당처리하는일괄처리수는병렬계획으로실행될때훨씬더작아집니다. 병렬쿼리가많이실행되는서버는일반적으로작은수의초당일괄처리요청수를가집니다 ( 예를들어, 100 이하의값 ). DMV 다음쿼리를사용하면특정세션에서병렬로실행중인활성요청을알수있습니다. 5 역자가실제로튜닝컨설팅을하면서경험한바로는 DOP 의개수에따라서 2 배이상의높은 CPU 가사용되는경우를자주경험했습니다.

13 SQL Server 2005 성능문제해결 13 select r.session_id, r.request_id, max(isnull(exec_context_id, 0)) as number_of_workers, r.sql_handle, r.statement_start_offset, r.statement_end_offset, r.plan_handle from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id = t.session_id join sys.dm_exec_sessions s on r.session_id = s.session_id where s.is_user_process = 0x1 group by r.session_id, r.request_id, r.sql_handle, r.plan_handle, r.statement_start_offset, r.statement_end_offset having max(isnull(exec_context_id, 0)) > 0 해당쿼리는 sys.dm_exec_sql_text 를사용해서쉽게구할수있으며, 쿼리계획은 sys.dm_exec_cached_plan 을사용해서구할수있습니다. 병렬실행에적합한계획을찾을수있습니다. 이는 Parallel 속성값이 0 이아닌관계연산자를가진캐시된계획을찾는것으로수행가능합니다. 이러한계획이병렬로실행되지않을수도있지만, 시스템에여유가있다면병렬처리가적합함을의미합니다 Find query plans that may run in parallel -- select p.*, q.*, cp.plan_handle from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_query_plan(cp.plan_handle) p cross apply sys.dm_exec_sql_text(cp.plan_handle) as q where cp.cacheobjtype = 'Compiled Plan' and p.query_plan.value('declare namespace p=" max(//p:relop/@parallel)', 'float') > 0 일반적으로, 쿼리실행시간은 CPU 시간보다깁니다. 이는쿼리에서잠금이나물리적 I/O 같은리소스대기에추가시간을소비하기때문입니다. CPU 시간이쿼리경과시간보다더많이소비하는경우는 CPU 를동시에사용하는다중쓰레드를가진병렬계획으로쿼리가실행되는경우입니다. 그러나모든병렬쿼리가이에해당되지는않습니다. Note 6 : 다음표에기술한코드중일부는가독성을위해서여러줄에표시되었습니다. 실제로는한줄로입력합니다. 6 원문코드의주석처리가제대로되어있지못해, 역자의판단으로주석 처리를합니다.

14 14 SQL Server 2005 성능문제해결 select qs.sql_handle, qs.statement_start_offset, qs.statement_end_offset, q.dbid, q.objectid, q.number, q.encrypted, q.text from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle) as q where qs.total_worker_time > qs.total_elapsed_time /* SQL Trace CPU 시간이 Duration 보다긴문장이나일괄처리를병렬쿼리의증상으로보고검색을수행 */ select EventClass, TextData from ::fn_trace_gettable('c:\temp\high_cpu_trace.trc', default) where EventClass in (10, 12) --RPC:Completed,SQL:BatchCompleted and CPU > Duration/ CPU 는밀리초,Duration 은밀리초나마이크로초 /* 병렬연산자를가진 showplan ( 인코딩되지않은 ) */ select EventClass, TextData from ::fn_trace_gettable('c:\temp\high_cpu_trace.trc', default) where TextData LIKE '%Parallelism%' 해결방안병렬계획으로실행되는쿼리는최적화프로그램이병렬처리에대한비용임계값 ( 대략 5 초의실행시간 ) 을초과할정도로높은비용임을의미합니다. 위코드를통해서식별된쿼리들이추가튜닝후보들입니다. 데이터베이스엔진튜닝관리자를사용해서인덱스변경, 인덱싱된뷰변경, 혹은테이블분할변경등이쿼리비용을줄일수있는지알아봅니다. 카디널리티예측이퀴리비용예측의주요요소이므로예측과실제카디널리티사이에큰차이가있는지확인합니다. 그러한차이가발견된다면 :

15 SQL Server 2005 성능문제해결 15 통계자동생성 데이터베이스옵션이해제된경우, Showplan( 혹은그래픽실행계획 ) 의 Warnings 칼럼에 NO STATS 7 ( 열에통계가없음 ) 로표기된항목이없는지확인합니다. 카디널리티예측이해제 (OFF) 되어있는테이블에 UPDATE STATISTICS 를실행해봅니다. 쿼리의구조가최적화프로그램으로하여금정확한예측을어렵게만들지않는지확인합니다. 다중문테이블 - 값함수혹은 CLR 함수, 테이블변수, Transact-SQL 변수를가진비교 ( 파라미터의경우에무관 ) 연산자의사용등이이에해당합니다. 쿼리를다른 Transact-SQL 문장혹은표현을사용해서보다효율적인형태로작성될수있는지검토합니다. 서투른커서사용 SQL Server 2005 이전버전에서는서버연결당하나의활성연결만을지원했습니다. 활성연결은쿼리가실행중이거나클라이언트로결과집합전송이진행중인상태를말합니다. 때로는, 클라이언트응용프로그램이결과집합전체를읽으면서각행별로다른쿼리를실행하기원할때가있습니다. 이러한동작은기본결과집합 8 상태에서는수행할수가없습니다. 이경우의일반적인해결방법은서버측커서를사용하도록연결속성을변경하는것입니다. 서버측커서사용시, 데이터베이스클라이언트소프트웨어 (OLE DB 공급자혹은 ODBC 드라이버 ) 는클라이언트요청을 sp_cursoropen, sp_cursorfetch 등과같은내부의특별한확장저장프로시저로투명하게캡슐화되어서호출됩니다. 이러한커서를 API 커서라고합니다 (TSQL 커서와상반되는 ). 사용자가쿼리를실행하면, 쿼리텍스트는 sp_cursoropen 을통해서서버에전송되며, 결과집합으로부터읽기요청은 sp_cursorfetch 를통해서지정된행수단위로반환됩니다. 반입 (Fetch) 될행수를조정함으로써 ODBC 드라이버혹은 OLE DB 공급자가하나이상의행을캐시할수있습니다. 이는서버입장에서클라이언트가전체행을모두읽을때까지대기하는상황을막아줍니다. 즉, 서버는해당연결에서새로운요청을받을수있는준비가되는것입니다. 커서를열고한번에한행씩 ( 혹은작은단위행수 ) 반입하는응용프로그램은특히광대역네트워크 (WAN) 와같은환경에서네트워크지연으로인한병목을아주쉽게유발합니다. 대량의동시사용자연결을가진상황에서많은커서요청을처리하는경우에그오버헤드는더욱분명해집니다. 결과집합내에서커서를특정위치로이동하는작업과관련된오버헤드, 개별요청을처리하는오버헤드, 그리고유사한처리작업들로인한오버헤드때문에, 한번에한행씩처리하면서 100 번의개별요청을처리하는것보다, 단일요청으로 100 개의행을반환하는것이보다효율적입니다. 발견커서사용문제를해결하기위해서다음방법들을사용할수있습니다. 7 한글버전을기준으로언급합니다. 8 SQL Server 가클라이언트로결과집합을전송하는기본방식으로, 이전의 Firehose 에해당합니다. 전진 - 전용, 읽기 - 전용방식입니다.

16 16 SQL Server 2005 성능문제해결 시스템모니터 (Perfmon) 성능카운터 SQL Server: Cursor Manager By Type Cursor Request/Sec 를조사하면시스템상의대략적인커서사용량을알수있습니다. 작은행단위반입으로인해높은 CPU 사용량을보이는시스템은보통초당수백의커서요청을가집니다. 반입되는버퍼크기에관해알려주는정확한카운터는없습니다. DMV 다음예제는 fetch buffer 크기가한행인 API 커서를사용하는연결을알수있습니다. 한행이상보다큰 fetch buffer 를사용하는것이훨씬더효율적입니다. select cur.* from sys.dm_exec_connections con cross apply sys.dm_exec_cursors(con.session_id) as cur where cur.fetch_buffer_size = 1 and cur.properties LIKE 'API%' -- API cursor (TSQL cursors always have fetch buffer of 1) 메모리병목 SQL Trace SQL Trace 에서 sp_cursorfetch 문장을검색하기위해 RPC: Completed 이벤트클래스를포함시켜서사용합니다. 4 번째매개변수값이반환되는행수입니다. 반환될최대행수는해당 RPC: Starting 이벤트클래스에입력파리미터로지정됩니다. 해결방안 커서가가장적합한방법인지에대해서재고해야합니다. 일반적으로집합기반처리가보다효율적입니다. SQL Server 2005 에연결한다면다중활성결과집합 (MARS) 사용을고려합니다. 사용하는 API 관련문서를참조해서어느정도 fetch buffer 크기가커서에적합한지확인합니다 : ODBC - SQL_ATTR_ROW_ARRAY_SIZE OLE DB IRowset::GetNextRows or IRowsetLocate::GetRowsAt 이번절은특히적은메모리구성에서메모리에대한진단분석, 메모리관련오류, 해당원인및해결방안등을다룹니다. 배경 서로다른메모리리소스이지만그냥메모리라는용어를사용하는것이일반적입니다. 메모리리소스의유형이여러가지이므로, 실제론어떤메모리리소스를말하는지이해하고구분하는것이중요합니다.

17 SQL Server 2005 성능문제해결 17 가상주소공간과실제메모리마이크로소프트 Windows 는, 각프로세스단위로가상주소공간 (VAS) 을가집니다. 프로세스에서가용한모든가상주소집합으로 VAS 크기를구성합니다. VAS 의크기는아키텍처 (32 혹은 64 비트 ) 와운영시스템에따라결정됩니다. 메모리문제해결측면에서가상메모리는고갈될수있는메모리리소스이며 64 비트플랫폼에서실제메모리가가용한상황에서도응용프로그램이메모리부족을경험할수있다는점을이해하는것이중요합니다. 가상주소공간에대한추가정보는 SQL Server 온라인설명서의 프로세스주소공간 에대한항목과 MSDN 기사 Virtual Address Space ( 9 ) 을참조하십시오. Address Windowing Extensions (AWE) 과 SQL Server Address Windowing Extensions (AWE) 는 32 비트응용프로그램이 32 비트주소한계를초과하는실제메모리를사용하도록지원하는 API 입니다. AWE 메커니즘은기술적으로 64 비트플랫폼에서필요치않지만계속지원하고있습니다. 64 비트플랫폼에서 AWE 메커니즘을통해서할당된메모리페이지를잠긴페이지 (locked page) 라고합니다. 32 비트와 64 비트양쪽플랫폼에서 AWE 메커니즘을통해할당된메모리는페이징되지않습니다. 이것이응용프로그램에이득이될수있습니다. ( 이점이 64 비트플랫폼에서 AWE 메커니즘을사용하는이유중의하나입니다.) 그러나이러한동작이시스템과다른응용프로그램이사용할 RAM 용량에부정적인영향을줍니다. 그런이유로, AWE 를사용하기위해서는 SQL Server 를실행하는 Windows 계정에대해메모리페이지잠그기 (Lock Pages in Memeory) 사용권한이부여되어야합니다. 문제해결측면에서본다면 SQL Server 버퍼풀이 AWE 매핑메모리를사용한다는점입니다 ; 그러나, 데이터베이스 ( 해시 ) 페이지만이 AWE 를통해할당된메모리의모든이득을얻을수있습니다. AWE 메커니즘을통해할당된메모리정보는작업관리자혹은 Process: Private Bytes 성능카운터를통해서는제공되지않습니다. 이러한정보를얻기위해서는 SQL Server 특정카운터나동적관리뷰 (DMV) 를사용해야합니다. AWE 매핑메모리에대한자세한정보는 SQL Server 온라인설명서의목차에서 큰데이터베이스의메모리관리, 메모리아키텍처 항목과 MSDN 기사 Large Memory Support ( 10 ) 를참조하십시오. 다음표는 SQL Server 2005 의각구성별지원가능한최대메모리옵션을요약한것입니다. (SQL Server 혹은 Windows 각에디션별로지원메모리크기에대한제한이있음을참고하십시오.) 표 1 구성 VAS 최대실제 메모리 AWE/ 잠긴메모리지원 Native 32-bit on 32-bit OS 2 GB 64 GB Yes 9 원본의주소가달라변경합니다. 10 원본의주소가달라변경합니다.

18 18 SQL Server 2005 성능문제해결 구성 VAS 최대실제 메모리 AWE/ 잠긴메모리지원 with /3GB boot parameter 1 3 GB 16 GB Yes 32-bit on x64 OS (WOW) 4 GB 64 GB Yes 32-bit on IA64 OS (WOW) 2 GB 2 GB No Native 64-bit on x64 OS 8 terabyte 1 terabyte Yes Native 64-bit on IA64 OS 7 terabyte 1 terabyte Yes 1 boot 매개변수에대한상세정보는 SQL Server 온라인설명서목차의 AWE 사용 을 참조하십시오. 메모리부족메모리부족이란가용메모리가제한된상태를말합니다. SQL Server 가메모리부족상태에서실행되고있음을확인하는것은메모리관련문제를해결하는데도움을줍니다. SQL Server 는메모리부족유형에따라다른응답을합니다. 다음표는일반적인메모리부족유형과근본원인을요약합니다. 표 2 부족외부내부 실제 실제메모리 (RAM) 가많지않음. 이로인해현재실행중인프로세스의작업메모리가감소하게되고결국성능저하를유발합니다. SQL Server 는이러한상태를감지하고구성옵션설정상태에따라버퍼풀에대상메모리크기를줄이고내부캐시를해제할수있습니다. SQL Server 는내부구성요소간의메모리재분배를유발하는내부의높은메모리소비를감지합니다. 내부메모리부족상태의원인은 : 외부메모리부족상태를반영. 메모리설정변경 ( 예, 최대서버메모리 ). 내부구성요소간의메모리분배변경 ( 버퍼풀에서높은비율의예약공간과 stolen page 로인해 ). 가상 시스템페이지파일공간이부족한상태로실행. 이는현재할당된메모리가페이징될수없도록만들고, 시스템에서메모리할당이실패하는원인이됩니다. 이러한상태가결국시스템전체응답을매우느려지게만들거나정지 조각 ( 많은 VAS 가가용하지만작은블록단위 ), 혹은메모리소비 ( 직접할당, SQL Server VAS 로 DLL 로드, 대량의쓰레드 ) 로인한낮은 VAS 상태로실행. SQL Server 가이러한상태를

19 SQL Server 2005 성능문제해결 19 부족외부내부 상태로만들수있습니다. 감지하고 VAS 에예약된영역감소, 버퍼풀의대상메모리감소, 그리고캐시를축소시킬수있습니다. Windows 는실제메모리가높은혹은낮은상태로실행하면알림을주는메커니즘를가지고있습니다. SQL Server 는이러한메커니즘을사용해서메모리를관리합니다. 표 3 은경우에따른일반적인메모리문제해결단계를설명합니다. 표 3 부족외부내부 실제 시스템메모리를소비하는주범을찾습니다. ( 가능하다면 ) 제거합니다. 시스템 RAM 이충분한지검사하고 RAM 추가를고려합니다. SQL Server 내부의메모리소비주범을찾습니다. 서버구성옵션설정을확인합니다. 조사결과에따른추가조치 : 작업부하 ; 설계문제 ; 다른리소스병목등을검사합니다. 가상 도구 스와핑파일크기를증가시킵니다. 실제메모리소비주범을검사하고위에서소개한외부실제메모리부족에대한수행단계를따릅니다. 위에서소개한내부실제메모리부족단계를따릅니다. 메모리부족발견 다음도구와정보들을이용해서문제를해결할수있습니다. 메모리관련 DMV DBCC MEMORYSTATUS 명령 성능카운터 : SQL Server 특정개체에대한성능모니터혹은 DMV Task Manager 작업관리자 이벤트뷰어 : 응용프로그램로그, 시스템로그 메모리부족그자체가문제를나타내지는않습니다. 메모리부족이란서버의메모리가필요하지만충분하지는않은상태로나중에메모리

20 20 SQL Server 2005 성능문제해결 오류가발생할수있음을말합니다. 메모리가부족한상태에서작업하는것이서버에정상적인운영상태일수있습니다. 그러나, 메모리부족으로인한증상은서버가자신의용량에근접한상태로실행중이며잠재적인메모리부족오류가있음을나타내는것이기도합니다. 정상적으로운영중인서버의경우엔이러한정보들이차후메모리부족에대한판단근거의기준선으로제공될수있습니다. 외부실제메모리부족작업관리자를열고성능탭에서사용가능실제메모리항목의사용가능값을검사합니다. 사용가능메모리양이작다면, 외부메모리가부족한것입니다. 정확한값은여러가지요소에따라달라지지만그값이 MB 로떨어진경우에조사를시작합니다. 이값이 10MB 이하로떨어지면분명히외부메모리가부족한상태입니다. 동일한정보를시스템모니터의 Memory: Available Bytes 카운터를통해서얻을수있습니다. 외부메모리부족이존재하고메모리관련오류를만난다면시스템의실제메모리를소비하는주범이누군인지찾아내야합니다. Process: Working Set 성능카운터나작업관리자의프로세스탭에서메모리사용열을살펴보고그주범을찾아냅니다. 다음카운터들을요약함으로써시스템의실제메모리전체사용량을대략적으로구할수있습니다. 각프로세스별 Process 개체, Working Set 카운터 Memory 개체 시스템작업집합 (working set) 에대한 Cache Bytes 카운터 비페이지풀크기에대한 Pool Nonpaged Bytes 카운터 Available Bytes ( 작업관리자의사용가능값과동일 ) 외부메모리부족이없다면, Process: Private Bytes 카운터나작업관리자의가상메모리크기가작업집합 (Process: Working Set 혹은작업관리자사용가능 ) 크기에가깝습니다. 이는메모리가페이징되지않음을의미합니다. 작업관리자의메모리사용열그리고해당하는성능카운터는 AWE 를통해서할당된메모리는계산하지않음을참고하십시오. 다음처럼 sys.dm_os_memory_clerks DMV 를사용해서 AWE 메커니즘을통해서할당된 SQL Server 메모리양을알아낼수있습니다. select sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb] from sys.dm_os_memory_clerks SQL Server 는현재 AWE 옵션이활성화된경우에버퍼풀클럭 (type = MEMORYCLERK_SQLBUFFERPOOL ) 에대해서만이메커니즘을사용한다는점을참고하십시오. 가능한실제메모리를소비하는주범을식별하고제거함으로써외부메모리부족을제거합니다, 혹은더많은메모리를추가함으로써메모리관련문제를해결할수있습니다.

21 SQL Server 2005 성능문제해결 21 외부가상메모리부족페이지파일이현재메모리할당을수용할만한충분한공간이있는지를판단해야합니다. 이를위해, 작업관리자를열고성능탭에서할당된메모리항목를검사합니다. 전체가한도에가깝다면, 페이지파일공간이부족할가능성이있습니다. 한도는페이지파일공간을확장하지않고할당될수있는최대메모리크기를나타냅니다. 작업관리자의할당된메모리전체는페이지파일사용에대한잠재성이며, 실제사용을나타내지않습니다. 실제사용은실제메모리부족상태에서증가합니다. 동일한정보를 Memory: Commit Limit, Paging File: %Usage, Paging File: %Usage Peak 카운터에서얻을수있습니다. Process: Private Bytes 카운터에서 Process: Working Set 값을빼면프로세스당페이징되는메모리크기를대략적으로예측할수있습니다. Paging File: %Usage Peak ( 혹은할당된메모리최고 ) 가높으면, 페이지파일증가혹은 running low on virtual memory 알림을나타내는이벤트가있는지시스템이벤트로그를확인합니다. 페이지파일크기를증가시킵니다. 높은 Paging File: %Usage 는실제메모리가과도하게소비되었으며외부실제메모리의부족상태를함께고려해야함을나타냅니다. 내부실제메모리부족내부메모리부족은 SQL Server 자체설정에서비롯되므로, 그논리적인단계는 SQL Server 내부의메모리분배를살펴보고버퍼분배에어떤이상이없는지를검사하는것입니다. 정상적으로는버퍼풀이 SQL Server 에의해서할당된메모리의대부분을차지합니다. 버퍼풀에속한메모리크기를판단하기위해 DBCC MEMORYSTATUS 출력을살펴봅니다. Buffer Counts 부분에서 Target 값을찾습니다. 다음은서버가정상적인부하를가진상태에서 DBCC MEMORYSTATUS 출력의일부를보여줍니다. Buffer Counts Buffers Committed Target Hashed Reserved Potential Stolen Potential External Reservation 0 Min Free 256 Visible Available Paging File Target 은페이징없이할당될수있는 8KB 페이지수로 SQL Server 에 의해서계산됩니다. Target 은 Windows 로부터낮은 / 높은메모리알림에 응답하며정기적으로다시계산됩니다. 정상적으로부하를가진서버에서 target 페이지의수가감소되는것은외부실제메모리부족을나타낼수 있습니다. SQL Server 가많은메모리를소비한다면 (Process: Private Bytes 혹은작업관리자의메모리사용열에의해서판단할수있는 ), Target 카운트가메모리의상당부분을차지하는지확인합니다. AWE 옵션이활성화상태라면 AWE 로할당된메모리는 sys.dm_os_memory_clerks 나 DBCC MEMORYSTATUS 출력에서계산해야합니다.

22 22 SQL Server 2005 성능문제해결 위예제 (AWE 는비활성화 ) 에서 Target * 8KB = 1.53GB 라고가정합니다, Process: Private Bytes 가대략 1.62GB 라면 SQL Server 가소비하는메모리의 94% 가버퍼풀 target 에해당합니다. 서버가정상적인부하를포함한상태가아니라면, 정상적인상황일때는 Target 이 Process: Private Bytes 성능카운터값을초과할수있습니다. Target 이낮은반면, 서버의 Process: Private Bytes 혹은작업관리자의메모리사용열이높다면, 이는버퍼풀이외에다른메모리구성요소로부터내부메모리부족을나타낼수있습니다. SQL Server 프로세스영역으로로드되는 COM 개체, 연결된서버, 확장저장프로시저, SQLCLR 및기타구성요소들이버퍼풀외부에서메모리를소비하는메모리구성요소들입니다. 특히이구성요소들이 SQL Server 메모리인터페이스를사용하지않을경우소비되는메모리를추적하기가어렵습니다. SQL Server 메모리관리메커니즘을사용하는구성요소들은작은메모리할당을위해버퍼풀을사용합니다. 이구성요소들이 8KB 이상을할당할경우, 다중페이지할당자 (multi-page allocator) 인터페이스를통해서버퍼풀외부의메모리를사용합니다. 다음은다중페이지할당자를통해서소비된메모리양을확인할수있는빠른방법입니다. -- 다중페이지할당자인터페이스를통해서할당된메모리크기 select sum(multi_pages_kb) from sys.dm_os_memory_clerks 다음코드로다중페이지할당자를통해서할당된메모리에대한분배정보를상세하게알수있습니다 : select type, sum(multi_pages_kb) from sys.dm_os_memory_clerks where multi_pages_kb!= 0 group by type type MEMORYCLERK_SQLSTORENG 56 MEMORYCLERK_SQLOPTIMIZER 48 MEMORYCLERK_SQLGENERAL 2176 MEMORYCLERK_SQLBUFFERPOOL 536 MEMORYCLERK_SOSNODE CACHESTORE_STACKFRAMES 16 MEMORYCLERK_SQLSERVICEBROKER 192 MEMORYCLERK_SNI 32 다중페이지할당자를통해할당된메모리양이크다면 ( MB 이상 ), 추가조사가필요합니다. 다중페이지할당자를통해서할당된대량메모리가보인다면, 서버구성을확인하고이전혹은다음쿼리를사용해서메모리소비주범이어떤구성요소인지를판단합니다. Target 은낮지만백분율로는 SQL Server 메모리의대부분을차지한다면, 이전 ( 외부실제메모리부족 ) 에소개한외부메모리부족에대한원인을찾아보거나서버메모리구성값을확인해봅니다.

23 SQL Server 2005 성능문제해결 23 최대서버메모리나최소서버메모리옵션을설정했다면 target 값과비교해봐야합니다. 최대서버메모리는버퍼풀에의해서소비되는최대메모리를제한합니다, 그러나서버전체적으로는더많은메모리를소비할수있습니다. 최소서버메모리는버퍼풀메모리가그이하로는내려가지않도록합니다. Target 이최소서버메모리설정보다작고서버가정상적으로부하를가진상황이라면이는서버가외부메모리부족상태이며옵션에지정된메모리양만큼확보할수없음을나타냅니다. 더불어위에서언급한내부구성요소의메모리부족도의미하는것입니다. Target 카운트는최대서버메모리옵션설정을초과할수없습니다. 우선, DBCC MEMORYSTATUS 출력에서 stolen 페이지카운트를검사합니다. Buffer Distribution Buffers Stolen Free Cached 1513 Database (clean) Database (dirty) 259 I/O 0 Latched 0 stolen 페이지가 target 에비례해서높은백분율 (75-80% 이상 ) 을가진다면 내부메모리부족을나타냅니다. 서버구성요소별메모리할당에대한자세한정보는 sys.dm_os_memory_clerks DMV 를참조합니다. -- 버퍼풀이외의구성요소에의해소비된메모리계산 -- single_pages_kb 는 BPool 에해당하므로제외 -- BPool 은다음쿼리에서계산 select sum(multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb) as [Overall used w/o BPool, Kb] from sys.dm_os_memory_clerks where type <> 'MEMORYCLERK_SQLBUFFERPOOL' -- BPool 에의해소비된메모리계산 -- 현재 AWE 는 BPool 에서만사용 select sum(multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) as [Used by BPool with AWE, Kb] from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL' 각구성요소별상세정보는다음쿼리에서구할수있습니다. ( 버퍼풀과그이외에구성요소들모두를포함합니다.)

24 24 SQL Server 2005 성능문제해결 bigint table ( type nvarchar(128) collate database_default,allocated bigint,virtual_res bigint,virtual_com bigint,awe bigint,shared_res bigint,shared_com bigint,topfive nvarchar(128),grand_total bigint ); -- 정상적인상황에서버퍼풀의 committed memory 는 -- 대량메모리소비자를나타내므로계산에서제외 = sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb) from sys.dm_os_memory_clerks print 'Total allocated (including from Buffer Pool): ' + CAST(@total_alloc as varchar(10)) + ' Kb' insert select type,sum(single_pages_kb + multi_pages_kb) as allocated,sum(virtual_memory_reserved_kb) as vertual_res,sum(virtual_memory_committed_kb) as virtual_com,sum(awe_allocated_kb) as awe,sum(shared_memory_reserved_kb) as shared_res,sum(shared_memory_committed_kb) as shared_com,case when ( (sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb))/ (@total_alloc + 0.0)) >= 0.05 then type else 'Other' end as topfive,(sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb)) as grand_total from sys.dm_os_memory_clerks group by type

25 SQL Server 2005 성능문제해결 25 order by (sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb)) desc select * 버퍼풀은단일페이지할당자를통해서다른구성요소에메모리를제공하므로이전쿼리에서는다르게처리합니다. 다음쿼리를사용해서버퍼풀페이지 ( 단일페이지할당자를통하는 ) 의상위 10 위소비자를알수있습니다. -- Bpool 의메모리소비상위 10 위 select top 10 type, sum(single_pages_kb) as [SPA Mem, Kb] from sys.dm_os_memory_clerks group by type order by sum(single_pages_kb) desc 일반적으로는내부구성요소가소비하는메모리에대한통제권이없습니다. 그러나, 어떤구성요소가메모리소비주범인지를판단하는것은메모리문제에대한조사범위를좁히는데도움을줄것입니다. 시스템모니터 (Perfmon) 메모리가부족한지판단하기위해서다음성능카운터를확인할수있습니다. ( 상세한설명은 SQL Server 온라인설명서를참조하십시오.): SQL Server: Buffer Manager 개체 Buffer cache hit ratio 가작은경우 Page life expectancy 가작은경우 Checkpoint pages/sec 가높은경우 Lazy writes/sec 가높은경우 메모리부족과 I/O 오버헤드는일반적으로서로관련된병목입니다. 본문서의 I/O 병목를참조하십시오. 캐시와메모리부족외부및내부메모리부족을조사하는또다른방법은메모리캐시동작을조사하는것입니다. SQL Server 2005 의내부구현에있어서, SQL Server 2000 과비교되는차이점의하나는바로캐싱 (caching) 프레임워크의단일화입니다. 프레임워크는캐시에서사용빈도가적은항목들을제거하기위해클럭알고리즘을구현합니다. 현재는내부클럭포인터와외부클럭포인터로두개의클럭포인터 11 (clock hands) 를가집니다. 내부클럭포인터는다른캐시에비례하여캐시크기를조절합니다. 해당캐시가한계점에다달았음을프레임워크가예상하게되면이동을시작합니다. 11 온라인설명서의번역을따릅니다.

26 26 SQL Server 2005 성능문제해결 외부클럭포인트는 SQL Server 메모리가부족한상태가되면이동을시작합니다. 외부클럭포인터의이동은외부및내부메모리의부족이그원인이될수있습니다. 내부및외부클럭포인터의이동을내부및외부메모리부족과혼동하지마십시오. 다음코드와같이클럭포인터이동에관한정보는 sys.dm_os_memory_cache_clock_hands DMV 를통해서알수있습니다. 각캐시항목은내부및외부클럭포힌터별로하나의행을가집니다. rounds_count 와 removed_all_rounds_count 값이증가한다면서버의내부 / 외부메모리가부족한상태입니다. select * from sys.dm_os_memory_cache_clock_hands where rounds_count > 0 and removed_all_rounds_count > 0 다음과같이 sys.dm_os_cache_counters DMV 와조인해서캐시크기와같은추가정보들을얻을수있습니다. select distinct cc.cache_address, cc.name, cc.type, cc.single_pages_kb + cc.multi_pages_kb as total_kb, cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb as total_in_use_kb, cc.entries_count, cc.entries_in_use_count, ch.removed_all_rounds_count, ch.removed_last_round_count from sys.dm_os_memory_cache_counters cc join sys.dm_os_memory_cache_clock_hands ch on (cc.cache_address =ch.cache_address) /* -- 포인터가이동된캐시만을볼경우이블록주석을제거 where ch.rounds_count > 0 and ch.removed_all_rounds_count > 0 */ order by total_kb desc USERSTORE 항목에대해서는사용중인페이지정보가출력되지않으며대신 NULL 이됨을참고하십시오. 링버퍼메모리정보진단을위한의미있는크기는 sys.dm_os_ring_buffers DMV 에서얻을수있습니다. 각링버퍼는특정유형에마지막알림정보를가지는하나의 record 를유지합니다. RING_BUFFER_RESOURCE_MONITOR 메모리상태변경을식별하기위해리소스모니터알림정보를이용할수있습니다. 내부적으로서로다른메모리부족을모니터하는프레임워크를가지고있습니다. 메모리상태가변경되면, 리소스모니터가알림을생성합니다. 이알림은메모리상태에따른메모리사용조정을위해서

27 SQL Server 2005 성능문제해결 27 해당구성요소가내부적으로사용하며다음코드와같이 sys.dm_os_ring_buffers DMV 를통해서사용자에보여집니다. select record from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' record 는다음과같습니다 : <Record id="1701" type="ring_buffer_resource_monitor" time=" "> <ResourceMonitor> <Notification>RESOURCE_MEMPHYSICAL_LOW< /Notification> <Indicators>2</Indicators> <NodeId>0</NodeId> </ResourceMonitor> <MemoryNode id="0"> <ReservedMemory> </ReservedMemory> <CommittedMemory>432388</CommittedMemory> <SharedMemory>0</SharedMemory> <AWEMemory>0</AWEMemory> <SinglePagesMemory>26592</SinglePagesMemory> <MultiplePagesMemory>17128</MultiplePagesMemory> <CachedMemory>17624</CachedMemory> </MemoryNode> <MemoryRecord> <MemoryUtilization>50</MemoryUtilization> <TotalPhysicalMemory> </TotalPhysicalMemory> <AvailablePhysicalMemory> < /AvailablePhysicalMemory> <TotalPageFile> </TotalPageFile> <AvailablePageFile> </AvailablePageFile> <TotalVirtualAddressSpace> < /TotalVirtualAddressSpace> <AvailableVirtualAddressSpace> </AvailableVirtualAddressSpace> <AvailableExtendedVirtualAddressSpace>0 </AvailableExtendedVirtualAddressSpace> </MemoryRecord> </Record> 이레코드에서서버가실제메모리부족알림을받았음을알수있습니다. 더불어킬로바이트단위의메모리크기도알수있습니다. SQL Server 의 XML 기능을활용해서이러한정보들을쿼리할수있습니다.

28 28 SQL Server 2005 성능문제해결 select x.value('(//notification)[1]', 'varchar(max)') as [Type], 'bigint') as [Time Stamp], x.value('(//availablephysicalmemory)[1]', 'int') as [Avail Phys Mem, Kb], x.value('(//availablevirtualaddressspace)[1]', 'int') as [Avail VAS, Kb] from (select cast(record as xml) from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') as R(x) order by [Time Stamp] desc 메모리부족알림을받았으므로, 버퍼풀 target 크기를다시계산합니다. target 카운트는최소서버메모리와최대서버메모리옵션에의해서지정된범위내에있음을참고하십시오. 버퍼풀을위해새로할당된 target 크기가현재크기보다작은경우, 버퍼풀은외부실제메모리부족이없어질때까지축소합니다. 참고로 SQL Server 2000 에서는 AWE 가활성화된경우실제메모리부족에반응하지못했습니다. RING_BUFFER_OOM 이링버퍼는다음예제코드처럼서버메모리부족 (out-of-memory) 를나타내는레코드를포함합니다. select record from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_OOM' record 는다음과같습니다 : <Record id="7301" type="ring_buffer_oom" time=" "> <OOM> <Action>FAIL_VIRTUAL_COMMIT</Action> <Resources>4096</Resources> </OOM> 이레코드는실패한작업의유형 ( 커밋, 예약, 혹은페이지할당 ) 과요청된메모리크기를알려줍니다. RING_BUFFER_MEMORY_BROKER 와내부메모리부족 내부메모리부족이감지되면, 메모리할당원본이되는버퍼풀을사용하는구성요소의메모리부족알림 (low memory notification) 이설정됩니다. 이는캐시와또다른구성요소로부터페이지반환을허용하는것입니다. 내부메모리부족은최대서버메모리옵션을조정하는경우혹은 stolen 페이지가버퍼풀의 80% 를초과하는경우에도발생할수있습니다. 내부메모리부족알림 ( Shrink ) 은, 다음예제코드와같이메모리브로커 (memory broker) 링버퍼를쿼리함으로써알수있습니다.

29 SQL Server 2005 성능문제해결 29 select 'bigint') as [Time Stamp], x.value('(//notification)[1]', 'varchar(100)') as [Last Notification] from (select cast(record as xml) from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER') as R(x) order by [Time Stamp] desc RING_BUFFER_BUFFER_POOL 이링버퍼는버퍼풀의메모리부족상태를포함한중대한버퍼풀실패를나타내는레코드를포함합니다. select record from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL' record 는다음과같습니다 : <Record id="1234" type="ring_buffer_buffer_pool" time=" "> < BufferPoolFailure id="fail_oom"> <CommittedCount>84344 </CommittedCount> <CommittedTarget>84350 </CommittedTarget > <FreeCount>20</FreeCount> <HashedCount>20345</HashedCount> <StolenCount>64001 </StolenCount> <ReservedCount>64001 </ReservedCount> </ BufferPoolFailure > 이레코드는실패내용 (FAIL_OOM, FAIL_MAP, FAIL_RESERVE_ADJUST, FAIL_LAZYWRITER_NO_BUFFERS) 과그시점을알려줍니다. 내부가상메모리부족 VAS( 가상주소공간 ) 소비는 sys.dm_os_virtual_address_dump DMV 를사용해서추적할수있습니다. 다음과같은뷰를사용해서 VAS 요약정보를구할수있습니다.

30 30 SQL Server 2005 성능문제해결 -- 가상주소공간요약뷰 -- SQL Server 영역에대한목록을생성 -- 예약되거나빈영역수를보여줌 CREATE VIEW VASummary AS SELECT Size = VaDump.Size, Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1 END), Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END) FROM ( --- 기준주소포인트가 0 인경우를제외하고, --- 할당기준주소별로영역크기집계 SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size, region_allocation_base_address AS Base FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address <> 0x0 GROUP BY region_allocation_base_address UNION --- 기준주소포인트가 0 인경우, 그룹화없이개별반환 SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address = 0x0 ) AS VaDump GROUP BY Size 다음쿼리로 VAS 상태를평가할수있습니다. -- 모든 free 영역가용메모리구하기 SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB] FROM VASummary WHERE Free <> 0 -- 최대가용메모리크기구하기 SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB] FROM VASummary WHERE Free <> 0 최대가용메모리크기가 4MB 보다작다면 VAS 부족을경험할수있습니다. SQL Server 2005 는 VAS 부족을모니터하고이에반응합니다. SQL Server 2000 은 VAS 부족상태를능동적으로모니터하지않지만, 가상메모리부족오류가발생할때캐시를비우는동작을합니다. 메모리오류에대한일반적인문제해결단계 다음목록은메모리오류문제를해결하는데도움을주는일반적인단계를개략적으로소개합니다. 1. 서버가외부메모리부족상태인지확인합니다. 존재한다면먼저메모리부족문제를해결한뒤해당문제나오류가계속발생하는지확인합니다.

31 SQL Server 2005 성능문제해결 SQL Server: Buffer Manager, SQL Server: Memory Manger 의성능모니터카운터를수집합니다. 3. 메모리구성매개변수 (sp_configure), 쿼리당최소메모리, 최소 / 최대서버메모리, awe enabled, 그리고메모리페이지잠그기사용권한을확인합니다. 잘못된설정을찾고필요에따라수정합니다. SQL Server 2005 에서증가된메모리요구사항을검토합니다. 4. sp_configure 매개변수중간적접으로서버에영향을미칠수있는비기본값이있는지확인합니다. 5. 내부메모리부족여부를확인합니다. 6. 메모리오류메시지를만나면 DBCC MEMORYSTATUS 출력을살펴봅니다. 7. 작업부하 ( 동시세션수, 실행쿼리수 ) 를확인합니다. 메모리오류 701 시스템메모리가부족하여이쿼리를실행할수없습니다. 원인 가장일반적인서버메모리부족오류입니다. 메모리할당실패를나타내며현재작업부하상에서메모리한계를벗어난경우를포함해서다양한원인들이존재합니다. SQL Server 2005 메모리요구사항증가와특정구성설정 ( 최대서버메모리옵션같은 ) 으로사용자는 SQL Server 2000 보다이오류를더많이만날수있습니다. 일반적으로실패한트랜잭션은이오류의원인이아닙니다. 문제해결 오류의일관성이나반복성 ( 동일상태 ) 혹은임시발생 ( 서로다른상태로불특정시점에나타남 ) 여부에관계없이, 오류가발생하는동안서버메모리분배를조사합니다. 이오류가발생하면진단분석쿼리또한실패할가능성이있습니다. 외부요소평가에서부터조사를시작하십시오. 메모리오류에대한일반적인문제해결단계를따릅니다. 해결방안 : 외부메모리부족문제제거. 최대서버메모리설정증가. 다음명령중하나를사용해서캐시비우기 : DBCC FREESYSTEMCACHE, DBCC FREESESSIONCACHE, 혹은 DBCC FREEPROCCACHE. 문제가계속되면, 작업부하를줄입니다. 802 버퍼풀에사용할수있는메모리가부족합니다. 원인 이오류가반드시메모리부족상태를나타내는것은아닙니다. 버퍼풀메모리가사용중임을나타냅니다. SQL Server 2005 에서는드물게발생될것입니다. 문제해결 701 오류에서소개한일반적인문제해결단계의권장사항을따릅니다 쿼리최적화를기다리는중시간이초과되었습니다. 쿼리를다시실행하십시오. 원인

32 32 SQL Server 2005 성능문제해결 이오류는쿼리컴파일작업을완료하는데필요한메모리확보에실패한경우를나타냅니다. 쿼리는구문해석, 대수연산처리, 그리고최적화를포함한컴파일처리를거쳐야합니다. 따라서다른쿼리와의메모리리소스경합이일어납니다. 쿼리가리소스를기다리는동안정의된시간을초과하면오류가반환됩니다. 이오류의주된원인은서버에서대량의쿼리컴파일이발생하는경우입니다. 문제해결 1. 서버메모리소비로인한영향인지알아보기위해일반적인문제해결단계를따릅니다. 2. 작업부하를검사합니다. 각구성요소별소비되는메모리양을확인합니다. ( 본문서의내부실제메모리부족참조 ) 3. DBCC MEMORYSTATUS 출력에서각게이트웨이상의대기자 (waiters) 수를점검합니다. ( 이정보는많은메모리를소비하는다른쿼리가실행중인지를알려줍니다 ). Small Gateway Value Configured Units 8 Available Units 8 Acquires 0 Waiters 0 Threshold Factor Threshold (6 개행적용됨 ) Medium Gateway Value Configured Units 2 Available Units 2 Acquires 0 Waiters 0 Threshold Factor 12 (5 개행적용됨 ) Big Gateway Value Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 4. 할수있다면작업부하를줄입니다 쿼리를실행하기위해메모리리소스를기다리는중시간이초과되었습니다. 쿼리를다시실행하십시오. 12 제품에포함된한글오류메시지와는다르게번역하였습니다.

33 SQL Server 2005 성능문제해결 33 원인 이오류는메모리집중적인쿼리가동시에많이실행되고있음을나타냅니다. 정렬 (ORDER BY) 과조인을사용하는쿼리는실행하는동안많은메모리를소비할수있습니다. 최대병렬처리수준설정값이높거나쿼리가정렬되지않은인덱스를가진분할테이블상에서동작하는경우요구되는메모리양은상당히증가합니다. 쿼리에서필요한메모리리소스를기다리는동안정의된시간을초과하면 ( 기본값은 sp_configure query wait 설정값이나쿼리예측비용의 25 배 ) 이오류를받게됩니다. 일반적으로이오류를받은쿼리는메모리를소비하는쿼리가아닙니다. 문제해결 1. 서버메모리상태를평가하는일반적인단계를따릅니다. 2. 의심되는쿼리를확인합니다 : 분할테이블을사용하는많은수의쿼리가있다면, 정렬되지않은인덱스를사용하는지점검합니다, 조인혹은정렬를포함한쿼리가많은지점검합니다. 3. sp_configure 매개변수인최대병렬처리수준과쿼리당최소메모리를점검합니다. 최대병렬처리수준을낮추어보거나쿼리당최소메모리가높게설정되어있지않는지확인합니다. 높은값이라면, 작은쿼리조차도지정된양의메모리를요구하게됩니다. 4. 쿼리가 RESOURCE_SEMAPHORE 로대기중인지알아봅니다, 이는이후에차단에서소개됩니다 최소쿼리메모리를사용할수없어서요청한작업을수행할수없습니다. 쿼리당최소메모리 서버구성옵션의구성값을줄이십시오. 원인 8645 오류와유사한원인입니다. 또한일반적인서버메모리부족상태를나타낼수있습니다. 쿼리당최소메모리 (min memory per query) 옵션이너무높은경우이오류가발생할수있습니다. 문제해결 1. 일반적인메모리오류문제해결단계를따릅니다. 2. sp_configure 의쿼리당최소메모리옵션설정을확인합니다. I/O 병목 SQL Server 성능은 I/O 하위시스템의사용량에따라결정됩니다. 데이터베이스가실제메모리크기에적합하지않다면 SQL Server 는데이터베이스페이지에서버퍼풀로가져오거나혹은가져가는동작이빈번하게일어날것입니다. 이는상당한 I/O 트래픽을발생시킵니다. 더불어로그레코드또한트랜잭션이커밋되기전에디스크로기록됩니다. 마침내 SQL Server 는중간결과저장, 정렬, 행버전관리등의다양한목적으로 tempdb 를사용합니다. 따라서충분한 I/O 하위시스템이 SQL Server 성능에핵심이됩니다. tempdb 를포함한데이터파일이랜덤하게액세스되는반면, 로그파일액세스는트랜잭션이롤백되는경우를제외하고는순차적입니다. 따라서일반적인규정대로, 보다좋은성능을위해로그파일은데이터파일과분리된물리적디스크상에두어야합니다. 본문서의촛점은 I/O 장치를

34 34 SQL Server 2005 성능문제해결 구성하는방법이아니라 I/O 병목이있는지를식별하기위한방법을설명하는것입니다. I/O 병목이확인되면, I/O 하위시스템의재구성을고려할필요가있습니다. 느린 I/O 하위시스템을가지고있다면, 사용자들은느린응답시간이나시간초과로인한작업취소와같은성능문제를경험할것입니다. I/O 병목을식별하기위해서다음성능카운터를사용할수있습니다. 참고로, 이러한평균값은수집간격을짧게가져야합니다. 예를들어, 60 초간격으로수집하는경우엔 I/O 가치솟는특성을알려주기가어렵습니다. 또한, 하나의카운터만으로병목을판단해서는안됩니다. 여러카운터값을참조해서검증해야합니다. PhysicalDisk Object: Avg. Disk Queue Length 물리적디스크의수집기간동안큐에대기한평균물리적읽기와쓰기수입니다. I/O 하위시스템의부하가크다면더많은읽기 / 쓰기작업이대기상태가될것입니다. SQL Server 가장많이사용되는기간동안카운터가지속적으로 2 를초과한다면 I/O 병목을가정할수있습니다. Avg. Disk Sec/Read 디스크에서데이터를읽는평균시간 ( 초 ) 입니다. 10ms 이하 매우좋음 10-20ms 사이 괜찮음 20-50ms 사이 느림, 주의요망 50ms 이상 심각한 I/O 병목 Avg. Disk Sec/Write 디스크에데이터를쓰는평균시간 ( 초 ) 입니다. 이전가이드를참조하십시오. Physical Disk: %Disk Time 디스크드라이브가읽기및쓰기요청을서비스하는데소비한경과시간의비율입니다. 일반적인가이드로는 50% 이상일경우 I/O 병목이라고말할수있습니다. Avg. Disk Reads/Sec 디스크의읽기작업비율입니다. 디스크용량의 85% 를초과하지않아야합니다. 디스크액세스시간은 85% 를초과하면서기하급수적으로증가합니다. Avg. Disk Writes/Sec 디스크의쓰기작업비율입니다. 디스크용량의 85% 를초과하지않아야합니다. 디스크액세스시간은 85% 를초과하면서기하급수적으로증가합니다. 위카운터를사용할때는, 다음수식을사용해서 RAID 구성에대한적정값을찾아야합니다. Raid 0 -- 디스크당 I/O = ( 읽기 + 쓰기 ) / 디스크수 Raid 1 -- 디스크당 I/O = [ 읽기 + (2 * 쓰기 )] / 2 Raid 5 -- 디스크당 I/O = [ 읽기 + (4 * 쓰기 )] / 디스크수 Raid 디스크당 I/O = [ 읽기 + (2 * 쓰기 )] / 디스크수예를들어, 두개의물리적디스크로구성된 RAID-1 시스템에서다음과같은카운터값을보인다면, Disk Reads/sec 80 Disk Writes/sec 70 Avg. Disk Queue Length 5 이경우, 디스크당 I/O 는 110 = (80 + (2 * 70)) / 2 이며디스크대기는 2.5 = 5/2 이므로, I/O 병목경계선에있음을나타냅니다. 또한래치 (latch) 대기를검사해서 I/O 병목을식별할수도있습니다. 래치대기는버퍼풀에없는페이지를읽거나혹은쓰기위해액세스하는페이지에대한물리적인 I/O 대기입니다. 페이지를버퍼풀에서찾지못하면비동기 I/O 를지시하고 I/O 상태를검사합니다. I/O 가이미

35 SQL Server 2005 성능문제해결 35 완료되었으면, 정상적으로진행합니다. 그렇치않으면, 요청작업의유형에따라 PAGEIOLATCH_EX 혹은 PAGEIOLATCH_SH 대기가발생합니다. 다음 DMV 쿼리로 I/O 래치대기정보를알수있습니다. Select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms 13 from sys.dm_os_wait_stats where wait_type like 'PAGEIOLATCH%' order by wait_type wait_type waiting_tasks_count wait_time_ms signal_wai t_time_ms PAGEIOLATCH_DT PAGEIOLATCH_EX PAGEIOLATCH_KP PAGEIOLATCH_NL PAGEIOLATCH_SH PAGEIOLATCH_UP I/O 가완료되면작업자가실행가능큐에놓입니다. I/O 완료시간과해당 작업자가실제로예약될때까지의시간차이가 signal_wait_time_ms 열에계산됩니다. waiting_task_counts 와 wait_time_ms 가 평소와는상당히다를때 I/O 문제를식별할수있습니다. 이를위해, SQL Server 가원할하게수행중일때성능카운터와핵심 DMV 쿼리결과에 대한기준선을가지고있는것이중요합니다. wait_types 정보가 I/O 하위시스템이병목을가지고있는나타낼수있지만, 물리적디스크에 발생하는문제에대한어떤가시성을제공하지는않기때문입니다. 현재지연되고있는 I/O 요청을알아내기위해다음 DMV 쿼리를사용할수있습니다. I/O 하위시스템의검진을위해이쿼리를정기적으로실행합니다. select database_id, file_id, io_stall, io_pending_ms_ticks, scheduler_address from sys.dm_io_virtual_file_stats(null, NULL)t1, sys.dm_io_pending_io_requests as t2 where t1.file_handle = t2.io_handle 예제출력은다음과같습니다. 특정데이터베이스에 3 가지 I/O 대기를보여줍니다. database_id 와 file_id 를이용해서물리적디스크를알아낼수있습니다. io_pending_ms_ticks 는큐에서대기중인개별 I/O 의총시간을나타냅니다. 13 원본에는생략되었던열입니다. 결과와맞추기위해추가합니다.

36 36 SQL Server 2005 성능문제해결 문제해결 Database_id File_Id io_stall io_pending_ms_ticks scheduler_address x0227A x0227A x I/O 병목을식별하면, 다음중하나이상의작업을수행해서해당문제에대처할수있습니다 : SQL Server 메모리구성을검사합니다. 메모리가부족하면더많은 I/O 오버헤드가발생합니다. 다음카운터를조사해서메모리부족을확인합니다. Buffer Cache hit ratio Page Life Expectancy Checkpoint pages/sec Lazywrites/sec 메모리부족에대한추가정보는본문서의메모리부족을참조하십시오. I/O 대역폭증가. 현재디스크어레이에물리적인드라이브를추가하거나더빠른드라이브로대체합니다. 이것으로읽기및쓰기성능을향상시키는데도움을줍니다. 더빠른혹은새로운 I/O 컨트롤러를추가합니다. 현재컨트롤러에캐시를추가하는것도고려할수있습니다. 실행계획을통해서어떤계획이많은 I/O 를소비하는지점검합니다. 더좋은계획 ( 예를들어, 인덱스 ) 이 I/O 를최소화할수있습니다. 인덱스가없다면, 필요한인덱스를알아내기위해데이터베이스엔진튜닝관리자를실행해볼수있습니다. 다음 DMV 쿼리로 I/O 의대부분을차지하는해당일괄처리나요청작업을찾을수있습니다. 물리적쓰기는제공되지않음을알수있습니다. 데이터베이스작업방식을검토하는것으로는충분합니다. DML/DDL 문장은데이터페이지를바로디스크에쓰지않습니다. 대신에, 디스크로의물리적인쓰기는트랜잭션을커밋하는문장에의해서만수행됩니다. 일반적으로물리적쓰기는검사점 (Checkpoint) 이나 SQL Server 지연기록기 (lazy writer) 에의해서수행됩니다. DMV 쿼리는 I/O 의대부분을발생시키는상위 5 개요청을찾아냅니다. 해당쿼리가더적은논리적읽기를수행하도록튜닝함으로써버퍼풀부족문제를해소할수있습니다. 다음에수행되는다른요청이필요한데이터를버퍼풀에서바로찾을수있도록도와줍니다. 결국엔시스템전체성능이향상됩니다. select top 5 (total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes, (total_physical_reads/execution_count) as avg_phys_reads, Execution_count, statement_start_offset as stmt_start_offset, sql_handle, plan_handle from sys.dm_exec_query_stats order by (total_logical_reads + total_logical_writes) Desc

37 SQL Server 2005 성능문제해결 37 물론다른분석결과을얻기위해뷰를변경할수도있습니다. 예를들어, 단일실행에서가장많은 I/O 를차지하는상위 5 개요청을알수있습니다 : (total_logical_reads + total_logical_writes) / execution_count 또는, 물리적 I/O 등을기준으로정렬할수도있습니다. 그러나, 논리적읽기 / 쓰기숫자가쿼리계획의최적여부를판단하는데매우유용합니다. 예를들어, 인덱스를사용하는대신테이블스캔을수행하는것입니다. 어떤쿼리는중첩반복조인으로높은논리적카운터를보이지만동일페이지를재사용하는측면에서캐시에보다효율적입니다. 예 : 각각 1000 행과 8000 이상의행크기 ( 페이지당한행 ) 가진두테이블을사용하도록구성된두개의일괄처리 (Batch) 가있습니다. 일괄처리 -1 select c1, c5 from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4 order by c2 일괄처리 -2 select * from t1 예제를위한목적으로, DMV 쿼리를실행하기전에다음명령을사용해서버퍼풀과프로시저캐시를정리합니다. checkpoint dbcc freeproccache dbcc dropcleanbuffers DMV 쿼리출력은다음과같습니다. 두일괄처리를나타내는두행을볼수있습니다. Avg_logical_reads Avg_logical_writes Avg_phys_reads Execution_count stmt_start_offset sql_handle plan_handle x EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB x ec8520a x EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB x ec8520a 두번째일괄처리는논리적읽기가발생하고물리적 I/O 가없음을알수있습니다. 이것은첫번째쿼리실행에의해서필요한데이터가이미캐시에있기때문입니다. ( 충분한메모리가있다고가정 ) 다음쿼리를실행해서해당쿼리를알수있습니다. select text from sys.dm_exec_sql_text( 0x EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB) -- 출력은다음과같습니다.

38 38 SQL Server 2005 성능문제해결 Tempdb select c1, c5 from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4 order by c2 또한다음과같은형식의쿼리를사용해서실제로수행된개별문장을알수있습니다 : select substring(text, (<statement_start_offset>/2), (<statement_end_offset> - <statement_start_offset>)/2) from sys.dm_exec_sql_text (0x EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB) statement_start_offfset 과 statement_end_offset 값은 SQL Server 가이러한유형의데이터를유니코드로저장한다는점을보정하기위해서 2 로나눕니다. statement_end_offset 이 -1 이면, 해당문장이일괄처리끝까지임을나타냅니다. 그러나 substring() 함수는 -1 을적절한값으로받아들이지않으므로 -1 대신 (<statement_end_offset> - <statement_start_offset>)/2 14 를사용합니다. 이방법으로, 대형저장프로시저나일괄처리에서장시간혹은많은리소스를소비하는해당문장만을추출할수있습니다. 유사하게, 다음쿼리를사용해서, 대량의 I/O 가잘못된실행계획선택의결과인지알아볼수있습니다. select * from sys.dm_exec_query_plan (0x EC8520A ) Tempdb 는 SQL Server 운영중에생성되는내부개체와사용자개체, 임시테이블, 개체, 저장프로시저들을모두저장합니다. SQL Server 인스턴스별로하나의 tempdb 가존재하므로, 성능및디스크공간의병목이일어날수있습니다. tempdb 는과도한 DDL/DML 작업과가용공간측면에서많은부하가발생합니다. 이것은또한서버에서실행중인다른응용프로그램을느리게만들거나문제가발생하는원인이될수도있습니다. 다음은 tempdb 와관련된공통적인문제들입니다 : tempdb 저장공간부족. tempdb I/O 병목으로인한쿼리성능저하. 이는본문서의 I/O 병목에서다룹니다. 시스템테이블에병목을유발하는과도한 DDL 작업. 할당경합. tempdb 문제를진단하기전에, 먼저 tempdb 저장공간이어떻게사용되지는살펴보겠습니다. 14 원문의 offset 계산공식은잘못되었습니다. 온라인설명서에서 sys.dm_exec_sql_text DMV 의예제를참조하십시오.

39 SQL Server 2005 성능문제해결 39 사용자개체 사용세션에의해서명시적으로생성되며시스템카탈로그에서추적됩니다. 해당항목들은다음과같습니다 : 테이블과인덱스. 전역 (global) 임시테이블 (##t1) 과인덱스. 로컬 (local) 임시테이블 (#t1) 과인덱스. 세션범위. 현재범위내의저장프로시저 테이블변수 (@t1). 세션범위. 현재범위내의저장프로시저. 내부개체 다음은쿼리를처리하기위해 SQL Server 가생성및제거하는문장범위개체들입니다. 시스템카탈로그에의해서추적되지않습니다 : 작업파일 ( 해시조인 ) 정렬수행 작업테이블 ( 커서, 스풀과임시대용량개체데이터형식 (LOB) 저장소 ) 최적화를위해서작업테이블이삭제되면, 하나의 IAM 페이지와익스텐트는새로운작업테이블에사용되도록보관됩니다. 두가지예외가있습니다. 일괄처리범위의임시 LOB 저장소와세션범위의커서작업테이블입니다. 버전관리저장소 여유공간 새로운행버전관리저장을위해서사용됩니다. MARS, 온라인인덱스, 트리거와스냅숏기반격리수준등이행버전관리에기반합니다. 이것은 SQL Server 2005 의새기능입니다. tempdb 에서사용가능한디스크공간을나타냅니다. tempdb 디스크공간모니터링 tempdb 에의해서사용되는전체공간은사용개체 + 내부개체 + 버전관리저장소 + 여유공간에해당합니다. 이여유공간은성능카운터의 free space in tempdb 와같습니다. 문제의해결보다는예방이더중요합니다. tempdb 사용공간을모니터하기위해다음성능카운터를사용할수있습니다.

40 40 SQL Server 2005 성능문제해결 디스크공간문제해결 Free Space in tempdb (KB). 이카운터는 tempdb 의여유공간을킬로바이트단위로추적합니다. 관리자는이카운터를통해서 tempdb 의저장공간부족여부를판단할수있습니다. 그러나위에서정의한각범주별로 tempdb 에서사용되는디스크공간을확인하는것이보다흥미롭고유익한조사대상입니다 다음쿼리는사용자와내부개체에의해서사용된 tempdb 공간을반환합니다. 현재는 tempdb 에대해서만정보를제공합니다. Select SUM (user_object_reserved_page_count)*8 as user_objects_kb, SUM (internal_object_reserved_page_count)*8 as internal_objects_kb, SUM (version_store_reserved_page_count)*8 as version_store_kb, SUM (unallocated_extent_page_count)*8 as freespace_kb From sys.dm_db_file_space_usage Where database_id = 2 다음은출력결과입니다 (KB 단위 ). user_objets_kb internal_objects_kb version_store_kb free space_kb 혼합익스텐트내페이지에대해서는계산하지않고있음을참고하십시오. 혼합익스텐트내페이지는사용자개체와내부개체에의해서할당될수 있습니다. 사용자개체, 내부개체, 그리고버전저장소모두 tempdb 디스크공간문제를유발할수있습니다. 이번절에서는, 각범주별문제해결방법을검토합니다. 사용자개체사용자개체는특정세션에소유되지않으므로, 개체를생성하는응용프로그램의요구사항을이해하고, 필요에따라 tempdb 크기를조정합니다. 사용자개체에대한사용공간은 exec <user-object> 를실행해서알수있습니다.

41 SQL Server 2005 성능문제해결 41 DECLARE userobj_cursor CURSOR FOR select sys.schemas.name + '.' + sys.objects.name from sys.objects, sys.schemas where object_id > 100 and type_desc = 'USER_TABLE'and sys.objects.schema_id = sys.schemas.schema_id go open userobj_cursor go varchar(256) fetch userobj_cursor while (@@FETCH_STATUS = 0) begin exec fetch userobj_cursor end close userobj_cursor 버전저장소 SQL Server 2005 는기존기능과새로운기능을구현하는데사용되는행버전관리프레임워크를제공합니다. 현재, 다음기능들이행버전관리프레임워크를사용합니다. 각기능에대한추가정보는, SQL Server 온라인설명서를참조하십시오. 트리거 MARS 온라인인덱스 행버전기반격리수준 : 데이터베이스수준의옵션설정이필요 행버전관리는세션간의공유됩니다. 행버전생성자는행버전이재사용되는경우의제어권을가지고있지않습니다. 행버전을모두정리할때이를방해하는가장오랜시간실행되는트랜잭션을찾아서가능한제거하는것이필요합니다. 다음쿼리는버전저장소에서버전에따라가장오랜시간실행되는트랜잭션상위 2 건을반환합니다. select top 2 transaction_id, transaction_sequence_num, elapsed_time_seconds from sys.dm_tran_active_snapshot_database_transactions order by elapsed_time_seconds DESC 다음출력은 6523 초동안활성화상태인 Transaction ID 8906 이고 XSN 3 인트랜잭션에제를보여줍니다.

42 42 SQL Server 2005 성능문제해결 transaction_id transaction_sequence_num elapsed_time_seconds 두번째트랜잭션은상대적으로짧은기간이므로, 첫번째트랜잭션을제거하는것으로버전저장소의상당한양을정리할수있습니다. 그러나, 얼마나많은공간이반환될지는예측할수없습니다. 필요한공간을정리하기위해서는좀더많은트랜잭션을제거할필요가있습니다. 버전저장소를포함해서 tempdb 용량을적절히계산하거나, 혹은스냅숏격리상태에서오랫동안실행되는트랜잭션이나, read-committed-snapshot 상태에서오랫동안실행되는쿼리를제거함으로써이러한문제를줄일수있습니다. 다음수식을사용해서대략적인버전저장소크기를예측할수있습니다. ( 인수 2 는최악의경우, 장시간실행되는트랜잭션 2 개가겹치는시나리오를포함시킨것입니다.) [Size of version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction] 모든데이터베이스에서격리수준에기반한행버전관리가가능하며, 트랜잭션을위해분단위로생성되는버전저장소데이터는분단위로생성되는로그와대략유사합니다. 그러나몇가지예외가있습니다 : 유일한차이점은변경에대한로그입니다 ; 그리고새로게입력된데이터행은버전관리되지않습니다다만대량로그작업및복구모델이전체복구로설정되지않은경우에따라서로그가발생할수있습니다. 좀더정확한계산을위해 Version Generation Rate 와 Version Cleanup Rate 성능카운터를사용할수도있습니다. Version Cleanup Rate 가 0 이면이는버전저장소정리를방해하는장시간수행되는트랜잭션이존재함을암시합니다. 덧붙여, tempdb 공간부족오류가발생하기전에, SQL Server 2005 는버전저장소축소를강제하도록마지막시도를합니다. 축소작업중에, 아직행버전이생성되지않은가장긴트랜잭션은희생자 (victims) 로표시됩니다. 이를통해사용되는버전저장소공간을확보할수있습니다. 메시지 이그와같은각희생자트랜잭션을위해오류로그에생성됩니다. 트랜잭션이희생자로표시되면버전저장소에서해당행버전을읽거나새로운것을생성할수없게됩니다. 메시지 3966 은희생자트랜잭션이행버전을읽으려고할때그트랜잭션이롤백되면서발생합니다. 버전저장소의축소가성공하면 tempdb 에서추가공간이확보됩니다. 그렇치않으면, tempdb 는저장소가부족하게됩니다. 내부개체내부개체는각문장에의해서생성되고제거됩니다. tempdb 가지나치게크다면어떤세션이나작업이그렇게공간을많이소비하는지해당주범을찾아서해결할필요가있습니다. SQL Server 2005 는두가지추가 DMV 를제공합니다 : sys.dm_db_session_space_usage 와 sys.dm_db_task_space_usage 는각각세션과작업별로할당된 tempdb 공간을추적합니다. 작업 (task) 은세션 15 한글오류메시지에는 victim 에대한직접적인해석대신 교착상태가발생... 으로표현되어있습니다. 해당메시지를직접참조하십시오.

43 SQL Server 2005 성능문제해결 43 범위내에서실행되므로, 작업에의해서사용된공간은작업이완료된후에세션에포함됩니다. 다음쿼리를사용해서내부개체를할당하고있는상위세션을알수있습니다. 이쿼리는세션에서완료된작업만포함됨을참고하십시오. select session_id, internal_objects_alloc_page_count, internal_objects_dealloc_page_count from sys.dm_db_session_space_usage order by internal_objects_alloc_page_count DESC 현재활성작업을포함해서내부개체를할당하고있는상위사용자세션을알아내기위해다음쿼리를사용할수있습니다. SELECT t1.session_id, (t1.internal_objects_alloc_page_count + task_alloc) as allocated, (t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated from sys.dm_db_session_space_usage as t1, (select session_id, sum(internal_objects_alloc_page_count) as task_alloc, sum (internal_objects_dealloc_page_count) as task_dealloc from sys.dm_db_task_space_usage group by session_id) as t2 where t1.session_id = t2.session_id and t1.session_id >50 order by allocated DESC 다음은출력결과입니다. session_id allocated deallocated 내부개체할당이많이발생하는작업이별도로분리되어있다면, 해당 Transact-SQL 문장이무엇인지그리고어떤쿼리계획인지를알아내서 보다상세한분석이가능합니다.

44 44 SQL Server 2005 성능문제해결 select t1.session_id, t1.request_id, t1.task_alloc, t1.task_dealloc, t2.sql_handle, t2.statement_start_offset, t2.statement_end_offset, t2.plan_handle from (Select session_id, request_id, sum(internal_objects_alloc_page_count) as task_alloc, sum (internal_objects_dealloc_page_count) as task_dealloc from sys.dm_db_task_space_usage group by session_id, request_id) as t1, sys.dm_exec_requests as t2 where t1.session_id = t2.session_id and (t1.request_id = t2.request_id) order by t1.task_alloc DESC 다음은출력결과입니다. session_id request_id task_alloc task_dealloc sql_handle statement_start_offset x D490961BDD2A8BE3B0FB81ED67655EFEEB statement_end_offset plan_handle x D490961BA8C 다음과같이 sql_handle 과 plan_handle 을사용해서해당 SQL 문장과쿼리계획을얻을수있습니다 : select text from sys.dm_exec_sql_text(@sql_handle) select * from sys.dm_exec_query_plan(@plan_handle) 참고로원하는쿼리계획이캐시에없을수도있습니다. 쿼리계획을원한다면, 계획캐시를자주확인하고그결과를저장해서 ( 테이블에저장하는것을선호함 ), 나중에참조할수있습니다. SQL Server 가재시작할때마다, tempdb 크기는초기구성크기로되돌아가고필요에따라증가합니다. 이는 tempdb 크기를확장시키지위해자동증가를하는동안새로운익스텐트할당으로인한차단과, 데이터베이스의조각화를유도하고추가오버헤드를발생시킵니다. 결국작업성능에영향을주게됩니다. tempdb 의초기크기를보다큰값으로적절히조정하는것이권장사항입니다.

45 SQL Server 2005 성능문제해결 45 과도한 DDL 및할당작업 다음과같은상황에서두가지원인의경합이 tempdb 에일어날수있습니다. 대량의임시테이블및테이블변수의생성과삭제는메터데이터의경합을유발할수있습니다. SQL Server 2005 에서는, 메터데이터경합을최소화하기위해로컬임시테이블및테이블변수가캐시에저장합니다. 그러나다음조건을만족해야하며, 그렇치않을경우캐시되지않습니다. 테이블에명명된제약조건이없음. 테이블생성문장이후에다른 DDL 이없음 ( 예, CREATE INDEX, CREATE STATISTICS). 일반적으로, 대부분의임시 / 작업테이블은힙 (Heap) 에해당합니다 ; 그러므로 insert, delete, 혹은 drop 작업은페이지의사용가능한공간 (Page Free Space, PFS) 페이지에많은경합을유발합니다. 이러한테이블대부분이 64KB 미만이므로혼합익스텐트를사용하게되며, 이로인해공유전역할당맵 (Shared Global Allocation Map, SGAM) 페이지에많은경합이일어납니다. SQL Server 2005 는할당경합을최소화하기위해서하나의데이터페이지와하나의 IAM 페이지를캐시에저장합니다. 이러한캐시동작은 SQL Server 2000 에서작업테이블을대상으로이미수행되었습니다. SGAM 과 PFS 페이지는데이터파일에서고정된간격으로발생함으로, 해당리소스내용을찾기가어렵지않습니다. 예를들어, 2:1:1 는 tempdb 의첫번째 PFS 페이지 ( 데이터베이스 -id = 2, 파일 -id = 1, 페이지 -id = 1) 를나타내며, 2:1:3 은첫번째 SGAM 페이지를나타냅니다. SGAM 페이지는 페이지간격으로발생하며 PFS 페이지는 8088 페이지단위로발생합니다. 이러한정보를통해서 tempdb 의모든파일에모든 PFS 및 SGAM 페이지를찾을수있습니다. 이러한페이지에래치를얻기위해작업이대기하는시간을, sys.dm_os_waiting_tasks 를통해서볼수있습니다. 래치대기는일시적이므로차후에분석을위해서는이테이블을자주쿼리하고 ( 대략 10 초간격 ) 해당데이터를수집해야합니다. 예를들어, 다음쿼리는 analysis 데이터베이스의 waiting_tasks 테이블로 tempdb 페이지에대기중인모든작업정보를입력합니다. -- 현재시간구하기 datetime = getdate() -- 차후분석을위해테이블에입력 insert into analysis..waiting_tasks select session_id, wait_duration_ms, from sys.dm_os_waiting_tasks where wait_type like PAGE%LATCH_% and resource_description like 2:% tempdb 페이지에래치를얻기위해대기중인작업을볼때, 이것이 PFS 나 SGAM 페이지때문인지분석할수있습니다. 그렇다면, 이는 tempdb 에할당경합을암시하는것입니다. tempdb 의다른페이지에

46 46 SQL Server 2005 성능문제해결 느린쿼리 경합이보이며그페이지가시스템테이블에속한다면이것은과도한 DDL 작업으로인한경합을암시합니다. 다음성능카운터를통해서이례적인임시개체할당이나위치처리활동의증가를모니터할수있습니다. SQL Server:Access Methods\Workfiles Created /Sec SQL Server:Access Methods\Worktables Created /Sec SQL Server:Access Methods\Mixed Page Allocations /Sec SQL Server:General Statistics\Temp Tables Created /Sec SQL Server:General Statistics\Temp Tables for destruction 문제해결 tempdb 경합이과도한 DDL 작업때문이라면, 사용중인응용프로그램을살펴보고 DDL 작업의최소화가가능한지알아봅니다. 다음제안사항들을시도해볼수있습니다. 저장프로시저범위의임시테이블을사용한다면, 저장프로시저외부로이동시킬수있는지를고려합니다. 그렇치않으면, 저장프로시저가실행할때마다임시테이블의생성및삭제가일어날것입니다. 쿼리계획이많은임시개체, 스풀, 정렬, 혹은작업테이블을생성하는지점검합니다. 임시개체를제거할필요가있습니다. 예를들어, ORDER BY 에사용되는열에인덱스를생성하면정렬을제거할수있습니다. SGAM 과 PFS 페이지에경합이원인이라면, 다음사항을시도함으로써경감시킬수있습니다 : 모든디스크와파일에걸쳐작업부하를분산시키도록 tempdb 데이터파일크기를동일한크기로증가시킵니다. 이상적으로는 CPU 개수 ( 프로세스선호도를고려해서 ) 와동일한수의데이터파일을만듭니다. 혼합익스텐트할당을하지않도록 TF 를사용합니다. 느리거나오래실행되는쿼리는리소스를과도하게소비할수있으며쿼리차단으로이어집니다. 과도한리소스소비는 CPU 리소스에만국한되지않고, I/O 저장소대역폭과메모리대역폭또한포함될수있습니다. SQL Server 쿼리가 WHERE 절을통해서결과집합을제한함으로써테이블전체스캔을피하도록설계되었더라도, 해당쿼리를지원하는적절한인덱스가없는경우에예상한대로수행되지않게됩니다. 또한, 사용자입력에따라응용프로그램에서동적으로 WHERE 절이구성되는경우, 기존인덱스가모든경우의조건을다룰수가없게됩니다. Transact-SQL 문장에의한과도한 CPU, I/O, 그리고메모리소비는본문서전반부에서다루어졌습니다. 16 추적플래그 (Trace Flag) 를말합니다.

47 SQL Server 2005 성능문제해결 47 인덱스부재와함께사용되지않는인덱스도존재할수있습니다. 모든인덱스는유지관리대상이므로, 쿼리성능에는영향을미치지않더라도, DML 쿼리에는영향을줍니다. 논리적읽기를위한대기상태혹은쿼리를차단하고있는시스템리소스에대한대기상태로인해쿼리가느리게실행될수있습니다. 차단의원인은서투른응용프로그램설계, 잘못된쿼리계획, 유용한인덱스부재, 그리고작업부하를고려해서적절히구성되지못한 SQL Server 인스턴스와같은것들이될수있습니다. 이번절은느린쿼리의두가지원인, 차단과인덱스문제에촛점을맞춥니다. 차단 차단은주로논리적읽기에대한차단입니다. 이는특정리소스에 X 잠금획득을위한대기나래치같은저수준동기화개체로인한대기와같습니다. 논리적읽기대기는이미잠긴리소스에호환되지않은다른잠금을요청할때발생합니다. 이는 Transact-SQL 문장을실행할때트랜잭션격리수준에따라데이터일관성을제공하기위해서필요하지만, 최종사용자에게는 SQL Server 가느리게실행된다는인식을줍니다. 쿼리가차단된다는것이시스템에어떤리소스를소비하는것은아니므로, 시간은오래걸리지만리소스소비는작은쿼리를찾을수있습니다. 동시성제어및차단에대한상세정보는 SQL Server 온라인설명서를참조하십시오. 저수준의동기화개체에대한대기는시스템이작업부하를처리할수있도록구성되지못한결과입니다. 차단 / 대기에대한공통적인시나리오는 : 차단주범식별 오래걸리는차단식별 개체별차단 페이지래치문제 차단으로인한전반적인성능평가를위해 SQL Server 대기사용 요청을처리하는데필요한시스템리소스 ( 혹은잠금 ) 가현재이용할수없는상태이면 SQL Server 세션은대기상태에놓입니다. 다른말로하자면, 리소스가미해결요청에대한큐를가지는것입니다. DMV 는리소스대기중인세션에대한정보를제공합니다. SQL Server 2005 는보다상세하고일관된대기정보를제공합니다, SQL Server 2000 은 76 개의대기유형을지원한반면 SQL Server 2005 는대략 125 개이상의대기유형을보고해줍니다. DMV 는이러한정보들을두가지범위로나누어제공합니다. SQL Server 전체에누적된대기를위한 sys.dm_os_wait_statistics 17 와, 세션단위로분류된대기를위한세션범위의 sys.dm_os_waiting_tasks 입니다. 다음의 DMV 는특정리소스에대기중인작업의대기큐에대한상세정보를제공합니다. 예를들어, 17 온라인설명서에찾을수없으며, 쿼리로도실행되지않습니다. 역자의판단으로내용상 sys.dm_os_wait_stats 해당될것으로보입니다.

48 48 SQL Server 2005 성능문제해결 다음쿼리를실행해서차단된세션 56 에대한상세정보를찾을수있습니다. select * from sys.dm_os_waiting_tasks where session_id=56 waiting_task_address session_id exec_context_id wait_duration_ms wait_type resource_address blocking_task_address blocking_session_id blocking_exec_context_id resource_description x022A LCK_M_S 0 x x022A8D48 53 NULL ridlock fileid=1 pageid=143 dbid=9 id=lock3667d00 mode=x associatedobjectid= 이결과에서세션 53 이세션 56 에의해서특정잠금대기로 밀리초동안차단되고있음을보여줍니다. 잠금이허가된혹은잠금을대기중인세션을찾기위해서, sys.dm_tran_locks DMV 를사용할수있습니다. 각행은현재활성요청을나타내는것으로, 잠금관리자를통해잠금을허가받았거나혹은그요청에의해차단된상태로허가를기다리고있는요청입니다. 일반적인잠금에대해서는, 허가된요청은해당리소스에잠금이요청자에게허가되었음을나타냅니다. 대기중인요청은아직허가받지못했음을나타냅니다. 예를들어, 다음쿼리는세션 56 이리소스 1:143:3 에대해서세션 53 에의한 X 모드가있으므로차단되었음을보여줍니다.

49 SQL Server 2005 성능문제해결 49 select request_session_id as spid, resource_type as rt, resource_database_id as rdb, (case resource_type WHEN 'OBJECT' then object_name(resource_associated_entity_id) WHEN 'DATABASE' then ' ' ELSE (select object_name(object_id) from sys.partitions where hobt_id=resource_associated_entity_id) END) as objname, resource_description as rd, request_mode as rm, request_status as rs from sys.dm_tran_locks 다음은출력결과입니다. spid rt rdb objname rd rm rs DATABASE 9 S GRANT 53 DATABASE 9 S G RANT 56 PAGE 9 t_lock 1:143 IS G RANT 53 PAGE 9 t_lock 1:143 IX G RANT 53 PAGE 9 t_lock 1:153 IX G RANT 56 OBJECT 9 t_lock IS G RANT 53 OBJECT 9 t_lock IX G RANT 53 KEY 9 t_lock (a400c34cb X GRANT 53 RID 9 t_lock 1:143:3 X G RANT 56 RID 9 t_lock 1:143:3 S WAIT 사실저장프로시저 sp_block 에서보여지는대로위두 DMV 를조인할수 있습니다. 그림 1 은차단된세션과그세션을차단하는세션목록을 보여줍니다. 소스코드는부록 B 에서찾을수있습니다. 필요하면저장 프로시저를수행해서원하는속성을결과집합에추가하거나제거할수 옵션매개변수는특정 spid 를차단하는세션과잠금 요청에대한상세정보를제공합니다.

50 50 SQL Server 2005 성능문제해결 그림 1: sp_block 결과 원본크기이미지로보기 SQL Server 2000 에서는, 다음문장을사용해서차단된 spid 에대한정보를알수있습니다. select * from master..sysprocesses where blocked <> 0 저장프로시저 sp_lock 으로관련된잠금정보를볼수있습니다. 오래걸리는차단식별이전에언급한대로, SQL Server 에서차단은일반적인것이며트랜잭션의일관성을유지하기위해필요한논리적잠금의결과입니다. 그러나, 잠금에대한대기가한계를초과하면응답시간에영향을미칩니다. 오래걸리는차단을식별하기위해 BlockedProcessThreshold 구성매개변수를사용하면서버범위의차단임계값을구성할수있습니다. 임계값은초단위시간을정의합니다. 차단이임계값을초과하면 SQL Trace 에서추적할수있도록이벤트가발생합니다. 예를들어, 다음과같이 SQL Server Management Studio 에서차단프로세스임계값을 200 초로구성할수있습니다 : 1. Execute Sp_configure blocked process threshold, Reconfigure with override 차단프로세스임계값을구성하고나서, SQL Trace 나프로파일러를가지고추적이벤트를캡처합니다. 3. SQL Trace 를사용한다면, event_id = 137 로 sp_trace_setevent 를 설정합니다. 4. SQL Server 프로파일러를사용한다면, (Errors and Warnings 개체 밑에 ) Blocked Process Report 이벤트클래스를선택합니다. 그림 2 를 참조하십시오. 그림 2: 오래걸리는차단과교착상태추적 원본크기이미지로보기 참고이것은가벼운추적작업에해당합니다. (1) 차단이임계값을초과하거나 (2) 교착상태가발생할때만이벤트가캡처됩니다. 차단잠금에대해매 200 초간격으로추적이벤트가발생합니다. 이는

51 SQL Server 2005 성능문제해결 초동안차단된단일잠금의경우 3 번의추적이벤트가발생하는것입니다. 그림 3 을참조하십시오. 그림 3: 차단보고 > 차단임계값 원본크기이미지로보기 추적이벤트에는차단하고있는프로세스와차단당하는프로세스양쪽의전체 SQL 문장을포함합니다. 위경우 Update Customers 문장이 Select from Customers 문장을차단하고있습니다. 상대적으로, SQL Server 2000 에서는차단시나리오를검사하기위해 Sysprocesses 시스템테이블을조회하고결과를처리하는별도의코드를필요로합니다. sys.dm_db_index_operational_stats 를사용한개체별차단 SQL Server 2005 의새로운 DMV 인 sys.dm_db_index_operational_stats 는차단을포함해서총체적인인덱스사용통계정보를제공합니다. 차단으로표현하자면, 테이블, 인덱스, 분할별상세한잠금통계정보를제공합니다. 예를들어주어진인덱스나테이블에대한액세스, 잠금 (row_lock_count), 차단 (row_lock_wait_count), 그리고대기 (row_lock_wait_in_ms) 이력정보를포함합니다. 이 DMV 를통해서얻을수있는정보들은다음과같습니다 : 잠금보유누적개수, 예 ) 행혹은페이지. 차단혹은대기누적개수, 예 ) 행, 페이지. 차단혹은대기누적시간, 예 ) 행, 페이지. 페이지래치대기누적개수. page_latch_wait 대기누적시간 : 순차키입력과같은경우의특정페이지에대한경합과관련됩니다. 그런경우, 마지막페이지에동시에여러프로세스가쓰기작업을수행하기위해배타적페이지래치를요구하게되므로마지막페이지에핫스팟 (hot spot) 이일어나게됩니다. 이것이 Pagelatch 대기로나타나게됩니다. page_io_latch_wait 대기누적시간 : I/O 래치는사용자가요청한페이지가버퍼풀에존재하지않을때발생합니다. 느린 I/O 하위시스템, 혹은용량을초과한 I/O 하위시스템은높은 PageIOlatch 를경험할수있으며이것이실제 I/O 문제를나타냅니다. 이러한문제는

52 52 SQL Server 2005 성능문제해결 캐시플러시나인덱스부재등에의해서도일어날수있습니다. 페이지래치대기시간 18. 차단관련정보이외에도, 인덱스액세스에대한추가정보들이제공됩니다. 액세스유형, 예 ) 범위, 단일조회. 리프수준에서의 insert, update, delete. 리프수준상위수준에서의 insert, update, delete. 리프상위의동작은인덱스유지관리작업니다. 상위수준에는각리프페이지의첫행이입력됩니다. 리프에새로운페이지가할당되면, 해당페이지의첫행이상위페이지에입력됩니다. 리프수준에서의페이지병합은행삭제로인해할당이해제된빈페이지를나타냅니다. 인덱스유지관리. 리프수준상위에서의페이지병합은리프에서행삭제로인해중간수준페이지가빈상태로남겨지는, 할당해제된빈페이지입니다. 리프페이지의첫행은상위수준에입력되어있습니다. 리프수준에서충분한수의행이삭제되면, 리프페이지에대한첫행을입력으로포함하고있던중간수준의인덱스페이지들은빈페이지가됩니다. 이것은리프상위수준에서의병합을유발합니다. 이정보는 SQL Server 인스턴스가시작되면서부터누적됩니다. 이정보는인스턴스가재시작하면사라지며, 또한재설정하는방법도없습니다. 이 DMV 에의해서반환되는데이터는해당힙이나인덱스를나타내는메타데이터캐시가사용가능한경우에만존재합니다. 힙이나인덱스를메타데이터캐시로가져갈때마다각열값은 0 으로설정됩니다. 통계정보는메타데이터캐시에서캐시개체가제거될때까지누적됩니다. 따라서, 이후분석을위해서는주기적으로데이터를수집해서저장해두어야합니다. 부록 B 인덱스작업데이터를수집하는데사용할수있는저장프로시저들을제공합니다. 원하는주기로해당데이터를분석할수있습니다. 다음은부록 B 에소개한저장프로시저를사용하는단계입니다. 1. init_index_operational_stats 를사용해서 indexstats 를초기화합니다. 2. insert_indexstats 를사용해서기준상태를캡처합니다. 3. 작업부하를발생시킵니다. 4. insert_indexstats 를사용해서인덱스통계정보에대한최종정보를캡처합니다. 5. 수집된인덱스통계정보를분석하기위해서, 저장프로시저 get_indexstats 를실행합니다. 평균잠금개수 ( 인덱스와분할에대한 Row_lock_count), 차단, 그리고인덱스별대기정보를생성합니다. 높은값의 blocking % 혹은높은평균대기는인덱스전략문제나쿼리문제를나타냅니다. 다음은저장프로시저를사용해서얻을수있는정보들의몇가지예제 19 를보여줍니다. 18 원문에서실수로반복된항목이아닐까생각됩니다.

53 SQL Server 2005 성능문제해결 53 인덱스사용량을정렬기준으로모든데이터베이스의상위 5 개인덱스출력. usage' 잠금승격을시도한적이있는상위 5 개인덱스잠금승격출력. lock lock promotion attempts] > 0' 평균잠금대기시간이 2ms 를초과하는상위 5 개단일조회작업에대해 wait, scan, singleton 열을포함해서출력. @order='singleton row lock wait ms] > 2' 모든데이터베이스에서행잠금대기가 1 을초과하는상위 10 개에대해 avg, wait 열을포함해서대기시간을기준으로출력. lock wait lock waits] > 1' 평균행잠금대기시간을정렬기준으로상위 5 개행출력. row lock wait ms' 평균페이지래치대기시간을정렬기준으로상위 5 개행출력. page latch wait ms' 평균페이지 I/O 래치시간을정렬기준으로상위 3% 의인덱스통계출력. 5 pageio latch wait latch waits] > 0' 19 원본예제는각예제별설명과실제코드간의차이가있어서, 역자 임의로조정했습니다.

54 54 SQL Server 2005 성능문제해결 db=5 에서 block% 를기준으로 0.1 을초과하는상위 10 위정보출력. %] > 0.1' 그림 4 는차단분석보고예제를보여줍니다. 그림 4: 차단분석보고 원본크기이미지로보기 SQL Server 2000 에서는개체나인덱스사용량에대한통계정보를제공하지않습니다. 차단으로인한전반적인성능평가를위해 SQL 대기사용 SQL Server 2000 은 76 개의대기유형을보고합니다. SQL Server 2005 는응용프로그램성능추적을위해서 100 개이상의추가대기유형을제공합니다. 사용자연결이대기중인동안, SQL Server 는대기시간을누적합니다. 예를들어, 응용프로그램이 I/O, 잠금, 혹은메모리같은리소스를요청하면해당리소스를사용가능할때까지기다릴수있습니다. 작업부하가발생했을때해당하는성능프로필을얻을수있도록이러한대기정보들이모든연결에걸쳐요약되고분류됩니다. 따라서, SQL 대기유형은응용프로그램작업부하로부터발생하는사용자 ( 혹은쓰레드 ) 대기정보를식별하고분류합니다. 다음쿼리는 SQL Server 에상위 10 위대기정보을보여줍니다. 이대기정보는누적되는정보이지만 DBCC SQLPERF ([sys.dm_os_wait_stats], clear) 를사용해서재설정할수가있습니다. select top 10 * from sys.dm_os_wait_stats order by wait_time_ms desc 다음은출력결과입니다. 주목할만한몇가지핵심요점은 : 지연기록기 (lazy writer) 와같이백그라운드쓰레드로인한몇가지대기는정상적입니다. 몇가지세션은 SH 잠금을얻기위해장시간대기했습니다. signal wait 은작업자 (worker) 가리소스에액세스를허가받았을때부터 CPU 예약을획득한시점까지의대기시간입니다. 오랜 signal wait 은높은 CPU 경합을암시합니다. wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms LAZYWRITER_SLEEP SQLTRACE_BUFFER_FLUSH LCK_M_S WRITELOG

55 SQL Server 2005 성능문제해결 55 LOGMGR_RESERVE_APPEND SLEEP_BPOOL_FLUSH LCK_M_X PAGEIOLATCH_SH PAGEIOLATCH_UP IO_COMPLETION 대기정보를분석하기위해서, 정기적으로데이터를수집해야합니다. 부록 B 에서두가지저장프로시저를제공합니다. Track_waitstats. 원하는샘플숫자와샘플간격으로데이터를수집할수있습니다. 다음은호출예제입니다. exec Get_waitstats. 이전단계에서수집된데이터를분석합니다. 다음은호출예제입니다. exec [dbo].[get_waitstats_2005] spid 가실행중입니다. 그다음현재사용할수없는리소스를필요로합니다. 리소스를사용할수없으므로, 시간 T0 에리소스대기목록으로이동합니다. Signal 은리소스가사용가능함을나타냅니다, 따라서 spid 는시간 T1 에실행가능큐로이동합니다. spid 는 cpu 가실행가능큐에도착한순서대로작업을하고 T2 에실행상태가될때까지기다립니다. 이저장프로시저를사용해서리소스대기와신호 (signal) 대기를분석하거나자원경합을격리시킬수있습니다. 그림 5 보고서예제 그림 5: 대기에대한통계정보분석리포트 원본크기이미지로보기 그림 5 의대기분석리포트예제는성능문제가차단 (LCK_M_S) 과메모리할당 (RESOURCE_SEMAPHORE) 에있음을알려줍니다. 특히전체대기중메모리요구로인한문제가 43% 인반면, 55% 가공유잠금입니다. 개체별차단분석은경합의핵심요점을식별합니다. 인덱스사용량모니터링 쿼리성능의또다른관점은 DML 쿼리, 데이터를삭제, 추가, 변경하는쿼리와관련됩니다. 테이블에더많은인덱스를정의하면데이터를수정하는데더많은리소스를필요로합니다. 트랜잭션에서점유하는잠금과결합해서, 오랜시간동안의변경작업은동시성을방해하게