TEMPDB Log Flush increasing

Question

TEMPDB Log Flush increasing

RVO

SSCertifiable

Points: 5495
More actions
March 13, 2017 at 7:49 am

#341603

Guys,
I am monitoring TEMPDB
and noticed [Log Bytes Flushed/sec] increased dramatically in the last 3 weeks.
From
689,669,877,760 bytes
to
1,121,023,757,824
Same with [Log Flushes/sec]:
From
11,241,718
to
18,267,846
counter_name---------------cntr_value--------------------Date
Transactions/sec----------1,072,556,492---------------2017-02-25 13:18:00
Transactions/sec----------1,783,772,988---------------2017-03-13 11:27:00
I understand Log Flush indicates how much Log data is being written from CACHE to DISK.
If I'm correct, TEMPDB Log activity is increasing?
I just wonder why.
Because we didn't change anything and daily data volumes are the same.
See the trend below . . .
Query used:
SELECT object_name,counter_name,instance_name,cntr_value,cntr_type FROM sys.dm_os_performance_counters AS dopc WHERE dopc.object_name = 'SQLServer:Databases' AND dopc.instance_name = 'tempdb';

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

RVO - Monday, March 13, 2017 7:49 AM
Guys,
I am monitoring TEMPDB
and noticed [Log Bytes Flushed/sec] increased dramatically in the last 3 weeks.
From
689,669,877,760 bytes
to
1,121,023,757,824
Same with [Log Flushes/sec]:
From
11,241,718
to
18,267,846
counter_name---------------cntr_value--------------------Date
Transactions/sec----------1,072,556,492---------------2017-02-25 13:18:00
Transactions/sec----------1,783,772,988---------------2017-03-13 11:27:00
I understand Log Flush indicates how much Log data is being written from CACHE to DISK.
If I'm correct, TEMPDB Log activity is increasing?
I just wonder why.
Because we didn't change anything and daily data volumes are the same.
See the trend below . . .
Query used:
SELECT object_name,counter_name,instance_name,cntr_value,cntr_type FROM sys.dm_os_performance_counters AS dopc WHERE dopc.object_name = 'SQLServer:Databases' AND dopc.instance_name = 'tempdb';

You need to look at the counter type when you interpret those.
Log Bytes Flushed/sec has a type of 272696576
That type translates to PERF_COUNTER_BULK_COUNT
So it's cumulative. Check this for some more information:
Interpreting the counter values from sys.dm_os_performance_counters

But this one is probably better for what you are probably trying to do. It shows some of the ways to do the calculations for some of the counters. For the cumulative counters, I've usually seen them done using ms_ticks from dm_os_sys_info and there is an example of doing that in the examples. About two thirds of the way into the article, look for the line "Another method is to use get the ms_ticks value" and you'll find it.
Troubleshooting SQL Server issues with sys.dm_os_performance_counters

Sue

RVO SSCertifiable Points: 5495 More actions · Answer 2

You are right.

All biggest cntr_values are coming from PERF_COUNTER_BULK_COUNT cntr_type

cntr_type----------------cntr_type_definition
272696576--------------PERF_COUNTER_BULK_COUNT

so it's not useful at all to look at these values?
Even if you see big recent change?

Sue_H SSC Guru Points: 90860 More actions · Answer 3

RVO - Monday, March 13, 2017 2:04 PM
You are right.
All biggest cntr_values are coming from PERF_COUNTER_BULK_COUNT cntr_type
cntr_type----------------cntr_type_definition
272696576--------------PERF_COUNTER_BULK_COUNT
so it's not useful at all to look at these values?
Even if you see big recent change?

A big change relative to what? And what constitutes a big change? No one can say so that's part of the issue.
For those particular types of perf mon counters, the values aren't necessarily useless but they need to be computed differently to have any meaning. Many perf mon countrs don't tell you much unless you do some computations with them. Some are ratios, some are cumulative.
And then just like other measurements, you'd want to see baseline measurements over time to see what is an issue and what is really normal. You can see big changes over the weekend on a lot of systems - often from maintenance being done. Other systems have major imports on certain days of the month that affects different performance metrics, etc.

Sue