Post reply

What does Logging mean in the Resource Waits section of Activity Monitor in Sql Server 2008?

  • November 9, 2009 at 2:33 am

    #216529

    I have just found the exellent Activity Monitor tool in 2008. Whilst going through the different views I noticed that in my resource waits, the entry Logging is extremely high.

    Over 1000ms/sec which I reckon is pretty nasty.

    I have been trying to find out what Logging is actually showing. Can anyone give me any pointers to where I should be looking. Is this transaction logging or harddrive logging or what. What performance counters should I be looking at.

    In the resource monitor I can see my HD is about 10% normal use but the Most active used is always way higher.

    I am at a loss.

    Any suggestions would be gratefully received.

    Thanks,

    Graeme.

  • November 16, 2009 at 6:36 am

    #1079631

    Great question - anyone got any info beyond BOL?

    eg: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/568d89ed-2c96-4795-8a0c-2f3e375081da.htm

  • November 16, 2009 at 7:00 am

    #1079648

    Thanks for the pointer to BOL.

    I have ran the query and the worst offending seem to be the following

    NAME Wait CountWait Time MSMax Wait Time MSSignal WaitTime

    LAZYWRITER_SLEEP 392136382873704187632982

    REQUEST_FOR_DEADLOCK_SEARCH765713828719645001382871964

    XE_TIMER_EVENT 1276438287109930001382869148

    XE_DISPATCHER_WAIT 872382811081272706510

    LOGMGR_QUEUE 858754438252896614396500086

    SQLTRACE_BUFFER_FLUSH 9533038135609949033124

    FT_IFTS_SCHEDULER_IDLE_WAIT632937968365360388297

    CHECKPOINT_QUEUE 58583632929512132168600

    WRITELOG 85613792020721745678645332

    SLEEP_TASK 4508822193493788358126874

    BROKER_TO_FLUSH 186919 19143219218898667

    ASYNC_NETWORK_IO 7850808203245215400120107

    PAGEIOLATCH_SH 103881123072621500326797

    This was ordered by total wait time. But call me a numpty, I have read the BOL description for most of the above and it means nothing to me.

    If you saw these figures apart from being appalled what would you then go and look at? We have a SCSI raid drive as we are on a hosted server using the web edition of SQL.

    Many thanks. Graeme.

  • November 16, 2009 at 9:32 am

    #1079754

    Try looking in here: http://technet.microsoft.com/en-gb/library/cc966413.aspx

    Looks like a handy whitepaper "SQL Server 2005 Waits and Queues SQL Server Best Practices Article"

  • November 16, 2009 at 9:46 am

    #1079765

    Thanks for that. I will have a read of that this evening.

    I assume the majority of it will be relevant to 2008 also.

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

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