SQL Server Performance

Sql Locks:Average wait time counter ?

Discussion in 'Performance Tuning for DBAs' started by seanlo@jdg.co.za, Nov 10, 2005.

  1. seanlo@jdg.co.za New Member

    Hi There

    I am wondering why my performance counter for average wait time is always 0, yesterday we had very bad blocking in the database for hours and this figure was always 0, i have checked sysperfinfo and the wait time counter is always incrementing but the average wait time counter stays 0 permanently ???

    If i run performance montitor with these 2 counters i can see wait time going up and down with the "average" block showing values, but the actual average wait time counter stays 0 ???

    How can i get accurate Sql Server Locks : average wait time stats ???

    Thanx
  2. satya Moderator

  3. seanlo@jdg.co.za New Member

    Hi Satya

    Ok i have read the articles and i am familiar with all the information.
    My question is why is wait time always 0 on my perfroamnce traces when i know there is alot of wait time happening on the server, it is almost as though the average wait time counter is not working ?

    Thanx
  4. seanlo@jdg.co.za New Member

    Anyone got feedback for me ?

    I know this sounds really odd, i have googled it aswell with nothing.
    Am i misunderstanding this counter ?

    As i said during a period where i know there is alot of blocking happening this counter stays 0, in fact over a period of a week this counter was nothing but 0 ?

    As mentioned aswell if i look at sysperfinfo this value is always 0, any reason why this counter would not function ???

    Thanx
  5. seanlo@jdg.co.za New Member

    For example:

    select data from sysperfinfo

    Object Name Counter_name instance cntr_value cntr_type
    SQLServer:Locks Lock Waits/sec _Total 50896 272696320
    SQLServer:Locks Lock Wait Time (ms) _Total 171697228 272696320
    SQLServer:Locks Average Wait Time (ms) _Total 0 537003008

    I wait 1 minute and run it again:

    Object Name Counter_name instance cntr_value cntr_type
    SQLServer:Locks Lock Waits/sec _Total 50957 272696320
    SQLServer:Locks Lock Wait Time (ms) _Total 171769236 272696320
    SQLServer:Locks Average Wait Time (ms) _Total 0 537003008

    So in this period
    there were (50896 - 50957) 61 Locks that had to wait.
    there was o total wait time for these 61 locks of (171697228 - 171769236) 72008 ms.

    72008/61 = average wait time of 1180ms during that period.

    So why is average wait time always 0.

    Yes i could write a script that does this, but my entire baseline work is based on set perforamnce traces that run and the data is pulled into a sql table and excel etc.

    having to correlate a seperate set of statitics from such a job would make the process harder, and besides that this counetr should work should it not, many resources i ahve read refer to this counter, but in my experience it is always 0, dont know how anyone uses it ?

    I have checked all instances of sql server in our environment and it is all the same.

    I must be doing or missing something really stupid here???

    Please help ?

    Thanx

Share This Page