Impact of HD (de)fragmentation on SQL Server performance

It seems logical to me, that defragmented HDs would yield better performance
than fragmented ones.
Are there any published metrics that demonstrate this for Transactional
Databases?
(e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
fragmentation vs. no fragmentation -- using the same load in both scenarios
-- in terms of any performance indicators: disk seek time, R/W Queue, etc).
Thanks,
Tea C."Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
> performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
> scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
> etc).
>
The fragmentation percentage is almost meaningless for large databases. A
100g database in 2 fragments would equate to 50% disk fragmentation, but has
0.00000001% affect on performance. On the other hand, a small, hot database
in many fragments could adversly affect performance without causing alarming
disk fragmentation percentage.
David|||Hi
Having the Logs on a RAID-5 affects performance more than fragmentation at
OS level.
Auto grow and Auto shrink have an effect as it causes your DB to become
fragmented.
Sometime s with RAID-5, having file fragmentation is actually good as it
might then involve a different spindle than if the data was contiguous.
Test the performance differences in your environment, setup may play a big
role.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
etc).
> Thanks,
> --
> Tea C.sql