This morning, while going through my regular SQL
Server Logs reports, for one of our production server, I found a different
error.
SQL Server has encountered 52 occurrence(s) of I/O requests
taking longer than 15 seconds to complete on file [E:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\DATA\TempDB.mdf] in database [TempDB] (2). The OS file
handle is 0x00000884. The offset of the latest long I/O is: 0x00000457490000
First thing that I searched about this error was that
is this a critical message?
And answer I found was YES. Basically when talk about I/O in SQL Server, we always have measurements of mille seconds in our mind and waits of several seconds is considered too odd. SQL Server I/O wait time can be examined by following query:
And answer I found was YES. Basically when talk about I/O in SQL Server, we always have measurements of mille seconds in our mind and waits of several seconds is considered too odd. SQL Server I/O wait time can be examined by following query:
SELECT *
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH%'
How to check you hard
drive performance?
To check, server IO
subsystems I trust on Performance Monitor IO Counter PhysicalDisk Object:
Avg. Disk Queue Length. Monitor this counter for at least 10 minutes. If
the Avg. Disk Queue Length exceeds 2 for next ten minutes for each individual
disk drive in an array, then it is sure that you have IO bottleneck.
Who is the culprit, SQL Server or Operating System?
Problem is only your SAN or
Local disk IO subsystem. In my case, I found that few other applications were
also installed by client on same drive and which were pushing SQL Server to wait
for too long to complete its IO requests.
------------------------------------------------------------------------------------
Read More
about SQL Server Log Errors/Messages
- SQL Server has encountered 1 occurrence(s) of cachestore flush
- DeviceIoControl failed, error 21
- This instance of SQL Server has been using a process ID of xxxx since mm/dd/yyyy
- The time stamp counter of CPU on scheduler id 13 is not synchronized with other CPUs.
- I/O is frozen on database DatabaseName
No comments:
Post a Comment
All suggestions are welcome