DEFRAG All Tables in a DB

Question

Post reply

DEFRAG All Tables in a DB

manus4u

Ten Centuries

Points: 1238
More actions
August 24, 2008 at 12:40 pm

#71784

Can someone provide me a script to defrag all tables in a db?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110206 More actions · Answer 1

You cannot defrag tables. You can defrag only indexes.

To be defragmented table must have clustered index first.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 2

Sergiy is correct... the "table" can't be defragged without a clustered index.

Now, with that in mind, you will, unfortunately, need a loop of some sort to get throgh all of the tables and indexes in the database. AND, there's no sense in trying to defrag something that doesn't need it. So, rather than me regurgitating what's in Books Online, lookup DBCC ShowContig and take a look at example "E" near the bottom of the "article". If you change the cursors to "Read Only, Forward Only", they'll be as "good" as a Temp table with a While loop. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

By the way... DBCC IndexDefrag will allow the indexes to still be used while the index is in the process of defragging where DBCC DBReIndex will not. The big difference is that DBCC DBReIndex will move pages whereas DBCC IndexDefrag only moves the data within existing pages. In other words, DBCC DBReIndex supposedly resolves the problem of "interleaved" extents (extents that have index data from more than one table) where DBCC IndexDefrag supposedly will not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

manus4u Ten Centuries Points: 1238 More actions · Answer 4

Well, thats was lotsa info that i got...Thanks a million for all those..

I was trying to defrag the indexes only, from all tables in a DB..

But i think i typed it wrong..

Thanks again !!

RP_DBA SSCertifiable Points: 5116 More actions · Answer 5

If you're interested, here's a script we run over the weekend (downtime) to identify and defrag indexes. It uses extent switching and logical scan fragmentation as the defrag indicators.

Comments are welcome.

set nocount on

declare@tbl_namevarchar(100)

, @ctrint

, @max-2int

, @indx_namevarchar(200)

, @db_namevarchar(100)

select @db_name = DB_Name(DB_ID())

CREATE TABLE #fraglist (

ObjectName CHAR (255),

ObjectId INT,

IndexName CHAR (255),

IndexId INT,

Lvl INT,

CountPages INT,

CountRows INT,

MinRecSize INT,

MaxRecSize INT,

AvgRecSize INT,

ForRecCount INT,

Extents INT,

ExtentSwitches INT,

AvgFreeBytes INT,

AvgPageDensity INT,

ScanDensity DECIMAL,

BestCount INT,

ActualCount INT,

LogicalFrag DECIMAL,

ExtentFrag DECIMAL)

declare @tbls table (

tbl_id int not null

, tbl_name varchar(200) not null

, indx_name varchar(200) not null

, [fillfactor] smallint

, defrag tinyint not null default 0

, test_ExtentSwitches tinyint null

, test_LogicalFrag tinyint null

, test_PageDensity tinyint null

, Pages int null

, Extents int null

, ExtentSwitches int null

, ScanDensity decimal null

, LogicalFrag decimal null

, AvgPageDensity int null )

-- id tbls for potential defrag, ignore heap tbls

insert into @tbls (

tbl_id

, tbl_name

, indx_name

, [fillfactor] )

selectso.id

, '[' + so.name + ']'

, si.name

, si.origfillfactor

fromdbo.sysobjects so

inner join dbo.sysindexes si on so.id = si.id and si.indid = 1 -- clustered index

whereso.xtype = 'U'

andso.uid = user_id('dbo')

-- define limits for loop

select@ctr = min(tbl_id)

, @max-2 = max(tbl_id)

from@tbls

-- loop through tbls to update statistics and determine fragmentation

while @ctr <= @max-2

begin

select@tbl_name = tbl_name

from@tbls

wheretbl_id = @ctr

exec ('update statistics ' + @tbl_name )

INSERT INTO #fraglist

exec ('dbcc showcontig (' + @tbl_name + ') WITH TABLERESULTS, NO_INFOMSGS')

select@ctr = min(tbl_id)

from@tbls

wheretbl_id > @ctr

end

-- id tbls that might benefit from defrag

updatet

setdefrag = 1

, test_ExtentSwitches = case when f.extentswitches > (f.extents - 1) then 1 else 0 end

, test_LogicalFrag = case when f.logicalfrag > 10 then 1 else 0 end

, test_PageDensity = case when (f.actualcount > 1 and f.avgpagedensity < t.[fillfactor]) then 1 else 0 end

, Pages = f.CountPages

, Extents = f.Extents

, ExtentSwitches = f.ExtentSwitches

, ScanDensity = f.ScanDensity

