patternMinor
IO request time and less write latency
Viewed 0 times
requestwritetimelessandlatency
Problem
I am facing issues related to constantly growing log file due to which I am getting error.
When I checked SQL Log I found below messages (error log is filled of these messages almost 90%)
SQL Server has encountered 1 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
This is happening almost for all databases, including temdb [.mdf and .ndf files]
along with that I am getting below messages also
average throughput: 0.34 MB/sec I/O saturation: 196 context switches
1210
last target outstanding: 530 avgWriteLatency 2
FlushCache: cleaned up 6233 bufs with 384 writes in 142370 ms (avoided
99 new dirty bufs) for db 6:0
My temdb size and other database and log file size is big enough.
History:
My action plans:
-
I have found that log file initial size is small with 10% growth. I am planning to increase the initial size by 512 MB with 512 MB growth to have reasonable number of VLF's.
Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file?
Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it?
-
I am planning to remove all database and log files from antivirus check.
-
I am planning to change target recovery time to
Question 5: Am I going in wrong direction? Is there is something which I am missing that should be done to rectify this issue?
Edit Why I believe this is related to Log file size?
If there is an IO issue then read write ability of SQL will decrease, this will result in long running transaction hence cause large log file size.
When I checked SQL Log I found below messages (error log is filled of these messages almost 90%)
SQL Server has encountered 1 occurrence(s) of I/O
requests taking longer than 15 seconds to complete on file
This is happening almost for all databases, including temdb [.mdf and .ndf files]
along with that I am getting below messages also
average throughput: 0.34 MB/sec I/O saturation: 196 context switches
1210
last target outstanding: 530 avgWriteLatency 2
FlushCache: cleaned up 6233 bufs with 384 writes in 142370 ms (avoided
99 new dirty bufs) for db 6:0
My temdb size and other database and log file size is big enough.
History:
- Previous developers had done both database shrinking and log shrinking many times.
- All database is in compressed mode.
My action plans:
-
I have found that log file initial size is small with 10% growth. I am planning to increase the initial size by 512 MB with 512 MB growth to have reasonable number of VLF's.
Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file?
Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it?
-
I am planning to remove all database and log files from antivirus check.
-
I am planning to change target recovery time to
Question 5: Am I going in wrong direction? Is there is something which I am missing that should be done to rectify this issue?
Edit Why I believe this is related to Log file size?
If there is an IO issue then read write ability of SQL will decrease, this will result in long running transaction hence cause large log file size.
Solution
Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file?
No this cannot damage your Log file.
Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it?
Yes database compression affects I/O and as far as my experience goes it decreases the I/O and is actually benefitial if you look at I/O consumption. I will tell you how.
This is excellent article on data compression, its big, but would help you in understanding data compression
You must remove all SQL Server related folder and files from Antivirus check specially if you have McAfee Antivirus.
•I am planning to change target recovery time to
-
Recommendation to reduce Allocation contention in Tempdb database
-
Monitoring Allocation Bottleneck In Tempdb
Edit:
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file
This message means that a particular session or query requested for I/O to get data from disk but that session had to wait more than 15 sec and after that request was catered. You can guess that 15 sec is threshold value moment this time is crossed message is dumped in errorlog. This MOSTLY means that disk is not able to cope up with I/O request which is getting generated and in turn means disk might be slow. Since you said 90 % of your errorlog is filled with this information I am forced to believe that underlying hardware is slow or might require a firmware upgrade. This Article will help you in understanding the issue and fixing it
No this cannot damage your Log file.
Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it?
Yes database compression affects I/O and as far as my experience goes it decreases the I/O and is actually benefitial if you look at I/O consumption. I will tell you how.
- Compressed data will take fewer pages when stored on disk hence when data is read fewer I/O is required to fetch data in memory.
- Again since fewer pages are bought into memory this increases buffer pool availibility.
- CPU is what consumed when compression or decompression happens. But in most cases you would find that benefits gained by space and I/O would outweight the CPU consumption
- Please remember a compressed page will remain compressed on disk as well as compressed when brought in memory for reading or writing. So no additional work of decompressing in memory
This is excellent article on data compression, its big, but would help you in understanding data compression
You must remove all SQL Server related folder and files from Antivirus check specially if you have McAfee Antivirus.
•I am planning to change target recovery time to
-
Recommendation to reduce Allocation contention in Tempdb database
-
Monitoring Allocation Bottleneck In Tempdb
Edit:
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file
This message means that a particular session or query requested for I/O to get data from disk but that session had to wait more than 15 sec and after that request was catered. You can guess that 15 sec is threshold value moment this time is crossed message is dumped in errorlog. This MOSTLY means that disk is not able to cope up with I/O request which is getting generated and in turn means disk might be slow. Since you said 90 % of your errorlog is filled with this information I am forced to believe that underlying hardware is slow or might require a firmware upgrade. This Article will help you in understanding the issue and fixing it
Code Snippets
select
session_id,
wait_duration_ms,
resource_description
from sys.dm_os_waiting_tasks
where wait_type like 'PAGE%LATCH_%' and
resource_description like '2:%'Context
StackExchange Database Administrators Q#100863, answer score: 3
Revisions (0)
No revisions yet.