SQL Server Performance Forum – Threads Archive

Strange % disk time values?

Hello! 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. 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: OS – 2 disks (Raid 1) (Placed on controller card 1 (128MB cache), U160) System DBs – 2 disks (Raid 1) (Placed on controller card 1 (128MB cache), U160) userDB-logs – 2 disks (Raid 1) (Placed on controller card 1 (128MB cache), U160) userDB – 6 disks (Raid 10) (Placed on controller card 2 (128MB cache), U320) All the disks are 36GB 15k. 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. But we have some weird values on the %disk time value. 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. 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? 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. 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? 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=’

‘ /> How can I investigate this futher so we can find a solution? How can I calculate the relation of read and writes in the database? We run approximately 1.3 million queries per hour. The CPU avarage 20 during peak hours.

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.

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".

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.

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

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!

brimba, Check out the code I discuss here in this thread. IT will let you analyze your IO at the SQL file level. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9202 Let me know if you have any questions.
Bert

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.

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.

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.

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.

]]>