SQL Server Performance

% Disk Time Counter Query

Discussion in 'Performance Tuning for DBAs' started by justin.hannan, Jun 27, 2003.

  1. justin.hannan New Member

    I've been running the % Disk Time counter on a volume on our SAN, presented only to SQL Server and been getting some curious results...

    I've looked at the info on this site about I/O counters, but I've been getting results returned in the counter log all over the place...

    e.g.

    Date/Time % Disk Time

    06/26/2003 09:35:36.723 555.1056607
    06/26/2003 09:35:51.724 188.5543027
    06/26/2003 09:36:06.716 49.36749667
    06/26/2003 09:36:21.717 845.712998
    06/26/2003 09:36:36.719 4190.152479
    06/26/2003 09:36:51.721 5740.561225

    I assume this figure is a straight % and I don't have to convert it, if so it seems bad to me and an I/O bottlenet is present?

    Any comments?

    J
    justin.hannan, Jun 27, 2003
    #2

  2. satya Moderator

    How does other counter Average Disk Queue Length looks like?

    And how about DBCC checks and rebuild indexes job on SQL Server?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Jun 27, 2003
    #2

  3. justin.hannan New Member

    These are the disk queues for that volume...

    06/26/2003 09:35:36.723555.105660716
    06/26/2003 09:35:51.724188.55430270
    06/26/2003 09:36:06.71649.367496672
    06/26/2003 09:36:21.717845.71299836
    06/26/2003 09:36:36.7194190.15247972
    06/26/2003 09:36:51.7215740.5612250

    So they are really high (4 disks in the raid array make up the volume) but they don't last for 5-10 mins or should I be taking an average of queue length over this time period?

    Didn't log DBBC or rebuilds, but I'll have a look at those, thanks.

    J
    justin.hannan, Jun 27, 2003
    #2

  4. gaurav_bindlish New Member

    Referhttp://www.oreillynet.com/lpt/a/1503 for more understanding of %disk time.

    However what is the no. of disks in these array?

    A continuous disk queue lengh of more than 2 for periods like 10 munutes or more is considered a bottleneck. SO divide the queue lengh observed for each array by the no. of disks in the array and see if any of them come above 2. If it oes then u know your bottlneck.


    HTH.

    Gaurav
    gaurav_bindlish, Jun 27, 2003
    #2

  5. Luis Martin Moderator

    Or, if you have RAID 5 may be you have to evaluate RAID 10.

    Luis Martin
    Luis Martin, Jun 27, 2003
    #2

  6. sqljunkie New Member

    You could also query the sysprocesses table and check what the wait types are. If you see pagelatch wait types when you execute:

    select * from master..sysprocesses where waittime>0 and spid>50

    you probably have a disk bottleneck.
    sqljunkie, Jul 1, 2003
    #2

Share This Page