August 19, 2008 at 10:50 am
Hi,
Will faster disks improve query performance?
Thanks.
August 19, 2008 at 11:32 am
That is a fairly deep question, even though it doesn't seem that way. The answer is Yes, but it has to be qualified with "it depends" and it really does depend on a whole lot. However, as a very strong rule of thumb, faster disk will improve query performance.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 20, 2008 at 2:53 pm
Thankyou.
August 22, 2008 at 5:37 am
with disks more spindels equals better performane. althogh you can almost always get a quick win by aligning ntfs with sql. all you need to does is align your mbr with a 64k offset using diskpart.exe.
sorry for the txt format im typing this on my new pda 🙂
August 25, 2008 at 1:35 pm
Faster disks will only help query speed in cases where you have a low data buffer cache hit ratio. Part of database tuning is optimizing memory usage so that you have high data and procedure cache hits and low OS page swapping. Most people try to get data cache hit ratio percent in the mid to upper 90s, so the faster disks would only help in the 1%-5% of the cases where the data wasn't already in memory so a physical read had to be performed.
August 25, 2008 at 3:15 pm
charshman (8/25/2008)
Faster disks will only help query speed in cases where you have a low data buffer cache hit ratio.
This is not true. It's the total amount of time spent waiting for the DiskIO's that matters here. Cache ratios do figure into this, but they are a secondary indicator, not the primary.
Part of database tuning is optimizing memory usage so that you have high data and procedure cache hits and low OS page swapping. Most people try to get data cache hit ratio percent in the mid to upper 90s, so the faster disks would only help in the 1%-5% of the cases where the data wasn't already in memory so a physical read had to be performed.
All true, except the conclusion (above). Because random disk IO's are so much slower than anything else in side the server, Disk IO's tend to dominate in performance factors. Even if all of your IO attempts are Cache eligible and you have 97% hit sucess, that means 3% or your IO attempts become actual (physical) IOs. If that IO is all to the same disk, is random (ie., not sequential) and gets says 20ms service time, that would mean that performance impacts would start to be noticable at about 800 IO attempts/sec and would be pegged at around 1600 IO attempts/sec.
Add to that the fact that some significant amount of IO attempts (usually way more than 3%) are not even eligible for caching and you can have significant IO performance problems, even with Cache Hit ratios above 95%.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply