SQL Server Performance

Strange % disk time values?

Discussion in 'Performance Tuning for Hardware Configurations' started by brimba, Aug 19, 2005.

  1. brimba New Member

    Hello!<br /><br />We have a database that is 5 GB and we have 8GB of ram in the machine so the whole database fits in the RAM.<br /><br />We had disk problems and decided to move from 4 disks (raid 5) (userDB-logs, userDB, system DBs) and 2 disks (raid 1) with OS to a system with:<br /><br />OS - 2 disks (Raid 1) (Placed on controller card 1 (128MB cache), U160)<br />System DBs - 2 disks (Raid 1) (Placed on controller card 1 (128MB cache), U160)<br />userDB-logs - 2 disks (Raid 1) (Placed on controller card 1 (128MB cache), U160)<br />userDB - 6 disks (Raid 10) (Placed on controller card 2 (128MB cache), U320)<br /><br />All the disks are 36GB 15k.<br /><br />And that solved the huge values of "avg disk que length". We now avarage 4-5 on the userDB array over a 5 minute period.<br /><br />But we have some weird values on the %disk time value.<br /><br />Over almost every 5 minute periods the %disk time avarage around 400. There is a spike ~every 5 minute or so. The spike sometimes maximise 60000 and that is what makes the avarage number so high.<br /><br />I thought that this was because it was doing a "checkpoint" where all the changed or added data written to RAM is stored on disk. But how can I check when a checkpoint occour?<br /><br />I thought I could use the undocumented ::ft_dblog(null,null) function, but after the spike on disk time it is not sure that the table is empty.<br />Another "strange" thing is that when I count number of posts in ::ft_dblog(null,null) I have got as many as 8 million posts, and that seems like a rather big number?<br /><br />Its only the Raid 10 array with 6 disks and the userDB (we only have one database) that have problems with large values on the %disk time. The other arrays avarage like 0.4 or so <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />How can I investigate this futher so we can find a solution?<br />How can I calculate the relation of read and writes in the database? <br />We run approximately 1.3 million queries per hour. <br />The CPU avarage 20 during peak hours.<br /><br />
  2. Luis Martin Moderator

    All queries were tuning?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. brimba New Member

    Yes, most of the queries are using the indexes and makes index seeks. In some cases that cannot be made, we had to make offer some to gain some other, and in those cases there are index scans or table scans.

    of course we can tune the queries more but its not just a specific query that is making the %disk time to spike. I know this since I have traced the queries and try to see what queries that run when the spikes occour. It doesnt seem to be a specific query and that is why I began to think of the "checkpoint".

  4. brimba New Member

    We have this statistical spreads of queries

    SELECTs - 98.5%
    UPDATEs - 0.85%
    INSERTs - 0.59%
    DELETEs - 0.07%

    Total number of queries during the data collection: 550000.
  5. joechang New Member

    yes it most probably is the checkpoint,
    since your db fits in memory, the checkpoint is of no concern,

    ignore the %disk time counter as it is useless.
    just keep long term logs for each data & log disk of:
    reads/sec, writes/sec,
    reads bytes/sec, writes bytes/sec,
    avg sec/read, avg sec/write,
    avg read queue, avg write queue

    this will tell when you need more disk performance
  6. brimba New Member

    Ok. With these counters I noticed something weird.

    There is alot more reading on my System database-drive than on my userDb-drive.

    For example (this is a typical 5 minute period)

    System DB Drive:
    Reads bytes / sec : 350000 (650000 max)

    User DB Drive:
    Reads bytes / sec : 100000 (1000000 max)

    So my UserDB has alot higher max but a lower avarage.

    I also noticed that the system db drive and my user db drive writes "in spikes". Like it stores alot of data to write and then suddenly it decides to write it to disk and therefore it gets busy. My UserDB log drive writes the whole time, avarage of 65 writes / sec, over a 5 minute period.

    From these counters how can I tell when I need more disk performance?
    I know that one thing that we could do is to move the tempdb to its own Raid 1 array, but if its just waste of money I dont want to do it.

    Thanks!
  7. bertcord New Member

  8. satya Moderator

    Also check if the SQL logins default database is marked as master then ensure to change to appropriate user database, in order to avoid round trip to system databases from an initial connection.

    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.
  9. brimba New Member

    Ok, I will check that.

    Our UserDB (6 15k 36GB disks, Raid 10) has during a peak hour an "avg. disk write queue length" of 8.765. How much could it help to put in two more disks, so that we get 8 disks? I read that if this value exceeds 2 for more than a 10 min period its not so good.

    The array only holds one 5Gb database.
  10. Luis Martin Moderator

    You will need to calculate this figure because Performance Monitor does not know how many physical drives are in your array. For example, if you have an array of 6 physical disks, and the Avg. Disk Queue Length is 10 for a particular array, then the actual Avg. Disk Queue Length for each drive is 1.66 (10/6=1.66), which is well within the recommended 2 per physical disk.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  11. satya Moderator

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9065 for relevancy.

    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.

Share This Page