Sql-server – IO request time and less write latency

compressionsql-server-2012tempdbtransaction-log

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:

  • 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 < 1 minute of data base (specifically tempdb and my database)

    Question 3: Is this going to impact my database? I mean flushing buffer and writing to disk should improve performance, right?

    Question 4: I have three data file for tempdb already have 3 data files. As per Microsoft recommendation, do I have to increase my temdb data files/log files? My current processor configuration is mentioned below

enter image description here

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.

Best Answer

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.

  1. 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.
  2. Again since fewer pages are bought into memory this increases buffer pool availibility.
  3. 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
  4. 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 < 1 minute of data base (specifically tempdb and my database)

I can only say please leave it to default, I am not sure how recovery time has anything to do with log file and its configuration

Regarding Tempdb make sure you have Data files equal to number of physical cores. Paul has more to say about how many files you can have as per cores IMHO you can start with 4 tempdb files but keep monitoring for contention. Make sure that all tempdb data files have SAME INITIAL SIZE and SAME AUTOGROWTH setting. You can also enable TF 1118 to avoid contention

To check contention you can run below query

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:%'

There are some good article for you to read about Tempdb and Contention

  1. Troubleshooting Tempdb Contention by Paul Randal

  2. Recommendation to reduce Allocation contention in Tempdb database

  3. 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

Related Question