REBUILD INDEXES
-
I found following about rebuilding indexes:
_________________________________________________________
The below command will rebuild all indexes on a database.
We need to run the below command in the context of each database that is of interest.
sp_MSForEachTable "DBCC DBREINDEX('?')"
The below command is for updating the statistics with full scan.
We also need to run this command in the context of each database.
sp_MSForEachTable "UPDATE STATISTICS ? with fullscan"
__________________________________________________________
I do not know much about indexes.
Could you, please, tell something more like:
1) what is actual need for doing this,
2) how often,
3) how does it affect performance,
4) what if database have small & midsize tables and only few tables with max 100.000 rows, is it still important to do above mentioned steps on all tables?
Thanks a lot
-
You posted this in 2005 forum so I believe you are using 2005...
In 2005 you can rebuild the indexes online while users are accessing...using ALTERT TABLE command...
Check the BOL topic "defragementing indexes"
Read the following article which is for sql 2000 but it applies to 2005 also...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
MohammedU
Microsoft SQL Server MVP -
You don't need to update statistics after doing dbreindex. DBReindex updates the stats as part of its operation. IndexDefrag doesn't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
try this article:-
http://www.sql-server-performance.com/rebuilding_indexes.asp
-
try this article:-
http://www.sql-server-performance.com/rebuilding_indexes.asp
-
What version of SQL Server are you using?
-WM
-
Thank you guys for your replies
I'm using SQL Server 2005 x64 Developer
-
If you're using 2005, you should be using the ALTER INDEX ... REBULD command, rather than the old DBCC DBREINDEX
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I use this script in a scheduled job to perform online indexrebuilds whenever possible (EE and DevEdtn only !)
set nocount on
declare @OnlineRebuild char(1)
Set @OnlineRebuild = 'Y'
print '-- Begin ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
Declare @SQLStmt varchar(max);
DECLARE @Tellerke BIGINT
SET @Tellerke = 0
declare c1 cursor for
--print 'use [' + db_name() + ']' + char(10) + 'GO'
-- Using a CTE to avoid multiple scans
-- Select Object that cannot use Online-reindexing
-- (disabled indexes, colomns of datatype (n)text, image, (n)varchar(max),.. )
WITH cteExcludedObjects (TABLE_SCHEMA , TABLE_NAME, INDEX_NAME, INDEX_ID )
AS (
Select T.TABLE_SCHEMA , T.TABLE_NAME, I.[name], I.[index_id]
from INFORMATION_SCHEMA.Tables T with (nolock)
Inner Join sys.indexes I with (nolock)
ON I.[object_id] = object_id('[' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + ']')
and I.is_disabled = 0 -- Only select active indexes !
where exists ( Select *
from sys.indexes I with (nolock)
where I.[object_id] = object_id('[' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + ']')
and I.is_disabled = 1 )
OR EXISTS (SELECT *
FROM INFORMATION_SCHEMA.Columns C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
AND ( C.CHARACTER_OCTET_LENGTH > 8000
or C.CHARACTER_OCTET_LENGTH = (-1) )
)
)
Select 'use [' + db_name() + '] ;
print '' tabel [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] '' + convert(varchar(25),getdate(),121) ;
ALTER INDEX ALL ON [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] REBUILD '
+ case @OnlineRebuild when 'Y' then ' WITH ( ONLINE = ON )' else '' end + ' ;'
from INFORMATION_SCHEMA.Tables T with (nolock)
Left Join cteExcludedObjects D
on T.TABLE_SCHEMA = D.TABLE_SCHEMA
and T.TABLE_NAME = D.TABLE_NAME
Where T.TABLE_TYPE = 'BASE TABLE'
and D.TABLE_SCHEMA IS NULL
and T.TABLE_NAME <> 'dtproperties'
Union ALL
Select 'use [' + db_name() + '];
print '' tabel [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] IX ' + cast(INDEX_ID as varchar(15)) + ' [' + INDEX_NAME + '] '' + convert(varchar(25),getdate(),121) ;
ALTER INDEX [' + INDEX_NAME + '] ON [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] REBUILD WITH ( ONLINE = OFF ) ;'
from INFORMATION_SCHEMA.Tables T with (nolock)
inner join cteExcludedObjects D
on T.TABLE_SCHEMA = D.TABLE_SCHEMA
and T.TABLE_NAME = D.TABLE_NAME
Where T.TABLE_TYPE = 'BASE TABLE'
and INDEX_NAME is not NULL -- not for Heaps
order by 1 ;
open c1
FETCH NEXT FROM c1 INTO @SQLStmt
WHILE @@FETCH_STATUS = 0
BEGIN
-- print @sqlstmt
exec ( @SQLStmt )
set @Tellerke = @Tellerke + 1
FETCH NEXT FROM c1 INTO @SQLStmt
END
-- Cursor cleanup
CLOSE c1
DEALLOCATE c1
Print ' '
Print '# Objects rebuilt'
Print '------------------------'
Print @Tellerke
print ' '
print '-- Usage ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
DBCC UPDATEUSAGE (0) with count_rows ;
print '-- Usage Statistics ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
EXEC sp_updatestats ;
print '-- The end ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply