Troubleshooting Slow Disk I/O in SQL Server

Troubleshooting Slow Disk I/O in SQL Server


Link tham khảo nhanh: http://blogs.msdn.com/b/askjay/archive/2011/07/08/troubleshooting-slow-disk-i-o-in-sql-server.aspx

http://www.sqltuners.net/blog/13-05-...L_Servers.aspx


https://www.mssqltips.com/sqlservert...ms-sql-server/

http://sqlblog.com/files/folders/beta/entry42453.aspx


I> Disk Bottleneck

1. Xem 2 thông số Papeiolatch_EX và Papeiolatch_HS cột Avg.wait.ms nhỏ hơn 20 là Ok

Chạy query sau và xem hình

SELECT
*
,wait_time_ms/waiting_tasks_count AS 'Avg Wait in ms'
FROM
sys.dm_os_wait_stats
WHERE
waiting_tasks_count > 0
ORDER BY
wait_time_ms DESC

1.1

Processor: %Processor Time



1.1 Dynamic Management Views


Chay 2 query sau xac dinh thoi gian cho de biet la IO bottlenecks






There are some useful Dynamic Management Views (DMVs) to check I/O bottlenecks.
An I/O latch wait occurs when a page is accessed for reading or writing but the page is not available in the buffer pool. It causes waits on PAGEIOLATCH_EX or PAGEIOLATCH_SH, depending upon the type of request. These wait types can indicate an I/O bottleneck. You can query the sys.dm_os_wait_stats DMV to find latch wait statistics. You can identify I/O problems if you save query outputs of waiting_task_counts and wait_time_ms values from a normal working state of your SQL Server and compare these values when performance is degraded.

select *
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%'
order by wait_type asc

Pending I/O requests can be found by querying the following DMVs and can be used to identify which disk is responsible for the bottleneck.

select database_id,
file_id, io_stall,
io_pending_ms_ticks,
scheduler_address

from sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,
sys.dm_io_pending_io_requests as iopior

where iovfs.file_handle = iopior.io_handle

2. Current Disk Queue Length This counter will tell us how far behind the disk currently is running. i.e. how many outstanding IO requests are queued up and awaiting service from the disk

Thông số này nên này nên nhỏ hơn 2 trên 1 disk, nếu ổ cứng lưu trữ SQL Data là Raid thì thông số này không nên lớn hơn 8 (trung bình 2 request/1 single disk là OK)

Lớn hơn 8 or nhiều hơn là 30 or 40... nghĩa là Disk bị bottleneck





II> RAM

3. SQL Server:Buffer Manager ----> Page Life Expectancy

Thông thường SQL Server sẽ lấy hết ram của Server để hoạt động, vì vậy 1 số người tưởng rằng Server thiếu RAM nhưng thực tế thì không.

Do cơ chế hoạt động nên SQL sẽ chiếm hết ram của Server nhưng thực tế thì SQL còn nhiều ram và xem SQL có thiếu ram hay không bằng cách.


Thông số này càng lớn thì RAM trong SQL còn nhiều ---> bình thường và SQL không hiếu RAM

Thông số này nếu nhỏ hơn 300 thì khả năng có vấn đề về RAM





4. Xem thông số Memory: Available Mbytes

Nên free 1GB là ok, nếu không còn RAM thì windows sẽ chạy đến Page File ----> sẽ làm chậm toàn bộ hệ thống ---> bad performance






5. CPU

Processor: %Processor Time ---> khong nen tren 80%





If you are seeing extended periods where this counter is above 80% then CPU can be a bottleneck on your server. The first thing to check is whether SQL Server is using the CPU or if it is another process. You can do this by checking the counter Process: % Processor Time for the sqlservr instance. I haven’t come across this problem nearly as much as I have come across low PLE or high disk latency