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

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.

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.

IIF statment in an SQL statment.

I am trying to convert a logical and date fields into number fields. I am
using IIF() but I can't seem to get the syntax correct. Help Please.
SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
FROM Order2
GROUP BY MagazineName
The overall goal is to
1) count then number of records
2) count how many are renewal's
3) count how many are canceled
example:
CHILD 50 5 1
Scott BurkeHey Scott,
Use the CASE statement in SQL, not IIF. Like this:
SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag =false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
1 END as CLRFL
FROM Order2
GROUP BY MagazineName
Michael C
"Scott Burke" wrote:
> I am trying to convert a logical and date fields into number fields. I am
> using IIF() but I can't seem to get the syntax correct. Help Please.
> SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> FROM Order2
> GROUP BY MagazineName
> The overall goal is to
> 1) count then number of records
> 2) count how many are renewal's
> 3) count how many are canceled
> example:
> CHILD 50 5 1
> Scott Burke|||Hi Michael. Thanks for the suggestion. It worked exactly the way I wonted to.
The case statement looks a lot like an IIF() statement to me.
Time to do some research.
Thanks again.
Scott Burke
"Michael C" wrote:
> Hey Scott,
> Use the CASE statement in SQL, not IIF. Like this:
>
> SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag => false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
> 1 END as CLRFL
> FROM Order2
> GROUP BY MagazineName
>
> Michael C
> "Scott Burke" wrote:
> > I am trying to convert a logical and date fields into number fields. I am
> > using IIF() but I can't seem to get the syntax correct. Help Please.
> >
> > SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> > 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> > FROM Order2
> > GROUP BY MagazineName
> >
> > The overall goal is to
> > 1) count then number of records
> > 2) count how many are renewal's
> > 3) count how many are canceled
> >
> > example:
> > CHILD 50 5 1
> >
> > Scott Burke