Logical Scan fragmentation ?

Can someone explain this statement ?
"An out of order page is one for which the next page indicated in an IAM is
a different page than the page pointed to by the next page pointer in the
leaf page."
Does this mean that DBCC checktable may result in some allocation errors ?Hi Hassan
No, this is not an error. The IAM indicates the next physical page and the
pointers indicate the next logical page. It just means that the a page is
out of order. :-)
So let's say your table is on page 82, 86 and 95. The IAM would indicate
them in that physical order. But it may be that the first rows in the index
are on page 95 (for example, valus A-G) then the next logical values are on
page 86 (values H - S) and the next are on page 82 (values T - Z). When you
are on page 86, the pointers will say that the next page is 82, but the IAM
will say that the next page is 95. This is just fragmentation, not an
error.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:egCGeILnDHA.1708@.TK2MSFTNGP12.phx.gbl...
> Can someone explain this statement ?
> "An out of order page is one for which the next page indicated in an IAM
is
> a different page than the page pointed to by the next page pointer in the
> leaf page."
> Does this mean that DBCC checktable may result in some allocation errors ?
>|||Read the whitepaper at:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which should explain everything for you.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:egCGeILnDHA.1708@.TK2MSFTNGP12.phx.gbl...
> Can someone explain this statement ?
> "An out of order page is one for which the next page indicated in an IAM
is
> a different page than the page pointed to by the next page pointer in the
> leaf page."
> Does this mean that DBCC checktable may result in some allocation errors ?
>

Logical fragmentation

Hi:

when does a table not show any Logical fragmentation?

1). Is it possible if the table has only non-clustered indexes and no clustered indexes, it wont show any logical fragmentation at all or shows less logical fragmentation?.

Also what is the difference between logical fragmentation and Physical fragmentation of a index?.Is there actually something like a physical fragmentation for a index?

Thanks

AK

An example repro T-SQL Script would be helpful. Experts please comment.|||

Logical fragmentation only applies to indexes (clustered or non-clustered). If the physical location of key orders from leaf level of an index is the same as physcial page order in the database files, then logical fragmentation is 0. You can find more information in Book Online from DBCC SHOWCONTIG in SQL Server 2000/2005 and sys.dm_db_index_physcial_stats in SQL Server 2005.

To answer your first question, if a table does not have clustered index (it is heap), then SQL Server cannot do a order scan on the data. Heap will have 0 logical fragmentation; but your query performance may not be good. Logical fragmentation is independent from different indexes/heaps. You may see logical fragmentation from non-clustered indexes.

From SQL Server point of view, it has no control of physcial fragmentation (aka file fragmentation for a database file). The physical fragmentation is from OS when allocating space for database files. It does not know if an index has physcial fragmentation.

I suggest that you read the following white paper ("Microsoft SQL Server 2000 Index Defragmentation Best Practices") to gain more ideas about logical fragmentation and file fragmentation: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Thanks

Sherry

Logical Disk Fragmentation on Database Drive

We are using Disk Keeper Server from Executive Software version 7.0.410.0.
I am trying to minimize the logical fragmentation on the drive where our
database (.MDF) is located.
The problem I am having is that I do not have the drive space to move
the .MDF to another drive in order to defragment.
I have tried to stop the MSSQLSERVER and SQLSERVERAGENT services and
then attempt to defrag without moving the file and this has not been
successful.
I thought I may be doing something wrong with Diskeeper or SQL 2000.
Any thoughts or ideas would be very helpful.
TIA,
-Ben
Ben wrote:
> We are using Disk Keeper Server from Executive Software version
> 7.0.410.0.
> I am trying to minimize the logical fragmentation on the drive where
> our database (.MDF) is located.
> The problem I am having is that I do not have the drive space to move
> the .MDF to another drive in order to defragment.
> I have tried to stop the MSSQLSERVER and SQLSERVERAGENT services and
> then attempt to defrag without moving the file and this has not been
> successful.
> I thought I may be doing something wrong with Diskeeper or SQL 2000.
> Any thoughts or ideas would be very helpful.
> TIA,
> -Ben
Diskeeper requires a good amount of free drive space to best defrag
files. Since the MDF file you are defragging is large (I assume) and
there is little drive space left, Diskeeper appears to be having
trouble. How much free space do you have and how much space is there in
total? Do you know for a fact that the MDF file is heavily fragmented?
You could try shrinking the file using DBCC SHRINKFILE.
Run sp_spaceused on the database in question and see if there is
unallocated space. If so, shrink the file size, defrag, and then expand
the database again from SQL EM before returning the database to
production.
David G.

