SQL Server 2012 – Resolving Connection Issues During CPU Drop

The FlushCache message in the error log is caused by checkpoint logging, and in this case by a long checkpoint (which is defined as a checkpoint that is taking longer than the recovery interval). Whether it's logged or not, the behavior is different in pre-2012 and 2012+. Before SQL Server 2012, to get checkpoint logging you'd have to turn on a trace flag (T3504). But starting in SQL Server 2012 that message is logged by default when a long checkpoint is encountered.

Now as for the question of "is this actually bad?", you really need to start looking at these numbers given their context. It took you 97+ seconds to flush only about 93 MB of dirty buffers. This looks like this could potentially be a mixture of a lot of data churn (during the actual checkpoint itself, about 64 MB worth of buffers were also dirtied) and potentially storage that isn't keeping up with the data modification and/or the rest of the I/O workload.

What I would do is verify the health of your storage subsystem, look at waits, and just get an overall performance picture of the instance. Take a look at logical disk perfmon counters and see what the overall I/O churn is with throughput, latency, and IOps. It will help you paint a more vivid picture of how the disks are performing. If you have the ability to benchmark your storage, if you haven't already baselined it, you should see what these volumes in question are capable of (SQLIO is a great utility for that) and what they are doing right now (it's nice to have a benchmark baseline when the volumes were stood up to compare to a current benchmark).

Here is a great article explaining this message - How It Works: When is the FlushCache message added to SQL Server Error Log?

EDIT: Re-reading your question, I must've missed this comment:

I noticed that every morning around 5 am we are started to get this message

See what is happening on your storage at this time as per the guidance above. That sounds like textbook scheduled operation that is taking a toll on the storage causing the checkpoint performance to suffer and to be "long".

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