SQL Server Performance

% Idle Time Counter

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by seesyong, Dec 6, 2007.

  1. seesyong New Member

    Hi,
    I've been auditing our db server using the articles on this site. I'm running sqlserver2005 raid 1 with a scsi controller and I noticed that I got extremely high values for Physical Disk: % Disk time. I was very confused about the high values but then I read the forums here and one of users said that the % Disk time perfmon counter is useless if you are using scsi.
    I then started to wonder if there's another counter that could be useful. Looking at this microsoft article : http://support.microsoft.com/kb/310067. It says that Physical Disk: % Idle Time is useful. If that's the case does anyone have a good idea what values the % Idle Time counter should average and not exceed? Thanks.
    seesyong, Dec 6, 2007
    #2

  2. Luis Martin Moderator

    Check:
    http://www.oreillynet.com/pub/a/network/2002/01/18/diskperf.html?page=2
    More:
    Finally, let's look at a couple of indicators of well-functioning hard disks in your system. Watch the Physical Disk (instance)Disk Transfers/seccounter for each physical disk and if it goes above 25 disk I/Os persecond then you've got poor response time for your disk. A bottleneckfrom a disk can significantly impact response time for applicationsrunning on your system, so you should investigate this further bytracking Physical Disk(instance)% Idle Time, whichmeasures the percent time that your hard disk is idle during themeasurement interval, and if you see this counter fall below 20% thenyou've likely got read/write requests queuing up for your disk which isunable to service these requests in a timely fashion. In this case it'stime to upgrade your hardware to use faster disks or scale out yourapplication to better handle the load.
    Luis Martin, Dec 6, 2007
    #3

  3. seesyong New Member

    Thanks for the info. I took your advice and setup the disk transfers/sec counter for about 3 days. Anyway, the average disk transfers is 16 per disk and the max being 655. Disk transfers grow very large during our daily database backups and the % Idle Time does fall below 20% during that time. Do you think it would be a good idea then to improve disk i/o for this reason?
    seesyong, Dec 10, 2007
    #4

  4. Luis Martin Moderator

    For backup reason?. No.
    Luis Martin, Dec 10, 2007
    #5

  5. satya Moderator

    Apart from the backup tasks do you see any high values?
    satya, Jan 2, 2008
    #6

Share This Page