Troubleshooting Performance Problems in SQL Server 2005

Top of page

Memory Bottlenecks

This section specifically addresses low memory conditions and ways to diagnose them as well as different memory errors, possible reasons for them, and ways to troubleshoot.

Background

It is quite common to refer to different memory resources by using the single generic term memory. As there are several types of memory resources, it is important to understand and differentiate which particular memory resource is referred to.

Virtual address space and physical memory

In Microsoft Windows®, each process has its own virtual address space (VAS). The set of all virtual addresses available for process use constitutes the size of the VAS. The size of the VAS depends on the architecture (32- or 64-bit) and the operating system. In the context of troubleshooting, it is important to understand that virtual address space is a consumable memory resource and an application can run out of it even on a 64-bit platform while physical memory may still be available.

For more information about virtual address space, see “Process Address Space” in SQL Server Books Online and the article called Virtual Address Space (http://msdn2.microsoft.com/en-us/library/aa366912.aspx) on MSDN.

Address Windowing Extensions (AWE) and SQL Server

Address Windowing Extensions (AWE) is an API that allows a 32-bit application to manipulate physical memory beyond the inherent 32-bit address limit. AWE mechanism technically is not necessary on 64-bit platform. It is, however, present there. Memory pages that are allocated through the AWE mechanism are referred as locked pages on the 64-bit platform.

On both 32- and 64-bit platforms, memory that is allocated through the AWE mechanism cannot be paged out. This can be beneficial to the application. (This is one of the reasons for using AWE mechanism on 64-bit platform.) This also affects the amount of RAM that is available to the system and to other applications, which may have detrimental effects. For this reason, in order to use AWE, the Lock Pages in Memory privilege must be enabled for the account that runs SQL Server.

From a troubleshooting perspective, an important point is that the SQL Server buffer pool uses AWE mapped memory; however, only database (hashed) pages can take full advantage of memory allocated through AWE. Memory allocated through the AWE mechanism is not reported by Task Manager or in the Process: Private Bytes performance counter. You need to use SQL Server specific counters or Dynamic Management Views to obtain this information.

For more information about AWE mapped memory, see “Managing memory for large databases” and “Memory Architecture” in SQL Server Books Online  topics and Large Memory Support (http://msdn2.microsoft.com/library/aa366718.aspx) on MSDN.

The following table summarizes the maximum memory support options for different configurations of SQL Server 2005. (Note that a particular edition of SQL Server or Windows may put more restrictive limits on the amount of supported memory.)

Table 1

Memory pressures

Memory pressure denotes a condition when limited amount of memory is available. Identifying when SQL Server runs under a memory pressure will help you troubleshoot memory-related issues. SQL Server responds differently depending on the type of memory pressure that is present. The following table summarizes the types of memory pressures, and their general underlying causes. In all cases, you are more likely to see timeout or explicit out-of-memory error messages.

Table 2

Windows has a notification mechanism¹ if physical memory is running high or low. SQL Server uses this mechanism in its memory management decisions.

General troubleshooting steps in each case are explained in Table 3.

Table 3

Tools

The following tools and sources of information could be used for troubleshooting.

Detecting memory pressures

Memory pressure by itself does not indicate a problem. Memory pressure is a necessary but not a sufficient condition for the server to encounter memory errors later on. Working under memory pressure could be a normal operating condition of the server. However, signs of memory pressure may indicate that the server runs close to its capacity and the potential for out-of-memory errors exists. In the case of normally operating server, this information could serve as a baseline for determining reasons for out-of-memory conditions later.

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

External virtual memory pressure

You need to determine if page file(s) have enough space to accommodate current memory allocations. To check this, open Task Manager in Performance view and check the Commit Charge section. If Total is close to the Limit, then there exists the potential that page file space may be running low. Limit indicates the maximum amount of memory that can be committed without extending page file space. Note that the Commit Charge Total in Task Manager indicates the potential for page file use, not the actual use. Actual use of the page file will increase under physical memory pressure.

Equivalent information can be obtained from the following counters: Memory: Commit Limit, Paging File: %Usage, Paging File: %Usage Peak.

You can roughly estimate the amount of memory that is paged out per process by subtracting the value of Process: Working Set from the Process Private Bytes counters.

If Paging File: %Usage Peak (or Peak Commit Charge) is high, check the System Event Log for events indicating page file growth or notifications of “running low on virtual memory”. You may need to increase the size of your page file(s). High Paging File: %Usage indicates a physical memory over commitment and should be considered together with external physical memory pressure (large consumers, adequate amount of RAM installed).

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

-- amount of mem allocated though multipage  
allocator interface 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

-- amount of memory consumed by components 
outside the Buffer pool  
-- note that we exclude single_pages_kb as 
they come from BPool 
-- BPool is accounted for by the next query 
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' 
 
-- amount of memory consumed by BPool 
-- note that currenlty only BPool uses AWE 
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 
); 
-- note that this total excludes buffer pool  
committed memory as it represents the largest 
consumer which is normal 
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

-- top 10 consumers of memory from BPool 
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

Caches and memory pressure

An alternative way to look at external and internal memory pressure is to look at the behavior of memory caches.

One of the differences of internal implementation of SQL Server  2005  compared to SQL Server 2000 is uniform caching framework. In order to remove the least recently used entries from caches, the framework implements a clock algorithm. Currently it uses two clock hands—an internal clock hand and an external clock hand.

The internal clock hand controls the size of a cache relative to other caches. It starts moving when the framework predicts that the cache is about to reach its cap.

the external clock hand starts to move when SQL Server as a whole gets into memory pressure. Movement of the external clock hand can be due external as well as internal memory pressure. Do not confuse movement of the internal and external clock hands with internal and external memory pressure.

Information about clock hands movements is exposed through the sys.dm_os_memory_cache_clock_hands DMV as shown in the following code. Each cache entry has a separate row for the internal and the external clock hand. If you see increasing rounds_count and removed_all_rounds_count, then the server is under the internal/external memory pressure.

select * 
from  
    sys.dm_os_memory_cache_clock_hands 
where  
    rounds_count > 0 
    and removed_all_rounds_count > 0

You can get additional information about the caches such as their size by joining with sys.dm_os_cache_counters DMV as follows.

Note: Some parts of the code snippet presented in the following table have been displayed in multiple lines only for better readability. These should be entered in a single line.

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) 
/* 
--uncomment this block to have the information only 
for moving hands caches 
where  
    ch.rounds_count > 0 
    and ch.removed_all_rounds_count > 0 
*/ 
order by total_kb desc

Note that for USERSTORE entries, the amount of pages in use is not reported and thus will be NULL.

Ring buffers

Significant amount of diagnostic memory information can be obtained from the sys.dm_os_ring_buffers ring buffers DMV. Each ring buffer keeps a record of the last number of notifications of a certain kind. Detailed information on specific ring buffers is provided next.

RING_BUFFER_RESOURCE_MONITOR

You can use information from resource monitor notifications to identify memory state changes. Internally, SQL Server has a framework that monitors different memory pressures. When the memory state changes, the resource monitor task generates a notification. This notification is used internally by the components to adjust their memory usage according to the memory state and it is exposed to the user through sys.dm_os_ring_buffers DMV as in the following code.

select record  
from sys.dm_os_ring_buffers  
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

A record may look like this:

Note: Some parts of the code snippet presented in the following table have been displayed in multiple lines only for better readability. These should be entered in a single line.

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

From this record, you can deduce that the server received a low physical memory notification. You can also see the amounts of memory in kilobytes. You can query this information by using the XML capabilities of SQL Server, for example in the following code.

Note: Some parts of the code snippet presented in the following table have been displayed in multiple lines only for better readability. These should be entered in a single line.

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

Upon receiving a memory low notification, the buffer pool recalculates its target. Note that the target count stays within the limits specified by the min server memory and max server memory options. If the new committed target for the buffer pool is lower than the currently committed buffers, the buffer pool starts shrinking until external physical memory pressure is removed. Note that SQL Server 2000 did not react to physical memory pressure when running with AWE enabled.

RING_BUFFER_OOM

This ring buffer will contain records indicating server out-of-memory conditions as in the following code example.

select record  
from sys.dm_os_ring_buffers  
where ring_buffer_type = 'RING_BUFFER_OOM'

A record may look like this:

<Record id="7301" type="RING_BUFFER_OOM" time="345640123"> 
    <OOM> 
        <Action>FAIL_VIRTUAL_COMMIT</Action>  
        <Resources>4096</Resources>  
    </OOM>

This record tells which operation has failed (commit, reserve, or page allocation) and the amount of memory requested.

RING_BUFFER_MEMORY_BROKER and Internal Memory Pressure

As internal memory pressure is detected, low memory notification is turned on for components that use the buffer pool as the source of memory allocations. Turning on low memory notification allows reclaiming the pages from caches and other components using them.

Internal memory pressure can also be triggered by adjusting the max server memory option or when the percentage of the stolen pages from the buffer pool exceeds 80%.

Internal memory pressure notifications (‘Shrink’) can be observed by querying memory broker ring buffer as in the following code example.

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

This ring buffer will contain records indicating severe buffer pool failures, including buffer pool out of memory conditions.

select record  
from sys.dm_os_ring_buffers  
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'

A record may look like this:

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

This record will tell what failure (FAIL_OOM, FAIL_MAP, FAIL_RESERVE_ADJUST, FAIL_LAZYWRITER_NO_BUFFERS) and the buffer pool status at the time.

Internal virtual memory pressure

VAS consumption can be tracked by using the sys.dm_os_virtual_address_dump DMV. VAS summary can be queries using the following view.

Note: Some parts of the code snippet presented in the following table have been displayed in multiple lines only for better readability. These should be entered in a single line.

-- virtual address space summary view 
-- generates a list of SQL Server regions 
-- showing number of reserved and free regions 
of a given size  
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 
( 
    --- combine all allocation according with allocation 
base, don't take into 
    --- account allocations with zero allocation_base 
    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   
       --- we shouldn't be grouping allocations with 
       zero allocation base 
       --- just get them as is 
    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

The following queries can be used to assess VAS state.

-- available memory in all free regions 
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]  
FROM VASummary  
WHERE Free <> 0 
 
-- get size of largest availble region 
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]  
FROM VASummary  
WHERE Free <> 0

If the largest available region is smaller than 4 MB, we are likely to be experiencing VAS pressure. SQL Server 2005 monitors and responds to VAS pressure. SQL Server 2000 does not actively monitor for VAS pressure, but reacts by clearing caches when an out-of-virtual-memory error occurs.

Memory errors

701 - There is insufficient system memory to run this query.

Causes

This is very generic out-of-memory error for the server. It indicates a failed memory allocation. It can be due to a variety of reasons, including hitting memory limits on the current workload. With increased memory requirements for SQL Server 2005 and certain configuration settings (such as the max server memory option) users are more likely to see this error as compared to SQL Server 2000. Usually the transaction that failed is not the cause of this error.

Troubleshooting

Regardless of whether the error is consistent and repeatable (same state) or random (appears at random times with different states), you will need to investigate server memory distribution during the time you see this error. When this error is present, it is possible that the diagnostic queries will fail. Start investigation from external assessment. Follow the steps outlined in General troubleshooting steps in case of memory errors.

Possible solutions include: Remove external memory pressure. Increase the max server memory setting. Free caches by using one of the following commands: DBCC FREESYSTEMCACHE, DBCC FREESESSIONCACHE, or DBCC FREEPROCCACHE. If the problem reappears, reduce workload.

802 - There is insufficient memory available in the buffer pool.

Causes

This error does not necessarily indicate an out-of-memory condition. It might indicate that the buffer pool memory is used by someone else. In SQL Server 2005, this error should be relatively rare.

Troubleshooting

Use the general troubleshooting steps and recommendations outlined for the 701 error.

8628 - A time out occurred while waiting to optimize the query. Rerun the query.

Causes

This error indicates that a query compilation process failed because it was unable to obtain the amount of memory required to complete the process. As a query undergoes through the compilation process, which includes parsing, algebraization, and optimization, its memory requirements may increase. Thus the query will compete for memory resources with other queries. If the query exceeds a predefined timeout (which increases as the memory consumption for the query increases) while waiting for resources, this error is returned. The most likely reason for this is the presence of a number of large query compilations on the server.

Troubleshooting

Small Gateway                  Value 
------------------------------ -------------------- 
Configured Units               8 
Available Units                8 
Acquires                       0 
Waiters                        0 
Threshold Factor               250000 
Threshold                      250000 
 
(6 row(s) affected) 
 
Medium Gateway                 Value 
------------------------------ -------------------- 
Configured Units               2 
Available Units                2 
Acquires                       0 
Waiters                        0 
Threshold Factor               12 
 
(5 row(s) affected) 
 
Big Gateway                    Value 
------------------------------ -------------------- 
Configured Units               1 
Available Units                1 
Acquires                       0 
Waiters                        0 
Threshold Factor               8

8645 - A time out occurred while waiting for memory resources to execute the query. Rerun the query.

Causes

This error indicates that many concurrent memory intensive queries are being executed on the server. Queries that use sorts (ORDER BY) and joins may consume significant amount of memory during execution. Query memory requirements are significantly increased if there is a high degree of parallelism enabled or if a query operates on a partitioned table with non-aligned indexes. A query that cannot get the memory resources it requires within the predefined timeout (by default, the timeout is 25 times the estimated query cost or the sp_configure ‘query wait’ amount if set) receives this error. Usually, the query that receives the error is not the one that is consuming the memory.

Troubleshooting

8651 - Could not perform the requested operation because the minimum query memory is not available. Decrease the configured value for the 'min memory per query' server configuration option.

Causes

Causes in part are similar to the 8645 error; it may also be an indication of general memory low condition on the server. A min memory per query option setting that is too high may also generate this error.

Troubleshooting

Top of page

I/O Bottlenecks

SQL Server performance depends heavily on the I/O subsystem. Unless your database fits into physical memory, SQL Server constantly brings database pages in and out of the buffer pool. This generates substantial I/O traffic. Similarly, the log records need to be flushed to the disk before a transaction can be declared committed. And finally, SQL Server uses tempdb for various purposes such as to store intermediate results, to sort, to keep row versions and so on. So a good I/O subsystem is critical to the performance of SQL Server.

Access to log files is sequential except when a transaction needs to be rolled back while access to data files, including tempdb, is randomly accessed. So as a general rule, you should have log files on a separate physical disk than data files for better performance. The focus of this paper is not how to configure your I/O devices but to describe ways to identify if you have I/O bottleneck. Once an I/O bottleneck is identified, you may need to reconfigure your I/O subsystem.

If you have a slow I/O subsystem, your users may experience performance problems such as slow response times, and tasks that abort due to timeouts.

You can use the following performance counters to identify I/O bottlenecks. Note, these AVG values tend to be skewed (to the low side) if you have an infrequent collection interval. For example, it is hard to tell the nature of an I/O spike with 60-second snapshots. Also, you should not rely on one counter to determine a bottleneck; look for multiple counters to cross check the validity of your findings.

Less than 10 ms - very good 
Between 10 - 20 ms - okay 
Between 20 - 50 ms - slow, needs attention 
Greater than 50 ms – Serious I/O bottleneck

When using above counters, you may need to adjust the values for RAID configurations using the following formulas.

Raid 0 -- I/Os per disk = (reads + writes) / number of disks 
Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2 
Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks 
Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks

For example, you have a RAID-1 system with two physical disks with the following values of the counters.

Disk Reads/sec            80 
Disk Writes/sec           70 
Avg. Disk Queue Length    5

In that case, you are encountering (80 + (2 * 70))/2 = 110 I/Os per disk and your disk queue length = 5/2 = 2.5 which indicates a border line I/O bottleneck.

You can also identify I/O bottlenecks by examining the latch waits. These latch waits account for the physical I/O waits when a page is accessed for reading or writing and the page is not available in the buffer pool. When the page is not found in the buffer pool, an asynchronous I/O is posted and then the status of the I/O is checked. If I/O has already completed, the worker proceeds normally. Otherwise, it waits on PAGEIOLATCH_EX or PAGEIOLATCH_SH, depending upon the type of request. The following DMV query can be used to find I/O latch wait statistics.

Select  wait_type,  
        waiting_tasks_count,  
        wait_time_ms 
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

Here the latch waits of interest are the underlined ones. When the I/O completes, the worker is placed in the runnable queue. The time between I/O completions until the time the worker is actually scheduled is accounted under the signal_wait_time_ms column. You can identify an I/O problem if your waiting_task_counts and wait_time_ms deviate significantly from what you see normally. For this, it is important to get a baseline of performance counters and key DMV query outputs when SQL Server is running smoothly. These wait_types can indicate whether your I/O subsystem is experiencing a bottleneck, but they do not provide any visibility on the physical disk(s) that are experiencing the problem.

You can use the following DMV query to find currently pending I/O requests. You can execute this query periodically to check the health of I/O subsystem and to isolate physical disk(s) that are involved in the I/O bottlenecks.

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

A sample output is as follows. It shows that on a given database, there are three pending I/Os at this moment. You can use the database_id and file_id to find the physical disk the files are mapped to. The io_pending_ms_ticks represent the total time individual I/Os are waiting in the pending queue.

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

Resolution

When you have identified an I/O bottleneck, you can address it by doing one or more of the following:

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 
 
Batch-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) 
 
Here is the output. 
 
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)

Top of page

Tempdb

Tempdb globally stores both internal and user objects and the temporary tables, objects, and stored procedures that are created during SQL Server operation.

There is a single tempdb for each SQL Server instance. It can be a performance and disk space bottleneck. The tempdb can become overloaded in terms of space available and excessive DDL/DML operations. This can cause unrelated applications running on the server to slow down or fail.

Some of the common issues with tempdb are as follows:

Before we start diagnosing problems with tempdb, let us first look at how the space in tempdb is used. It can be grouped into four main categories.

The total space used by tempdb equal to the User Objects plus the Internal Objects plus the Version Store plus the Free Space.

This free space is same as the performance counter free space in tempdb.

Monitoring tempdb space

It is better to prevent a problem then work to solve it later. You can use the following performance counters to monitor the amount of space tempdb is using.

However, identifying how the different categories, as defined above, are using the disk space in tempdb is a more interesting, and productive, question.

The following query returns the tempdb space used by user and by internal objects. Currently, it provides information for tempdb only.

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

Here is one sample output (with space in KBs).

user_objets_kb   internal_objects_kb   version_store_kb   freespace_kb 
---------------- -------------------- ------------------ ------------ 
8736               128                    64                    448

Note that these calculations don’t account for pages in mixed extents. The pages in mixed extents can be allocated to user and internal objects.

Troubleshooting space issues

User objects, internal objects, and version storage can all cause space issues in tempdb. In this section, we consider how you can troubleshoot each of these categories.

User objects

Since user objects are not owned by any specific sessions, you need to understand the specifications of the application that created them and adjust the tempdb size requirements accordingly. You can find the space used by individual user objects by executing exec sp_spaceused @objname='<user-object>'. For example, you can run the following script to enumerate all the tempdb objects.

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

Version store

SQL Server 2005 provides a row versioning framework that is used to implement new and existing features. Currently, the following features use row versioning framework. For more information about the following features, see SQL Server Books Online.

Row versions are shared across sessions. The creator of the row version has no control over when the row version can be reclaimed. You will need to find and then possibly kill the longest running transaction that is preventing the row version cleanup.

The following query returns the top two longest running transactions that depend on the versions in the version store.

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

Here is a sample output that shows that a transaction with XSN 3 and Transaction ID 8609 has been active for 6523 seconds.

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