Sql-server – Resource Waits: Mathematical link between Wait Time and Average Waiter Count

activity-monitorsql serversql-server-2008

Just curious, per BOL

Wait Time (ms/sec) is the wait time in milliseconds for all tasks waiting for one or more resources in the category since last update.

Average Waiter Count is the number of tasks waiting.

Is it a coincidence that on my server Average Waiter Count is always Wait Time / 1000 rounded to nearest 10th? Is there a link here that makes it so? Am I going to be embarrassed when the answer is extremely logical but for some reason escaping me at the moment?

i.e here are sample values from the last 15 minutes for latch but other wait categories are the same (when any values exist) – I've noticed it for quite some time (much longer than last 15 mins) it just never piqued my interest enough to investigate until now.

Wait Time         Average Waiter Count
______________________________________
576               0.6
1403              1.4
1856              1.9
1233              1.2
1365              1.4

It just seems to me that since wait time is a cumulative count and waiters is an average the numbers shouldn't follow a ratio of each other as tightly as they appear.

Best Answer

I think a task is considered waiting if it is waiting for a whole second -- 1000ms. So if wait time for 1 second is 2000ms that means that 2 tasks where waiting in that second.

Related Solutions

Sql-server – Sql Server And Very Hot CPU % of Total Wait Time

SOS_SCHEDULER_YEILD is not the cause, it's a side effect. It just means that something else is burning the cycles. In your graph, the second is LOGGING; this makes sense considering you are running replication. Based on my prior experience, you are probably experiencing log reader. This is typical; you can reduce your PollingInterval and increase CommitBatchSize

Use ProcessExplorer, sort by highest CPU usage process, must be sqlservr. Right click on sqlsrvr, go to properties, click on threads tab, sort by highest CPU column to see the highest cpu consuming thread. Note the thread id(s) down. Execute the below statement to see what is query is using the most CPU

select r.session_id, st.text, qp.query_plan from sys.dm_os_threads as ot join sys.dm_os_tasks as t on t.worker_address=ot.worker_address join sys.dm_exec_requests as r on t.session_id=r.session_id cross apply sys.dm_exec_sql_text(r.sql_handle) as st cross apply sys.dm_exec_query_plan(r.plan_handle) as qp where os_thread_id=<from process explorer high CPU thread>

Sql-server – Average, between specific date/time, of the difference between current field and previous field

I used your SQL Fiddle and posted a working solution here.

WITH Calc AS
(
    SELECT
        S.Pair,
        S.Date,
        S.Time,
        S.Bid,
        ABS(S.Bid - T.Bid) AS Diff
    FROM dbo.forexData AS S
    CROSS APPLY
    (
        SELECT TOP (1)
            T.Bid
        FROM dbo.forexData AS T
        WHERE
            T.Id > S.Id
            AND t.Pair = s.Pair
        ORDER BY
            T.Bid DESC
    ) AS T
)
SELECT
    Pair,
    AVG(DIFF)
FROM Calc
GROUP BY
    Pair;

To summarize:

I fixed a hole in your existing logic - currently your CROSS APPLY doesn't link to the same pair value so if you have multiple pairs then your DIFF could be incorrect.
I put the fixed logic into a CTE
I queried the CTE with a GROUP BY on pair
I added an ORDER BY specification to determine the TOP ordering

You can add a date filter in a WHERE to the outer SELECT if you desire.

Related Question