엘로이티큐브 RSS FEED

페이지 위쪽

메모리 병목

이번 절은 특히 적은 메모리 구성에서 메모리에 대한 진단 분석, 메모리 관련 오류, 해당 원인 및 해결 방안 등을 다룹니다.

배경

서로 다른 메모리 리소스이지만 그냥 메모리라는 용어를 사용하는 것이 일반적입니다. 메모리 리소스의 유형이 여러가지이므로, 실제론 어떤 메모리 리소스를 말하는지 이해하고 구분하는 것이 중요합니다.

가상 주소 공간과 실제 메모리

마이크로소프트 Windows®는, 각 프로세스 단위로 가상 주소 공간(VAS)을 가집니다. 프로세스에서 가용한 모든 가상 주소 집합으로 VAS 크기를 구성합니다. VAS의 크기는 아키텍처(32 혹은 64비트)와 운영 시스템에 따라 결정됩니다. 메모리 문제 해결 측면에서 가상 메모리는 고갈될 수 있는 메모리 리소스이며 64비트 플랫폼에서 실제 메모리가 가용한 상황에서도 응용 프로그램이 메모리 부족을 경험할 수 있다는점을 이해하는 것이 중요합니다.

가상 주소 공간에 대한 추가 정보는 SQL Server 온라인 설명서의 “프로세스 주소 공간”에 대한 항목과 MSDN 기사 Virtual Address Space (영문) (http://msdn2.microsoft.com/en-us/library/aa366912.aspx[역주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 (영문) (http://msdn2.microsoft.com/en-us/library/aa366718.aspx[역주10])를 참조하십시오.

다음 표는 SQL Server 2005의 각 구성별 지원 가능한 최대 메모리 옵션을 요약한 것입니다. (SQL Server 혹은 Windows 각 에디션별로 지원 메모리 크기에 대한 제한이 있음을 참고하십시오.)

표 1

메모리 부족

메모리 부족이란 가용 메모리가 제한된 상태를 말합니다. SQL Server가 메모리 부족 상태에서 실행되고 있음을 확인하는 것은 메모리 관련 문제를 해결하는데 도움을 줍니다. SQL Server는 메모리 부족 유형에 따라 다른 응답을 합니다. 다음 표는 일반적인 메모리 부족 유형과 근본 원인을 요약합니다.

표 2

Windows는 실제 메모리가 높은 혹은 낮은 상태로 실행하면 알림을 주는 메커니즘를 가지고 있습니다. SQL Server는 이러한 메커니즘을 사용해서 메모리를 관리합니다.

표 3은 경우에 따른 일반적인 메모리 문제 해결 단계를 설명합니다.

표 3

도구

다음 도구와 정보들을 이용해서 문제를 해결할 수 있습니다.

메모리 부족 발견

메모리 부족 그 자체가 문제를 나타내지는 않습니다. 메모리 부족이란 서버의 메모리가 필요하지만 충분하지는 않은 상태로 나중에 메모리 오류가 발생할 수 있음을 말합니다. 메모리가 부족한 상태에서 작업하는 것이 서버에 정상적인 운영 상태일 수 있습니다. 그러나, 메모리 부족으로 인한 증상은 서버가 자신의 용량에 근접한 상태로 실행 중이며 잠재적인 메모리 부족 오류가 있음을 나타내는 것이기도 합니다. 정상적으로 운영 중인 서버의 경우엔 이러한 정보들이 차후 메모리 부족에 대한 판단 근거의 기준선으로 제공될 수 있습니다.

select  
    sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]  
from  
    sys.dm_os_memory_clerks

외부 가상 메모리 부족

페이지 파일이 현재 메모리 할당을 수용할만한 충분한 공간이 있는지를 판단해야 합니다. 이를 위해, 작업 관리자를 열고 성능 탭에서 할당된 메모리 항목를 검사합니다. 전체가 한도에 가깝다면, 페이지 파일 공간이 부족할 가능성이 있습니다. 한도는 페이지 파일 공간을 확장하지 않고 할당될 수 있는 최대 메모리 크기를 나타냅니다. 작업 관리자의 할당된 메모리 전체는 페이지 파일 사용에 대한 잠재성이며, 실제 사용을 나타내지 않습니다. 실제 사용은 실제 메모리 부족 상태에서 증가합니다.

동일한 정보를 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는 실제 메모리가 과도하게 소비되었으며 외부 실제 메모리의 부족 상태를 함께 고려해야 함을 나타냅니다.

Buffer Counts                  Buffers 
------------------------------ -------------------- 
Committed                      201120 
Target                         201120 
Hashed                         166517 
Reserved Potential             143388 
Stolen Potential               173556 
External Reservation           0 
Min Free                       256 
Visible                        201120 
Available Paging File          460640

-- 다중 페이지 할당자 인터페이스를 통해서 할당된 메모리 크기 
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                        16288 
CACHESTORE_STACKFRAMES                     16 
MEMORYCLERK_SQLSERVICEBROKER               192 
MEMORYCLERK_SNI                            32

Buffer Distribution            Buffers 
------------------------------ ----------- 
Stolen                         32871 
Free                           17845 
Cached                         1513 
Database (clean)               148864 
Database (dirty)               259 
I/O                            0 
Latched                        0

-- 버퍼풀 이외의 구성 요소에의해 소비된 메모리 계산
-- 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'

declare @total_alloc bigint  
declare @tab 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는 대량 메모리 소비자를 나타내므로 계산에서 제외
select 
    @total_alloc =  
        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 into @tab 
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 
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  * from @tab

-- 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

캐시와 메모리 부족

외부 및 내부 메모리 부족을 조사하는 또 다른 방법은 메모리 캐시 동작을 조사하는 것입니다.

SQL Server 2005의 내부 구현에 있어서, SQL Server 2000과 비교되는 차이점의 하나는 바로 캐싱(caching) 프레임워크의 단일화입니다. 프레임워크는 캐시에서 사용 빈도가 적은 항목들을 제거하기위해 클럭 알고리즘을 구현합니다. 현재는 내부 클럭 포인터와 외부 클럭 포인터로 두 개의 클럭 포인터[역주11] (clock hands)를 가집니다.

내부 클럭 포인터는 다른 캐시에 비례하여 캐시 크기를 조절합니다. 해당 캐시가 한계점에 다달았음을 프레임워크가 예상하게 되면 이동을 시작합니다.

외부 클럭 포인트는 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

메모리 상태 변경을 식별하기 위해 리소스 모니터 알림 정보를 이용할 수 있습니다. 내부적으로 서로 다른 메모리 부족을 모니터하는 프레임워크를 가지고 있습니다. 메모리 상태가 변경되면, 리소스 모니터가 알림을 생성합니다. 이 알림은 메모리 상태에 따른 메모리 사용 조정을 위해서 해당 구성 요소가 내부적으로 사용하며 다음 코드와 같이 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="149740267"> 
    <ResourceMonitor> 
        <Notification>RESOURCE_MEMPHYSICAL_LOW< 
        /Notification>  
        <Indicators>2</Indicators>  
        <NodeId>0</NodeId>  
    </ResourceMonitor> 
    <MemoryNode id="0"> 
        <ReservedMemory>1646380</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>3833132</TotalPhysicalMemory>  
        <AvailablePhysicalMemory>3240228< 
        /AvailablePhysicalMemory>  
        <TotalPageFile>5732340</TotalPageFile>  
        <AvailablePageFile>5057100</AvailablePageFile>  
        <TotalVirtualAddressSpace>2097024< 
        /TotalVirtualAddressSpace>  
        <AvailableVirtualAddressSpace>336760
        </AvailableVirtualAddressSpace>  
        <AvailableExtendedVirtualAddressSpace>0 
           </AvailableExtendedVirtualAddressSpace>  
    </MemoryRecord> 
</Record>

이 레코드에서 서버가 실제 메모리 부족 알림을 받았음을 알 수 있습니다. 더불어 킬로바이트단위의 메모리 크기도 알 수 있습니다. SQL Server의 XML 기능을 활용해서 이러한 정보들을 쿼리할 수 있습니다.

참고: 다음 표에 기술한 코드 중 일부는 가독성을 위해서 여러 줄에 표시되었습니다. 실제로는 한 줄로 입력합니다.

select  
    x.value('(//Notification)[1]', 'varchar(max)') as [Type], 
    x.value('(//Record/@time)[1]', '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="345640123"> 
    <OOM> 
        <Action>FAIL_VIRTUAL_COMMIT</Action>  
        <Resources>4096</Resources>  
    </OOM>

이 레코드는 실패한 작업의 유형(커밋, 예약, 혹은 페이지 할당)과 요청된 메모리 크기를 알려줍니다.

RING_BUFFER_MEMORY_BROKER 와 내부 메모리 부족

내부 메모리 부족이 감지되면, 메모리 할당 원본이 되는 버퍼 풀을 사용하는 구성 요소의 메모리 부족 알림(low memory notification)이 설정됩니다. 이는 캐시와 또 다른 구성 요소로부터 페이지 반환을 허용하는 것입니다.

내부 메모리 부족은 최대 서버 메모리 옵션을 조정하는 경우 혹은 stolen 페이지가 버퍼 풀의 80%를 초과하는 경우에도 발생할 수 있습니다.

내부 메모리 부족 알림(‘Shrink’)은, 다음 예제 코드와 같이 메모리 브로커(memory broker) 링 버퍼를 쿼리함으로써 알 수 있습니다.

select 
    x.value('(//Record/@time)[1]', '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="345640123"> 
    < 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 요약 정보를 구할 수 있습니다.

참고: 다음 표에 기술한 코드 중 일부는 가독성을 위해서 여러 줄에 표시되었습니다. 실제로는 한 줄로 입력합니다.

-- 가상 주소 공간 요약 뷰
-- 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 부족 상태를 능동적으로 모니터하지 않지만, 가상 메모리 부족 오류가 발생할 때 캐시를 비우는 동작을 합니다.

메모리 오류

701 – 시스템 메모리가 부족하여 이 쿼리를 실행할 수 없습니다.

원인

가장 일반적인 서버 메모리 부족 오류입니다. 메모리 할당 실패를 나타내며 현재 작업 부하 상에서 메모리 한계를 벗어난 경우를 포함해서 다양한 원인들이 존재합니다. SQL Server 2005 메모리 요구 사항 증가와 특정 구성 설정( 최대 서버 메모리 옵션 같은)으로 사용자는 SQL Server 2000 보다 이 오류를 더 많이 만날 수 있습니다. 일반적으로 실패한 트랜잭션은 이 오류의 원인이 아닙니다.

문제 해결

오류의 일관성이나 반복성(동일 상태) 혹은 임시 발생(서로 다른 상태로 불특정 시점에 나타남) 여부에 관계 없이, 오류가 발생하는 동안 서버 메모리 분배를 조사합니다. 이 오류가 발생하면 진단 분석 쿼리 또한 실패할 가능성이 있습니다. 외부 요소 평가에서부터 조사를 시작하십시오. 메모리 오류에 대한 일반적인 문제 해결 단계를 따릅니다.

해결 방안: 외부 메모리 부족 문제 제거. 최대 서버 메모리 설정 증가. 다음 명령 중 하나를 사용해서 캐시 비우기: DBCC FREESYSTEMCACHE, DBCC FREESESSIONCACHE, 혹은DBCC FREEPROCCACHE. 문제가 계속되면, 작업 부하를 줄입니다.

802 – 버퍼 풀에 사용할 수 있는 메모리가 부족합니다.

원인

이 오류가 반드시 메모리 부족 상태를 나타내는 것은 아닙니다. 버퍼 풀 메모리가 사용 중임을 나타냅니다. SQL Server 2005에서는 드물게 발생될 것입니다.

문제 해결

701 오류에서 소개한 일반적인 문제 해결 단계의 권장 사항을 따릅니다.

8628 – 쿼리 최적화를 기다리는 중 시간이 초과되었습니다. 쿼리를 다시 실행하십시오.

원인

이 오류는 쿼리 컴파일 작업을 완료하는데 필요한 메모리 확보에 실패한 경우를 나타냅니다. 쿼리는 구문 해석, 대수 연산 처리, 그리고 최적화를 포함한 컴파일 처리를 거쳐야 합니다. 따라서 다른 쿼리와의 메모리 리소스 경합이 일어납니다. 쿼리가 리소스를 기다리는 동안 정의된 시간을 초과하면 오류가 반환됩니다. 이 오류의 주된 원인은 서버에서 대량의 쿼리 컴파일이 발생하는 경우입니다.

문제 해결

Small Gateway                  Value 
------------------------------ -------------------- 
Configured Units               8 
Available Units                8 
Acquires                       0 
Waiters                        0 
Threshold Factor               250000 
Threshold                      250000 
 
(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

8645 – 쿼리를 실행하기 위해 메모리 리소스를 기다리는 중 시간이 초과되었습니다. 쿼리를 다시 실행하십시오.[역주12]

원인

이 오류는 메모리 집중적인 쿼리가 동시에 많이 실행되고 있음을 나타냅니다. 정렬(ORDER BY)과 조인을 사용하는 쿼리는 실행하는 동안 많은 메모리를 소비할 수 있습니다. 최대 병렬 처리 수준 설정 값이 높거나 쿼리가 정렬되지 않은 인덱스를 가진 분할 테이블 상에서 동작하는 경우 요구되는 메모리 양은 상당히 증가합니다. 쿼리에서 필요한 메모리 리소스를 기다리는 동안 정의된 시간을 초과하면(기본값은 sp_configure ‘query wait’ 설정 값이나 쿼리 예측 비용의 25배) 이 오류를 받게 됩니다. 일반적으로 이 오류를 받은 쿼리는 메모리를 소비하는 쿼리가 아닙니다.

문제 해결

8651 – 최소 쿼리 메모리를 사용할 수 없어서 요청한 작업을 수행할 수 없습니다. ‘쿼리 당 최소 메모리’ 서버 구성 옵션의 구성 값을 줄이십시오.

원인

8645 오류와 유사한 원인입니다. 또한 일반적인 서버 메모리 부족 상태를 나타낼 수 있습니다. 쿼리 당 최소 메모리(min memory per query) 옵션이 너무 높은 경우 이 오류가 발생할 수 있습니다.

문제 해결

페이지 위쪽

I/O 병목

SQL Server 성능은 I/O 하위 시스템의 사용량에 따라 결정됩니다. 데이터베이스가 실제 메모리 크기에 적합하지 않다면 SQL Server는 데이터베이스 페이지에서 버퍼 풀로 가져오거나 혹은 가져가는 동작이 빈번하게 일어날 것입니다. 이는 상당한 I/O 트래픽을 발생시킵니다. 더불어 로그 레코드 또한 트랜잭션이 커밋되기 전에 디스크로 기록됩니다. 마침내 SQL Server는 중간 결과 저장, 정렬, 행 버전 관리 등의 다양한 목적으로 tempdb를 사용합니다. 따라서 충분한 I/O 하위 시스템이 SQL Server 성능에 핵심이 됩니다.

tempdb를 포함한 데이터 파일이 랜덤하게 액세스되는 반면, 로그 파일 액세스는 트랜잭션이 롤백되는 경우를 제외하고는 순차적입니다. 따라서 일반적인 규정대로, 보다 좋은 성능을 위해 로그 파일은 데이터 파일과 분리된 물리적 디스크 상에 두어야 합니다. 본 문서의 촛점은 I/O 장치를 구성하는 방법이 아니라 I/O 병목이 있는지를 식별하기 위한 방법을 설명하는 것입니다. I/O 병목이 확인되면, I/O 하위 시스템의 재구성을 고려할 필요가 있습니다.

느린 I/O 하위 시스템을 가지고 있다면, 사용자들은 느린 응답 시간이나 시간 초과로 인한 작업 취소와 같은 성능 문제를 경험할 것입니다.

I/O 병목을 식별하기 위해서 다음 성능 카운터를 사용할 수 있습니다. 참고로, 이러한 평균값은 수집 간격을 짧게 가져야 합니다. 예를 들어, 60초 간격으로 수집하는 경우엔 I/O가 치솟는 특성을 알려주기가 어렵습니다. 또한, 하나의 카운터만으로 병목을 판단해서는 안됩니다. 여러 카운터 값을 참조해서 검증해야 합니다.

10ms 이하 – 매우 좋음
10 - 20ms 사이 – 괜찮음 
20 - 50ms 사이 – 느림, 주의 요망
50ms 이상 – 심각한 I/O 병목

위 카운터를 사용할 때는, 다음 수식을 사용해서 RAID 구성에 대한 적정 값을 찾아야 합니다.

Raid 0 -- 디스크 당 I/O = (읽기 + 쓰기) / 디스크 수 
Raid 1 -- 디스크 당 I/O = [읽기 + (2 * 쓰기)] / 2 
Raid 5 -- 디스크 당 I/O = [읽기 + (4 * 쓰기)] / 디스크 수
Raid 10 -- 디스크 당 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가 이미 완료되었으면, 정상적으로 진행합니다. 그렇치 않으면, 요청 작업의 유형에 따라 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_wait_time_ms 
----------------------------------------------------------------------- 
PAGEIOLATCH_DT  0                    0                    0 
PAGEIOLATCH_EX  1230                 791                  11 
PAGEIOLATCH_KP  0                    0                    0 
PAGEIOLATCH_NL  0                    0                    0 
PAGEIOLATCH_SH  13756                7241                 180 
PAGEIOLATCH_UP  80                   66                   0

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의 총 시간을 나타냅니다.

Database_id File_Id io_stall io_pending_ms_ticks scheduler_address 
---------------------------------------------------------------------- 
6           1        10804        78            0x0227A040 
6           1        10804        78            0x0227A040 
6           2        101451       31            0x02720040

문제 해결

I/O 병목을 식별하면, 다음 중 하나 이상의 작업을 수행해서 해당 문제에 대처할 수 있습니다:

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

select  
    c1,  
    c5 
from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4 
order by c2 
 
일괄 처리-2 
select * from t1

checkpoint 
dbcc freeproccache 
dbcc dropcleanbuffers

Avg_logical_reads     Avg_logical_writes      Avg_phys_reads      Execution_count     stmt_start_offset  
------------------------------------------------------------------------------------------------------------ 
2794                           1                       385                               1                 0                       
1005                           0                       0                                  1                 146          
 
sql_handle                                                                          plan_handle 
-----------------------------------------------------------------------------------------------------
0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB x0600050099EC8520A8619803000000000000000000000000 
0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB x0600050099EC8520A8619803000000000000000000000000

select text  
from sys.dm_exec_sql_text (0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB) 
 
출력은 다음과 같습니다.
 
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 (0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB)

select *  
from sys.dm_exec_query_plan (0x0600050099EC8520A8619803000000000000000000000000)

페이지 위쪽

Tempdb

Tempdb는 SQL Server 운영 중에 생성되는 내부 개체와 사용자 개체, 임시 테이블, 개체, 저장 프로시저들을 모두 저장합니다.

SQL Server 인스턴스별로 하나의 tempdb가 존재하므로, 성능 및 디스크 공간의 병목이 일어날 수 있습니다. tempdb는 과도한 DDL/DML 작업과 가용 공간 측면에서 많은 부하가 발생합니다. 이것은 또한 서버에서 실행 중인 다른 응용 프로그램을 느리게 만들거나 문제가 발생하는 원인이 될 수도 있습니다.

다음은 tempdb와 관련된 공통적인 문제들입니다:

tempdb 문제를 진단하기 전에, 먼저 tempdb저장 공간이 어떻게 사용되지는 살펴보겠습니다.

tempdb에 의해서 사용되는 전체 공간은 사용 개체 + 내부 개체 + 버전 관리 저장소 + 여유 공간에 해당합니다.

이 여유 공간은 성능 카운터의 free space in tempdb 와 같습니다.

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   freespace_kb 
---------------- -------------------- ------------------ ------------ 
8736               128                    64                    448

혼합 익스텐트내 페이지에 대해서는 계산하지 않고 있음을 참고하십시오. 혼합 익스텐트내 페이지는 사용자 개체와 내부 개체에 의해서 할당될 수 있습니다.

디스크 공간 문제 해결

사용자 개체, 내부 개체, 그리고 버전 저장소 모두 tempdb 디스크 공간 문제를 유발할 수 있습니다. 이번 절에서는, 각 범주별 문제 해결 방법을 검토합니다.

사용자 개체

사용자 개체는 특정 세션에 소유되지 않으므로, 개체를 생성하는 응용 프로그램의 요구 사항을 이해하고, 필요에 따라 tempdb 크기를 조정합니다. 사용자 개체에 대한 사용 공간은 exec sp_spaceused @objname=’<user-object> ’ 를 실행해서 알 수 있습니다.

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 
 
declare @name varchar(256) 
fetch userobj_cursor into @name 
while (@@FETCH_STATUS = 0)  
begin 
    exec sp_spaceused @objname = @name 
    fetch userobj_cursor into @name     
end 
close userobj_cursor

버전 저장소

SQL Server 2005는 기존 기능과 새로운 기능을 구현하는데 사용되는 행 버전 관리 프레임워크를 제공합니다. 현재, 다음 기능들이 행 버전 관리 프레임워크를 사용합니다. 각 기능에 대한 추가 정보는, SQL Server 온라인 설명서를 참조하십시오.

행 버전 관리는 세션간의 공유됩니다. 행 버전 생성자는 행 버전이 재사용되는 경우의 제어권을 가지고 있지 않습니다. 행 버전을 모두 정리할 때 이를 방해하는 가장 오랜 시간 실행되는 트랜잭션을 찾아서 가능한 제거하는 것이 필요합니다.

다음 쿼리는 버전 저장소에서 버전에 따라 가장 오랜 시간 실행되는 트랜잭션 상위 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인 트랜잭션 에제를 보여줍니다.

transaction_id       transaction_sequence_num elapsed_time_seconds 
-------------------- ------------------------ -------------------- 
8609                 3                        6523 
20156                25                       783

두 번째 트랜잭션은 상대적으로 짧은 기간이므로, 첫 번째 트랜잭션을 제거하는 것으로 버전 저장소의 상당한 양을 정리할 수 있습니다. 그러나, 얼마나 많은 공간이 반환될지는 예측할 수 없습니다. 필요한 공간을 정리하기 위해서는 좀 더 많은 트랜잭션을 제거할 필요가 있습니다.

버전 저장소를 포함해서 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)로 표시됩니다. 이를 통해 사용되는 버전 저장소 공간을 확보할수 있습니다. 메시지 3967[역주15] 이 그와 같은 각 희생자 트랜잭션을 위해 오류 로그에 생성됩니다. 트랜잭션이 희생자로 표시되면 버전 저장소에서 해당 행 버전을 읽거나 새로운 것을 생성할 수 없게 됩니다. 메시지 3966은 희생자 트랜잭션이 행 버전을 읽으려고 할 때 그 트랜잭션이 롤백되면서 발생합니다. 버전 저장소의 축소가 성공하면 tempdb에서 추가 공간이 확보됩니다. 그렇치 않으면, tempdb는 저장소가 부족하게 됩니다.

내부 개체

내부 개체는 각 문장에 의해서 생성되고 제거됩니다. tempdb가 지나치게 크다면 어떤 세션이나 작업이 그렇게 공간을 많이 소비하는지 해당 주범을 찾아서 해결할 필요가 있습니다.

SQL Server 2005는 두 가지 추가 DMV를 제공합니다: sys.dm_db_session_space_usage 와 sys.dm_db_task_space_usage는 각각 세션과 작업별로 할당된 tempdb공간을 추적합니다. 작업(task)은 세션 범위 내에서 실행되므로, 작업에 의해서 사용된 공간은 작업이 완료된 후에 세션에 포함됩니다.

다음 쿼리를 사용해서 내부 개체를 할당하고 있는 상위 세션을 알 수 있습니다. 이 쿼리는 세션에서 완료된 작업만 포함됨을 참고하십시오.

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 
---------- -------------------- -------------------- 
52         5120                 5136 
51         16                   0

내부 개체 할당이 많이 발생하는 작업이 별도로 분리되어 있다면, 해당 Transact-SQL 문장이 무엇인지 그리고 어떤 쿼리 계획인지를 알아내서 보다 상세한 분석이 가능합니다.

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   
------------------------------------------------------------------
52            0               1024                          1024        
sql_handle                                                                             statement_start_offset  
---------------------------------------------------------------------------------------------
0x02000000D490961BDD2A8BE3B0FB81ED67655EFEEB360172   356   
 
statement_end_offset  plan_handle       
-------------------------------------                                        
-1                           0x06000500D490961BA8C19503000000000000000000000000

다음과 같이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의 초기 크기를 보다 큰 값으로 적절히 조정하는 것이 권장 사항입니다.

과도한 DDL 및 할당 작업

다음과 같은 상황에서 두 가지 원인의 경합이 tempdb에 일어날 수 있습니다.

SGAM과 PFS 페이지는 데이터 파일에서 고정된 간격으로 발생함으로, 해당 리소스 내용을 찾기가 어렵지 않습니다. 예를 들어, 2:1:1는 tempdb의 첫 번째 PFS 페이지(데이터베이스-id = 2, 파일-id = 1, 페이지-id = 1)를 나타내며, 2:1:3은 첫 번째 SGAM 페이지를 나타냅니다. SGAM 페이지는 511232 페이지 간격으로 발생하며 PFS 페이지는 8088 페이지 단위로 발생합니다. 이러한 정보를 통해서 tempdb의 모든 파일에 모든 PFS 및 SGAM 페이지를 찾을 수 있습니다. 이러한 페이지에 래치를 얻기 위해 작업이 대기하는 시간을, sys.dm_os_waiting_tasks를 통해서 볼 수 있습니다. 래치 대기는 일시적이므로 차후에 분석을 위해서는 이 테이블을 자주 쿼리하고(대략 10초 간격) 해당 데이터를 수집해야 합니다. 예를 들어, 다음 쿼리는 analysis 데이터베이스의 waiting_tasks 테이블로 tempdb 페이지에 대기 중인 모든 작업 정보를 입력합니다.

-- 현재 시간 구하기
declare @now datetime  
select @now = getdate() 
 
-- 차후 분석을 위해 테이블에 입력 
insert into analysis..waiting_tasks 
      select  
          session_id,  
          wait_duration_ms,  
          resource_description,  
          @now 
      from sys.dm_os_waiting_tasks 
      where wait_type like ‘PAGE%LATCH_%’ and 
            resource_description like ‘2:%’

tempdb 페이지에 래치를 얻기 위해 대기 중인 작업을 볼 때, 이것이 PFS나 SGAM 페이지 때문인지 분석할 수 있습니다. 그렇다면, 이는 tempdb에 할당 경합을 암시하는 것입니다. tempdb의 다른 페이지에 경합이 보이며 그 페이지가 시스템 테이블에 속한다면 이것은 과도한 DDL 작업으로 인한 경합을 암시합니다.

다음 성능 카운터를 통해서 이례적인 임시 개체 할당이나 위치 처리 활동의 증가를 모니터할 수 있습니다.

문제 해결

tempdb 경합이 과도한 DDL 작업 때문이라면, 사용 중인 응용 프로그램을 살펴보고 DDL 작업의 최소화가 가능한지 알아봅니다. 다음 제안 사항들을 시도해 볼 수 있습니다.

SGAM과 PFS 페이지에 경합이 원인이라면, 다음 사항을 시도함으로써 경감시킬 수 있습니다:

차단

차단은 주로 논리적 읽기에 대한 차단입니다. 이는 특정 리소스에 X 잠금 획득을 위한 대기나 래치 같은 저 수준 동기화 개체로 인한 대기와 같습니다.

논리적 읽기 대기는 이미 잠긴 리소스에 호환되지 않은 다른 잠금을 요청할 때 발생합니다. 이는 Transact-SQL 문장을 실행할 때 트랜잭션 격리 수준에 따라 데이터 일관성을 제공하기 위해서 필요하지만, 최종 사용자에게는 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는 특정 리소스에 대기 중인 작업의 대기 큐에 대한 상세 정보를 제공합니다. 예를 들어, 다음 쿼리를 실행해서 차단된 세션 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 
-------------------- ---------- --------------- -------------------- ----- 
------------------------------------------------------- ------------------  
--------------------- ------------------- ------------------------ ------- 
-------------------------------------------------------------------------- 
-------------------------------------------------------------------------- 
-------------------------------------------------------------------------- 
--------------------------- 
0x022A8898           56         0               1103500               
LCK_M_S                                                      0x03696820     
     0x022A8D48            53                  NULL                      
ridlock fileid=1 pageid=143 dbid=9 id=lock3667d00 mode=X  
associatedObjectId=72057594038321152

이 결과에서 세션 53이 세션 56에 의해서 특정 잠금 대기로 1103500 밀리초 동안 차단되고 있음을 보여줍니다.

잠금이 허가된 혹은 잠금을 대기 중인 세션을 찾기 위해서, sys.dm_tran_locks DMV를 사용할 수 있습니다. 각 행은 현재 활성 요청을 나타내는 것으로, 잠금 관리자를 통해 잠금을 허가 받았거나 혹은 그 요청에 의해 차단된 상태로 허가를 기다리고 있는 요청입니다. 일반적인 잠금에 대해서는, 허가된 요청은 해당 리소스에 잠금이 요청자에게 허가되었음을 나타냅니다. 대기 중인 요청은 아직 허가받지 못했음을 나타냅니다. 예를 들어, 다음 쿼리는 세션 56이 리소스 1:143:3에 대해서 세션 53에 의한 X 모드가 있으므로 차단되었음을 보여줍니다.

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 
-------------------------------------------------------------------------- 
---                                                                        
                                 56    DATABASE   9                    
            S          GRANT 
53    DATABASE    9                                S         GRANT 
56    PAGE        9       t_lock      1:143       IS         GRANT 
53    PAGE        9       t_lock      1:143       IX         GRANT 
53    PAGE        9       t_lock      1:153       IX         GRANT 
56   OBJECT       9       t_lock                  IS         GRANT 
53   OBJECT       9        t_lock                 IX         GRANT 
53    KEY         9        t_lock      (a400c34cb X          GRANT 
53    RID         9        t_lock      1:143:3    X          GRANT 
56    RID         9        t_lock      1:143:3    S           WAIT

사실 저장 프로시저 sp_block에서 보여지는대로 위 두 DMV를 조인할 수 있습니다. 그림 1은 차단된 세션과 그 세션을 차단하는 세션 목록을 보여줍니다. 소스 코드는 부록 B에서 찾을 수 있습니다. 필요하면 저장 프로시저를 수행해서 원하는 속성을 결과 집합에 추가하거나 제거할 수 있습니다. @spid 옵션 매개변수는 특정 spid를 차단하는 세션과 잠금 요청에 대한 상세 정보를 제공합니다.

그림 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’, 200
2.	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초 간격으로 추적 이벤트가 발생합니다. 이는 600초 동안 차단된 단일 잠금의 경우 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를 통해서 얻을 수 있는 정보들은 다음과 같습니다:

차단 관련 정보 이외에도, 인덱스 액세스에 대한 추가 정보들이 제공됩니다.

이 정보는 SQL Server 인스턴스가 시작되면서부터 누적됩니다. 이 정보는 인스턴스가 재시작하면 사라지며, 또한 재설정하는 방법도 없습니다. 이 DMV에 의해서 반환되는 데이터는 해당 힙이나 인덱스를 나타내는 메타 데이터 캐시가 사용 가능한 경우에만 존재합니다. 힙이나 인덱스를 메타 데이터 캐시로 가져갈 때마다 각 열 값은 0으로 설정됩니다. 통계 정보는 메타 데이터 캐시에서 캐시 개체가 제거될 때까지 누적됩니다. 따라서, 이후 분석을 위해서는 주기적으로 데이터를 수집해서 저장해 두어야 합니다.

부록 B 인덱스 작업 데이터를 수집하는데 사용할 수 있는 저장 프로시저들을 제공합니다. 원하는 주기로 해당 데이터를 분석할 수 있습니다. 다음은 부록 B에 소개한 저장 프로시저를 사용하는 단계입니다.

다음은 저장 프로시저를 사용해서 얻을 수 있는 정보들의 몇 가지 예제[역주19] 를 보여줍니다.

exec get_indexstats  
            @dbid=-1, 
            @top='top 5',  
            @columns='index, usage',  
            @order='index usage'

exec get_indexstats  
            @dbid=-1, 
            @top='top 5', 
            @order='index lock promotions', 
            @threshold='[index lock promotion attempts] > 0'

exec get_indexstats  
        @dbid=5, 
        @top='top 5', 
        @columns='wait,scan,singleton', 
        @order='singleton lookups', 
        @threshold='[avg row lock wait ms] > 2'

exec get_indexstats  
            @dbid=-1, 
            @top='top 10 ', 
            @columns='wait,row', 
            @order='row lock wait ms',  
             @threshold='[row lock waits] > 1'

exec get_indexstats  
        @dbid=-1, 
        @top='top 5', 
        @order='avg row lock wait ms'

exec get_indexstats  
        @dbid=-1, 
        @top='top 5', 
        @order='avg page latch wait ms'

exec get_indexstats  
        @dbid=-1, 
        @top='top 3 percent', 
        @order='avg pageio latch wait ms', 
        @threshold='[pageio latch waits] > 0'

exec get_indexstats  
        @dbid=-1, 
        @top='top 10', 
        @order='block %', 
        @threshold='[block %] > 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

다음은 출력 결과입니다. 주목할 만한 몇 가지 핵심 요점은:

wait_type     waiting_tasks_count  wait_time_ms     max_wait_time_ms  
 signal_wait_time_ms    
------------------ -------------------- -------------------- ------------- 
------- ------- 
LAZYWRITER_SLEEP      415088               415048437            1812       
           156 
SQLTRACE_BUFFER_FLUSH 103762               415044000            4000       
          0 
LCK_M_S               6                    25016812             23240921   
          0 
WRITELOG              7413                 86843                187        
          406 
LOGMGR_RESERVE_APPEND 82                   82000                1000       
          0 
SLEEP_BPOOL_FLUSH     4948                 28687                31         
          15 
LCK_M_X               1                    20000                20000      
         0 
PAGEIOLATCH_SH        871                  11718                140        
         15 
PAGEIOLATCH_UP        755                  9484                 187        
         0 
IO_COMPLETION         636                  7031                 203        
         0

대기 정보를 분석하기 위해서, 정기적으로 데이터를 수집해야 합니다. 부록 B에서 두 가지 저장 프로시저를 제공합니다.

exec dbo.track_waitstats @num_samples=6 
    ,@delay_interval=30 
    ,@delay_type='s' 
    ,@truncate_history='y' 
    ,@clear_waitstats='y'

exec [dbo].[get_waitstats_2005]

이 저장 프로시저를 사용해서 리소스 대기와 신호(signal) 대기를 분석하거나 자원 경합을 격리시킬 수 있습니다.

그림5 보고서 예제

그림 5: 대기에 대한 통계 정보 분석 리포트
원본 크기 이미지로 보기

그림 5의 대기 분석 리포트 예제는 성능 문제가 차단(LCK_M_S)과 메모리 할당(RESOURCE_SEMAPHORE)에 있음을 알려줍니다. 특히 전체 대기 중 메모리 요구로 인한 문제가 43%인 반면, 55%가 공유 잠금입니다. 개체 별 차단 분석은 경합의 핵심 요점을 식별합니다.

페이지 위쪽

부록 B: 차단 스크립트(Blocking Scripts)

부록은 본 문서에서 참조한 저장 프로시저의 소스 목록입니다. 필요에 따라 수정해서 사용하십시오.

sp_block

create proc dbo.sp_block (@spid bigint=NULL) 
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 
-- 
-- T. Davidson 
-- This proc reports blocks 
--    1. optional parameter @spid  
-- 
 
 
select  
    t1.resource_type, 
    'database'=db_name(resource_database_id), 
    'blk object' = t1.resource_associated_entity_id, 
    t1.request_mode, 
    t1.request_session_id, 
    t2.blocking_session_id      
from  
    sys.dm_tran_locks as t1,  
    sys.dm_os_waiting_tasks as t2 
where  
    t1.lock_owner_address = t2.resource_address and 
    t1.request_session_id = isnull(@spid,t1.request_session_id)

인덱스 사용 정보 분석

아래 저장 프로시저들은 인덱스 사용량 분석에 사용할 수 있습니다.

get_indexstats

create proc dbo.get_indexstats  
    (@dbid smallint=-1 
    ,@top varchar(100)=NULL 
    ,@columns varchar(500)=NULL 
    ,@order varchar(100)='lock waits' 
    ,@threshold varchar(500)=NULL) 
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 
-- 
-- T. Davidson 
-- This proc analyzes index statistics including accesses, overhead,  
-- locks, blocks, and waits 
-- 
-- Instructions: Order of execution is as follows: 
--    (1) truncate indexstats with init_indexstats 
--    (2) take initial index snapshot using insert_indexstats 
--    (3) Run workload 
--    (4) take final index snapshot using insert_indexstats 
--    (5) analyze with get_indexstats     
 
-- @dbid limits analysis to a database 
-- @top allows you to specify TOP n 
-- @columns is used to specify what columns from  
--            sys.dm_db_index_operational_stats will be included in  
-- the report 
--            For example, @columns='scans,lookups,waits' will include  
-- columns 
--            containing these keywords 
-- @order used to order results 
-- @threshold used to add a threshold,  
--            example: @threshold='[block %] > 5' only include if  
-- blocking is over 5% 
-- 
------  definition of some computed columns returned 
-- [blk %] = percentage of locks that cause blocks e.g. blk% = 100 * lock  
-- waits / locks 
-- [index usage] = range_scan_count + singleton_lookup_count +  
-- leaf_insert_count 
-- [nonleaf index overhead]=nonleaf_insert_count + nonleaf_delete_count +  
-- nonleaf_update_count 
-- [avg row lock wait ms]=row_lock_wait_in_ms/row_lock_wait_count 
-- [avg page lock wait ms]=page_lock_wait_in_ms/page_lock_wait_count 
-- [avg page latch wait ms]=page_latch_wait_in_ms/page_latch_wait_count 
-- [avg pageio latch wait  
-- ms]=page_io_latch_wait_in_ms/page_io_latch_wait_count 
-------------------------------------------------------------------------- 
------------------------- 
--- Case 1 - only one snapshot of sys.dm_db_operational_index_stats was  
-- stored in  
---            indexstats. This is an error - return errormsg to user 
--- Case 2 - beginning snapshot taken, however some objects were not  
-- referenced 
---            at the time of the beginning snapshot. Thus, they will  
-- not be in the initial 
---            snapshot of sys.dm_db_operational_index_stats, use 0 for  
-- starting values. 
---            Print INFO msg for informational purposes. 
--- Case 3 - beginning and ending snapshots, beginning values for all  
-- objects and indexes 
---            this should be the normal case, especially if SQL Server  
-- is up a long time 
-------------------------------------------------------------------------- 
------------------------- 
set nocount on 
declare @orderby varchar(100), @where_dbid_is varchar(100), @temp  
varchar(500), @threshold_temptab varchar(500) 
declare @cmd varchar(max),@col_stmt varchar(500),@addcol varchar(500) 
declare @begintime datetime, @endtime datetime, @duration datetime,  
@mincount int, @maxcount int 
 
select @begintime = min(now), @endtime = max(now) from indexstats 
 
if @begintime = @endtime 
    begin 
        print 'Error: indexstats contains only 1 snapshot of  
sys.dm_db_index_operational_stats' 
        print 'Order of execution is as follows: ' 
        print '    (1) truncate indexstats with init_indexstats' 
        print '    (2) take initial index snapshot using insert_indexstats' 
        print '    (3) Run workload' 
        print '    (4) take final index snapshot using insert_indexstats' 
        print '    (5) analyze with get_indexstats' 
        return -99 
    end 
 
select @mincount = count(*) from indexstats where now = @begintime 
select @maxcount = count(*) from indexstats where now = @endtime 
 
if @mincount < @maxcount 
    begin 
        print 'InfoMsg1: sys.dm_db_index_operational_stats only contains  
entries for objects referenced since last SQL re-cycle' 
        print 'InfoMsg2: Any newly referenced objects and indexes captured  
in the ending snapshot will use 0 as a beginning value' 
    end 
 
select @top = case  
        when @top is NULL then '' 
        else lower(@top) 
    end, 
        @where_dbid_is = case (@dbid) 
        when -1 then '' 
        else ' and i1.database_id = ' + cast(@dbid as varchar(10)) 
    end, 
--- thresholding requires a temp table 
        @threshold_temptab = case  
        when @threshold is NULL then '' 
        else ' select * from #t where ' + @threshold 
    end 
--- thresholding requires temp table, add 'into #t' to select statement  
select @temp = case (@threshold_temptab) 
        when '' then '' 
        else ' into #t ' 
    end 
select @orderby=case(@order) 
when 'leaf inserts' then 'order by [' + @order + ']' 
when 'leaf deletes' then 'order by [' + @order + ']' 
when 'leaf updates' then 'order by [' + @order + ']' 
when 'nonleaf inserts' then 'order by [' + @order + ']' 
when 'nonleaf deletes' then 'order by [' + @order + ']' 
when 'nonleaf updates' then 'order by [' + @order + ']' 
when 'nonleaf index overhead' then 'order by [' + @order + ']'  
when 'leaf allocations' then 'order by [' + @order + ']' 
when 'nonleaf allocations' then 'order by [' + @order + ']' 
when 'allocations' then 'order by [' + @order + ']' 
when 'leaf page merges' then 'order by [' + @order + ']' 
when 'nonleaf page merges' then 'order by [' + @order + ']' 
when 'range scans' then 'order by [' + @order + ']' 
when 'singleton lookups' then 'order by [' + @order + ']' 
when 'index usage' then 'order by [' + @order + ']' 
when 'row locks' then 'order by [' + @order + ']' 
when 'row lock waits' then 'order by [' + @order + ']' 
when 'block %' then 'order by [' + @order + ']'  
when 'row lock wait ms' then 'order by [' + @order + ']' 
when 'avg row lock wait ms' then 'order by [' + @order + ']' 
when 'page locks' then 'order by [' + @order + ']' 
when 'page lock waits' then 'order by [' + @order + ']' 
when 'page lock wait ms' then 'order by [' + @order + ']' 
when 'avg page lock wait ms' then 'order by [' + @order + ']' 
when 'index lock promotion attempts' then 'order by [' + @order + ']' 
when 'index lock promotions' then 'order by [' + @order + ']' 
when 'page latch waits' then 'order by [' + @order + ']' 
when 'page latch wait ms' then 'order by [' + @order + ']' 
when 'pageio latch waits' then 'order by [' + @order + ']' 
when 'pageio latch wait ms' then 'order by [' + @order + ']' 
else '' 
end 
 
if @orderby <> '' select @orderby = @orderby + ' desc' 
select  
    'start time'=@begintime, 
    'end time'=@endtime, 
    'duration (hh:mm:ss:ms)'=convert(varchar(50), 
    @endtime-@begintime,14), 
    'Report'=case (@dbid)  
               when -1 then 'all databases' 
               else db_name(@dbid) 
             end + 
            case  
                when @top = '' then '' 
                when @top is NULL then '' 
                when @top = 'none' then '' 
                else ', ' + @top 
            end + 
            case  
                when @columns = '' then '' 
                when @columns is NULL then '' 
                when @columns = 'none' then '' 
                else ', include only columns containing ' + @columns 
            end + 
            case(@orderby) 
                when '' then '' 
                when NULL then '' 
                when 'none' then '' 
                else ', ' + @orderby 
            end +  
            case 
                when @threshold = '' then '' 
                when @threshold is NULL then '' 
                when @threshold = 'none' then '' 
                else ', threshold on ' + @threshold 
            end 
 
select @cmd = ' select i2.database_id, i2.object_id, i2.index_id,  
i2.partition_number ' 
select @cmd = @cmd +' , begintime=case min(i1.now) when max(i2.now) then  
NULL else min(i1.now) end ' 
select @cmd = @cmd +'     , endtime=max(i2.now) ' 
select @cmd = @cmd +' into #i ' 
select @cmd = @cmd +' from indexstats i2 ' 
select @cmd = @cmd +' full outer join ' 
select @cmd = @cmd +'     indexstats i1 ' 
select @cmd = @cmd +' on i1.database_id = i2.database_id ' 
select @cmd = @cmd +' and i1.object_id = i2.object_id ' 
select @cmd = @cmd +' and i1.index_id = i2.index_id ' 
select @cmd = @cmd +' and i1.partition_number = i2.partition_number ' 
select @cmd = @cmd +' where i1.now >= ''' +   
convert(varchar(100),@begintime, 109) + '''' 
select @cmd = @cmd +' and i2.now = ''' + convert(varchar(100),@endtime,  
109) + '''' 
select @cmd = @cmd + ' ' + @where_dbid_is + ' ' 
select @cmd = @cmd + ' group by i2.database_id, i2.object_id, i2.index_id,  
i2.partition_number ' 
select @cmd = @cmd + ' select ' + @top + ' i.database_id,  
db_name=db_name(i.database_id),  
object=isnull(object_name(i.object_id),i.object_id), indid=i.index_id,  
part_no=i.partition_number ' 
 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[leaf inserts]=i2.leaf_insert_count -   
         isnull(i1.leaf_insert_count,0)' 
 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
     @add_stmt=@addcol out, 
     @cols_containing=@columns,@col_stmt=' , 
       [leaf deletes]=i2.leaf_delete_count – 
       isnull(i1.leaf_delete_count,0)' 
 
select @cmd = @cmd + @addcol 
 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[leaf updates]=i2.leaf_update_count –  
isnull(i1.leaf_update_count,0)' 
 
select @cmd = @cmd + @addcol 
 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[nonleaf inserts]=i2.nonleaf_insert_count –  
isnull(i1.nonleaf_insert_count,0)' 
 
select @cmd = @cmd + @addcol 
 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[nonleaf deletes]=i2.nonleaf_delete_count –  
isnull(i1.nonleaf_delete_count,0)' 
 
select @cmd = @cmd + @addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[nonleaf updates]=i2.nonleaf_update_count –  
isnull(i1.nonleaf_update_count,0)' 
 
select @cmd = @cmd + @addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[nonleaf index overhead]=(i2.nonleaf_insert_count –  
isnull(i1.nonleaf_insert_count,0)) + (i2.nonleaf_delete_count –  
isnull(i1.nonleaf_delete_count,0)) + (i2.nonleaf_update_count –  
isnull(i1.nonleaf_update_count,0))' 
 
select @cmd = @cmd + @addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[leaf allocations]=i2.leaf_allocation_count –  
isnull(i1.leaf_allocation_count,0)' 
 
select @cmd = @cmd + @addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[nonleaf allocations]=i2.nonleaf_allocation_count –  
isnull(i1.nonleaf_allocation_count,0)' 
 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[allocations]=(i2.leaf_allocation_count –  
isnull(i1.leaf_allocation_count,0)) + (i2.nonleaf_allocation_count –  
isnull(i1.nonleaf_allocation_count,0))' 
 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[leaf page merges]=i2.leaf_page_merge_count –  
isnull(i1.leaf_page_merge_count,0)' 
 
select @cmd = @cmd + @addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[nonleaf page merges]=i2.nonleaf_page_merge_count –  
isnull(i1.nonleaf_page_merge_count,0)' 
 
select @cmd = @cmd + @addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[range scans]=i2.range_scan_count –  
isnull(i1.range_scan_count,0)' 
 
select @cmd = @cmd + @addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing= @columns, 
    @col_stmt=' ,[singleton lookups]=i2.singleton_lookup_count –  
isnull(i1.singleton_lookup_count,0)' 
 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[index usage]=(i2.range_scan_count –  
isnull(i1.range_scan_count,0)) + (i2.singleton_lookup_count –  
isnull(i1.singleton_lookup_count,0)) + (i2.leaf_insert_count –  
isnull(i1.leaf_insert_count,0))' 
select @cmd = @cmd + @addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[row locks]=i2.row_lock_count –  
isnull(i1.row_lock_count,0)' 
select @cmd = @cmd + @addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[row lock waits]=i2.row_lock_wait_count –  
isnull(i1.row_lock_wait_count,0)' 
 
select @cmd = @cmd + @addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[block %]=cast (100.0 * (i2.row_lock_wait_count –  
isnull(i1.row_lock_wait_count,0)) / (1 + i2.row_lock_count –  
isnull(i1.row_lock_count,0)) as numeric(5,2))' 
 
select @cmd = @cmd + @addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[row lock wait ms]=i2.row_lock_wait_in_ms –  
isnull(i1.row_lock_wait_in_ms,0)' 
 
select @cmd = @cmd + @addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[avg row lock wait ms]=cast ((1.0*(i2.row_lock_wait_in_ms 
- isnull(i1.row_lock_wait_in_ms,0)))/(1 + i2.row_lock_wait_count -  
isnull(i1.row_lock_wait_count,0)) as numeric(20,1))' 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[page locks]=i2.page_lock_count –  
isnull(i1.page_lock_count,0)' 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[page lock waits]=i2.page_lock_wait_count –  
isnull(i1.page_lock_wait_count,0)' 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[page lock wait ms]=i2.page_lock_wait_in_ms –  
isnull(i1.page_lock_wait_in_ms,0)' 
select @cmd = @cmd +@addcol 
exec dbo.add_column     
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[avg page lock wait ms]=cast  
((1.0*(i2.page_lock_wait_in_ms - isnull(i1.page_lock_wait_in_ms,0)))/(1 +  
i2.page_lock_wait_count - isnull(i1.page_lock_wait_count,0)) as  
numeric(20,1))' 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[index lock promotion  
attempts]=i2.index_lock_promotion_attempt_count –  
isnull(i1.index_lock_promotion_attempt_count,0)' 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[index lock promotions]=i2.index_lock_promotion_count –  
isnull(i1.index_lock_promotion_count,0)' 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[page latch waits]=i2.page_latch_wait_count –  
isnull(i1.page_latch_wait_count,0)' 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[page latch wait ms]=i2.page_latch_wait_in_ms –  
isnull(i1.page_latch_wait_in_ms,0)' 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[avg page latch wait ms]=cast  
((1.0*(i2.page_latch_wait_in_ms - isnull(i1.page_latch_wait_in_ms,0)))/(1  
+ i2.page_latch_wait_count - isnull(i1.page_latch_wait_count,0)) as  
numeric(20,1))' 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[pageio latch waits]=i2.page_io_latch_wait_count –  
isnull(i1.page_latch_wait_count,0)' 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[pageio latch wait ms]=i2.page_io_latch_wait_in_ms –  
isnull(i1.page_latch_wait_in_ms,0)' 
select @cmd = @cmd +@addcol 
exec dbo.add_column  
    @add_stmt=@addcol out, 
    @cols_containing=@columns, 
    @col_stmt=' ,[avg pageio latch wait ms]=cast  
((1.0*(i2.page_io_latch_wait_in_ms –  
isnull(i1.page_io_latch_wait_in_ms,0)))/(1 + i2.page_io_latch_wait_count –  
isnull(i1.page_io_latch_wait_count,0)) as numeric(20,1))' 
 
select @cmd = @cmd +@addcol 
select @cmd = @cmd + @temp 
select @cmd = @cmd + ' from #i i ' 
select @cmd = @cmd + ' left join indexstats i1 on i.begintime = i1.now and  
i.database_id = i1.database_id and i.object_id = i1.object_id and  
i.index_id = i1.index_id and i.partition_number = i1.partition_number ' 
 
select @cmd = @cmd + ' left join indexstats i2 on i.endtime = i2.now and  
i.database_id = i2.database_id and i.object_id = i2.object_id and  
i.index_id = i2.index_id and i.partition_number = i2.partition_number ' 
select @cmd = @cmd + ' ' + @orderby + ' ' 
select @cmd = @cmd + @threshold_temptab 
exec ( @cmd ) 
go

insert_indexstats

create proc insert_indexstats (@dbid smallint=NULL, 
                               @objid int=NULL, 
                               @indid int=NULL, 
                               @partitionid int=NULL) 
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 stored procedure stores a snapshot of  
sys.dm_db_index_operational_stats into the table indexstas 
-- for later analysis by the stored procedure get_indexstats. Please note  
that the indexstats table has an additional  
-- column to store the timestamp when the snapshot is taken 
--  
-- T. Davidson 
-- snapshot sys.dm_db_index_operational_stats 
-- 
declare @now datetime 
select @now = getdate() 
insert into indexstats  
        (database_id 
        ,object_id 
        ,index_id 
        ,partition_number 
        ,leaf_insert_count 
        ,leaf_delete_count 
        ,leaf_update_count 
        ,leaf_ghost_count 
        ,nonleaf_insert_count 
        ,nonleaf_delete_count 
        ,nonleaf_update_count 
        ,leaf_allocation_count  
        ,nonleaf_allocation_count 
        ,leaf_page_merge_count  
        ,nonleaf_page_merge_count 
        ,range_scan_count 
        ,singleton_lookup_count 
        ,forwarded_fetch_count 
        ,lob_fetch_in_pages 
        ,lob_fetch_in_bytes 
        ,lob_orphan_create_count 
        ,lob_orphan_insert_count 
        ,row_overflow_fetch_in_pages 
        ,row_overflow_fetch_in_bytes 
        ,column_value_push_off_row_count 
        ,column_value_pull_in_row_count 
        ,row_lock_count 
        ,row_lock_wait_count 
        ,row_lock_wait_in_ms 
        ,page_lock_count 
        ,page_lock_wait_count 
        ,page_lock_wait_in_ms 
        ,index_lock_promotion_attempt_count 
        ,index_lock_promotion_count 
        ,page_latch_wait_count 
        ,page_latch_wait_in_ms 
        ,page_io_latch_wait_count 
        ,page_io_latch_wait_in_ms, 
        now) 
select  database_id 
        ,object_id 
        ,index_id 
        ,partition_number 
        ,leaf_insert_count 
        ,leaf_delete_count 
        ,leaf_update_count 
        ,leaf_ghost_count 
        ,nonleaf_insert_count 
        ,nonleaf_delete_count 
        ,nonleaf_update_count 
        ,leaf_allocation_count 
        ,nonleaf_allocation_count 
        ,leaf_page_merge_count 
        ,nonleaf_page_merge_count 
        ,range_scan_count 
        ,singleton_lookup_count 
        ,forwarded_fetch_count 
        ,lob_fetch_in_pages 
        ,lob_fetch_in_bytes 
        ,lob_orphan_create_count 
        ,lob_orphan_insert_count 
        ,row_overflow_fetch_in_pages 
        ,row_overflow_fetch_in_bytes 
        ,column_value_push_off_row_count 
        ,column_value_pull_in_row_count 
        ,row_lock_count 
        ,row_lock_wait_count 
        ,row_lock_wait_in_ms 
        ,page_lock_count 
        ,page_lock_wait_count 
        ,page_lock_wait_in_ms 
        ,index_lock_promotion_attempt_count 
        ,index_lock_promotion_count 
        ,page_latch_wait_count 
        ,page_latch_wait_in_ms 
        ,page_io_latch_wait_count 
        ,page_io_latch_wait_in_ms 
        ,@now  
from sys.dm_db_index_operational_stats(@dbid,@objid,@indid,@partitionid) 
go

init_index_operational_stats

CREATE proc dbo.init_index_operational_stats 
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 
-- 
-- T. Davidson 
--  
-- create indexstats table if it doesn't exist, otherwise truncate 
-- 
set nocount on 
if not exists (select 1 from dbo.sysobjects where  
id=object_id(N'[dbo].[indexstats]') and OBJECTPROPERTY(id, N'IsUserTable') 
= 1) 
    create table dbo.indexstats ( 
        database_id smallint NOT NULL 
        ,object_id int NOT NULL 
        ,index_id int NOT NULL 
        ,partition_number int NOT NULL 
        ,leaf_insert_count bigint NOT NULL 
        ,leaf_delete_count bigint NOT NULL 
        ,leaf_update_count bigint NOT NULL 
        ,leaf_ghost_count bigint NOT NULL 
        ,nonleaf_insert_count bigint NOT NULL 
        ,nonleaf_delete_count bigint NOT NULL 
        ,nonleaf_update_count bigint NOT NULL 
        ,leaf_allocation_count bigint NOT NULL 
        ,nonleaf_allocation_count bigint NOT NULL 
        ,leaf_page_merge_count bigint NOT NULL 
        ,nonleaf_page_merge_count bigint NOT NULL 
        ,range_scan_count bigint NOT NULL 
        ,singleton_lookup_count bigint NOT NULL 
        ,forwarded_fetch_count bigint NOT NULL 
        ,lob_fetch_in_pages bigint NOT NULL 
        ,lob_fetch_in_bytes bigint NOT NULL 
        ,lob_orphan_create_count bigint NOT NULL 
        ,lob_orphan_insert_count bigint NOT NULL 
        ,row_overflow_fetch_in_pages bigint NOT NULL 
        ,row_overflow_fetch_in_bytes bigint NOT NULL 
        ,column_value_push_off_row_count bigint NOT NULL 
        ,column_value_pull_in_row_count bigint NOT NULL 
        ,row_lock_count bigint NOT NULL 
        ,row_lock_wait_count bigint NOT NULL 
        ,row_lock_wait_in_ms bigint NOT NULL 
        ,page_lock_count bigint NOT NULL 
        ,page_lock_wait_count bigint NOT NULL 
        ,page_lock_wait_in_ms bigint NOT NULL 
        ,index_lock_promotion_attempt_count bigint NOT NULL 
        ,index_lock_promotion_count bigint NOT NULL 
        ,page_latch_wait_count bigint NOT NULL 
        ,page_latch_wait_in_ms bigint NOT NULL 
        ,page_io_latch_wait_count bigint NOT NULL 
        ,page_io_latch_wait_in_ms bigint NOT NULL 
        ,now datetime default getdate()) 
else     truncate table dbo.indexstats 
go

add_column

create proc dbo.add_column ( 
            @add_stmt varchar(500) output, 
            @find varchar(100)=NULL, 
            @cols_containing varchar(500)=NULL,     
            @col_stmt varchar(max)) 
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 
-- 
-- T. Davidson 
-- @add_stmt is the result passed back to the caller 
-- @find is a keyword from @cols_containing 
-- @cols_containing is the list of keywords to include in the report 
-- @col_stmt is the statement that will be compared with @find.  
--            If @col_stmt contains @find, include this statement.  
--            set @add_stmt = @col_stmt 
--  
declare @length int, @strindex int, @EOS bit 
if @cols_containing is NULL  
    begin 
        select @add_stmt=@col_stmt 
        return 
    end 
select @add_stmt = '', @EOS = 0 
 
while @add_stmt is not null and @EOS = 0  
            @dbid=-1, 
    select @strindex = charindex(',',@cols_containing) 
    if @strindex = 0 
            select @find = @cols_containing, @EOS = 1 
    else 
    begin 
        select @find = substring(@cols_containing,1,@strindex-1) 
        select @cols_containing =       
            substring(@cols_containing, 
                      @strindex+1, 
                      datalength(@cols_containing) - @strindex) 
    end 
    select @add_stmt=case 
--when @cols_containing is NULL then NULL 
    when charindex(@find,@col_stmt) > 0 then NULL 
    else '' 
    end 
end 
--- NULL indicates this statement is to be passed back through out parm  
@add_stmt 
if @add_stmt is NULL select @add_stmt=@col_stmt 
go

Wait states

이 저장 프로시저들은 차단 분석에 사용할 수 있습니다.

track_waitstats_2005

CREATE proc [dbo].[track_waitstats_2005] ( 
                            @num_samples int=10, 
                            @delay_interval int=1, 
                            @delay_type nvarchar(10)='minutes', 
                            @truncate_history nvarchar(1)='N', 
                            @clear_waitstats nvarchar(1)='Y') 
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 
-- 
-- T. Davidson 
-- @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 - can be minutes or seconds 
-- delaytype specifies whether the delay interval is minutes or seconds 
-- create waitstats table if it doesn't exist, otherwise truncate 
-- Revision: 4/19/05  
--- (1) added object owner qualifier 
--- (2) optional parameters to truncate history and clear waitstats 
set nocount on 
if not exists (select 1  
               from sys.objects  
               where object_id = object_id ( N'[dbo].[waitstats]') and  
               OBJECTPROPERTY(object_id, N'IsUserTable') = 1) 
    create table [dbo].[waitstats]  
        ([wait_type] nvarchar(60) not null,  
        [waiting_tasks_count] bigint not null, 
        [wait_time_ms] bigint not null, 
        [max_wait_time_ms] bigint not null, 
        [signal_wait_time_ms] bigint not null, 
        now datetime not null default getdate()) 
 
If lower(@truncate_history) not in (N'y',N'n') 
    begin 
        raiserror ('valid @truncate_history values are ''y'' or  
''n''',16,1) with nowait     
    end 
If lower(@clear_waitstats) not in (N'y',N'n') 
    begin 
        raiserror ('valid @clear_waitstats values are ''y'' or  
''n''',16,1) with nowait     
    end 
If lower(@truncate_history) = N'y'  
    truncate table dbo.waitstats 
 
If lower (@clear_waitstats) = N'y'  
    -- clear out waitstats 
    dbcc sqlperf ([sys.dm_os_wait_stats],clear) with no_infomsgs  
 
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(@delay_type) 
    when N'minutes' then 'm' 
    when N'minute' then 'm' 
    when N'min' then 'm' 
    when N'mi' then 'm' 
    when N'n' then 'm' 
    when N'm' then 'm' 
    when N'seconds' then 's' 
    when N'second' then 's' 
    when N'sec' then 's' 
    when N'ss' then 's' 
    when N's' then 's' 
    else @delay_type 
end 
 
if @dt not in ('s','m') 
begin 
    raiserror ('delay type must be either ''seconds'' or  
''minutes''',16,1) with nowait 
    return 
end 
if @dt = 's' 
begin 
    select @sec = @delay_interval % 60, @min = cast((@delay_interval / 60)  
as int), @hr = cast((@min / 60) as int) 
end 
if @dt = 'm' 
begin 
    select @sec = 0, @min = @delay_interval % 60, @hr =  
cast((@delay_interval / 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 'delay interval and type: ' + convert  
(varchar(10),@delay_interval) + ',' + @delay_type + ' converts to ' +  
@delay 
    raiserror ('hh:mm:ss delay time cannot > 23:59:59',16,1) with nowait 
    return 
end 
while (@i <= @num_samples) 
begin 
    select @now = getdate() 
    insert into [dbo].[waitstats] ( 
                        [wait_type],  
                        [waiting_tasks_count],  
                        [wait_time_ms],  
                        [max_wait_time_ms],  
                        [signal_wait_time_ms],  
                        now)     
            select  
                [wait_type],  
                [waiting_tasks_count],  
                [wait_time_ms],  
                [max_wait_time_ms],  
                [signal_wait_time_ms],  
                @now 
            from sys.dm_os_wait_stats 
 
    insert into [dbo].[waitstats] ( 
                        [wait_type],  
                        [waiting_tasks_count],  
                        [wait_time_ms],  
                        [max_wait_time_ms],  
                        [signal_wait_time_ms],  
                        now)     
            select  
                'Total', 
                sum([waiting_tasks_count]),  
                sum([wait_time_ms]),  
                0,  
                sum([signal_wait_time_ms]), 
                @now 
            from [dbo].[waitstats] 
            where now = @now 
 
    select @i = @i + 1 
    waitfor delay @delay 
end 
--- create waitstats report 
execute dbo.get_waitstats_2005 
go 
exec dbo.track_waitstats @num_samples=6 
    ,@delay_interval=30 
    ,@delay_type='s' 
    ,@truncate_history='y' 
    ,@clear_waitstats='y'

get_waitstats_2005

CREATE proc [dbo].[get_waitstats_2005] ( 
                @report_format varchar(20)='all',  
                @report_order varchar(20)='resource') 
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.  
--     (1) total wait time is the sum of resource & signal waits,  
--            @report_format='all' reports resource & signal 
--    (2) Basics of execution model (simplified) 
--        a. spid is running then needs unavailable resource, moves to  
--         resource wait list at time T0 
--        b. a signal indicates resource available, spid moves to  
--         runnable queue at time T1 
--        c. spid awaits running status until T2 as cpu works its way 
--         through runnable queue in order of arrival 
--    (3) resource wait time is the actual time waiting for the 
--        resource to be available, T1-T0 
--    (4) signal wait time is the time it takes from the point the  
--        resource is available (T1) 
--        to the point in which the process is running again at T2.  
--        Thus, signal waits are T2-T1 
--    (5) Key questions: Are Resource and Signal time significant? 
--        a. Highest waits indicate the bottleneck you need to solve  
--          for scalability 
--        b. Generally if you have LOW% SIGNAL WAITS, the CPU is  
--         handling the workload e.g. spids spend move through  
--         runnable queue quickly 
--        c. HIGH % SIGNAL WAITS indicates CPU can't keep up,  
--         significant time for spids to move up the runnable queue  
--         to reach running status 
--     (6) This proc can be run when track_waitstats is executing 
-- 
-- Revision 4/19/2005 
-- (1) add computation for CPU Resource Waits = Sum(signal waits /  
--                                    total waits) 
-- (2) add @report_order parm to allow sorting by resource, signal  
--     or total waits 
-- 
set nocount on 
 
declare @now datetime,  
        @totalwait numeric(20,1),  
        @totalsignalwait numeric(20,1),  
        @totalresourcewait numeric(20,1), 
        @endtime datetime,@begintime datetime, 
        @hr int,  
        @min int,  
        @sec int 
 
if not exists (select 1  
                from sysobjects  
                where id = object_id ( N'[dbo].[waitstats]') and  
                      OBJECTPROPERTY(id, N'IsUserTable') = 1) 
begin 
        raiserror('Error [dbo].[waitstats] table does not exist', 
                 16, 1) with nowait 
        return 
end 
 
if lower(@report_format) not in ('all','detail','simple') 
    begin 
        raiserror ('@report_format must be either ''all'', 
                    ''detail'', or ''simple''',16,1) with nowait 
        return 
    end 
if lower(@report_order) not in ('resource','signal','total') 
    begin 
        raiserror ('@report_order must be either ''resource'',  
            ''signal'', or ''total''',16,1) with nowait 
        return 
    end 
if lower(@report_format) = 'simple' and lower(@report_order) <> 'total' 
    begin 
        raiserror ('@report_format is simple so order defaults to  
''total''', 
                        16,1) with nowait 
        select @report_order = 'total' 
    end 
 
 
select   
    @now=max(now), 
    @begintime=min(now), 
    @endtime=max(now) 
from [dbo].[waitstats]  
where [wait_type] = 'Total' 
 
--- subtract waitfor, sleep, and resource_queue from Total 
select @totalwait = sum([wait_time_ms]) + 1, @totalsignalwait =  
sum([signal_wait_time_ms]) + 1  
from waitstats  
where [wait_type] not in ( 
        'CLR_SEMAPHORE', 
        'LAZYWRITER_SLEEP', 
        'RESOURCE_QUEUE', 
        'SLEEP_TASK', 
        'SLEEP_SYSTEMTASK', 
        'Total' ,'WAITFOR',  
        '***total***') and  
    now = @now 
 
select @totalresourcewait = 1 + @totalwait - @totalsignalwait 
 
-- insert adjusted totals, rank by percentage descending 
delete waitstats  
where [wait_type] = '***total***' and  
now = @now 
 
insert into waitstats  
select  
    '***total***', 
    0,@totalwait, 
    0, 
    @totalsignalwait, 
    @now  
 
select 'start time'=@begintime,'end time'=@endtime, 
       'duration (hh:mm:ss:ms)'=convert(varchar(50),@endtime- 
@begintime,14), 
       'report format'=@report_format, 'report order'=@report_order 
 
if lower(@report_format) in ('all','detail')  
begin 
----- format=detail, column order is resource, signal, total. order by  
resource desc 
    if lower(@report_order) = 'resource' 
        select [wait_type],[waiting_tasks_count], 
            'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms], 
            'res_wt_%'=cast (100*([wait_time_ms] -  
                    [signal_wait_time_ms]) /@totalresourcewait as  
numeric(20,1)), 
            'Signal wt (T2-T1)'=[signal_wait_time_ms], 
            'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as  
numeric(20,1)), 
            'Total wt (T2-T0)'=[wait_time_ms], 
            'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1)) 
        from waitstats  
        where [wait_type] not in ( 
                'CLR_SEMAPHORE', 
                'LAZYWRITER_SLEEP', 
                'RESOURCE_QUEUE', 
                'SLEEP_TASK', 
                'SLEEP_SYSTEMTASK', 
                'Total',  
                'WAITFOR') and 
                now = @now 
        order by 'res_wt_%' desc 
 
----- format=detail, column order signal, resource, total. order by signal 
desc 
    if lower(@report_order) = 'signal' 
        select    [wait_type], 
                [waiting_tasks_count], 
                'Signal wt (T2-T1)'=[signal_wait_time_ms], 
                'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait  
                as numeric(20,1)), 
                'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms], 
                'res_wt_%'=cast (100*([wait_time_ms] -  
                        [signal_wait_time_ms]) /@totalresourcewait as  
numeric(20,1)), 
                'Total wt (T2-T0)'=[wait_time_ms], 
                'wt_%'=cast (100*[wait_time_ms]/@totalwait as  
numeric(20,1)) 
        from waitstats  
        where [wait_type] not in ( 
                    'CLR_SEMAPHORE', 
                    'LAZYWRITER_SLEEP', 
                    'RESOURCE_QUEUE', 
                    'SLEEP_TASK', 
                    'SLEEP_SYSTEMTASK', 
                    'Total',  
                    'WAITFOR') and 
                    now = @now 
        order by 'sig_wt_%' desc 
 
----- format=detail, column order total, resource, signal. order by total 
 desc 
    if lower(@report_order) = 'total' 
        select  
            [wait_type], 
            [waiting_tasks_count], 
            'Total wt (T2-T0)'=[wait_time_ms], 
            'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1)), 
            'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms], 
            'res_wt_%'=cast (100*([wait_time_ms] -  
                [signal_wait_time_ms]) /@totalresourcewait as numeric(20,1)), 
            'Signal wt (T2-T1)'=[signal_wait_time_ms], 
            'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as 
 numeric(20,1)) 
        from waitstats  
        where [wait_type] not in ( 
                'CLR_SEMAPHORE', 
                'LAZYWRITER_SLEEP', 
                'RESOURCE_QUEUE', 
                'SLEEP_TASK', 
                'SLEEP_SYSTEMTASK', 
                'Total', 
                'WAITFOR') and 
                now = @now 
        order by 'wt_%' desc 
end 
else 
---- simple format, total waits only 
    select  
        [wait_type], 
        [wait_time_ms], 
        percentage=cast (100*[wait_time_ms]/@totalwait as numeric(20,1)) 
    from waitstats  
    where [wait_type] not in ( 
                    'CLR_SEMAPHORE', 
                    'LAZYWRITER_SLEEP', 
                    'RESOURCE_QUEUE', 
                    'SLEEP_TASK', 
                    'SLEEP_SYSTEMTASK', 
                    'Total',  
                    'WAITFOR') and 
                now = @now 
    order by percentage desc 
 
---- compute cpu resource waits 
select  
    'total waits'=[wait_time_ms], 
    'total signal=CPU waits'=[signal_wait_time_ms], 
    'CPU resource waits % = signal waits / total waits'= 
            cast (100*[signal_wait_time_ms]/[wait_time_ms] as 
 numeric(20,1)),  
    now 
from [dbo].[waitstats] 
where [wait_type] = '***total***' 
order by now 
go 
 
 
declare @now datetime  
select @now = getdate() 
select getdate()