SQL Server Technology Specialist Exchange Technology Specialist Workflow

강사 소개 • 현) 마이크로소프트 SQL Server Technology Specialist • Exchange Technology Specialist • Workflow Engine개발 – Exchange 5. 5 Server – Exchange 2000 Server – SQL Server • Tech. Ed, Dev. Con, Tech. Net, MSDN 세션 다수 발표

대기 유형 • DBCC sqlperf(waitstats, [clear]) – Waittype과 waittime의 누적 데이터

데모 대기유형 u u [작업 1] : Wait Type [작업 2] : Blocking SQL

Basics of execution model(simplified) Need a resource RUNNING! Switched No new reque st Yield Runnable List New request Worker. Pool 15 min idle Dead Timer Request Expired /signal ed Timer List Res available Created Resource Waiter List – spid is running then needs unavailable resource, moves to resource wait list at time T 0 – a signal indicates resource available, spid moves to runnable queue at time T 1 – spid awaits running status until T 2 as cpu works its way through runnable queue in order of arrival

대기 시간 • Requests – Number of times the wait state was entered • Resource wait time (T 1 – T 0) – Total Wait Time – Signal Wait Time • Signal Wait Time (T 2 – T 1) – Time in runnable queue prior to execution • Total Wait Time (T 2 – T 0) – Total time waiting on resource plus the time the worker thread gets scheduled so it can use the resource T 0 = Request resource, enter wait state T 1 = Signal Resource Availability T 2 = Use resource, reset wait state Signal Wait Time

Track_waitstats 코드 CREATE proc track_waitstats (@num_samples int=10, @delaynum int=1, @delaytype nvarchar(10)='minutes') as -- T. Davidson -- This stored procedure is provided "AS IS" with no warranties, and confers no rights. -- Use of included script samples are subject to the terms specified at http: //www. microsoft. com/info/cpyright. htm -- @num_samples is the number of times to capture waitstats, default is 10 times. default delay interval is 1 minute -- delaynum is the delay interval. delaytype specifies whether the delay interval is minutes or seconds -- create waitstats table if it doesn't exist, otherwise truncate set nocount on if not exists (select 1 from sysobjects where name = 'waitstats') create table waitstats ([wait type] varchar(80), requests numeric(20, 1), [wait time] numeric (20, 1), [signal wait time] numeric(20, 1), now datetime default getdate()) else truncate table waitstats dbcc sqlperf (waitstats, clear) -- clear out waitstats declare @i int, @delay varchar(8), @dt varchar(3), @now datetime, @totalwait numeric(20, 1) , @endtime datetime, @begintime datetime , @hr int, @min int, @sec int select @i = 1 select @dt = case lower(@delaytype) when 'minutes' then 'm' when 'minute' then 'm' when 'min' then 'm' when 'mm' then 'm' when 'mi' then 'm' when 'm' then 'm' when 'seconds' then 's' when 'second' then 's' when 'sec' then 's' when 'ss' then 's' when 's' then 's' else @delaytype end if @dt not in ('s', 'm') begin print 'please supply delay type e. g. seconds or minutes' return end

Track_waitstats 코드 if @dt = 's' begin select @sec = @delaynum % 60 select @min = cast((@delaynum / 60) as int) select @hr = cast((@min / 60) as int) select @min = @min % 60 end if @dt = 'm' begin select @sec = 0 select @min = @delaynum % 60 select @hr = cast((@delaynum / 60) as int) end select @delay= right('0'+ convert(varchar(2), @hr), 2) + ': ' + + right('0'+convert(varchar(2), @min), 2) + ': ' + + right('0'+convert(varchar(2), @sec), 2) if @hr > 23 or @min > 59 or @sec > 59 begin select 'hh: mm: ss delay time cannot > 23: 59' select 'delay interval and type: ' + convert (varchar(10), @delaynum) + ', ' + @delaytype + ' converts to ' + @delay return end while (@i <= @num_samples) begin insert into waitstats ([wait type], requests, [wait time], [signal wait time]) exec ('dbcc sqlperf(waitstats)') select @i = @i + 1 waitfor delay @delay End --- create waitstats report execute get_waitstats

Get_waitstats 코드 CREATE proc get_waitstats as -- This stored procedure is provided "AS IS" with no warranties, and confers no rights. -- Use of included script samples are subject to the terms specified at http: //www. microsoft. com/info/cpyright. htm --- this proc will create waitstats report listing wait types by percentage -- can be run when track_waitstats is executing set nocount on declare @now datetime, @totalwait numeric(20, 1) , @endtime datetime, @begintime datetime , @hr int, @min int, @sec int select @now=max(now), @begintime=min(now), @endtime=max(now) from waitstats where [wait type] = 'Total' --- subtract waitfor, sleep, and resource_queue from Total select @totalwait = sum([wait time]) + 1 from waitstats where [wait type] not in ('WAITFOR', 'SLEEP', 'RESOURCE_QUEUE', 'Total', '***total***') and now = @now -- insert adjusted totals, rank by percentage descending delete waitstats where [wait type] = '***total***' and now = @now insert into waitstats select '***total***', 0, @totalwait, @now select [wait type], [wait time], percentage=cast (100*[wait time]/@totalwait as numeric(20, 1)) from waitstats where [wait type] not in ('WAITFOR', 'SLEEP', 'RESOURCE_QUEUE', 'Total') and now = @now order by percentage desc

Track_waitstats 결과 예제

데모 대기유형 추적 u u [작업 1] : Get Waitstats [작업 2] : Track waitstats

Get_waitstats 2: verbose

데모 대기유형 추적(2) u u [작업 1] : Get Waitstats 2 [작업 2] : Track waitstats

관련 대기와 큐 IO 이슈인가 아니면 메모리 이슈인가? 대기 IO_Completion Async_IO_Completion Page. IOLatch_x Page. Latch_x 큐 1. SQL Buffer Mgr – Avg Page Life Expectancy (초 단 위) – Checkpoint pages/sec – Lazywrites/sec 2. Physical Disk – Avg disk sec/read – Avg disk sec/write – Disk queues 설명 이런 대기가 발생하면 IO 이슈가 있음을 나타 낸다 Avg. disk 초가 높으면 IO 이슈가 있음을 나타 낸다 그렇지만 Avg page life 수치가 낮으면 메모리 압박이 있음을 나타낸 다 예, 캐시 플러싱

관련 대기와 큐 IO 이슈인가 아니면 DB 디자인 이슈인가? 대기 1. IO_Completion 2. Async_IO_Completion 3. Writelog 큐 1. SQL Buffer Mgr – Avg Page Life Expectancy (초 단 위) – Checkpoint pages/sec – Lazywrites/sec 2. Physical Disk – Avg disk sec/read – Avg disk sec/write – Disk queues 설명 1. 프로필러에 다음이 발생하면: – Scan started – Reads – Writes 2. 실행계획에 다음이 나타나면: – Table Scan – Clustered index range scans – Nonclustered index range scans – 정렬(sorts)

SQL 2000 Waits & Queues * SQL Server Magazine Jan 2004

데모 가상 시스템 테이블 u u [작업 1] : fn_virtualfilestats (dbid, [fileid]) [작업 2] : Input. Buffer

Syscacheobjects와 실행계획 재사용

Eventsubclass=2 Stats changed • Table Type Empty Condition Threshold when empty Permanent < 500 Rows # of Changes >= 500 + (20% of Cardinality) Temporary < 6 Rows # of Changes >= 6 500 + (20% of Cardinality) Table Variable No thresholds 통계 업데이트로 인한 재컴파일을 피하고 싶을 때 – Add OPTION(KEEPFIXED PLAN) – Use table variables (no stats maintained, scoping issue) – Segregate re-comp statement & do a nested call Threshold when not empty No thresholds

dbcc sqlperf(umsstats) Item Definition num users Number of SQL threads on the scheduler num runnable Number of SQL threads that are “runnable” (Scheduler queue length) num workers Number of actual workers to process threads (thread pool) idle workers Number of idle workers cntxt switches Context switches between runnable threads cntxt switches(idle) Context switches to the “idle” thread

참고 자료 • Kalen Delaney 저 “Inside SQL Server 2000” • Whalen, Garcia, De. Luca, Thompson 저 “SQL Server 2000 Performance Tuning” • http: //msdn. microsoft. com “SQL Server 2000 Recompilation” – http: //msdn. microsoft. com/library/default. asp? url=/librar y/en-us/dnsql 2 k/html/sql_queryrecompilation. asp

© 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.