Logical Disk Fragmentation on Database Drive

We are using Disk Keeper Server from Executive Software version 7.0.410.0.
I am trying to minimize the logical fragmentation on the drive where our
database (.MDF) is located.
The problem I am having is that I do not have the drive space to move
the .MDF to another drive in order to defragment.
I have tried to stop the MSSQLSERVER and SQLSERVERAGENT services and
then attempt to defrag without moving the file and this has not been
successful.
I thought I may be doing something wrong with Diskeeper or SQL 2000.
Any thoughts or ideas would be very helpful.
TIA,
-BenBen wrote:
> We are using Disk Keeper Server from Executive Software version
> 7.0.410.0.
> I am trying to minimize the logical fragmentation on the drive where
> our database (.MDF) is located.
> The problem I am having is that I do not have the drive space to move
> the .MDF to another drive in order to defragment.
> I have tried to stop the MSSQLSERVER and SQLSERVERAGENT services and
> then attempt to defrag without moving the file and this has not been
> successful.
> I thought I may be doing something wrong with Diskeeper or SQL 2000.
> Any thoughts or ideas would be very helpful.
> TIA,
> -Ben
Diskeeper requires a good amount of free drive space to best defrag
files. Since the MDF file you are defragging is large (I assume) and
there is little drive space left, Diskeeper appears to be having
trouble. How much free space do you have and how much space is there in
total? Do you know for a fact that the MDF file is heavily fragmented?
You could try shrinking the file using DBCC SHRINKFILE.
Run sp_spaceused on the database in question and see if there is
unallocated space. If so, shrink the file size, defrag, and then expand
the database again from SQL EM before returning the database to
production.
David G.

Logical Disk Fragmentation on Database Drive

We are using Disk Keeper Server from Executive Software version 7.0.410.0.
I am trying to minimize the logical fragmentation on the drive where our
database (.MDF) is located.
The problem I am having is that I do not have the drive space to move
the .MDF to another drive in order to defragment.
I have tried to stop the MSSQLSERVER and SQLSERVERAGENT services and
then attempt to defrag without moving the file and this has not been
successful.
I thought I may be doing something wrong with Diskeeper or SQL 2000.
Any thoughts or ideas would be very helpful.
TIA,
-BenBen wrote:
> We are using Disk Keeper Server from Executive Software version
> 7.0.410.0.
> I am trying to minimize the logical fragmentation on the drive where
> our database (.MDF) is located.
> The problem I am having is that I do not have the drive space to move
> the .MDF to another drive in order to defragment.
> I have tried to stop the MSSQLSERVER and SQLSERVERAGENT services and
> then attempt to defrag without moving the file and this has not been
> successful.
> I thought I may be doing something wrong with Diskeeper or SQL 2000.
> Any thoughts or ideas would be very helpful.
> TIA,
> -Ben
Diskeeper requires a good amount of free drive space to best defrag
files. Since the MDF file you are defragging is large (I assume) and
there is little drive space left, Diskeeper appears to be having
trouble. How much free space do you have and how much space is there in
total? Do you know for a fact that the MDF file is heavily fragmented?
You could try shrinking the file using DBCC SHRINKFILE.
Run sp_spaceused on the database in question and see if there is
unallocated space. If so, shrink the file size, defrag, and then expand
the database again from SQL EM before returning the database to
production.
David G.

Logical

I would like to re-index two tables (t1 and t2) if the logical scan
fragmentation is greater 80% on these tables.
Please help me create a t-sql script to create this task.
Thank You,Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D219E592-9000-49FB-AFE1-B109678802AC@.microsoft.com...
> I would like to re-index two tables (t1 and t2) if the logical scan
> fragmentation is greater 80% on these tables.
> Please help me create a t-sql script to create this task.
> Thank You,