Re: sorted_data_reorg

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ron Talmage (rtalmage_at_prospice.com)
Date: 09/22/04 

Date: Tue, 21 Sep 2004 22:51:52 -0700

Neil,

You need a clustered index to defragment the data pages initially. After a
period of time, you may see some fragmentation if updates have caused SQL
Server to split pages.

Ron 

-- 
Ron Talmage
SQL Server MVP
"Neil K" <NeilK@discussions.microsoft.com> wrote in message
news:AE6B8B2B-7F9E-4C72-B3BF-B0BA11B66B78@microsoft.com...
> I have been working with a fairly simple (no clustered indexs) but large
SQL
> 2000 Table. I have "selected into" to copy the table, recreated the
indexes
> etc.. on the new table, dropped the original and renamed the copy to the
same
> as original. This has all worked fine, however I notice that the table has
> now shrunk to almost half its original size even tho it now has more
columns
> (of which some are not null with default).
>
> Does this mean the original data pages were heavily fragmented ? Running
> ShowContig on the original table gave
>
> Scan Density [Best Count:Actual Count]:  99.98% [302443:302489]
> Extent Fragmentation:   64%.
> Avg. Page Density (full):    85.22%
>
> Any thoughts ? How can i defrag the Data Pages as oppose to the Index
Pages ?
Quantcast