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
|
Trích dẫn:
|
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.
|
Trích dẫn:
|
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.
|
Trích dẫn:
|
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
