SQL Server Performance

Trying to interpret SQL wait times

Discussion in 'Performance Tuning for DBAs' started by GilaMonster, Jan 10, 2005.

  1. GilaMonster New Member

    Hi all

    I'm trying to locate performance problems with my SQL server and I had a look at the wait stats. Problem is, I'm not sure how to interpret them. I notice a high average LCK_M_S wait time and a very high number of LATCH_EX waits.

    The server was restarted at 1pm yesterday, and the wait times read at 8am this morning. The server has a large number of jobs that run during the night, many of them querying other databases.

    Can someone who is familiar with this please offer some comments?

    Thanks

    --------------------

    Wait Type Requests Wait Time Signal Wait Time
    -------------------------------- ------------------------ ------------------------ ------------------------
    MISCELLANEOUS 154.0 0.0 0.0
    LCK_M_SCH_S 0.0 0.0 0.0
    LCK_M_SCH_M 0.0 0.0 0.0
    LCK_M_S 1670.0 2039964.0 1428.0
    LCK_M_U 465.0 134020.0 1937.0
    LCK_M_X 1762.0 873514.0 110639.0
    LCK_M_IS 75.0 811986.0 92.0
    LCK_M_IU 0.0 0.0 0.0
    LCK_M_IX 130.0 1685792.0 265.0
    LCK_M_SIU 0.0 0.0 0.0
    LCK_M_SIX 0.0 0.0 0.0
    LCK_M_UIX 0.0 0.0 0.0
    LCK_M_BU 0.0 0.0 0.0
    LCK_M_RS_S 0.0 0.0 0.0
    LCK_M_RS_U 0.0 0.0 0.0
    LCK_M_RIn_NL 0.0 0.0 0.0
    LCK_M_RIn_S 0.0 0.0 0.0
    LCK_M_RIn_U 0.0 0.0 0.0
    LCK_M_RIn_X 0.0 0.0 0.0
    LCK_M_RX_S 0.0 0.0 0.0
    LCK_M_RX_U 0.0 0.0 0.0
    LCK_M_RX_X 0.0 0.0 0.0
    SLEEP 549990.0 6.8502976E+7 6.8412496E+7
    IO_COMPLETION 202802.0 274370.0 2620.0
    ASYNC_IO_COMPLETION 28.0 172.0 0.0
    RESOURCE_SEMAPHORE 0.0 0.0 0.0
    DTC 565.0 28490.0 501.0
    OLEDB 2824604.0 4.4249318E+8 2.7451707E+9
    FAILPOINT 0.0 0.0 0.0
    RESOURCE_QUEUE 1656328.0 2.722647E+8 6.8763904E+7
    ASYNC_DISKPOOL_LOCK 3911.0 0.0 0.0
    UMS_THREAD 0.0 0.0 0.0
    PIPELINE_INDEX_STAT 0.0 0.0 0.0
    PIPELINE_LOG 0.0 0.0 0.0
    PIPELINE_VLM 0.0 0.0 0.0
    WRITELOG 701255.0 1885143.0 211630.0
    PSS_CHILD 0.0 0.0 0.0
    EXCHANGE 2554.0 63159.0 1765.0
    XCB 0.0 0.0 0.0
    DBTABLE 4.0 14109.0 0.0
    EC 0.0 0.0 0.0
    TEMPOBJ 0.0 0.0 0.0
    XACTLOCKINFO 0.0 0.0 0.0
    LOGMGR 0.0 0.0 0.0
    CMEMTHREAD 63840.0 5134.0 3805.0
    CXPACKET 6149708.0 5.8710752E+7 1135793.0
    PAGESUPP 71407.0 205496.0 21251.0
    SHUTDOWN 0.0 0.0 0.0
    WAITFOR 79.0 436509.0 436509.0
    CURSOR 0.0 0.0 0.0
    EXECSYNC 69.0 534389.0 78.0
    LATCH_NL 0.0 0.0 0.0
    LATCH_KP 0.0 0.0 0.0
    LATCH_SH 2.0 0.0 0.0
    LATCH_UP 2312.0 139429.0 64.0
    LATCH_EX 1.532737E+7 1.7517056E+7 4113430.0
    LATCH_DT 0.0 0.0 0.0
    PAGELATCH_NL 0.0 0.0 0.0
    PAGELATCH_KP 86.0 64.0 48.0
    PAGELATCH_SH 1226309.0 22066.0 16428.0
    PAGELATCH_UP 169802.0 77257.0 66192.0
    PAGELATCH_EX 1256123.0 43215.0 36906.0
    PAGELATCH_DT 0.0 0.0 0.0
    PAGEIOLATCH_NL 0.0 0.0 0.0
    PAGEIOLATCH_KP 0.0 0.0 0.0
    PAGEIOLATCH_SH 763118.0 8967530.0 13694.0
    PAGEIOLATCH_UP 5605.0 41693.0 421.0
    PAGEIOLATCH_EX 466602.0 6764646.0 11960.0
    PAGEIOLATCH_DT 0.0 0.0 0.0
    TRAN_MARK_NL 0.0 0.0 0.0
    TRAN_MARK_KP 0.0 0.0 0.0
    TRAN_MARK_SH 0.0 0.0 0.0
    TRAN_MARK_UP 0.0 0.0 0.0
    TRAN_MARK_EX 0.0 0.0 0.0
    TRAN_MARK_DT 0.0 0.0 0.0
    NETWORKIO 816426.0 3969610.0 0.0
    Total 3.2265156E+7 8.8850637E+8 2.8885345E+9

    (77 row(s) affected)
  2. satya Moderator

    LCK_M_S indicates Share lock last waittype, as per this KBAhttp://support.microsoft.com/kb/822101 and it could be a transaction management issue. To resolve it for shared locks, check Isolation level for transaction & keep transaction as short as possible.

    Also check for memory pressure, which causes more physical I/O, thus prolonging the duration of transactions and locks.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. joechang New Member

    is your server a Xeon with HT enabled?
    if so, consider one of: disabling HT, restricting SQL Server to one logical CPU on each physical CPU, or disabling parallelism or limiting max degree of par. to 2
    this is related to the cxpacket item
  4. GilaMonster New Member

    Thanks for the help. I've got a fair bit of experience optimising SQL, but not at this level, and I'm not the DBA.

    The server has 8 xeon processors, hyperthreading is not enabled and the max degree of parallelism is set to 4

    I think we are running a little tight on memory. The server has 8GB. I'm currently looking into the transaction handling in the system.

    Gail
  5. satya Moderator

    Capture the PERFMON *SYSMON) counters for memory, cpu, physical disk, SQL memory, buffer manager for further assessment or during the slow performance on the system.

    Review the articles on this website for capturing and tips on PERFMON counters.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. Argyle New Member

    Have a look at:
    http://sqldev.net/misc/WaitTypes.htm

    I use the script below to check wait type usage:

    ---------------


    -- simple waitstate script
    -- GertD@SQLDev.Net
    --
    -- List of Wait Types: Seehttp://sqldev.net/misc/WaitTypes.htm
    --
    if ((object_id('waitstats') is not null) and
    (objectproperty(object_id('waitstats'), 'IsUserTable') = 1))
    drop table waitstats

    create table waitstats (
    wait_type varchar(80),
    requests numeric(18, 1),
    wait_time numeric (18 ,1),
    signal_wait_time numeric(18, 1),
    time_stamp datetime default getdate()
    )

    declare @start int,
    @finish int
    select @start = 1,
    @finish = 10 -- Increments of 10 seconds, change this for longer tests

    dbcc sqlperf(waitstats,clear) -- clear out wait statistics

    while (@start < @finish)
    begin
    begin transaction
    insert into waitstats(wait_type, requests, wait_time,signal_wait_time)
    exec ('dbcc sqlperf(waitstats)')
    commit

    select @start = @start + 1
    waitfor delay '00:00:10' -- every 10 seconds
    end

    declare @begin_time datetime,
    @end_time datetime,
    @total_wait numeric(18, 1)

    select @begin_time = min(time_stamp),
    @end_time = max(time_stamp)
    from waitstats where wait_type = 'Total'

    --- subtract waitfor, sleep, and resource_queue from Total
    select @total_wait = sum(wait_time) + 1
    from waitstats
    where wait_type not in ('WAITFOR', 'SLEEP', 'RESOURCE_QUEUE', 'Total',
    '***total***')
    and time_stamp = @end_time

    -- insert adjusted totals, rank by percentage descending
    insert into waitstats values('***total***', 0, @total_wait, @total_wait,
    @end_time)

    -- reporting
    select @begin_time as [begin time],
    @end_time as [end time],
    datediff(mi, @begin_time, @end_time) as [duration in min]

    select wait_type,
    wait_time,
    percentage = cast(100 * wait_time / @total_wait as numeric(18,1))
    from waitstats
    where wait_type not in ('WAITFOR', 'SLEEP', 'RESOURCE_QUEUE', 'Total')
    and time_stamp = @end_time
    order by percentage desc


Share This Page