October 12, 2012 at 9:12 am
Hi,
I have a requirement delete some old records and reindex the single table,how can we reindex a table?
If we delete some records from table,is it necessary to reindex the table?
What is the code for reindex the table in sql server 2005?
October 12, 2012 at 9:19 am
yogi123 (10/12/2012)
Hi,I have a requirement delete some old records and reindex the single table,how can we reindex a table?
If we delete some records from table,is it necessary to reindex the table?
What is the code for reindex the table in sql server 2005?
Take a look at: http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/
and
http://www.sql-server-performance.com/2007/rebuilding-indexes/
HTH,
Rob
October 12, 2012 at 2:09 pm
If by "reindex" you mean defragment or rebuild, than you can use Ola Hallengren's procedure.
[/url] Put it in a job.
If you want to find indexes that are not used, and indexes that are missing (that could potentially improve performance),
look here: http://www.brentozar.com/sql-server-training-videos/index-tuning-for-sql-server/[/url]
Do not create all the indexes that are reported as "missing", just maybe 1 or 2 most relevant ones. Some folks created all the indexes: script yield no indexes are misiing anymore, but performance was terrible due to vast amount if indexes that had to be managed on each row change.
Delete about 500 to 1000 rows at a time (in one DELETE command). If you cross 2000 rows, you are in danger of escalating the locks to whole table.
E.g.
declare @rowc int = 1
WHILE @rowc > 0 BEGIN
DELETE TOP 500 t
FROM MyTable t
WHERE <condition>
SET @rowc = @@ROWCOUNT
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply