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
  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
  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
  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
  5. Luis Martin Moderator

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

    Luis Martin
  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.

Share This Page