December 1, 2008 at 1:46 am
We have one job on prod server to rebuild the indexes, that job is using the below script.
dbcc dbreindex(tblname,'',0,sorted_data_reorg)
sp_recompile tblname
Here my question is sorted_data_reorg
sorted_data_reorg, was an option under Sql Server 6.x, It is no longer in SQL Server 2000 we are using 2000 only.
Here my question is
1. Why we are using sorted_data_reorg paramer in DBCC DBREINDEX, If I remove this what would be the impact.
2. sp_recompile tblname Is it really required after reindexing.
Rajesh Kasturi
December 1, 2008 at 2:37 am
For Question1:
No impact will be there
From 2K-BOL:
The SORTED_DATA_REORG clause of CREATE INDEX eliminated the sort performed when a clustered index was created. Replaced by the DROP_EXISTING clause of CREATE INDEX. Remove all references to the SORTED_DATA_REORG clause of CREATE INDEX and replace with references to DROP_EXISTING.
For Question2:
From 2K-BOL:
If object is the name of a table or view in sp_recompile, all the stored procedures that reference the table or view will be recompiled the next time they are run.
HTH 🙂
December 1, 2008 at 2:56 am
Hi
Thanks for the reply,
Right now I have statement like this
dbcc dbreindex(tblname,'',0,sorted_data_reorg)
If I modify the above statement as
dbcc dbreindex(tblname,'',0)-- You are saying that the while creating the clustered index automatically it will be sorted.
I am not using the CREATE INDEX statement, dbcc dbreindex will be dropped the indexes and recreated.
If I remove the sp_recombile statement will it be any performance impact.
Rajesh Kasturi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply