November 7, 2003 at 7:13 am
One thing I have been puzzling at for a while is the easiest way to defragment the data pages of a table which does not have a clustered index (i.e. a HEAP)
Using DBCC SHOWCONTIG and looking for an IndexId of 0 you can identify candidates for table defragmentation, but DBCC DBREINDEX and DBCC INDEXDEFRAG only seem to degrament index pages not data pages.
Defragmentation of the "data" pages on from a table with a clustered index can be achieved by defragmenting the clustered index itself. But tables without a clustered index this does not seem to be possible.
Unfortunately exporting/importing the data is not really an option (ref integrity issues, performance, availability etc).
Am I missing an easy option?
P.S. I don't want to simply put a clustered index on every table just to get around this problem
November 7, 2003 at 9:48 am
Like you said, you can create and drop a clustered index by which the data pages will be defraged. So you don't need to have an clustered index and by this you rebuilt the nonclustered indexes as well. This is one option i know of though it sounds not so good.
November 11, 2003 at 3:15 pm
I'd create a clustered index. I hear over and over that the query optimizer likes this. Even if it isn't meaningful, I think having one helps.
That being said, create and drop it if you really don't want it.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 11, 2003 at 8:11 pm
Are you telling that a table not having a clustered index is one of the WORST PRACTICES ???
Linto
November 12, 2003 at 1:35 am
Surely if I create the clusterd index - defrag it, then drop it, the data will first off all be copied to a bunch of index pages (in the clustered index) then back into a bunch of data pages (in the HEAP). Even if the index pages are defragmented there is no guarrentee that the HEAP will end up defragmented?
Is there any other way other than this workaround?
P.S. These are tables storing 10's of GB of data (often more), hence the reason copying the data around isn't something I am so reluctant.
November 12, 2003 at 1:59 am
Surely if I create the clusterd index - defrag it, then drop it, the data will first off all be copied to a bunch of index pages (in the clustered index) then back into a bunch of data pages (in the HEAP). Even if the index pages are defragmented there is no guarrentee that the HEAP will end up defragmented?
Is there any other way other than this workaround?
P.S. These are tables storing 10's of GB of data (often more), hence the reason copying the data around isn't something I am so reluctant.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply