Index defrag and Data defrag

Hi,
I have recently started delving into fragmentation in sql server. I have
some basic/conceptual level queries regarding the same -
1) Is index fragmentation and data fragmentation same/different in case of -
- table with clustered index
- table with no clustered index
2) Is there a separate process for data defragmentation? If yes, then to
which type of table does it apply to - with or without clustered index or any
other type?
3) Will running DBCC DBREINDEX for any kind of index (clustered and
non-clustered) defrag the data pages as well?
Please excuse me if any question seems completely stupid.
Thank you.
Regards,
Salil.Salil
Start looking at DBCC SHOWCONTIG,DBCC INDEXDEFRAG in the BOL.
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
> Please excuse me if any question seems completely stupid.
> Thank you.
> Regards,
> Salil.
>|||Hi Uri,
I have asked these queries only after not finding anything related to data
fragmentation in BOL. I've already gone through showcontig, indexdefrag and
dbreindex help provided in BOL.
Everything seems to be talking only about index defragmentation.
Salil.
"Uri Dimant" wrote:
> Salil
> Start looking at DBCC SHOWCONTIG,DBCC INDEXDEFRAG in the BOL.
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> > Hi,
> >
> > I have recently started delving into fragmentation in sql server. I have
> > some basic/conceptual level queries regarding the same -
> >
> > 1) Is index fragmentation and data fragmentation same/different in case
> of -
> > - table with clustered index
> > - table with no clustered index
> >
> > 2) Is there a separate process for data defragmentation? If yes, then to
> > which type of table does it apply to - with or without clustered index or
> any
> > other type?
> >
> > 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> > non-clustered) defrag the data pages as well?
> >
> > Please excuse me if any question seems completely stupid.
> >
> > Thank you.
> > Regards,
> > Salil.
> >
> >
>
>|||Salil
http://www.sql-server-performance.com/rd_index_fragmentation.asp
--
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:39B22717-9962-4252-ADD9-F44188DC99E2@.microsoft.com...
> Hi Uri,
> I have asked these queries only after not finding anything related to data
> fragmentation in BOL. I've already gone through showcontig, indexdefrag
and
> dbreindex help provided in BOL.
> Everything seems to be talking only about index defragmentation.
> Salil.
> "Uri Dimant" wrote:
> > Salil
> > Start looking at DBCC SHOWCONTIG,DBCC INDEXDEFRAG in the BOL.
> >
> > "Salil" <Salil@.discussions.microsoft.com> wrote in message
> > news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> > > Hi,
> > >
> > > I have recently started delving into fragmentation in sql server. I
have
> > > some basic/conceptual level queries regarding the same -
> > >
> > > 1) Is index fragmentation and data fragmentation same/different in
case
> > of -
> > > - table with clustered index
> > > - table with no clustered index
> > >
> > > 2) Is there a separate process for data defragmentation? If yes, then
to
> > > which type of table does it apply to - with or without clustered index
or
> > any
> > > other type?
> > >
> > > 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> > > non-clustered) defrag the data pages as well?
> > >
> > > Please excuse me if any question seems completely stupid.
> > >
> > > Thank you.
> > > Regards,
> > > Salil.
> > >
> > >
> >
> >
> >|||Thank you Uri.
I have already gone through these links before making this post.
My confusion still remains.
Salil.
"Uri Dimant" wrote:
> Salil
> http://www.sql-server-performance.com/rd_index_fragmentation.asp
> --
> http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
>
>
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:39B22717-9962-4252-ADD9-F44188DC99E2@.microsoft.com...
> > Hi Uri,
> >
> > I have asked these queries only after not finding anything related to data
> > fragmentation in BOL. I've already gone through showcontig, indexdefrag
> and
> > dbreindex help provided in BOL.
> >
> > Everything seems to be talking only about index defragmentation.
> >
> > Salil.
> >
> > "Uri Dimant" wrote:
> >
> > > Salil
> > > Start looking at DBCC SHOWCONTIG,DBCC INDEXDEFRAG in the BOL.
> > >
> > > "Salil" <Salil@.discussions.microsoft.com> wrote in message
> > > news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have recently started delving into fragmentation in sql server. I
> have
> > > > some basic/conceptual level queries regarding the same -
> > > >
> > > > 1) Is index fragmentation and data fragmentation same/different in
> case
> > > of -
> > > > - table with clustered index
> > > > - table with no clustered index
> > > >
> > > > 2) Is there a separate process for data defragmentation? If yes, then
> to
> > > > which type of table does it apply to - with or without clustered index
> or
> > > any
> > > > other type?
> > > >
> > > > 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> > > > non-clustered) defrag the data pages as well?
> > > >
> > > > Please excuse me if any question seems completely stupid.
> > > >
> > > > Thank you.
> > > > Regards,
> > > > Salil.
> > > >
> > > >
> > >
> > >
> > >
>
>|||See inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
>
Index fragmentation is index fragmentation regardless of the storage of the
data in the table.
If the table has a clustered index, it falls under normal index
fragmentation... However be aware that shrinking the database fragments the
indexes, the worst case being the clustered index.
Heaps ( tables with no clustered index) contain unordered data, so logical
fragmentation is not a problem.
When one speaks of fragmentation there is internal fragmentation ( how full
each page is.) and external fragmentation (how closely the liniked list
(index) order is to the physical order of the pages on the disk)...
The OTHER kind of fragmentation is the kind that Diskkeeper fixes, moving
all of the parts of a physical file into a single contiguous disk segment...
You can ( and most folks do) have a situation where this kind of
fragmentation does not exist, yet within the file, the rows for a table or
index are all around in the physical file ( the previous kind of
fragmentation above.)
Index defrag and dbcc dbreindex fix the first kind of fragmentation NOT the
same thing that diskkeeper works on.
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
Only indexdefrag and dbreindex, for clustered indexes. Some folks create
then drop a clustered index on a heap to clean it up...(This works also.)
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
The data pages ARE the leaf level of a clustered index, so defragging a
clustered index fixes the data pages. Since there is no ordering on a heap
table, creating then dropping a clustered index on a heap, fixes up the data
pages there as well..
> Please excuse me if any question seems completely stupid.
None of your questions are stupid... It is good that you are smart enough to
ask the questions, and know that there are issues you need to understand.
and this is the perfect place to do just that...
> Thank you.
> Regards,
> Salil.
>|||Also take a look at this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
> Please excuse me if any question seems completely stupid.
> Thank you.
> Regards,
> Salil.
>|||Thank you Wayne. The replies helped a lot !
I think I can conclude that running DBREINDEX on clustered indexes with
logical OR extent fragmentation will reduce both kinds of fragmentation.
One last query for now -
Since heaps do not have clustered indexes, will there be any decrease in
logical or extent fragmentation by simply running DBREINDEX WITHOUT creating
and dropping a clustered index ?
Salil.
"Wayne Snyder" wrote:
> See inline
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> > Hi,
> >
> > I have recently started delving into fragmentation in sql server. I have
> > some basic/conceptual level queries regarding the same -
> >
> > 1) Is index fragmentation and data fragmentation same/different in case
> of -
> > - table with clustered index
> > - table with no clustered index
> >
> Index fragmentation is index fragmentation regardless of the storage of the
> data in the table.
> If the table has a clustered index, it falls under normal index
> fragmentation... However be aware that shrinking the database fragments the
> indexes, the worst case being the clustered index.
> Heaps ( tables with no clustered index) contain unordered data, so logical
> fragmentation is not a problem.
> When one speaks of fragmentation there is internal fragmentation ( how full
> each page is.) and external fragmentation (how closely the liniked list
> (index) order is to the physical order of the pages on the disk)...
> The OTHER kind of fragmentation is the kind that Diskkeeper fixes, moving
> all of the parts of a physical file into a single contiguous disk segment...
> You can ( and most folks do) have a situation where this kind of
> fragmentation does not exist, yet within the file, the rows for a table or
> index are all around in the physical file ( the previous kind of
> fragmentation above.)
> Index defrag and dbcc dbreindex fix the first kind of fragmentation NOT the
> same thing that diskkeeper works on.
>
> > 2) Is there a separate process for data defragmentation? If yes, then to
> > which type of table does it apply to - with or without clustered index or
> any
> > other type?
> Only indexdefrag and dbreindex, for clustered indexes. Some folks create
> then drop a clustered index on a heap to clean it up...(This works also.)
> >
> > 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> > non-clustered) defrag the data pages as well?
> The data pages ARE the leaf level of a clustered index, so defragging a
> clustered index fixes the data pages. Since there is no ordering on a heap
> table, creating then dropping a clustered index on a heap, fixes up the data
> pages there as well..
> >
> > Please excuse me if any question seems completely stupid.
> None of your questions are stupid... It is good that you are smart enough to
> ask the questions, and know that there are issues you need to understand.
> and this is the perfect place to do just that...
> >
> > Thank you.
> > Regards,
> > Salil.
> >
> >
>
>|||Thank you Andrew. I have gone through this link earlier.
In fact I had a query regarding this too -
In this article, it is mentioned that logical fragmentation holds no meaning
for heaps (IND ID = 0). Wayne also mentioned the same thing. And as per this
article, Wayne and other articles it seems that the only way to defrag these
would be to create and then drop a clustered index on it.
As per BOL, IND ID = 1 is a clustered index. So I'll obviously have to run
the DBREINDEX process for these indexes.
Will running a DBREINDEX against IND ID = 2,3,4...(not in 0, 255) (i.e.
non-clustered indexes) help in reducing fragmentation?
Salil.
"Andrew J. Kelly" wrote:
> Also take a look at this:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Andrew J. Kelly SQL MVP
>
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> > Hi,
> >
> > I have recently started delving into fragmentation in sql server. I have
> > some basic/conceptual level queries regarding the same -
> >
> > 1) Is index fragmentation and data fragmentation same/different in case
> of -
> > - table with clustered index
> > - table with no clustered index
> >
> > 2) Is there a separate process for data defragmentation? If yes, then to
> > which type of table does it apply to - with or without clustered index or
> any
> > other type?
> >
> > 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> > non-clustered) defrag the data pages as well?
> >
> > Please excuse me if any question seems completely stupid.
> >
> > Thank you.
> > Regards,
> > Salil.
> >
> >
>
>|||> Since heaps do not have clustered indexes, will there be any decrease in
> logical or extent fragmentation by simply running DBREINDEX WITHOUT creating
> and dropping a clustered index ?
No, If a table doesn't have a clustered index, the rows are not stored in any particular physical order. This
is why we call such tables "heap".
Defragging or rebuilding non-clustered indexes for such tables does not move or touch the actual data pages. A
non-clustered index are only "pointers" to the data pages, after all...
Also, you need to think about what you mean when you consider data pages fragmented for a heap table. The rows
are not stored in any physical order. So, how can they become un-ordered? Sure, you can have free space on
pages, etc, but they cannot become un-ordered.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:1D9C65FE-CF51-4AD5-AC6E-25CCFC2C564B@.microsoft.com...
> Thank you Wayne. The replies helped a lot !
> I think I can conclude that running DBREINDEX on clustered indexes with
> logical OR extent fragmentation will reduce both kinds of fragmentation.
> One last query for now -
> Since heaps do not have clustered indexes, will there be any decrease in
> logical or extent fragmentation by simply running DBREINDEX WITHOUT creating
> and dropping a clustered index ?
> Salil.
> "Wayne Snyder" wrote:
> > See inline
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Salil" <Salil@.discussions.microsoft.com> wrote in message
> > news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> > > Hi,
> > >
> > > I have recently started delving into fragmentation in sql server. I have
> > > some basic/conceptual level queries regarding the same -
> > >
> > > 1) Is index fragmentation and data fragmentation same/different in case
> > of -
> > > - table with clustered index
> > > - table with no clustered index
> > >
> > Index fragmentation is index fragmentation regardless of the storage of the
> > data in the table.
> > If the table has a clustered index, it falls under normal index
> > fragmentation... However be aware that shrinking the database fragments the
> > indexes, the worst case being the clustered index.
> >
> > Heaps ( tables with no clustered index) contain unordered data, so logical
> > fragmentation is not a problem.
> >
> > When one speaks of fragmentation there is internal fragmentation ( how full
> > each page is.) and external fragmentation (how closely the liniked list
> > (index) order is to the physical order of the pages on the disk)...
> >
> > The OTHER kind of fragmentation is the kind that Diskkeeper fixes, moving
> > all of the parts of a physical file into a single contiguous disk segment...
> > You can ( and most folks do) have a situation where this kind of
> > fragmentation does not exist, yet within the file, the rows for a table or
> > index are all around in the physical file ( the previous kind of
> > fragmentation above.)
> > Index defrag and dbcc dbreindex fix the first kind of fragmentation NOT the
> > same thing that diskkeeper works on.
> >
> >
> > > 2) Is there a separate process for data defragmentation? If yes, then to
> > > which type of table does it apply to - with or without clustered index or
> > any
> > > other type?
> > Only indexdefrag and dbreindex, for clustered indexes. Some folks create
> > then drop a clustered index on a heap to clean it up...(This works also.)
> >
> > >
> > > 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> > > non-clustered) defrag the data pages as well?
> > The data pages ARE the leaf level of a clustered index, so defragging a
> > clustered index fixes the data pages. Since there is no ordering on a heap
> > table, creating then dropping a clustered index on a heap, fixes up the data
> > pages there as well..
> >
> > >
> > > Please excuse me if any question seems completely stupid.
> >
> > None of your questions are stupid... It is good that you are smart enough to
> > ask the questions, and know that there are issues you need to understand.
> > and this is the perfect place to do just that...
> > >
> > > Thank you.
> > > Regards,
> > > Salil.
> > >
> > >
> >
> >
> >|||> Will running a DBREINDEX against IND ID = 2,3,4...(not in 0, 255) (i.e.
> non-clustered indexes) help in reducing fragmentation?
Yes, it will reduce fragmentation for those indexes. It will not reduce fragmentation for the data pages per
se. See my other post for more info.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:60C9EA5F-152B-4BB7-B433-4D0196BE08CD@.microsoft.com...
> Thank you Andrew. I have gone through this link earlier.
> In fact I had a query regarding this too -
> In this article, it is mentioned that logical fragmentation holds no meaning
> for heaps (IND ID = 0). Wayne also mentioned the same thing. And as per this
> article, Wayne and other articles it seems that the only way to defrag these
> would be to create and then drop a clustered index on it.
> As per BOL, IND ID = 1 is a clustered index. So I'll obviously have to run
> the DBREINDEX process for these indexes.
> Will running a DBREINDEX against IND ID = 2,3,4...(not in 0, 255) (i.e.
> non-clustered indexes) help in reducing fragmentation?
> Salil.
> "Andrew J. Kelly" wrote:
> > Also take a look at this:
> >
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Salil" <Salil@.discussions.microsoft.com> wrote in message
> > news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> > > Hi,
> > >
> > > I have recently started delving into fragmentation in sql server. I have
> > > some basic/conceptual level queries regarding the same -
> > >
> > > 1) Is index fragmentation and data fragmentation same/different in case
> > of -
> > > - table with clustered index
> > > - table with no clustered index
> > >
> > > 2) Is there a separate process for data defragmentation? If yes, then to
> > > which type of table does it apply to - with or without clustered index or
> > any
> > > other type?
> > >
> > > 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> > > non-clustered) defrag the data pages as well?
> > >
> > > Please excuse me if any question seems completely stupid.
> > >
> > > Thank you.
> > > Regards,
> > > Salil.
> > >
> > >
> >
> >
> >|||Thank you all for your responses specially Wayne and Tibor.
I have answers to all queries I had...:)
Salil.
"Salil" wrote:
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case of -
> - table with clustered index
> - table with no clustered index
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or any
> other type?
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
> Please excuse me if any question seems completely stupid.
> Thank you.
> Regards,
> Salil.
>