How to schedule DBCC INDEXDEFRAG the best way

HI
I would like to optimize indexes on two databases dbA and
dbB. The optimization must be able to be configured by a
non DBA, with a script run after the installation of the
SQL Server and the user applications. The job has to be
scheduled.
Since the requirements are 24*7 access to all tables I
cannot (?)use DBCC REINDEX, which - what I understand -
should give me the best optimization of my existing
indexes regarding diskstructure etc., because it places
locks on the tables while running.
The next best thing must then - to my knowlegde - be DBCC
INDEXDEFRAG. (Note: Update of statistics are handled!)
Unfortunately I have some troubles scheduling DBCC
INDEXDEFRAG using a job.
The index optimization configured by the Maintenance Plan
Wizard uses DBCC REINDEX so that is out of the question,
except if xp_sqlmaint has another swith that can be used
instead of -RebldIdx (I cannot find an "indexdefrag
switch" it in BOL)'
Then I build a script which explicitely mentioned every
table and every index of both dbA and dbB to be optimized.
The script looked like this:
IF EXISTS (SELECT name FROM sysindexes WHERE name
= 'PK_ACT')
DBCC INDEXDEFRAG ('dbA', [ACT],[PK_ACT])
IF EXISTS (SELECT name FROM sysindexes WHERE name
= 'PK_ACT_ASSOC')
DBCC INDEXDEFRAG ('dbA', [ACT_ASSOC],[PK_ACT_ASSOC])
etc....
Unfortunalty, the script could not be pasted in the
command box of the job step since it migth have been to
long (670 linex approx.)
I of course could place the script externally in the file
system and have a call to osql.exe scheduled, but I would
rather have the DBCC INDEXDEFRAG statement:
- placed "internally" in the SQL Server rather than in the
file system.
- be able to make a call to DBCC INDEXDEFRAG without
having to specify each and every table/index to be
optimized, but - in the same way that DBCC REINDEX are
handled by the Maintenance Plan Wizard - just
specify: "these are the databases I want to process, now
do it for me!"
I captured and tweaked a script (bottom of message) to
setup the maintenance plan and job it runs okay, but with
DBCC REINDEX instead of DBCC INDEXDEFRAG:
The interesting part is this:
SET @.strMaintenanceCommand = 'EXECUTE
master.dbo.xp_sqlmaint ''-PlanID ' + convert(varchar
(256),@.MaintenancePlanID) + ' -WriteHistory -RebldIdx
100'''
If only "-RebldIdx" could be replaced with something else.
Could it'
yours truly
Jakob Persson
********************SCRIPT*************************''
USE MSDB
Go
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.MaintenancePlanID UNIQUEIDENTIFIER
DECLARE @.ReturnCode INT
DECLARE @.SQL VARCHAR(2000)
DECLARE @.strMaintenanceCommand VARCHAR(2000)
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM [msdb].[dbo].[sysdbmaintplans]
WHERE [msdb].[dbo].[sysdbmaintplans].[plan_name] = N'INDEXDEFRAG') >= 1
BEGIN
SET @.MaintenancePlanID = (
SELECT [plan_id]
FROM [msdb].[dbo].[sysdbmaintplans]
WHERE [msdb].[dbo].[sysdbmaintplans].[plan_name]
= 'INDEXDEFRAG'
)
SET @.SQL = 'EXEC sp_delete_maintenance_plan '
+ '''' + convert(varchar(256),@.MaintenancePlanID) + ''''
EXECUTE(@.SQL)
END
SET @.MaintenancePlanID = NULL
EXEC sp_add_maintenance_plan @.plan_name
= 'INDEXDEFRAG', @.plan_id = @.MaintenancePlanID OUTPUT
EXEC sp_add_maintenance_plan_db @.plan_id = @.MaintenancePlanID , @.db_name = 'DACCIS01'
EXEC sp_add_maintenance_plan_db @.plan_id = @.MaintenancePlanID , @.db_name = 'MIRROR'
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name
= N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Database
Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'DACCIS01 and
MIRROR ''INDEXDEFRAG''')
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <>
0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''DACCIS01 and
MIRROR ''INDEXDEFRAG'''' since there is already a multi-
server job with this name.', 16, 1)
-- GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'DACCIS01
and MIRROR ''INDEXDEFRAG'''
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'DACCIS01 and
MIRROR ''INDEXDEFRAG''', @.owner_login_name = N'SA',
@.description = N'No description available.',
@.category_name = N'Database Maintenance', @.enabled = 1,
@.notify_level_email = 0, @.notify_level_page = 0,
@.notify_level_netsend = 0, @.notify_level_eventlog = 2,
@.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
-- Add the job steps
SET @.strMaintenanceCommand = 'EXECUTE
master.dbo.xp_sqlmaint ''-PlanID ' + convert(varchar
(256),@.MaintenancePlanID) + ' -WriteHistory -RebldIdx
100'''
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep
@.job_id = @.JobID,
@.step_id = 1,
@.step_name
= N'Step 1',
@.command = @.strMaintenanceCommand,
@.database_name = N'master',
@.server = N'',
@.database_user_name = N'',
@.subsystem
= N'TSQL',
@.cmdexec_success_code = 0,
@.flags = 4,
@.retry_attempts = 0,
@.retry_interval = 0,
@.output_file_name = N'',
@.on_success_step_id = 0,
@.on_success_action = 1,
@.on_fail_step_id = 0,
@.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule
@.job_id = @.JobID, @.name = N'Schedule 1', @.enabled = 1,
@.freq_type = 4, @.active_start_date = 20031212,
@.active_start_time = 10000, @.freq_interval = 1,
@.freq_subday_type = 8, @.freq_subday_interval = 2,
@.freq_relative_interval = 0, @.freq_recurrence_factor = 0,
@.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id
= @.JobID, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:Jakob,
> I of course could place the script externally in the file
> system and have a call to osql.exe scheduled, but I would
> rather have the DBCC INDEXDEFRAG statement:
> - placed "internally" in the SQL Server rather than in the
> file system.
OSQL.EXE is not any more or less "internal" than SQL Server Agent. both are
client applications which logs on to SQL Server and executes SQL commands.
> If only "-RebldIdx" could be replaced with something else.
> Could it'
Xp_sqlmaint is basically just a wrapper around sqlmaint.exe, where the
sqlmaint.exe logs on to SQL Server and execute the commands as specified by
the command-line parameters. Unfortunately, there's no command-line
parameter which makes the exe execute DBCC INDEXDEFRAG commands (to the best
of my knowledge, supported by Books Online).
My suggestion is that you write a stored procedure which has first a cursor
that uses the sysobjects or INFORMATION_SCHEM.TABLES to for each table in a
cursor. Inside this cursor, you have another cursor (another loop) where you
use sysindexes and loop each index. Inside this second loop, you now have
both table and index name, so you can construct and execute the DBCC
INDEXDEFRAG command. Just make sure that you in the cursor definition
exclude system tables, statistics etc (use functions such as OBJECTPROPERTY,
INDERPROPERTY etc).
You can probably write this as an sp_ and put it in master. Then have your
own table in which you store the database name for the databases you want to
defrag (write a simple UI if this need to be configurable for an end-user).
Then in your job, you have a cursor which loop this table with database
names in a cursor (having dbname in a variable, @.db), and execute:
EXEC @.db..sp_DefragAllTable --or whatever you called the proc
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jakob Persson" <jakobpersson@.yahoo.dk> wrote in message
news:019801c3c160$b0f763d0$a001280a@.phx.gbl...
> HI
> I would like to optimize indexes on two databases dbA and
> dbB. The optimization must be able to be configured by a
> non DBA, with a script run after the installation of the
> SQL Server and the user applications. The job has to be
> scheduled.
> Since the requirements are 24*7 access to all tables I
> cannot (?)use DBCC REINDEX, which - what I understand -
> should give me the best optimization of my existing
> indexes regarding diskstructure etc., because it places
> locks on the tables while running.
> The next best thing must then - to my knowlegde - be DBCC
> INDEXDEFRAG. (Note: Update of statistics are handled!)
> Unfortunately I have some troubles scheduling DBCC
> INDEXDEFRAG using a job.
> The index optimization configured by the Maintenance Plan
> Wizard uses DBCC REINDEX so that is out of the question,
> except if xp_sqlmaint has another swith that can be used
> instead of -RebldIdx (I cannot find an "indexdefrag
> switch" it in BOL)'
> Then I build a script which explicitely mentioned every
> table and every index of both dbA and dbB to be optimized.
> The script looked like this:
> IF EXISTS (SELECT name FROM sysindexes WHERE name
> = 'PK_ACT')
> DBCC INDEXDEFRAG ('dbA', [ACT],[PK_ACT])
> IF EXISTS (SELECT name FROM sysindexes WHERE name
> = 'PK_ACT_ASSOC')
> DBCC INDEXDEFRAG ('dbA', [ACT_ASSOC],[PK_ACT_ASSOC])
> etc....
> Unfortunalty, the script could not be pasted in the
> command box of the job step since it migth have been to
> long (670 linex approx.)
> I of course could place the script externally in the file
> system and have a call to osql.exe scheduled, but I would
> rather have the DBCC INDEXDEFRAG statement:
> - placed "internally" in the SQL Server rather than in the
> file system.
> - be able to make a call to DBCC INDEXDEFRAG without
> having to specify each and every table/index to be
> optimized, but - in the same way that DBCC REINDEX are
> handled by the Maintenance Plan Wizard - just
> specify: "these are the databases I want to process, now
> do it for me!"
>
> I captured and tweaked a script (bottom of message) to
> setup the maintenance plan and job it runs okay, but with
> DBCC REINDEX instead of DBCC INDEXDEFRAG:
> The interesting part is this:
> SET @.strMaintenanceCommand = 'EXECUTE
> master.dbo.xp_sqlmaint ''-PlanID ' + convert(varchar
> (256),@.MaintenancePlanID) + ' -WriteHistory -RebldIdx
> 100'''
> If only "-RebldIdx" could be replaced with something else.
> Could it'
> yours truly
> Jakob Persson
> ********************SCRIPT*************************''
> USE MSDB
> Go
>
> BEGIN TRANSACTION
> DECLARE @.JobID BINARY(16)
> DECLARE @.MaintenancePlanID UNIQUEIDENTIFIER
> DECLARE @.ReturnCode INT
> DECLARE @.SQL VARCHAR(2000)
> DECLARE @.strMaintenanceCommand VARCHAR(2000)
> SELECT @.ReturnCode = 0
> IF (SELECT COUNT(*) FROM [msdb].[dbo].[sysdbmaintplans]
> WHERE [msdb].[dbo].[sysdbmaintplans].[plan_name] => N'INDEXDEFRAG') >= 1
> BEGIN
> SET @.MaintenancePlanID = (
> SELECT [plan_id]
> FROM [msdb].[dbo].[sysdbmaintplans]
> WHERE [msdb].[dbo].[sysdbmaintplans].[plan_name]
> = 'INDEXDEFRAG'
> )
> SET @.SQL = 'EXEC sp_delete_maintenance_plan '
> + '''' + convert(varchar(256),@.MaintenancePlanID) + ''''
> EXECUTE(@.SQL)
> END
> SET @.MaintenancePlanID = NULL
> EXEC sp_add_maintenance_plan @.plan_name
> = 'INDEXDEFRAG', @.plan_id = @.MaintenancePlanID OUTPUT
> EXEC sp_add_maintenance_plan_db @.plan_id => @.MaintenancePlanID , @.db_name = 'DACCIS01'
> EXEC sp_add_maintenance_plan_db @.plan_id => @.MaintenancePlanID , @.db_name = 'MIRROR'
>
> IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name
> = N'Database Maintenance') < 1
> EXECUTE msdb.dbo.sp_add_category @.name = N'Database
> Maintenance'
> -- Delete the job with the same name (if it exists)
> SELECT @.JobID = job_id
> FROM msdb.dbo.sysjobs
> WHERE (name = N'DACCIS01 and
> MIRROR ''INDEXDEFRAG''')
> IF (@.JobID IS NOT NULL)
> BEGIN
> -- Check if the job is a multi-server job
> IF (EXISTS (SELECT *
> FROM msdb.dbo.sysjobservers
> WHERE (job_id = @.JobID) AND (server_id <>
> 0)))
> BEGIN
> -- There is, so abort the script
> RAISERROR (N'Unable to import job ''DACCIS01 and
> MIRROR ''INDEXDEFRAG'''' since there is already a multi-
> server job with this name.', 16, 1)
> -- GOTO QuitWithRollback
> END
> ELSE
> -- Delete the [local] job
> EXECUTE msdb.dbo.sp_delete_job @.job_name = N'DACCIS01
> and MIRROR ''INDEXDEFRAG'''
> SELECT @.JobID = NULL
> END
>
> BEGIN
> -- Add the job
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id => @.JobID OUTPUT , @.job_name = N'DACCIS01 and
> MIRROR ''INDEXDEFRAG''', @.owner_login_name = N'SA',
> @.description = N'No description available.',
> @.category_name = N'Database Maintenance', @.enabled = 1,
> @.notify_level_email = 0, @.notify_level_page = 0,
> @.notify_level_netsend = 0, @.notify_level_eventlog = 2,
> @.delete_level= 0
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
>
> -- Add the job steps
> SET @.strMaintenanceCommand = 'EXECUTE
> master.dbo.xp_sqlmaint ''-PlanID ' + convert(varchar
> (256),@.MaintenancePlanID) + ' -WriteHistory -RebldIdx
> 100'''
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep
> @.job_id = @.JobID,
> @.step_id => 1,
> @.step_name
> = N'Step 1',
> @.command => @.strMaintenanceCommand,
>
> @.database_name = N'master',
> @.server => N'',
>
> @.database_user_name = N'',
> @.subsystem
> = N'TSQL',
>
> @.cmdexec_success_code = 0,
> @.flags => 4,
>
> @.retry_attempts = 0,
>
> @.retry_interval = 0,
>
> @.output_file_name = N'',
>
> @.on_success_step_id = 0,
>
> @.on_success_action = 1,
>
> @.on_fail_step_id = 0,
>
> @.on_fail_action = 2
>
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
> EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id => @.JobID, @.start_step_id = 1
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
> -- Add the job schedules
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule
> @.job_id = @.JobID, @.name = N'Schedule 1', @.enabled = 1,
> @.freq_type = 4, @.active_start_date = 20031212,
> @.active_start_time = 10000, @.freq_interval = 1,
> @.freq_subday_type = 8, @.freq_subday_interval = 2,
> @.freq_relative_interval = 0, @.freq_recurrence_factor = 0,
> @.active_end_date = 99991231, @.active_end_time = 235959
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
> -- Add the Target Servers
> EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id
> = @.JobID, @.server_name = N'(local)'
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO
> QuitWithRollback
> END
> COMMIT TRANSACTION
> GOTO EndSave
> QuitWithRollback:
> IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
> EndSave: