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...
> doing
> 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...
> doing
> 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/pro.../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...
>
|||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/pro.../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...
>
|||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/pro.../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...
>
|||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/pro.../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[vbcol=seagreen]
> 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...
writes[vbcol=seagreen]
run[vbcol=seagreen]
BOL
>
|||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/pro.../ss2kidbp.mspx
>
> 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...
>
|||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/pro.../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...
>