Afinamiento...

colegas,

Tengo una base con un nivel transaccional elevado (tiene 11 GB) y una
cantidad de indices en tablas altamente transaccionales que en mi opinion es
exagerado. Esta base de datos es de una aplicacion comercial de terceros,
por lo que borrar algunos indices que en mi opinion no son tan relevantes,
me parece una alternativa arriesgada. En mi servidor tengo windows 2000
server y sql server 2000 ent. edi. El servidor tiene 2 procesadores xeon de
3.6GHZ y 2GB de RAM, tengo implementado un raid 1 con 2 DD de 36.4 GB. Este
Raid tiene 2 unidades logicas, una para el SO y otra para la BD.

Para mejorar el rendimiento de la aplicacion, optamos por crear un job que
se ejecuta diariamente para reconstruir indices con un fill factor del 80% y
tambien realiza un chequeo de integridad. Mi pregunta es que tan
recomendable es reconstruir indices diariamente y que tanto pueden afectarse
las estadisticas de uso en el servidor, de tal forma que impacte en el
rendimiento de la aplicacion, ya que apartir de la ejecucion diaria de este
job no se han sentido mejoras significativas.

Adjunto script del job por si les sirve para analizarlo. De antemano
gracias por sus comentarios.

Edmundo J. Davila



BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database
Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'

SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'EXACTUS - Mantenimiento diario')
IF (@JobID IS NOT NULL)
BEGIN
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
RAISERROR (N'Unable to import job ''EXACTUS - Mantenimiento diario''
since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
EXECUTE msdb.dbo.sp_delete_job @job_name = N'EXACTUS - Mantenimiento
diario'
SELECT @JobID = NULL
END

BEGIN

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = N'EXACTUS - Mantenimiento diario', @owner_login_name =
N'MONISAIII\Administrator', @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

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
1, @step_name = N'LOG BACKUP', @command = N'BACKUP LOG [Exactus]
TO DISK = N''E:\Respaldo2K\ExactusBK''
WITH NOINIT , NOUNLOAD , NAME = N''Exactus Log backup'',
NOSKIP , STATS = 10, NOFORMAT', @database_name = N'master', @server = N'',
@database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0,
@flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name =
N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 4,
@on_fail_action = 4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
2, @step_name = N'LOG TRUNCATE', @command = N'BACKUP LOG Exactus
WITH TRUNCATE_ONLY', @database_name = N'master', @server = N'',
@database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0,
@flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name =
N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 4,
@on_fail_action = 4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
3, @step_name = N'LOG SHRINK', @command = N'dbcc shrinkfile
(''Exactus_db_log'', 100)', @database_name = N'Exactus', @server = N'',
@database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0,
@flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name =
N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 4,
@on_fail_action = 4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
4, @step_name = N'INTEGRITY CHECK WITH REPAIR OF MINOR ERRORS', @command =
N'use master

alter database exactus set SINGLE_USER
WITH ROLLBACK IMMEDIATE

DBCC CHECKDB( ''exactus'', REPAIR_FAST)

alter database exactus set MULTI_USER
with ROLLBACK IMMEDIATE
', @database_name = N'master', @server = N'', @database_user_name = N'',
@subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts
= 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0,
@on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
5, @step_name = N'OPTIMIZATION. INDEX REBUILD', @command = N'EXECUTE
master.dbo.xp_sqlmaint N''-D Exactus -WriteHistory -RebldIdx
15 -RmUnusedSpace 50 10 ''', @database_name = N'master', @server = N'',
@database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0,
@flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name =
N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0,
@on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
6, @step_name = N'COMPLETE DB BACKUP', @command = N'BACKUP DATABASE
[Exactus]
TO DISK = N''E:\Respaldo2K\ExactusBK''
WITH INIT , NOUNLOAD ,
NAME = N''Exactus backup'', NOSKIP ,
STATS = 10, NOFORMAT', @database_name = N'master', @server = N'',
@database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0,
@flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name =
N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0,
@on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
7, @step_name = N'TURN OFF THE SERVER', @command = N'Set wmi =
GetObject("winmgmts:{(Shutdown)}//./root/cimv2")
set objset = wmi.instancesof("win32_operatingsystem")
for each obj in objset
set os = obj : exit for
next

os.win32shutdown (8)', @database_name = N'VBScript', @server = N'',
@database_user_name = N'', @subsystem = N'ActiveScripting',
@cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval
= 1, @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

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name
= N'Diario', @enabled = 1, @freq_type = 8, @active_start_date = 20060428,
@active_start_time = 213000, @freq_interval = 62, @freq_subday_type = 1,
@freq_subday_interval = 0, @freq_relative_interval = 0,
@freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time =
235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

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: