September 16, 2003 at 9:04 am
Hi!
I have a 6xPIII-700 Xeon 4Gb RAM Raid1 (OS) Raid5 (6x18Gb,- data) Raid1 (logs)
The total size of databases is about 60Gb.
Recently noticed very large queue for disk on Raid5, it was about 60(Avg. Disk Queue Length counter).
At the same time the Avg. Disk Bytes/Transfer counter was 37015, that is only 36Kb.
Is it normal or I am having something wrong configured?
Thanks.
September 16, 2003 at 10:19 am
Could it be hardware issues? How long did you see Disk Queue Length around 60 lasts?
September 16, 2003 at 10:21 am
The disk counters can be a little misleading. Read this http://www.oreillynet.com/lpt/a/1503.
Not sure if it's your issue, but it's interesting. You could have a large Q with low transfer if the disk is fragmented and the data you are asking for is really small (small transfers in size).
Is this sustained? Over minutes? or is it a spot rate.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 16, 2003 at 7:10 pm
Hi there
Are you measuring read and write queues seperately? make sure you do for starters. After that, you need to know the total disks in the array as a divisor for the figure shown in perfmon. Have you got write cache enabled btw?
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 17, 2003 at 1:04 am
2Allen_Cui:
quote:
"Could it be hardware issues?"
-What kind of hardware problem can this be?
The server has ULTRA3 controller with 128Mb read/write cache (50%/50%)
I could see this for 100 seconds.
These are exact numbers:
Average Minimum Maximum Duration
Avg. Disk Queue Length59,2810374,2051:40
Avg. Disk Bytes/Transfer37015,042 0,00090173,5941:40
These are screenshots:
http://roustam.hotbox.ru/queue_length.jpg
http://roustam.hotbox.ru/disk_transfer.jpg
I guess this can be locale problem. In my locale comma ',' means fraction, point '.' devides large numbers into 3 digit portions. Because of mix of locales, comma is used for both purposes. Perhaps, in this case, the "Avg. Disk Bytes/Transfer" counter is actualy not 37015 bytes, but 37 015 042 bytes. But "Avg. Disk Queue Length" couter is 59 on the average. This is only a guess.
Because of mix of locales, comma is used for both purposes.
2Steve Jones:
quote:
Not sure if it's your issue, but it's interesting. You could have a large Q with low transfer if the disk is fragmented...
- Can a hardware RAID5 be fragmented?
quote:
...and the data you are asking for is really small (small transfers in size).
- As far as I know, MS SQL2000 reads data in extends, of 64kb in size, right?
quote:
Is this sustained? Over minutes? or is it a spot rate.
- See the above.
2ckempste:
quote:
Are you measuring read and write queues seperately? make sure you do for starters. After that, you need to know the total disks in the array as a divisor for the figure shown in perfmon. Have you got write cache enabled btw?
- I am having a very low write activity (less then 1%, compared to read activity)
I can divide queue of 59 on 5 disks (RAID5 on 6 disks) and get a queue of ~10 for each one. I can also divide the "Avg. Disk Bytes/Transfer" on these disks, but a disk transfer of ~7Kb for one disk does not make sense as well.
I am having a 128Mb read/write cache (50%/50%)
2All:
I have this software on the server: Win2k Adv. Server sp4 + MS SQL 2000 sp3.
September 17, 2003 at 5:14 am
Hopefully some answers to your questions...
a) Any type of disk storage can be fragmented. Fragmentation is a mainly a function of the file system, not the disk. Bad fragmentation will always hit performance.
b) SQL2K reads data in 64K extents. However, if your disk is not formatted with 64K stripe size and NTFS is not formatted with 64K allocation units, you can loose a measureable amount of performance. This can particularly occur if your 64K SQL extent has been allocated to a number of 4K NTFS extents scattered over the disk surface. Scatter/Gather I-O in Windows allows this to work, it does not make it perform...
c) Having a fancy controller may not help much if all your disks are on a single channel. Does your disk enclosure support multiple busses? Is each bus connected to a different port in your controller? Is your array made up of disks on each bus, or is it confined to one bus? If you want to maximise RAID performance, you need to spread your array over as many busses and ports as possible, as well as over as many disks as possible. If everything goes via a single bus and port, you are going to see a shedload of queueing.
"All information given is a personal opinion that may not match reality"
All information provided is a personal opinion that may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 17, 2003 at 5:56 am
2EdVassie:
quote:
a) Any type of disk storage can be fragmented. Fragmentation is a mainly a function of the file system, not the disk. Bad fragmentation will always hit performance.
- Standard defragment tools in Windows will not defragment a RAID array, it will make the situation even worse, right? Is there a way to defragment such disk?
quote:
b) SQL2K reads data in 64K extents. However, if your disk is not formatted with 64K stripe size and NTFS is not formatted with 64K allocation units, you can loose a measureable amount of performance. This can particularly occur if your 64K SQL extent has been allocated to a number of 4K NTFS extents scattered over the disk surface. Scatter/Gather I-O in Windows allows this to work, it does not make it perform...
-Right! The server had 32Kb stripe size & 4Kb allocation units.
Will correct this in a near future.
Thanks much!
September 17, 2003 at 6:55 am
I may be missing something... why do you think that Windows defrag will be a problem with RAID arrays
All information provided is a personal opinion that may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 17, 2003 at 7:21 am
I read that for hardware RAID, windows defrag will worsen performance. Unfortunately can't provide a link.
Just another question: how to figure out the allocation unit (cluster) size of NTFS volume, with standard Windows 2000 utils?
September 17, 2003 at 7:54 am
I agree that while defrag is running, performance may be bad. This could be a reason to not use auto-defrag products. However, if you run the defrag in a planned window, after it is complete you should see the normal benefits.
Finding NTFS allocation unit size can be tricky. Something I have used is to run the disk format GUI. It will show the current allocation unit size as the suggested size after the format. I don't try this with live boxes, as the OK button is far too close to the Cancel button (on the same desktop...) for me to feel safe using it. There are probably command line utilities in the resource kit that can more safely display this information.
All information provided is a personal opinion that may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 18, 2003 at 6:30 am
Hi!
Just want to correct myself, I used a wrong counter:
Avg. Disk Bytes/Transfer
That is:
quote:
Avg. Disk Bytes/Transfer is the average number of bytes transferred to or from the disk during write or read operations.
I.e.: an average amount of data for a single write or read operation
I should had used:
Disk Bytes/Sec
That is:
quote:
Disk Bytes/sec is the rate bytes are transferred to or from the disk during write or read operations.
I.e.: the total amount of data during read and write operations.
So when the Avg. Disk Queue Length was about 13 (on the average for 100 seconds), the
Disk Bytes/Sec counter was about 6Mb, that looks like true.
Average Duration
Avg. Disk Queue Length 13,6781:40
Disk Bytes/Sec 6045234 1:40
Though, EdVassie note about stripe and allocation unit size is a greate point too!
September 26, 2003 at 7:39 am
During some testing I found some info that might interest you..:
When using the default read-ahead setting of SQLServer, it reads 32*8192 byte 'clusters' at the time (using ReadFileScattered)
It looks like 64KB is an allocation size of an extent, not a size of I/O.
If you turn of readahead (-t652 or dbcc traceon(652), SQL will read 8192KB blocks at the time. Depending on what you are doing, this can be beneficial. During ERP benchmarks in the past we turned of RA. If you're doing a lot of tablescans/DSS type of work on large tables turning off RA is not such a good idea..Running with RA on also adds to the amount of I/O per sec, you're drive gets..
Next to this also note the effects of Parallel queries on the disks: It's easy to start a parallized query doing parallel readahead on a few disks: this is very demanding..
According to your data you are doing quite some I/O's transfering a lot of data: if you (think you) run an OLTP like system, you might consider turning of RA or bring down parallelism. Or both.
good luck,
mario
November 9, 2004 at 9:48 am
I am looking for a good resource to help me optimize my hardware configuration. I've been given a sizable budget to purchase new raid controllers and hard drives. My box is a compaq 4 x 900 xeon with 16gb of ram. We currently have the OS drive at raid 5 and the data and log files on the same drive set at raid 5. From what I gathered this isn't ideal. Please tell me if this would be a preferrable. Thanks your help.
OS = mirrored
Temp DB on a Striped Set without parity
Log Files on a Raid 5
Data Files on Raid 1+0
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply