Monday, February 20, 2012

Maintenance plan, reindexing

In the Enterprise Manager of SQL Server 2000 I have set up a maintenance plan which rebuilds my indexes. I've stuided the documentation, and from what I've learned what happens behind the curtain is that several DBCC REINDEX commands are being issued.
Question:
If I have 20 tables and 40 indexes: Will SQL Server do the maintance plan in 1 single transaction, or will it divide the it up to eg. 20 or 40 transactions?

-hIf your SQL Server Agent is installed with a Service Account,create Jobs to run the Reindex DBCC on the large tables and usethe Maintaince Plans for minor problems. It is also a good ideato document the maintaince plans. I think if the reindex includesrepair SQL Server must be in single user mode. One of the bestbooks on SQL Server indexes is by Ken England he quantifies theIAM(Index Allocation Mapping) pages. Hope this helps.
Kind regards,
Gift Peddie

No comments:

Post a Comment