接著上一章：CPU瓶頸

I/O瓶頸（I/O Bottlenecks）:

SQLServer的效能嚴重依賴I/O子系統。除非你的資料庫完全載入到實體記憶體中，否則SQLServer會不斷地把資料庫檔案從快取池中搬進搬出，這會引起大量的I/O傳輸。同樣地，日誌記錄在事務被宣告為已提交前必須寫入磁碟。最後，SQLServer基於許多原因使用tempdb，比如儲存臨時結果、排序和保持行版本。所以一個好的I/O子系統是SQLServer效能關鍵。
除非資料檔案包括tempdb需要回滾事務，否則日誌檔案是順序訪問的。而資料檔案和tempdb是隨機訪問的。所以作為常規規則，你應該把日誌檔案與資料檔案分離到獨立的磁碟中。本文不是關注於如何配置你的I/O裝置，但關注於如何識別你的系統是否有I/O瓶頸。在I/O瓶頸被識別之後，你應該重新配置你的I/O子系統。
如果你的I/O子系統很慢，你的使用者將體驗得到效能問題，響應時間過慢和因為超時而導致任務失敗。
可以使用以下的效能計數器去識別I/O瓶頸。但是要注意，如果你的收集間隔過短，那麼平均值會趨向傾斜於低值那段。比如，很難說明為什麼I/O會每60秒漲跌。同時，你也不能僅僅根據一個計數器的值來確定是否有瓶頸。需要通過多個值來反覆驗證你的想法：
PhysicalDisk Object:Avg.Disk Queue：物理讀寫請求鎖等待的平均佇列值。當該值長期超過2時，你的系統可能存在I/O瓶頸了。
Avg.Disk Sec/Read：是一個平均秒數，是每秒從磁碟上讀取資料的次數，下面是值及其代表意思：
• 小於10ms ——非常好
• 10~20ms——OK
• 20~50ms——慢，需要重視
• 大於50ms——嚴重的I/O瓶頸。
Avg.Disk Sec/Write：與Avg.Disk Sec/Read相對應。
Physical Disk:%Disk Time：是針對被選定的磁碟忙於讀寫請求所執行時間的百分數。一般的指標線是大於50%就意味著有I/O瓶頸。
Avg.Disk Reads/Sec：是讀操作在磁碟上的頻率。確保這個頻率低於磁碟極限的85%，當超過了85%後，訪問時間就會以指數速度增加。
Avg.Disk Writes/Sec：於Avg.Disk Reads/Sec相對應。
當你使用這些計數器時，你需要對於RAID作出調整，可以使用以下公式：
 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
比如，如果你有一個RAID-1，使用兩個物理磁碟，計數器值為：
Disk Reads/sec 80
Disk Writes/sec 70
Avg.Disk Queue length 5
這樣通過公式計算：(80 + (2 * 70))/2 = 110 I/Os 每個磁碟，而你的磁碟等待佇列長度等於5/2=2.5。意味著已經到達了I/O瓶頸邊界。
你也可以檢查lacth等待來識別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
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問題。對於這種情況，有必要在SQLServer執行正常時，建立效能基線和關鍵的DMV查詢輸出。這些等待型別能顯示出你的I/O子系統是否有嚴重的瓶頸。但它們不提供任何可見的物理磁碟問題
你可以通過下面的DMV查詢來找到目前正在掛起的I/O請求。你可以定期執行下面語句來檢查I/O子系統的健康情況和隔離那些有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_stallio_pending_ms_ticksscheduler_address
-------------------------------------------------------------
6 1 10804780x0227A040
6 1 10804780x0227A040
6 2 101451310x02720040




解決方案：
當你發現有I/O瓶頸時，你第一本能反應可能是升級I/O子系統，以應對目前的工作負載。這種方式當然有效，但是在此之前，你要考慮在硬體投入上的開銷，要檢查I/O瓶頸是否因為不正確的配置和/或查詢計劃導致的。我們建議你根據以下步驟去檢查：
1、 配置（Configuration）：檢查SQLServer的記憶體配置。如果SQLServer配置中存在記憶體不足的問題，這會引起更多I/O開銷。你可以檢查下面的計數器來識別是否存在記憶體壓力：
 Buffer Cache hit ratio
 Page Life Expectancy
 Checkpoint Pages/sec
 Lazywrites/sec
關於記憶體壓力將在記憶體篇詳細說明
2、 查詢計劃：檢查執行計劃和識別哪步導致了更多的I/O消耗。儘可能選擇更好的方法比如索引來最小化I/O。如果存在丟失索引，可以使用DTA來找到。
下面的DMV查詢可以用於發現批處理或者請求產生最多的I/O的查詢。注意這裡不統計物理寫，如果你懂得資料庫是如何運作的，就會知道為什麼。在同一個請求中DML和DDL語句，不是直接把資料頁寫入磁碟，而只有已經提交的事務才會被寫入磁碟。通常物理寫只在checkpoint或者lazywriter發生時才出現。可以使用下面的DMV來查詢產生最多I/O的5個查詢，優化這些查詢以便實現更少的邏輯讀，並進一步緩解快取池的壓力。這樣你能提高其他請求在快取池中直接找到資料的機會（特別在重複執行時），從而替代物理I/O的效能。因此，這個系統的效能都能得到改進。
下面是通過hash join來做兩表關聯的例子：

create table t1 (c1 int primary key, c2 int, c3 char(8000))
   create table t2  (C4 int, c5 char(8000))
go


   --load the data
declare @i int
select @i = 0
while (@i < 6000) 
begin
    insert into t1 values (@i, @i + 1000, 'hello')
   insert into t2 values (@i,'there')
   set @i = @i + 1
end
--now run the following query
select c1, c5
from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4
order by c2 


Run another query so that there are two queries to look at for I/O stats


select SUM(c1) from t1

這兩個查詢在一個批處理中執行，接下來。使用下面的DMV來檢查查詢引起的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, 
    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1 
            THEN LEN(CONVERT(nvarchar(MAX),text)) * 2 
                ELSE statement_end_offset 
            END - statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, 
      (SELECT query_plan from sys.dm_exec_query_plan(plan_handle)) as query_plan
FROM sys.dm_exec_query_stats  
ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC

你當然可以通過改變查詢語句來獲得不同的資料顯示。比如你可以按(total_logical_reads + total_logical_writes)/execution_count 來排序。作為選擇，你可能想去按物理I/O來排序等，但是，邏輯讀寫書對判斷是否有I/O問題是很有用的。輸出類似這樣：
avg_logical_reads    avg_logical_writes   avg_phys_reads       
-----------------    ------------------   ---------------
16639 10   1098
6023 0   0
execution_count      stmt_start_offset
---------------      -----------------
1 0
1 154


Query_text      Query_plan                        
-----------------------------------          -----------
select c1, c5  from t1 INNER HASH JOIN …     <link to query plan>
select SUM(c1) from t1                       <link to query plan>


這些輸出告訴你一些重要的資訊，第一，顯示最多的I/O。你也可以通過SQL Text列來檢視是否可以通過重寫語句來降低I/O。驗證這些執行計劃是否已經最佳的。比如，一個新的索引可能有幫助。第二、第二個批處理不引起任何物理I/O因為所有需要的表的頁面已經快取到緩衝區。第三、執行次數能用於識別是否它是一個一次性查詢或者它是否頻繁執行，因此需要對此詳細考量。
3、 資料壓縮：從2008開始，你能使用資料壓縮來降低表和索引的體積。壓縮程度完全取決於架構和資料分佈。一般情況下，可以達到50~60%的壓縮率。一些特殊情況下可以達到90%。意味著當你能壓縮到50%時，你已經比較有效地降低了I/O。資料壓縮會引起CPU增加。這裡有一些策略：
為什麼不把整個資料庫壓縮？對此，給出一個極端的例子：如果你有一個大表，叫做T，有10頁，而整個資料庫有1000萬頁。壓縮T沒有多大好處。即使SQLServer能把10頁壓縮到1頁，你努力減少資料庫的大小，但你可能會造成CPU的負擔增加。在現實的工作負載中，不能很明顯地作出選擇。但是這個例子只是你在壓縮前要考慮的情況而已。我們的建議是：在你壓縮一個物件之前，使用sp_estimate_data_compression_savings儲存過程來評估它的大小、利用情況和預估壓縮等資訊。注意以下資訊：
 物件的大小是否比資料庫總體大小小很多，這樣的情況不會給你帶來太多好處。
 如果物件經常被用於DML或者SELECT操作，你將面臨比較大的CPU消耗。特別是在CPU已經存在瓶頸時，你可以使用sys.dm_db_index_operational_stats去發現物件使用情況來判斷表、索引、分割槽等等的命中情況。
 壓縮預留情況是基於架構和基於資料的。實際上，一些物件，壓縮後可能會更大。或者節省的空間會微不足道。
如果你有一個分割槽表，且某些分割槽的資料不經常訪問。你可以使用頁壓縮來壓縮分割槽和重組索引。這適用在不長使用的分割槽上。相信資訊可以看：(http://blogs.msdn.com/sqlserverstorageengine/archive/tags/Data+Compression/default.aspx)
4、 升級I/O子系統：如果你確保SQLServer的配置合理，並且檢查執行計劃後仍然存在I/O瓶頸，最後的選擇就只能升級I/O頻寬了：
 增加更多的物理驅動或者更換更快的磁碟。
 增加更快的I/O控制器。

下一章：tempdb

原文：

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 storing intermediate results, sorting, and keeping row versions. 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 data files, including tempdb, are randomly accessed. So as a general rule, you should have log files on a physical disk that is separate from the 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 whether you have I/O bottleneck. After 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 do not complete due to time-outs. 
You can use the following performance counters to identify I/O bottlenecks. Note that 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.
PhysicalDisk Object: Avg. Disk Queue Length represents the average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If your I/O system is overloaded, more read/write operations will be waiting. If your disk queue length frequently exceeds a value of 2 during peak usage of SQL Server, you might have an I/O bottleneck. 
Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. The following list shows ranges of possible values and what the ranges mean:
• 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
Avg. Disk Sec/Write is the average time, in seconds, of a write of data to the disk. The guidelines for the Avg. Disk Sec/Read values apply here.
Physical Disk: %Disk Time is the percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value is greater than 50 percent, there is an I/O bottleneck. 
Avg. Disk Reads/Sec is the rate of read operations on the disk. Ensure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity. 
Avg. Disk Writes/Sec is the rate of write operations on the disk. Ensure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.
When you use these 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 might 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 borderline 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 the I/O has already completed, the worker proceeds normally. Otherwise, it waits on PAGEIOLATCH_EX or PAGEIOLATCH_SH, depending upon the type of request. You can use the following DMV query 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


A sample output follows.


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


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 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 columns to find the physical disk the files are mapped to. The io_pending_ms_ticks values represent the total time individual I/Os are waiting in the pending queue. 


Database_id File_Id io_stallio_pending_ms_ticksscheduler_address
----------------------------------------------------------------------
6 1 10804780x0227A040
6 1 10804780x0227A040
6 2 101451310x02720040
 
Resolution
When you see an I/O bottleneck, your first instinct might be to upgrade the I/O subsystem to meet the workload requirements. This will definitely help, but before you go out and invest money in hardware, examine the I/O bottleneck to see whether it is the result of poor configuration and/or query plans. We recommend you to follow the steps below in strict order.
1. Configuration: Check the memory configuration of SQL Server. If SQL Server has been configured with insufficient memory, it will incur more I/O overhead. You can examine the following counters to identify memory pressure:
• Buffer Cache hit ratio
• Page Life Expectancy
• Checkpoint pages/sec
• Lazywrites/sec
For more information about memory pressure, see Memory Bottlenecks earlier in this paper.
2. Query Plans: Examine execution plans and see which plans lead to more I/O being consumed. It is possible that a better plan (for example, index) can minimize I/O. If there are missing indexes, you may want to run Database Engine Tuning Advisor to find missing indexes.
The following DMV query can be used to find which batches or requests are generating the most I/O. Note that we are not accounting for physical writes. This is okay if you consider how databases work. The DML and DDL statements within a request do not directly write data pages to disk. Instead, the physical writes of pages to disks is triggered by statements only by committing transactions. Usually physical writes are done either by checkpoint or by the SQL Server lazy writer. You can use a DMV query like the following to find the five requests that generate the most I/Os. Tuning those queries so that they perform fewer logical reads can relieve pressure on the buffer pool. This enables other requests to find the necessary data in the buffer pool in repeated executions (instead of performing physical I/O). Hence, overall system performance is improved. 
Here is an example of a query that joins two tables with a hash join.


create table t1 (c1 int primary key, c2 int, c3 char(8000))
   create table t2  (C4 int, c5 char(8000))
go


   --load the data
declare @i int
select @i = 0
while (@i < 6000) 
begin
    insert into t1 values (@i, @i + 1000, 'hello')
   insert into t2 values (@i,'there')
   set @i = @i + 1
end
--now run the following query
select c1, c5
from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4
order by c2 


Run another query so that there are two queries to look at for I/O stats


select SUM(c1) from t1


These two queries are run in the single batch. Next, use the following DMV query to examine the queries that generate the most I/Os 


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, 
    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1 
            THEN LEN(CONVERT(nvarchar(MAX),text)) * 2 
                ELSE statement_end_offset 
            END - statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, 
      (SELECT query_plan from sys.dm_exec_query_plan(plan_handle)) as query_plan
FROM sys.dm_exec_query_stats  
ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC


You can, of course, change this query to get different views on the data. For example, to generate the five requests that generate the most I/Os in single execution, you can order by:
(total_logical_reads + total_logical_writes)/execution_count
Alternatively, you may want to order by physical I/Os and so on. However, logical read/write numbers are very helpful in determining whether or not the plan chosen by the query is optimal. The output of the query is as follows.


avg_logical_reads    avg_logical_writes   avg_phys_reads       
-----------------    ------------------   ---------------
16639 10   1098
6023 0   0
execution_count      stmt_start_offset
---------------      -----------------
1 0
1 154


Query_text      Query_plan                        
-----------------------------------          -----------
select c1, c5  from t1 INNER HASH JOIN …     <link to query plan>
select SUM(c1) from t1                       <link to query plan>


The output tells you several important things. First, it identifies the queries that are generating the most I/Os. You can also look at the SQL text to see whether the query needs to be re-examined to reduce I/Os. Verify that the query plan is optimal. For example, a new index might be helpful. Second, the second query in the batch does not incur any physical I/Os because all the pages needed for table t1 are already in the buffer pool. Third, the execution count can be used to identify whether it is a one-off query or the one that is executed frequently and therefore needs to be looked into carefully.
3. Data Compression: Starting with SQL Server 2008, you can use the data compression feature to reduce the size of tables and indexes, thereby reducing the size of the whole database. The compression achieved depends on the schema and the data distribution. Typically, you can achieve 50-60% compression. We have seen up to 90% compression in some cases. What it means to you is that if you are able to compress you active data 50%, you have in effect reduced your I/O requirements by half. Data compression comes at the cost of additional CPU, which needs to be weighed in for your workload. Here are some general strategies.
First, why isn’t compressing the whole database blindly such a good idea? Well, to give you an extreme example, if you have a heavily used table T with 10 pages in a database with millions of pages, there is no benefit in compressing T. Even if SQL Server could compress 10 pages to 1 page, you hardly made a dent in the size of the database, but you did add some CPU overhead instead. In a real-life workload, the choices are not this obvious, but this example shows that you must look before you compress. Our recommendation is this: Before you compress an object (for example, a table index or a partition), look at its size, usage, and estimated compression savings by using the sp_estimate_data_compression_savings stored procedure. 
Let us look at each of these in some detail:
• If the size of the object is much smaller than the overall size of the database, it does not buy you much.
• If the object is used heavily both for DML and SELECT operations, you will incur additional CPU overhead that can impact your workload, especially if it makes it CPU bound. You can use sys.dm_db_index _operational_stats to find the usage pattern of objects to identify which tables, indexes, and partitions are being hit the most.
• The compression savings are schema-dependent and data-dependent, and in fact, for some objects, the size after compression can be larger than before, or the space savings can be insignificant. 
If you have a partitioned table where data in some partitions is accessed infrequently, you may want to compress those partitions and associated indexes with page compression. This is a common scenario with partitioned tables where older partitions are referenced infrequently. For example, you might have a table in which sales data is partitioned by quarters across many years. Commonly the queries are run on the current quarter; data from other quarters is not referenced as frequently. So when the current quarter ends, you can change the compression setting for that quarter’s partition. 
For more information about data compression, see the SQL Server Storage Engine Blog (http://blogs.msdn.com/sqlserverstorageengine/archive/tags/Data+Compression/default.aspx) and SQL Server 2008 Books Online.
4. Upgrading the I/O Subsystem: If you have confirmed that SQL Server is configured correctly and examined the query plans and you are still experiencing I/O bottlenecks, the last option left is to upgrade your I/O subsystem to increase I/O bandwidth:
• Add more physical drives to the current disk arrays and/or replace your current disks with faster drives. This helps to boost both read and write access times. But don't add more drives to the array than your I/O controller can support.
• Add faster or additional I/O controllers. Consider adding more cache (if possible) to your current controllers.