Tuesday, February 21, 2012

Restore = Defrag?

If I backup a db and then delete and restore it, is that equivalent to doing
a defrag?"barryfz" <barryfz@.nospam.nospam> wrote in message
news:eW43XeJAFHA.3368@.TK2MSFTNGP15.phx.gbl...
> If I backup a db and then delete and restore it, is that equivalent to
doing
> a defrag?
>
When you say defrag, what are you referring to?
In general, No. SQL Server reads your data Extent by Extent and writes
that to the backup.
To defrag the data you need to defrag your clustered indexes and then run a
DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in the BOL
for more information about what they do for you.
Rick Sawtell
MCT, MCSD, MCDBA|||Be careful with this. First of all, SQL Server 2000 has a DBCC
SHRINKDATABASE, not SHRINKDB.
There is no specific reason to do a shrink when you're trying to defragment.
In fact, shrinking your data files, because it moves all the data to earlier
in the file wherever there happens to be room, can actually drastically
increase your fragmentation.
Shrinking a database is not rercommended, for many reasons. Please see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:euNveOPAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> "barryfz" <barryfz@.nospam.nospam> wrote in message
> news:eW43XeJAFHA.3368@.TK2MSFTNGP15.phx.gbl...
>> If I backup a db and then delete and restore it, is that equivalent to
> doing
>> a defrag?
>>
> When you say defrag, what are you referring to?
> In general, No. SQL Server reads your data Extent by Extent and writes
> that to the backup.
> To defrag the data you need to defrag your clustered indexes and then run
> a
> DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in the BOL
> for more information about what they do for you.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Hey, Kalen, while I got you on the line, I realize shrinking the database
could, in the end, cause more fragmentation, due to page splits, especially
if the fill factor is to high. However, my OS System Admins constantly
pester me about how much SQL Server database files are NTFS fragmented on
the disk. I realize that internally, SQL Server manages its own files by
page and extent placement and just because the file may be physically
sequential, this does not garauntee any page order in the logical sequence.
How would I go about explaining this to them that the logical fragementation
of data pages and/or index pages is more significant to performance than the
contigueousness of the physical file clusters?
Thanks,
Anthony Thomas
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u2UrLiPAFHA.1908@.TK2MSFTNGP15.phx.gbl...
Be careful with this. First of all, SQL Server 2000 has a DBCC
SHRINKDATABASE, not SHRINKDB.
There is no specific reason to do a shrink when you're trying to defragment.
In fact, shrinking your data files, because it moves all the data to earlier
in the file wherever there happens to be room, can actually drastically
increase your fragmentation.
Shrinking a database is not rercommended, for many reasons. Please see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:euNveOPAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> "barryfz" <barryfz@.nospam.nospam> wrote in message
> news:eW43XeJAFHA.3368@.TK2MSFTNGP15.phx.gbl...
>> If I backup a db and then delete and restore it, is that equivalent to
> doing
>> a defrag?
>>
> When you say defrag, what are you referring to?
> In general, No. SQL Server reads your data Extent by Extent and writes
> that to the backup.
> To defrag the data you need to defrag your clustered indexes and then run
> a
> DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in the BOL
> for more information about what they do for you.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Maybe this will help:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Hey, Kalen, while I got you on the line, I realize shrinking the database
> could, in the end, cause more fragmentation, due to page splits,
Actually a shrink does not split the pages. It simply moves them (or the
extents) from the back of the file to the first available space near the
front. The fragmentation you get from shrinking is that the extents are no
longer contiguous in the file (assuming they were to begin with). If you do
regular enough index maintenance you can assume that most of the pages and
extents are in the right order. The shrink will break that order at the
extent level for sure.
If the database files are created large enough to handle the data for quite
a while to begin with you should avoid the OS level fragmentation. That
comes from making the file too small and lots of growing and shrinking which
should be avoided when ever possible.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uN5n7yaAFHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hey, Kalen, while I got you on the line, I realize shrinking the database
> could, in the end, cause more fragmentation, due to page splits,
> especially
> if the fill factor is to high. However, my OS System Admins constantly
> pester me about how much SQL Server database files are NTFS fragmented on
> the disk. I realize that internally, SQL Server manages its own files by
> page and extent placement and just because the file may be physically
> sequential, this does not garauntee any page order in the logical
> sequence.
> How would I go about explaining this to them that the logical
> fragementation
> of data pages and/or index pages is more significant to performance than
> the
> contigueousness of the physical file clusters?
> Thanks,
> Anthony Thomas
>
> --
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u2UrLiPAFHA.1908@.TK2MSFTNGP15.phx.gbl...
> Be careful with this. First of all, SQL Server 2000 has a DBCC
> SHRINKDATABASE, not SHRINKDB.
> There is no specific reason to do a shrink when you're trying to
> defragment.
> In fact, shrinking your data files, because it moves all the data to
> earlier
> in the file wherever there happens to be room, can actually drastically
> increase your fragmentation.
> Shrinking a database is not rercommended, for many reasons. Please see:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:euNveOPAFHA.3264@.TK2MSFTNGP12.phx.gbl...
>> "barryfz" <barryfz@.nospam.nospam> wrote in message
>> news:eW43XeJAFHA.3368@.TK2MSFTNGP15.phx.gbl...
>> If I backup a db and then delete and restore it, is that equivalent to
>> doing
>> a defrag?
>>
>> When you say defrag, what are you referring to?
>> In general, No. SQL Server reads your data Extent by Extent and writes
>> that to the backup.
>> To defrag the data you need to defrag your clustered indexes and then run
>> a
>> DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in the BOL
>> for more information about what they do for you.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>|||Hi Anthony
I'm not sure how shrinking the files is going to remove the OS disk
fragmentation. I would think that would add more when the file had to grow
again.
Logical fragmentation is not always worse than contiguousness of the
physical file clusters. It really depends on what you are doing.
See if this paper helps:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uN5n7yaAFHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hey, Kalen, while I got you on the line, I realize shrinking the database
> could, in the end, cause more fragmentation, due to page splits,
> especially
> if the fill factor is to high. However, my OS System Admins constantly
> pester me about how much SQL Server database files are NTFS fragmented on
> the disk. I realize that internally, SQL Server manages its own files by
> page and extent placement and just because the file may be physically
> sequential, this does not garauntee any page order in the logical
> sequence.
> How would I go about explaining this to them that the logical
> fragementation
> of data pages and/or index pages is more significant to performance than
> the
> contigueousness of the physical file clusters?
> Thanks,
> Anthony Thomas
>
> --
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u2UrLiPAFHA.1908@.TK2MSFTNGP15.phx.gbl...
> Be careful with this. First of all, SQL Server 2000 has a DBCC
> SHRINKDATABASE, not SHRINKDB.
> There is no specific reason to do a shrink when you're trying to
> defragment.
> In fact, shrinking your data files, because it moves all the data to
> earlier
> in the file wherever there happens to be room, can actually drastically
> increase your fragmentation.
> Shrinking a database is not rercommended, for many reasons. Please see:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:euNveOPAFHA.3264@.TK2MSFTNGP12.phx.gbl...
>> "barryfz" <barryfz@.nospam.nospam> wrote in message
>> news:eW43XeJAFHA.3368@.TK2MSFTNGP15.phx.gbl...
>> If I backup a db and then delete and restore it, is that equivalent to
>> doing
>> a defrag?
>>
>> When you say defrag, what are you referring to?
>> In general, No. SQL Server reads your data Extent by Extent and writes
>> that to the backup.
>> To defrag the data you need to defrag your clustered indexes and then run
>> a
>> DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in the BOL
>> for more information about what they do for you.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>|||I'm not so sure about the last statement. Last year we had to scale up the
hardware of one of our co-hosted fail over clusters. We ended up migrating
the databases from the old server to the new, one database at a time, on
clean, freshly installed, shared cluster disks. This should have copied the
files in large contiguous chunks. Within 1 month, with weekly index
defrags, the OS file fragmentation was over 86%. Now, with minimal 1 to 2
GB growth per week, roughly 10% increaments, Autogrow, against about 70
databases, with 48 GB on a 60 GB partition, how could the OS fragmentation
get so high in so short a time?
Moreover, if we shut SQL Server down to defrag the disk, which would be a
very time consuming operation for a highly-available system, what would be
the point if it would be this fragmented again in such a short time?
What are do you see as a typical OS disk defragmenting schedule? I've
always told our OS System Admins to go suck a lemon because SQL Server
managed the logical data page organization when we defrag our clusterd
indexes and that we were never garaunteed a contigueous placement of these
data pages anyway.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u3lCGDbAFHA.2104@.TK2MSFTNGP14.phx.gbl...
Maybe this will help:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Hey, Kalen, while I got you on the line, I realize shrinking the database
> could, in the end, cause more fragmentation, due to page splits,
Actually a shrink does not split the pages. It simply moves them (or the
extents) from the back of the file to the first available space near the
front. The fragmentation you get from shrinking is that the extents are no
longer contiguous in the file (assuming they were to begin with). If you do
regular enough index maintenance you can assume that most of the pages and
extents are in the right order. The shrink will break that order at the
extent level for sure.
If the database files are created large enough to handle the data for quite
a while to begin with you should avoid the OS level fragmentation. That
comes from making the file too small and lots of growing and shrinking which
should be avoided when ever possible.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uN5n7yaAFHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hey, Kalen, while I got you on the line, I realize shrinking the database
> could, in the end, cause more fragmentation, due to page splits,
> especially
> if the fill factor is to high. However, my OS System Admins constantly
> pester me about how much SQL Server database files are NTFS fragmented on
> the disk. I realize that internally, SQL Server manages its own files by
> page and extent placement and just because the file may be physically
> sequential, this does not garauntee any page order in the logical
> sequence.
> How would I go about explaining this to them that the logical
> fragementation
> of data pages and/or index pages is more significant to performance than
> the
> contigueousness of the physical file clusters?
> Thanks,
> Anthony Thomas
>
> --
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u2UrLiPAFHA.1908@.TK2MSFTNGP15.phx.gbl...
> Be careful with this. First of all, SQL Server 2000 has a DBCC
> SHRINKDATABASE, not SHRINKDB.
> There is no specific reason to do a shrink when you're trying to
> defragment.
> In fact, shrinking your data files, because it moves all the data to
> earlier
> in the file wherever there happens to be room, can actually drastically
> increase your fragmentation.
> Shrinking a database is not rercommended, for many reasons. Please see:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:euNveOPAFHA.3264@.TK2MSFTNGP12.phx.gbl...
>> "barryfz" <barryfz@.nospam.nospam> wrote in message
>> news:eW43XeJAFHA.3368@.TK2MSFTNGP15.phx.gbl...
>> If I backup a db and then delete and restore it, is that equivalent to
>> doing
>> a defrag?
>>
>> When you say defrag, what are you referring to?
>> In general, No. SQL Server reads your data Extent by Extent and writes
>> that to the backup.
>> To defrag the data you need to defrag your clustered indexes and then run
>> a
>> DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in the BOL
>> for more information about what they do for you.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_00CB_01C50182.F7791110
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Thanks. That's a pretty good-sized paper. It'll take me a while to digest.
Thanks for the link.
By the way, I did not mean to imply that the SHRINK itself would cause page
splits, but by reducing the database size, perhaps reorganizing the data
pages, the fill factor would reapportion the free space, thus causing page
splits to occur more often.
Sincerely,
Anthony Thomas
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23w5OFDbAFHA.1564@.TK2MSFTNGP09.phx.gbl...
Hi Anthony
I'm not sure how shrinking the files is going to remove the OS disk
fragmentation. I would think that would add more when the file had to grow
again.
Logical fragmentation is not always worse than contiguousness of the
physical file clusters. It really depends on what you are doing.
See if this paper helps:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uN5n7yaAFHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hey, Kalen, while I got you on the line, I realize shrinking the
database
> could, in the end, cause more fragmentation, due to page splits,
> especially
> if the fill factor is to high. However, my OS System Admins constantly
> pester me about how much SQL Server database files are NTFS fragmented
on
> the disk. I realize that internally, SQL Server manages its own files
by
> page and extent placement and just because the file may be physically
> sequential, this does not garauntee any page order in the logical
> sequence.
>
> How would I go about explaining this to them that the logical
> fragementation
> of data pages and/or index pages is more significant to performance than
> the
> contigueousness of the physical file clusters?
>
> Thanks,
>
> Anthony Thomas
>
>
> --
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u2UrLiPAFHA.1908@.TK2MSFTNGP15.phx.gbl...
> Be careful with this. First of all, SQL Server 2000 has a DBCC
> SHRINKDATABASE, not SHRINKDB.
>
> There is no specific reason to do a shrink when you're trying to
> defragment.
> In fact, shrinking your data files, because it moves all the data to
> earlier
> in the file wherever there happens to be room, can actually drastically
> increase your fragmentation.
>
> Shrinking a database is not rercommended, for many reasons. Please see:
>
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:euNveOPAFHA.3264@.TK2MSFTNGP12.phx.gbl...
>>
>> "barryfz" <barryfz@.nospam.nospam> wrote in message
>> news:eW43XeJAFHA.3368@.TK2MSFTNGP15.phx.gbl...
>> If I backup a db and then delete and restore it, is that equivalent to
>> doing
>> a defrag?
>>
>>
>>
>> When you say defrag, what are you referring to?
>>
>> In general, No. SQL Server reads your data Extent by Extent and
writes
>> that to the backup.
>>
>> To defrag the data you need to defrag your clustered indexes and then
run
>> a
>> DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in the
BOL
>> for more information about what they do for you.
>>
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>>
>
>
--=_NextPart_000_00CB_01C50182.F7791110
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Thanks. That's a pretty =good-sized paper. It'll take me a while to digest. Thanks for the link.
By the way, I did not mean to imply =that the SHRINK itself would cause page splits, but by reducing the database =size, perhaps reorganizing the data pages, the fill factor would reapportion =the free space, thus causing page splits to occur more often.
Sincerely,
Anthony Thomas
--
"Kalen Delaney" wrote in message news:%23w5OFDbAFHA.=1564@.TK2MSFTNGP09.phx.gbl...Hi AnthonyI'm not sure how shrinking the files is going to remove =the OS disk fragmentation. I would think that would add more when the =file had to grow again.Logical fragmentation is not always worse than contiguousness of the physical file clusters. It really depends on =what you are doing.See if this paper helps:http://www.microsoft.com/technet/prodtechnol/sql/2000/mainta=in/ss2kidbp.mspx-- HTH--Kalen DelaneySQL Server MVPhttp://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Anthony Thomas" wrote in message news:uN5n7yaAFHA.3616=@.TK2MSFTNGP11.phx.gbl...> Hey, Kalen, while I got you on the line, I realize shrinking the database> could, in the end, cause more fragmentation, due to =page splits, > especially> if the fill factor is to =high. However, my OS System Admins constantly> pester me about how =much SQL Server database files are NTFS fragmented on> the disk. I =realize that internally, SQL Server manages its own files by> page and =extent placement and just because the file may be physically> =sequential, this does not garauntee any page order in the logical > sequence.>> How would I go about explaining this to them =that the logical > fragementation> of data pages and/or index =pages is more significant to performance than > the> =contigueousness of the physical file clusters?>> Thanks,>> =Anthony Thomas>>> -- >> "Kalen Delaney" = wrote in message> news:u2UrLiPAFHA.1908=@.TK2MSFTNGP15.phx.gbl...> Be careful with this. First of all, SQL Server 2000 has a DBCC> = SHRINKDATABASE, not SHRINKDB.>> There is no specific =reason to do a shrink when you're trying to > defragment.> In =fact, shrinking your data files, because it moves all the data to > earlier> in the file wherever there happens to be room, can =actually drastically> increase your fragmentation.>> =Shrinking a database is not rercommended, for many reasons. Please =see:>>http://www.karaszi.com/SQLServer/info_dont_shrink.asp">http://www=.karaszi.com/SQLServer/info_dont_shrink.asp>> -- > HTH> --> Kalen Delaney> =SQL Server MVP>http://www.SolidQualityLearning.com">www.SolidQualityLearning.com=>>> "Rick Sawtell" =wrote in message> news:euNveOPAFHA.3264=@.TK2MSFTNGP12.phx.gbl...>> "barryfz" =wrote in message> news:eW43XeJAFHA.3368=@.TK2MSFTNGP15.phx.gbl...> If I backup a db and then delete and restore it, is that equivalent to> doing> a defrag?>>>> When =you say defrag, what are you referring to?>> In general, = No. SQL Server reads your data Extent by Extent and writes> that to the backup.>> To =defrag the data you need to defrag your clustered indexes and then =run> a> DBCC SHRINKDB or DBCC SHRINKFILE command. =You can look those in the BOL> for more information about what they =do for you.>>> Rick Sawtell> MCT, =MCSD, MCDBA>>>>>

--=_NextPart_000_00CB_01C50182.F7791110--|||The fragmentation at the OS level has absolutely nothing to do with
reindexing or defragging within SQL Server. Sql Server doesn't manage the
physical file per say, the OS does. Only during the intial file creation,
growing or shrinking does SQL Server ask the OS to physically adjust the
file in terms of size. And even then Sql Server does not have any control
as to where the file sis placed physically on the disk. Defragmenting with
DBREINDEX or INDEXDEFRAG works solely within the boundaries of the file and
can not cause any change in the physical placement on disk other than
forcing an AutoGrow. It sounds like the db had AutoGrow and AutoShrink (or
a Shrink job) turned on. I am not convinced the copying of the DB even
created contiguous files to start with but unless you checked the OS level
fragmentation immediately after the copies it would be hard to say. If
that was on a SAN you may want to check to be sure there isn't anything
funky with the OS Frag tool and the SAN. But in all the years that I have
worked with 7.0 or 2000 I have only seen fragmentation at the OS level as a
result of growing and shrinking. But unless the OS level fragmentation is
extreme it willnot have as much effect as Page and Extent fragmentation
within the file. A few non-contiguous segments in a data file are nothing to
be concerned with.
--
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OWE2xRbAFHA.1400@.TK2MSFTNGP11.phx.gbl...
> I'm not so sure about the last statement. Last year we had to scale up
> the
> hardware of one of our co-hosted fail over clusters. We ended up
> migrating
> the databases from the old server to the new, one database at a time, on
> clean, freshly installed, shared cluster disks. This should have copied
> the
> files in large contiguous chunks. Within 1 month, with weekly index
> defrags, the OS file fragmentation was over 86%. Now, with minimal 1 to 2
> GB growth per week, roughly 10% increaments, Autogrow, against about 70
> databases, with 48 GB on a 60 GB partition, how could the OS fragmentation
> get so high in so short a time?
> Moreover, if we shut SQL Server down to defrag the disk, which would be a
> very time consuming operation for a highly-available system, what would be
> the point if it would be this fragmented again in such a short time?
> What are do you see as a typical OS disk defragmenting schedule? I've
> always told our OS System Admins to go suck a lemon because SQL Server
> managed the logical data page organization when we defrag our clusterd
> indexes and that we were never garaunteed a contigueous placement of these
> data pages anyway.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u3lCGDbAFHA.2104@.TK2MSFTNGP14.phx.gbl...
> Maybe this will help:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> Hey, Kalen, while I got you on the line, I realize shrinking the database
>> could, in the end, cause more fragmentation, due to page splits,
> Actually a shrink does not split the pages. It simply moves them (or the
> extents) from the back of the file to the first available space near the
> front. The fragmentation you get from shrinking is that the extents are
> no
> longer contiguous in the file (assuming they were to begin with). If you
> do
> regular enough index maintenance you can assume that most of the pages and
> extents are in the right order. The shrink will break that order at the
> extent level for sure.
> If the database files are created large enough to handle the data for
> quite
> a while to begin with you should avoid the OS level fragmentation. That
> comes from making the file too small and lots of growing and shrinking
> which
> should be avoided when ever possible.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:uN5n7yaAFHA.3616@.TK2MSFTNGP11.phx.gbl...
>> Hey, Kalen, while I got you on the line, I realize shrinking the database
>> could, in the end, cause more fragmentation, due to page splits,
>> especially
>> if the fill factor is to high. However, my OS System Admins constantly
>> pester me about how much SQL Server database files are NTFS fragmented on
>> the disk. I realize that internally, SQL Server manages its own files by
>> page and extent placement and just because the file may be physically
>> sequential, this does not garauntee any page order in the logical
>> sequence.
>> How would I go about explaining this to them that the logical
>> fragementation
>> of data pages and/or index pages is more significant to performance than
>> the
>> contigueousness of the physical file clusters?
>> Thanks,
>> Anthony Thomas
>>
>> --
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u2UrLiPAFHA.1908@.TK2MSFTNGP15.phx.gbl...
>> Be careful with this. First of all, SQL Server 2000 has a DBCC
>> SHRINKDATABASE, not SHRINKDB.
>> There is no specific reason to do a shrink when you're trying to
>> defragment.
>> In fact, shrinking your data files, because it moves all the data to
>> earlier
>> in the file wherever there happens to be room, can actually drastically
>> increase your fragmentation.
>> Shrinking a database is not rercommended, for many reasons. Please see:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:euNveOPAFHA.3264@.TK2MSFTNGP12.phx.gbl...
>> "barryfz" <barryfz@.nospam.nospam> wrote in message
>> news:eW43XeJAFHA.3368@.TK2MSFTNGP15.phx.gbl...
>> If I backup a db and then delete and restore it, is that equivalent to
>> doing
>> a defrag?
>>
>> When you say defrag, what are you referring to?
>> In general, No. SQL Server reads your data Extent by Extent and writes
>> that to the backup.
>> To defrag the data you need to defrag your clustered indexes and then
>> run
>> a
>> DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in the
>> BOL
>> for more information about what they do for you.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_04FD_01C5017D.FCF8F100
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Page splits only occur with clustered indexes. And when shrinking, the =pages from clustered indexes are moved as a unit, so there is no change =in free space on the pages themselves.
For heaps, shrinking will move individual rows, but page splits won't =apply.
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message =news:ODzkxVbAFHA.3852@.TK2MSFTNGP10.phx.gbl...
Thanks. That's a pretty good-sized paper. It'll take me a while to =digest. Thanks for the link.
By the way, I did not mean to imply that the SHRINK itself would cause =page splits, but by reducing the database size, perhaps reorganizing the =data pages, the fill factor would reapportion the free space, thus =causing page splits to occur more often.
Sincerely,
Anthony Thomas
-- "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message =news:%23w5OFDbAFHA.1564@.TK2MSFTNGP09.phx.gbl...
Hi Anthony
I'm not sure how shrinking the files is going to remove the OS disk fragmentation. I would think that would add more when the file had =to grow again.
Logical fragmentation is not always worse than contiguousness of the =
physical file clusters. It really depends on what you are doing.
See if this paper helps:
=http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.m=
spx
-- HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:uN5n7yaAFHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hey, Kalen, while I got you on the line, I realize shrinking the =database
> could, in the end, cause more fragmentation, due to page splits, > especially
> if the fill factor is to high. However, my OS System Admins =constantly
> pester me about how much SQL Server database files are NTFS =fragmented on
> the disk. I realize that internally, SQL Server manages its own =files by
> page and extent placement and just because the file may be =physically
> sequential, this does not garauntee any page order in the logical > sequence.
>
> How would I go about explaining this to them that the logical > fragementation
> of data pages and/or index pages is more significant to =performance than > the
> contigueousness of the physical file clusters?
>
> Thanks,
>
> Anthony Thomas
>
>
> -- >
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u2UrLiPAFHA.1908@.TK2MSFTNGP15.phx.gbl...
> Be careful with this. First of all, SQL Server 2000 has a DBCC
> SHRINKDATABASE, not SHRINKDB.
>
> There is no specific reason to do a shrink when you're trying to > defragment.
> In fact, shrinking your data files, because it moves all the data =to > earlier
> in the file wherever there happens to be room, can actually =drastically
> increase your fragmentation.
>
> Shrinking a database is not rercommended, for many reasons. Please =see:
>
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> -- > HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:euNveOPAFHA.3264@.TK2MSFTNGP12.phx.gbl...
>>
>> "barryfz" <barryfz@.nospam.nospam> wrote in message
>> news:eW43XeJAFHA.3368@.TK2MSFTNGP15.phx.gbl...
>> If I backup a db and then delete and restore it, is that =equivalent to
>> doing
>> a defrag?
>>
>>
>>
>> When you say defrag, what are you referring to?
>>
>> In general, No. SQL Server reads your data Extent by Extent and =writes
>> that to the backup.
>>
>> To defrag the data you need to defrag your clustered indexes and =then run
>> a
>> DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in =the BOL
>> for more information about what they do for you.
>>
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>>
>
>
--=_NextPart_000_04FD_01C5017D.FCF8F100
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Page splits only occur with clustered =indexes. And when shrinking, the pages from clustered indexes are moved as a unit, so =there is no change in free space on the pages themselves.
For heaps, shrinking will move =individual rows, but page splits won't apply.
-- HTH--Kalen DelaneySQL Server =MVPhttp://www.SolidQualityLearning.com">www.SolidQualityLearning.com=
"Anthony Thomas" wrote in =message news:ODzkxVbAFHA.3852=@.TK2MSFTNGP10.phx.gbl...
Thanks. That's a pretty =good-sized paper. It'll take me a while to digest. Thanks for =the link.

By the way, I did not mean to imply =that the SHRINK itself would cause page splits, but by reducing the database =size, perhaps reorganizing the data pages, the fill factor would reapportion =the free space, thus causing page splits to occur more often.

Sincerely,


Anthony Thomas

--
"Kalen Delaney" wrote in message news:%23w5OFDbAFHA.=1564@.TK2MSFTNGP09.phx.gbl...Hi AnthonyI'm not sure how shrinking the files is going to =remove the OS disk fragmentation. I would think that would add more when =the file had to grow again.Logical fragmentation is not always =worse than contiguousness of the physical file clusters. It really depends =on what you are doing.See if this paper helps:http://www.microsoft.com/technet/prodtechnol/sql/2000/mainta=in/ss2kidbp.mspx-- HTH--Kalen DelaneySQL Server MVPhttp://www.SolidQualityLearning.com">www.SolidQualityLearning.com="Anthony Thomas" wrote in message news:uN5n7yaAFHA.3616=@.TK2MSFTNGP11.phx.gbl...> Hey, Kalen, while I got you on the line, I realize shrinking the database> could, in the end, cause more fragmentation, due to =page splits, > especially> if the fill factor is to =high. However, my OS System Admins constantly> pester me about how =much SQL Server database files are NTFS fragmented on> the disk. =I realize that internally, SQL Server manages its own files by> =page and extent placement and just because the file may be =physically> sequential, this does not garauntee any page order in the logical => sequence.>> How would I go about explaining this to =them that the logical > fragementation> of data pages and/or =index pages is more significant to performance than > the> =contigueousness of the physical file clusters?>> =Thanks,>> Anthony Thomas>>> -- >> "Kalen =Delaney" wrote in message> news:u2UrLiPAFHA.1908=@.TK2MSFTNGP15.phx.gbl...> Be careful with this. First of all, SQL Server 2000 has a =DBCC> SHRINKDATABASE, not SHRINKDB.>> There is no specific =reason to do a shrink when you're trying to > defragment.> In =fact, shrinking your data files, because it moves all the data to > = earlier> in the file wherever there happens to be room, can =actually drastically> increase your fragmentation.>> =Shrinking a database is not rercommended, for many reasons. Please =see:>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp">http://www=.karaszi.com/SQLServer/info_dont_shrink.asp>> -- > HTH> --> Kalen =Delaney> SQL Server MVP>http://www.SolidQualityLearning.com">www.SolidQualityLearning.com=>>> "Rick Sawtell" =wrote in message> news:euNveOPAFHA.3264=@.TK2MSFTNGP12.phx.gbl...>> "barryfz" =wrote in message> news:eW43XeJAFHA.3368=@.TK2MSFTNGP15.phx.gbl...> If I backup a db and then delete and restore it, is that equivalent to> doing> a defrag?>>>> When =you say defrag, what are you referring to?>> In =general, No. SQL Server reads your data Extent by Extent and writes> that to the backup.>> To =defrag the data you need to defrag your clustered indexes and then =run> a> DBCC SHRINKDB or DBCC SHRINKFILE command. =You can look those in the BOL> for more information about what =they do for you.>>> Rick =Sawtell> MCT, MCSD, MCDBA>>>>>

