If you have to optimize a lot of tables and indexes. You could create a SQL script, which then could be scheduled.
Example a script that is starting with doing a reindex and the continues with indexdefrag.
| | | copy code | | ? |
CREATE proc DB_Maint AS |
set nocount on |
DBCC DBReIndex ([$Post Code], [$1]) |
DBCC DBReIndex ([$Customer], [$1]) |
commit |
RAISERROR ('Reindex done', 16, 1 ) with log, nowait |
DBCC IndexDefrag (0, [$Customer], [$Customer$0]) |
DBCC IndexDefrag (0, [$Customer], [$1]) |
DBCC IndexDefrag (0, [$Customer], [$2]) |
commit |
RAISERROR ('IndexDefrag Customer done', 16, 1 ) with log, nowait |
commit |
RAISERROR ('IndexDefrag Basis done', 16, 1 ) with log, nowait |
GO |
With the use of the RAISERROR command I am getting some log entries, and thereby I can always see how far the script is in its execution.