SQL Server Performance Forum – Threads Archive

defragmenting all indexes

How can I dbcc indexdefrag all indexes in a database?

Use SP_MSforeachtable ‘DBCC INDEXDEFRAG …..’ to get the result.
And best to schedule DBCC DBREINDEX to reduce overall fragmentation. A note from Kalen Delaney’s book:
Indexdefrag has as its only goal to remove logical scan fragmentation. It
does this by rearranging individual pairs of pages. Sometimes that may
result in an decrease in scan density but it was ‘unnecessary’ it that it
was needed for indexdefrag to remove logical scan fragmentation. Having such a low logical scan fragmentation will HELP your performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Sorry I’m not familiar with how SP_MSforeachtable and I couldn’t find any info on it at books online.
What’d be the full command to run?

The following script shows fragmentation of all the tables in the pubs database:
USE pubs
GO
EXEC sp_MSforeachtable @command1="print ‘?’ DBCC INDEXDEFRAG(‘?’)"
GO Keep in mind that the undocumented stored procedures could not be supported in the future SQL Server versions. So, you can use the sp_MSforeachtable undocumented system stored procedure at your own risk. You can rebuild all the indexes on all the tables in your database periodically (for example, one time per week at Sunday) to reduce fragmentation. The DBCC DBREINDEX statement cannot automatically rebuild all of the indexes on all the tables in a database it can only work on one table at a time. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Meanwhile, I found this script by Alexander Chigrik where you can use the script below to defragment all indexes in every table in the pubs database: USE pubs
DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = ‘base table’
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT ‘Derfagmenting index_id = ‘ + convert(char(3), @indid) + ‘of the ‘
+ rtrim(@TableName) + ‘ table’
IF @indid <> 255 DBCC INDEXDEFRAG (pubs, @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Other nice script: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=444 by Thom.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

]]>