4 added 381 characters in body
source | link

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

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?

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

3 added 271 characters in body
source | link

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?

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

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?

2 added 150 characters in body
source | link

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 counterslogical disk perfmon counters and see what the overall I/O churn is with throughputthroughput, latencylatency, and IOpsIOps. 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).

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

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

1
source | link