제2의 비엔지니어 인생관을 꿈꾸며

Posted
Filed under Study

SQL SERVER 2005 uses ALTER INDEX syntax to reindex database. SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions.

When any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. DBCC DBREINDEX statement can be used to rebuild all the indexes on all the tables in database. DBCC DBREINDEX is efficient over dropping and recreating indexes.

Execution of Stored Procedure sp_updatestats at the end of the Indexes process ensures updating stats of the database.

Method 1: My Preference

USE MyDatabase
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO

Method 2:

USE MyDatabase
GO
CREATE PROCEDURE spUtil_ReIndexDatabase_UpdateStats
AS
DECLARE
@MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT
table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
'Reindexing Table:  ' + @MyTable
DBCC DBREINDEX(@MyTable, '', 80)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE
myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
GO


SQL설치되고 나서 아무것도 모르면 시간이 흐르면 괜히 DB가 느려진다. 위와 같은 리인덱스 쿼리문을 작업배치로 돌려주면 간간히 부하가 걸리는 DB서버를 원할이 유지하는데 도움을 주기 때문에 블로그에 여기 남겨본다.
2009/12/05 01:40 2009/12/05 01:40