If we
create index on table and indexed table frequently changes occur than fragmentation
increases. This may reduce your query performance. So we need to rebuild the
index time to time.
We can use DBCC
DBREINDEX to rebuild the all index on all tables in database. DBCC DBREINDEX drops
index and creates again.
Rebuild all indexes in database with
specific fill factor
USE [BD_NAME]
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
|
Rebuild all indexes in database with
specific fill factor
USE [BD_NAME]
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
GO
EXEC sp_updatestats
GO
|
sp_updatestats
insures that “Statistics for all tables have been updated.”
Here question mark is a
placeholder for the table name (as it loops through each table in turn).
To rebuild all index in a table
USE [DB_NAME]
GO
ALTER INDEX ALL ON TBL_NAME
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE =
ON);
GO
|
No comments:
Post a Comment
Please do not enter any spam link in the comment box.