, LogicalFrag = f.LogicalFrag

, AvgPageDensity = f.AvgPageDensity

from@tbls t

inner join #fraglist f on t.tbl_id = f.objectid

wheref.extentswitches > case when f.logicalfrag = 0 then f.extents else (f.extents - 1) end -- extent switching

orf.logicalfrag > 10 -- logical scan fragmentation

-- drop temp table

drop table #fraglist

-- define limits for loop

select@ctr = min(tbl_id)

, @max-2 = max(tbl_id)

from@tbls

wheredefrag = 1

-- loop through tbls for defrag

while @ctr <= @max-2

begin

select@tbl_name = tbl_name

, @indx_name = indx_name

from@tbls

wheretbl_id = @ctr

exec ('DBCC DBREINDEX (' + @tbl_name + ',' + @indx_name + ') WITH NO_INFOMSGS')

exec ('update statistics ' + @tbl_name )

select@ctr = min(tbl_id)

from@tbls

wheretbl_id > @ctr

anddefrag = 1

end

_____________________________________________________________________
- Nate

@nate_hughes

Sergiy SSC Guru Points: 110206 More actions · Answer 6

Reindexing of clustered index changes order of pages for all other indexes on the same table.

So, clustered indexes must be processed first. I don't see where your script takes care of it.

After clustered index is done all statistics collected for other indexes become irrelevant and need to be recollected.

And moreover, I don't see a point of that statistics exercise at all.

REINDEX has a parameter which lets you define sensitive threshold. If index is not fragmented beyond that limit REINDEX will just ignore it.

So, just do all indexes in a row, every one. But don't forget that an attempt to defragment an index with indid=0 will cause an error.

_____________
Code for TallyGenerator

RP_DBA SSCertifiable Points: 5116 More actions · Answer 7

Sergiy (8/25/2008)
Reindexing of clustered index changes order of pages for all other indexes on the same table.
So, clustered indexes must be processed first. I don't see where your script takes care of it.

The script only processes clustered indexes. It's in the join syntax, sysindexes.indid = 1 which is the id for clustered index.

-- id tbls for potential defrag, ignore heap tbls

insert into @tbls (

tbl_id

, tbl_name

, indx_name

, [fillfactor] )

selectso.id

, '[' + so.name + ']'

, si.name

, si.origfillfactor

fromdbo.sysobjects so

inner join dbo.sysindexes si

on so.id = si.id

and si.indid = 1 -- clustered index

whereso.xtype = 'U'

andso.uid = user_id('dbo')

Sergiy (8/25/2008)
After clustered index is done all statistics collected for other indexes become irrelevant and need to be recollected.

I am updating statistics after issuing the DBCC DBREINDEX statement on the clustered index but are you recommending that all indexes be run through DBREINDEX individually?

exec ('DBCC DBREINDEX (' + @tbl_name + ',' + @indx_name + ') WITH NO_INFOMSGS')

exec ('update statistics ' + @tbl_name )

Sergiy (8/25/2008)
And moreover, I don't see a point of that statistics exercise at all.
REINDEX has a parameter which lets you define sensitive threshold. If index is not fragmented beyond that limit REINDEX will just ignore it.

This is run against SQL Server 2000 dbs which is why we're using DBREINDEX and not REINDEX (deprecated in 2000). DBREINDEX doesn't have a sensitivity threshold.

_____________________________________________________________________
- Nate

@nate_hughes

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

DBCC INDEXDEFRAG stored procedure

http://weblogs.sqlteam.com/tarad/archive/2004/07/30/1834.aspx

Sergiy SSC Guru Points: 110206 More actions · Answer 9

RP_DBA (8/25/2008)
The script only processes clustered indexes. It's in the join syntax, sysindexes.indid = 1 which is the id for clustered index.

Sorry, missed that piece.

Was looking for WHERE and ORDER BY clauses.

Because defragmenting clustered indexes only does not make much sense: in typical system most of queries use non-clustered indexes, so your optimisation won't make much difference.

I am updating statistics after issuing the DBCC DBREINDEX statement on the clustered index but are you recommending that all indexes be run through DBREINDEX individually?

I meant statistics collected in table @tbls

If to include non-clustered indexes then information collected for them in that table will become out of date after doing clustered index.

_____________
Code for TallyGenerator

riversoft SSC-Addicted Points: 415 More actions · Answer 10

I would look at doing the Update Statistics before the reindex.

The reindex update the stats on the indexes.

The update status updates all statistics even sql server generated one.

The difference is that upd stats uses a sampling whereas the reindex uses 100% sampling.