September 29, 2009 at 7:03 am
I use SQL Server 2005. I have a fragmentation problem in some of my databases.
I use the below script to see the fragmentation:-
USE SS_ORG
DECLARE @TableName sysname
DECLARE cur_showfragmentation CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_showfragmentation
FETCH NEXT FROM cur_showfragmentation INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Show fragmentation for the ' + @TableName + ' table'
DBCC SHOWCONTIG (@TableName)
FETCH NEXT FROM cur_showfragmentation INTO @TableName
END
CLOSE cur_showfragmentation
DEALLOCATE cur_showfragmentation
-----------------------
or
--------------------------
USE SS_ORG
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG('?')"
GO
-------------------------
and i see fragmentation on some databases.so i use the below script to remove fragmentation
---------------------
CREATE PROC ind_rebuild
AS
DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName + ' table'
DBCC DBREINDEX (@TableName, ' ', 80)
FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
GO
---------------------------- and alo this
USE SS_ORG
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
and let me tell you friends that these scripts does not remove fragmentation at all. so does any one have some script that will really remove fragmentation?
September 29, 2009 at 7:06 am
try dbcc indexdefrag(db-name,table-name) instead of DBReindex
once all done do an DBCC UPDATEUSAGE(Db-name)
and exec sp_updatestats.
September 29, 2009 at 7:28 am
thanks i will try it out
September 29, 2009 at 7:44 am
See also BOL SQL 2005 ---> Reorganizing and Rebuilding Indexes and http://www.sqlservercentral.com/Forums/Topic789128-149-1.aspx#bm789512.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply