RS First Dynamics NAV Blog


...from NAV 3.60 to NAV 2013
Archivio Posts
Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

Navision tables optimizing script

A SQL Script to simulate the "Navision table optimization routine"

un utile script sql per simulare le operazioni svolte dalla funzionalità standard "ottimizza tabelle" di navision che:

1. ottimizza le sift tables (versioni <= 5.0)

2. ricostruisce tutti gli indici (compreso il cluster)

perchè  utilizzarla:

l'ottimizzazione fatta da sql (configurabile nel maintenance plan) a volte cofigura ed ottimizza in modo non efficiente per il client nav in quanto utilizza logica diverse; questa routine simula quello che farebbe il client Navision

 

Navision tables optimizing script  (Sql Tuning indexes Like)

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'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Optimize Production Database')
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 ''Optimize Production Database'' 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'Optimize Production
Database'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = N'Optimize Production Database', @owner_login_name =
N'SymbiantClient\symbiant', @description = N'Reindexes Database in Verbose
Mode.', @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
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
1, @step_name = N'Create device-sets and Backup Database', @command = N'IF
(SELECT COUNT(name) FROM sysdevices WHERE (name = ''BeforeOptimization'')) < 1
BEGIN
EXEC sp_addumpdevice ''disk'', ''BeforeOptimization'',
''E:\Optimization\BeforeOptimization.BAK''
END
IF (SELECT COUNT(name) FROM sysdevices WHERE (name = ''AfterOptimization''))
< 1
BEGIN
EXEC sp_addumpdevice ''disk'', ''AfterOptimization'',
''E:\Optimization\\AfterOptimization.BAK''
END

BACKUP DATABASE [Navision Production] TO BeforeOptimization WITH INIT',
@database_name = N'master', @server = N'', @database_user_name = N'',
@subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @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 = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
2, @step_name = N'Optimize Primary Keys and Other Indexes', @command =
N'DECLARE @navtablename VARCHAR(250)
DECLARE @navobjectid INTEGER
DECLARE @navindexname VARCHAR(250)
DECLARE @navision_tables CURSOR
DECLARE @navision_primarykeys CURSOR
DECLARE @navision_otherindexes CURSOR

/* Cursor based optimizations (verbose for administrative purposes) */
SET @navision_tables = CURSOR FOR
SELECT name,id FROM sysobjects WHERE xtype = ''U''
OPEN @navision_tables
FETCH NEXT FROM @navision_tables INTO @navtablename, @navobjectid
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT(''Processing table: '' + @navtablename)

SET @navision_primarykeys = CURSOR FOR
SELECT name FROM sysobjects WHERE (type = ''K'' AND parent_obj =
@navobjectid)
OPEN @navision_primarykeys
FETCH NEXT FROM @navision_primarykeys INTO @navindexname
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT('' Optimizing Primary Index: '' + @navindexname)
DBCC DBREINDEX(@navtablename,@navindexname) WITH NO_INFOMSGS

SET @navision_otherindexes = CURSOR FOR
SELECT name FROM sysindexes WHERE (id = @navobjectid AND indid != ''1'')
OPEN @navision_otherindexes
FETCH NEXT FROM @navision_otherindexes INTO @navindexname
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT('' Optimizing Other Index: '' + @navindexname)
DBCC DBREINDEX(@navtablename,@navindexname) WITH NO_INFOMSGS
FETCH NEXT FROM @navision_otherindexes INTO @navindexname
END;
CLOSE @navision_otherindexes
DEALLOCATE @navision_otherindexes

FETCH NEXT FROM @navision_primarykeys INTO @navindexname
END;
CLOSE @navision_primarykeys
DEALLOCATE @navision_primarykeys

FETCH NEXT FROM @navision_tables INTO @navtablename, @navobjectid
END
CLOSE @navision_tables
DEALLOCATE @navision_tables', @database_name = N'Navision Production',
@server = N'', @database_user_name = N'', @subsystem = N'TSQL',
@cmdexec_success_code = 0, @flags = 4, @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 = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
3, @step_name = N'Backup Database and Truncate Log after Optimizations',
@command = N'BACKUP LOG [Navision Production] WITH TRUNCATE_ONLY
BACKUP DATABASE [Navision Production] TO AfterOptimization WITH INIT

PRINT('''')
PRINT(''Processed Successfully!'')', @database_name = N'Navision
Production', @server = N'', @database_user_name = N'', @subsystem = N'TSQL',
@cmdexec_success_code = 0, @flags = 4, @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

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name
= N'Every Saturday at 3:00 AM', @enabled = 1, @freq_type = 8,
@active_start_date = 20050317, @active_start_time = 30000, @freq_interval =
64, @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

-- 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:

Categoria: Dynamics NAV ALL
mercoledì, 23 dic 2009 Ore. 16.05
Statistiche
  • Views Home Page: 342.250
  • Views Posts: 718.719
  • Views Gallerie: 0
  • n° Posts: 345
  • n° Commenti: 0
Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003