Hi Luanne,
There are actually 3 types of fragmentation that can occur.
1) Fragmentation within the pages allocated for an object.
This type of fragmentation is caused by normal update activity. Data
gets out of sequence within the pages due to page splits. Defraging or
Rebuilding the indexes from time to time will solve this type of
fragmentation.
2) Fragmentation of object pages within the filegroup allocated for a
database.
This type of fragmentation is caused by normal update activity. As
tables and indexes grow within a SQL database they grab more pages
within the filegroup. This results in interleaving of pages for various
tables and indexes. Rebuilding the indexes from time to time will solve
this type of fragmentation. Note that a Defrag will only reorder the
pages within the current allocated pages and does nothing to alleviate
this type of fragmentation.
3) Fragmentation of the OS files that are allocated for the file groups of
a database.
This type of fragmentation is cause by the OS file for the database
filegroup growing. Each time the filegroup grows it adds another extent
on to the OS file. That new extent is most likely going to reside on
another section of disk. This type of fragmentation can be solved by
stopping the SQL Server Database Engine and defraging the hard drive or
by backing up and restoring the database.
With these fragmentation types in mind, there are 2 comments that should be
made: 1) It is a very good idea to have a clustered index on every table
in your database. This allows the data pages to be reorganized when the
clustered index is rebuilt, but it also has another benefit. When a page
split occurs, none of the secondary indexes need to be updated with the new
locations of the rows that were moved as all secondary indexes use the
logical key of the clustered index. & 2) It is a very bad idea to shrink
your database or database files on a regular bases as this causes OS file
fragmentation and poor performance.
Thanks, Mark
Mark Weeldreyer
SQL Server Database Administration
USBank
2751 Shepard Road - EP-MN-BRE
Saint Paul, MN 55116-3061
mark.[Email address protected]
"Lindeman Luanne
"
<mssqldba-ezmlmsh To
ield-x98637086.x1 "LazyDBA Discussion"
[Email Address Removed] <[Email address protected]
.com> cc
02/27/2007 10:38 Subject
AM Why doesn't dbreindex or
indexdefrag change results in
showcontig?
Hi,
When I runn DBCC SHOWCONTIG on a table with no indexes, I get
DBCC SHOWCONTIG scanning 'AssocLocation' table...
Table: 'AssocLocation' (226815870); index ID: 0, database ID: 17
TABLE level scan performed.
- Pages Scanned................................: 5
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 1.7
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 3128.6
- Avg. Page Density (full).....................: 61.35%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Should I be worried that the scan density is only 33%? And how do I
correct that if there is no index?
Also, when I run DBCC SHOWCONTIG on a table that has indexes - sometimes
after running DBCC DBREINDEX or DBCC INDEXDEFRAG the scan density doesn't
change. Does anyone understand why in some cases those two statements
don't seem to change anything? It is my understanding that scan density
should be near 100% and extent scan fragmentation should be close to 0 - is
that thinking correct?
Thanks everyone!
Luanne
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
------------------------------------------------------------------------------
Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation.
==============================================================================
MS Sql Server LazyDBA home page