"Avg Dist From EOL/LP Request" counter is very high

Question

We have been facing a slowness issue on our PROD DB.

While checking a few counters on sys.dm_os_performance_counters DMV, I could see one counter ("Avg Dist From EOL/LP Request") value is always high.

Is there any co-relation of performance degradation because this counter is too high?

Answer 1

you should probably hire someone

Avg Dist From EOL/LP Request is documented as

Average distance in bytes from end of log per log pool request, for requests in the last VLF.

Unless you're observing transaction log problems generally, you should probably look elsewhere. In fact, even if I were having transaction log problems, I probably wouldn't be looking here.

Beyond that, different perfmon counters need to be treated differently. You can't just keep querying the table and taking the numbers at face value as good or bad.

For example, Avg Dist From EOL/LP Request has a counter type of 1073874176, which means it will have a base counter part with a type of 1073939712. In this case, that's Log Pool Requests Base.

In order to effectively calculate a rate for them, you need to sample both and compare them. From the link above:

(A2 - A1) / (B2 - B1)

To do that, you need to do this:

--Clear'em out
DROP TABLE IF EXISTS
    #dm_os_performance_counters_1,
    #dm_os_performance_counters_2;

--Grab a first pass of data
SELECT 
    dopc.object_name,
    dopc.counter_name,
    dopc.instance_name,
    dopc.cntr_value,
    dopc.cntr_type,
    run_date = GETDATE()
INTO #dm_os_performance_counters_1
FROM sys.dm_os_performance_counters AS dopc
WHERE 
(
     dopc.counter_name LIKE '%Avg Dist From EOL/LP Request%'
  OR dopc.counter_name LIKE '%Log Pool Requests Base %'
)
AND   dopc.instance_name = N'_Total';

--Wait five seconds, or however long you want
WAITFOR DELAY '00:00:05.000';

--Grab a second pass of data
SELECT 
    dopc.object_name,
    dopc.counter_name,
    dopc.instance_name,
    dopc.cntr_value,
    dopc.cntr_type,
    run_date = GETDATE()
INTO #dm_os_performance_counters_2
FROM sys.dm_os_performance_counters AS dopc
WHERE 
(
     dopc.counter_name LIKE '%Avg Dist From EOL/LP Request%'
  OR dopc.counter_name LIKE '%Log Pool Requests Base %'
)
AND   dopc.instance_name = N'_Total';

--This does the (A2 - A1) calculation
WITH
    average AS 
(
SELECT
    d1.object_name,
    d1.counter_name,
    d1.instance_name,
    d1.cntr_value,
    d1.cntr_type,
    d1.run_date,
    average = d1.cntr_value - d2.cntr_value
FROM #dm_os_performance_counters_2 AS d1
JOIN #dm_os_performance_counters_1 AS d2
  ON  d1.object_name = d2.object_name
 AND  d1.counter_name = d2.counter_name
 AND  d1.instance_name = d2.instance_name
 AND  d1.run_date > d2.run_date
 AND  d1.cntr_type = 1073874176
),
    --This does the (B2 - B1) calculation
    base AS
(
SELECT
    d1.object_name,
    d1.counter_name,
    d1.instance_name,
    d1.cntr_value,
    d1.cntr_type,
    d1.run_date,
    base = d1.cntr_value - d2.cntr_value
FROM #dm_os_performance_counters_2 AS d1
JOIN #dm_os_performance_counters_1 AS d2
  ON  d1.object_name = d2.object_name
 AND  d1.counter_name = d2.counter_name
 AND  d1.instance_name = d2.instance_name
 AND  d1.run_date > d2.run_date
 AND  d1.cntr_type = 1073939712
)
SELECT
    b.object_name,
    base_counter_name =
        b.counter_name,
    average_counter_name =
        a.counter_name,
    b.instance_name,
    b.base,
    a.average,
    --This does the average / base calculation, with some divide by zero safety
    calculation = 
        ISNULL
        (
            average / NULLIF(base, 0), 
            0
        )
FROM average AS a
JOIN base AS b
  ON  b.object_name = a.object_name
 AND  b.instance_name = a.instance_name;

Unfortunately at the end of all that, all you know is:

Average distance in bytes from end of log per log pool request, for requests in the last VLF.