--=_NextPart_000_04FD_01C5017D.FCF8F100--|||My point exactly. The fragmentation of the indexes/data pages is a logical
construct. The links between pages may be reorganized to produce less
logical fragmentation of the data pages but SQL Server can do nothing as far
as which physical disk clusters they are assigned, which is the only
fragmentation the OS would be concerned over.
However, given that SQL Server has logically sorted a group of clustered
index pages and you had to traverse those pages sequentially during a scan,
the physical placement of those data pages could be helped by having them
physically located sequentially but, we are in the business to reduce scans
as often as possible.
Therefore, my logic is that physical fragmentation could hurt, or the
converse help, only little unless SQL Server is doing an abundance of
scanning. However, if you have seen evidence where a DBA should also be
concerned about physical database file fragmentation, I would be most
interested in seeing the analysis.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uoq6E3bAFHA.2104@.TK2MSFTNGP14.phx.gbl...
The fragmentation at the OS level has absolutely nothing to do with
reindexing or defragging within SQL Server. Sql Server doesn't manage the
physical file per say, the OS does. Only during the intial file creation,
growing or shrinking does SQL Server ask the OS to physically adjust the
file in terms of size. And even then Sql Server does not have any control
as to where the file sis placed physically on the disk. Defragmenting with
DBREINDEX or INDEXDEFRAG works solely within the boundaries of the file and
can not cause any change in the physical placement on disk other than
forcing an AutoGrow. It sounds like the db had AutoGrow and AutoShrink (or
a Shrink job) turned on. I am not convinced the copying of the DB even
created contiguous files to start with but unless you checked the OS level
fragmentation immediately after the copies it would be hard to say. If
that was on a SAN you may want to check to be sure there isn't anything
funky with the OS Frag tool and the SAN. But in all the years that I have
worked with 7.0 or 2000 I have only seen fragmentation at the OS level as a
result of growing and shrinking. But unless the OS level fragmentation is
extreme it willnot have as much effect as Page and Extent fragmentation
within the file. A few non-contiguous segments in a data file are nothing to
be concerned with.
--
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OWE2xRbAFHA.1400@.TK2MSFTNGP11.phx.gbl...
> I'm not so sure about the last statement. Last year we had to scale up
> the
> hardware of one of our co-hosted fail over clusters. We ended up
> migrating
> the databases from the old server to the new, one database at a time, on
> clean, freshly installed, shared cluster disks. This should have copied
> the
> files in large contiguous chunks. Within 1 month, with weekly index
> defrags, the OS file fragmentation was over 86%. Now, with minimal 1 to 2
> GB growth per week, roughly 10% increaments, Autogrow, against about 70
> databases, with 48 GB on a 60 GB partition, how could the OS fragmentation
> get so high in so short a time?
> Moreover, if we shut SQL Server down to defrag the disk, which would be a
> very time consuming operation for a highly-available system, what would be
> the point if it would be this fragmented again in such a short time?
> What are do you see as a typical OS disk defragmenting schedule? I've
> always told our OS System Admins to go suck a lemon because SQL Server
> managed the logical data page organization when we defrag our clusterd
> indexes and that we were never garaunteed a contigueous placement of these
> data pages anyway.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u3lCGDbAFHA.2104@.TK2MSFTNGP14.phx.gbl...
> Maybe this will help:
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> Hey, Kalen, while I got you on the line, I realize shrinking the database
>> could, in the end, cause more fragmentation, due to page splits,
> Actually a shrink does not split the pages. It simply moves them (or the
> extents) from the back of the file to the first available space near the
> front. The fragmentation you get from shrinking is that the extents are
> no
> longer contiguous in the file (assuming they were to begin with). If you
> do
> regular enough index maintenance you can assume that most of the pages and
> extents are in the right order. The shrink will break that order at the
> extent level for sure.
> If the database files are created large enough to handle the data for
> quite
> a while to begin with you should avoid the OS level fragmentation. That
> comes from making the file too small and lots of growing and shrinking
> which
> should be avoided when ever possible.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:uN5n7yaAFHA.3616@.TK2MSFTNGP11.phx.gbl...
>> Hey, Kalen, while I got you on the line, I realize shrinking the database
>> could, in the end, cause more fragmentation, due to page splits,
>> especially
>> if the fill factor is to high. However, my OS System Admins constantly
>> pester me about how much SQL Server database files are NTFS fragmented on
>> the disk. I realize that internally, SQL Server manages its own files by
>> page and extent placement and just because the file may be physically
>> sequential, this does not garauntee any page order in the logical
>> sequence.
>> How would I go about explaining this to them that the logical
>> fragementation
>> of data pages and/or index pages is more significant to performance than
>> the
>> contigueousness of the physical file clusters?
>> Thanks,
>> Anthony Thomas
>>
>> --
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u2UrLiPAFHA.1908@.TK2MSFTNGP15.phx.gbl...
>> Be careful with this. First of all, SQL Server 2000 has a DBCC
>> SHRINKDATABASE, not SHRINKDB.
>> There is no specific reason to do a shrink when you're trying to
>> defragment.
>> In fact, shrinking your data files, because it moves all the data to
>> earlier
>> in the file wherever there happens to be room, can actually drastically
>> increase your fragmentation.
>> Shrinking a database is not rercommended, for many reasons. Please see:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:euNveOPAFHA.3264@.TK2MSFTNGP12.phx.gbl...
>> "barryfz" <barryfz@.nospam.nospam> wrote in message
>> news:eW43XeJAFHA.3368@.TK2MSFTNGP15.phx.gbl...
>> If I backup a db and then delete and restore it, is that equivalent to
>> doing
>> a defrag?
>>
>> When you say defrag, what are you referring to?
>> In general, No. SQL Server reads your data Extent by Extent and writes
>> that to the backup.
>> To defrag the data you need to defrag your clustered indexes and then
>> run
>> a
>> DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in the
>> BOL
>> for more information about what they do for you.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>|||I see what you are getting at now. Yes that is mostly true in that if you
are only doing typical OLTP type operations (single or very few row inserts.
updates and deletes) then the fragmentation does not affect you as much. In
a reporting or DW situation it can affect you a great deal. But even with an
OLTP system you have to still be wary of fragmentation that causes page
splits or a situation where you have lots of deletes. In a situation like
that where you do not run regular index maintenance you will end up will
less optimally filled pages and that has a ripple effect. This requires
more disk I/O , more CPU and more memory space than it should and can reduce
the overall effectiveness of any system. So range scans are potentially
affected the most but that does not mean if you are not doing them it won't
be a problem. The white paper I referred you to goes into a lot of detail
in all of this. The author states something to the effect that
fragmentation is not really a problem in a OLTP system. Well I have many
real life clients who would disagree. I have proven time and again that
fragmentation does make a difference in performance in real life OLTP
systems because most are not purely OLTP type operations and range scans are
a part of life. They can never be totally avoided. And the results of page
splits and half empty pages plays a big factor when cache is tight. I think
we are pretty much on the same page though.
--
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eWR2TlcAFHA.3936@.TK2MSFTNGP11.phx.gbl...
> My point exactly. The fragmentation of the indexes/data pages is a
> logical
> construct. The links between pages may be reorganized to produce less
> logical fragmentation of the data pages but SQL Server can do nothing as
> far
> as which physical disk clusters they are assigned, which is the only
> fragmentation the OS would be concerned over.
> However, given that SQL Server has logically sorted a group of clustered
> index pages and you had to traverse those pages sequentially during a
> scan,
> the physical placement of those data pages could be helped by having them
> physically located sequentially but, we are in the business to reduce
> scans
> as often as possible.
> Therefore, my logic is that physical fragmentation could hurt, or the
> converse help, only little unless SQL Server is doing an abundance of
> scanning. However, if you have seen evidence where a DBA should also be
> concerned about physical database file fragmentation, I would be most
> interested in seeing the analysis.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uoq6E3bAFHA.2104@.TK2MSFTNGP14.phx.gbl...
> The fragmentation at the OS level has absolutely nothing to do with
> reindexing or defragging within SQL Server. Sql Server doesn't manage the
> physical file per say, the OS does. Only during the intial file creation,
> growing or shrinking does SQL Server ask the OS to physically adjust the
> file in terms of size. And even then Sql Server does not have any control
> as to where the file sis placed physically on the disk. Defragmenting with
> DBREINDEX or INDEXDEFRAG works solely within the boundaries of the file
> and
> can not cause any change in the physical placement on disk other than
> forcing an AutoGrow. It sounds like the db had AutoGrow and AutoShrink
> (or
> a Shrink job) turned on. I am not convinced the copying of the DB even
> created contiguous files to start with but unless you checked the OS level
> fragmentation immediately after the copies it would be hard to say. If
> that was on a SAN you may want to check to be sure there isn't anything
> funky with the OS Frag tool and the SAN. But in all the years that I have
> worked with 7.0 or 2000 I have only seen fragmentation at the OS level as
> a
> result of growing and shrinking. But unless the OS level fragmentation is
> extreme it willnot have as much effect as Page and Extent fragmentation
> within the file. A few non-contiguous segments in a data file are nothing
> to
> be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OWE2xRbAFHA.1400@.TK2MSFTNGP11.phx.gbl...
>> I'm not so sure about the last statement. Last year we had to scale up
>> the
>> hardware of one of our co-hosted fail over clusters. We ended up
>> migrating
>> the databases from the old server to the new, one database at a time, on
>> clean, freshly installed, shared cluster disks. This should have copied
>> the
>> files in large contiguous chunks. Within 1 month, with weekly index
>> defrags, the OS file fragmentation was over 86%. Now, with minimal 1 to
>> 2
>> GB growth per week, roughly 10% increaments, Autogrow, against about 70
>> databases, with 48 GB on a 60 GB partition, how could the OS
>> fragmentation
>> get so high in so short a time?
>> Moreover, if we shut SQL Server down to defrag the disk, which would be a
>> very time consuming operation for a highly-available system, what would
>> be
>> the point if it would be this fragmented again in such a short time?
>> What are do you see as a typical OS disk defragmenting schedule? I've
>> always told our OS System Admins to go suck a lemon because SQL Server
>> managed the logical data page organization when we defrag our clusterd
>> indexes and that we were never garaunteed a contigueous placement of
>> these
>> data pages anyway.
>> Sincerely,
>>
>> Anthony Thomas
>>
>>
>> --
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:u3lCGDbAFHA.2104@.TK2MSFTNGP14.phx.gbl...
>> Maybe this will help:
>>
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> Hey, Kalen, while I got you on the line, I realize shrinking the
>> database
>> could, in the end, cause more fragmentation, due to page splits,
>> Actually a shrink does not split the pages. It simply moves them (or the
>> extents) from the back of the file to the first available space near the
>> front. The fragmentation you get from shrinking is that the extents are
>> no
>> longer contiguous in the file (assuming they were to begin with). If you
>> do
>> regular enough index maintenance you can assume that most of the pages
>> and
>> extents are in the right order. The shrink will break that order at the
>> extent level for sure.
>> If the database files are created large enough to handle the data for
>> quite
>> a while to begin with you should avoid the OS level fragmentation. That
>> comes from making the file too small and lots of growing and shrinking
>> which
>> should be avoided when ever possible.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
>> news:uN5n7yaAFHA.3616@.TK2MSFTNGP11.phx.gbl...
>> Hey, Kalen, while I got you on the line, I realize shrinking the
>> database
>> could, in the end, cause more fragmentation, due to page splits,
>> especially
>> if the fill factor is to high. However, my OS System Admins constantly
>> pester me about how much SQL Server database files are NTFS fragmented
>> on
>> the disk. I realize that internally, SQL Server manages its own files
>> by
>> page and extent placement and just because the file may be physically
>> sequential, this does not garauntee any page order in the logical
>> sequence.
>> How would I go about explaining this to them that the logical
>> fragementation
>> of data pages and/or index pages is more significant to performance than
>> the
>> contigueousness of the physical file clusters?
>> Thanks,
>> Anthony Thomas
>>
>> --
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u2UrLiPAFHA.1908@.TK2MSFTNGP15.phx.gbl...
>> Be careful with this. First of all, SQL Server 2000 has a DBCC
>> SHRINKDATABASE, not SHRINKDB.
>> There is no specific reason to do a shrink when you're trying to
>> defragment.
>> In fact, shrinking your data files, because it moves all the data to
>> earlier
>> in the file wherever there happens to be room, can actually drastically
>> increase your fragmentation.
>> Shrinking a database is not rercommended, for many reasons. Please see:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:euNveOPAFHA.3264@.TK2MSFTNGP12.phx.gbl...
>> "barryfz" <barryfz@.nospam.nospam> wrote in message
>> news:eW43XeJAFHA.3368@.TK2MSFTNGP15.phx.gbl...
>> If I backup a db and then delete and restore it, is that equivalent to
>> doing
>> a defrag?
>>
>> When you say defrag, what are you referring to?
>> In general, No. SQL Server reads your data Extent by Extent and
>> writes
>> that to the backup.
>> To defrag the data you need to defrag your clustered indexes and then
>> run
>> a
>> DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in the
>> BOL
>> for more information about what they do for you.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>>
>|||I concur. Also, there is NO true OLTP systems. Everyone wants to do SOME
DSS reporting, however minor.
Yes, I'm currently reading the White Paper both you and Kalen pointed me to.
Thanks again for both of you drilling into my questions. And thank you
barryfz for permitting to interject into your thread. I hope you got YOUR
question answered.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23kxX32hAFHA.3404@.TK2MSFTNGP10.phx.gbl...
I see what you are getting at now. Yes that is mostly true in that if you
are only doing typical OLTP type operations (single or very few row inserts.
updates and deletes) then the fragmentation does not affect you as much. In
a reporting or DW situation it can affect you a great deal. But even with an
OLTP system you have to still be wary of fragmentation that causes page
splits or a situation where you have lots of deletes. In a situation like
that where you do not run regular index maintenance you will end up will
less optimally filled pages and that has a ripple effect. This requires
more disk I/O , more CPU and more memory space than it should and can reduce
the overall effectiveness of any system. So range scans are potentially
affected the most but that does not mean if you are not doing them it won't
be a problem. The white paper I referred you to goes into a lot of detail
in all of this. The author states something to the effect that
fragmentation is not really a problem in a OLTP system. Well I have many
real life clients who would disagree. I have proven time and again that
fragmentation does make a difference in performance in real life OLTP
systems because most are not purely OLTP type operations and range scans are
a part of life. They can never be totally avoided. And the results of page
splits and half empty pages plays a big factor when cache is tight. I think
we are pretty much on the same page though.
--
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eWR2TlcAFHA.3936@.TK2MSFTNGP11.phx.gbl...
> My point exactly. The fragmentation of the indexes/data pages is a
> logical
> construct. The links between pages may be reorganized to produce less
> logical fragmentation of the data pages but SQL Server can do nothing as
> far
> as which physical disk clusters they are assigned, which is the only
> fragmentation the OS would be concerned over.
> However, given that SQL Server has logically sorted a group of clustered
> index pages and you had to traverse those pages sequentially during a
> scan,
> the physical placement of those data pages could be helped by having them
> physically located sequentially but, we are in the business to reduce
> scans
> as often as possible.
> Therefore, my logic is that physical fragmentation could hurt, or the
> converse help, only little unless SQL Server is doing an abundance of
> scanning. However, if you have seen evidence where a DBA should also be
> concerned about physical database file fragmentation, I would be most
> interested in seeing the analysis.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uoq6E3bAFHA.2104@.TK2MSFTNGP14.phx.gbl...
> The fragmentation at the OS level has absolutely nothing to do with
> reindexing or defragging within SQL Server. Sql Server doesn't manage the
> physical file per say, the OS does. Only during the intial file creation,
> growing or shrinking does SQL Server ask the OS to physically adjust the
> file in terms of size. And even then Sql Server does not have any control
> as to where the file sis placed physically on the disk. Defragmenting with
> DBREINDEX or INDEXDEFRAG works solely within the boundaries of the file
> and
> can not cause any change in the physical placement on disk other than
> forcing an AutoGrow. It sounds like the db had AutoGrow and AutoShrink
> (or
> a Shrink job) turned on. I am not convinced the copying of the DB even
> created contiguous files to start with but unless you checked the OS level
> fragmentation immediately after the copies it would be hard to say. If
> that was on a SAN you may want to check to be sure there isn't anything
> funky with the OS Frag tool and the SAN. But in all the years that I have
> worked with 7.0 or 2000 I have only seen fragmentation at the OS level as
> a
> result of growing and shrinking. But unless the OS level fragmentation is
> extreme it willnot have as much effect as Page and Extent fragmentation
> within the file. A few non-contiguous segments in a data file are nothing
> to
> be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OWE2xRbAFHA.1400@.TK2MSFTNGP11.phx.gbl...
>> I'm not so sure about the last statement. Last year we had to scale up
>> the
>> hardware of one of our co-hosted fail over clusters. We ended up
>> migrating
>> the databases from the old server to the new, one database at a time, on
>> clean, freshly installed, shared cluster disks. This should have copied
>> the
>> files in large contiguous chunks. Within 1 month, with weekly index
>> defrags, the OS file fragmentation was over 86%. Now, with minimal 1 to
>> 2
>> GB growth per week, roughly 10% increaments, Autogrow, against about 70
>> databases, with 48 GB on a 60 GB partition, how could the OS
>> fragmentation
>> get so high in so short a time?
>> Moreover, if we shut SQL Server down to defrag the disk, which would be a
>> very time consuming operation for a highly-available system, what would
>> be
>> the point if it would be this fragmented again in such a short time?
>> What are do you see as a typical OS disk defragmenting schedule? I've
>> always told our OS System Admins to go suck a lemon because SQL Server
>> managed the logical data page organization when we defrag our clusterd
>> indexes and that we were never garaunteed a contigueous placement of
>> these
>> data pages anyway.
>> Sincerely,
>>
>> Anthony Thomas
>>
>>
>> --
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:u3lCGDbAFHA.2104@.TK2MSFTNGP14.phx.gbl...
>> Maybe this will help:
>>
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> Hey, Kalen, while I got you on the line, I realize shrinking the
>> database
>> could, in the end, cause more fragmentation, due to page splits,
>> Actually a shrink does not split the pages. It simply moves them (or the
>> extents) from the back of the file to the first available space near the
>> front. The fragmentation you get from shrinking is that the extents are
>> no
>> longer contiguous in the file (assuming they were to begin with). If you
>> do
>> regular enough index maintenance you can assume that most of the pages
>> and
>> extents are in the right order. The shrink will break that order at the
>> extent level for sure.
>> If the database files are created large enough to handle the data for
>> quite
>> a while to begin with you should avoid the OS level fragmentation. That
>> comes from making the file too small and lots of growing and shrinking
>> which
>> should be avoided when ever possible.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
>> news:uN5n7yaAFHA.3616@.TK2MSFTNGP11.phx.gbl...
>> Hey, Kalen, while I got you on the line, I realize shrinking the
>> database
>> could, in the end, cause more fragmentation, due to page splits,
>> especially
>> if the fill factor is to high. However, my OS System Admins constantly
>> pester me about how much SQL Server database files are NTFS fragmented
>> on
>> the disk. I realize that internally, SQL Server manages its own files
>> by
>> page and extent placement and just because the file may be physically
>> sequential, this does not garauntee any page order in the logical
>> sequence.
>> How would I go about explaining this to them that the logical
>> fragementation
>> of data pages and/or index pages is more significant to performance than
>> the
>> contigueousness of the physical file clusters?
>> Thanks,
>> Anthony Thomas
>>
>> --
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u2UrLiPAFHA.1908@.TK2MSFTNGP15.phx.gbl...
>> Be careful with this. First of all, SQL Server 2000 has a DBCC
>> SHRINKDATABASE, not SHRINKDB.
>> There is no specific reason to do a shrink when you're trying to
>> defragment.
>> In fact, shrinking your data files, because it moves all the data to
>> earlier
>> in the file wherever there happens to be room, can actually drastically
>> increase your fragmentation.
>> Shrinking a database is not rercommended, for many reasons. Please see:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:euNveOPAFHA.3264@.TK2MSFTNGP12.phx.gbl...
>> "barryfz" <barryfz@.nospam.nospam> wrote in message
>> news:eW43XeJAFHA.3368@.TK2MSFTNGP15.phx.gbl...
>> If I backup a db and then delete and restore it, is that equivalent to
>> doing
>> a defrag?
>>
>> When you say defrag, what are you referring to?
>> In general, No. SQL Server reads your data Extent by Extent and
>> writes
>> that to the backup.
>> To defrag the data you need to defrag your clustered indexes and then
>> run
>> a
>> DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in the
>> BOL
>> for more information about what they do for you.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>>
>

No comments:

Post a Comment