Saturday, August 11, 2018

Reindexing Dynamis AX database

Below script will create job to re-index and update statistics of dynamics ax database.

In script below fill factor is used as 30%, review and adjust it as per your requirements.

This will be useful as part of Dynamics AX database maintenance.

Replace <Domain UserName> with domain user name.
Replace DatabaseName with database name.

 
USE [msdb]
GO

/****** Object:  Job [<DatabaseName> - Index and Statistics maintenance]    Script Date: 9/26/2017 12:28:53 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 9/26/2017 12:28:53 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DatabaseName - Index and Statistics maintenance',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'<Domain UserName>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Update column statistics only]    Script Date: 9/26/2017 12:28:54 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update column statistics only',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=3,
  @on_success_step_id=0,
  @on_fail_action=3,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'SET NOCOUNT ON
DECLARE @init INT = 1, @cnt INT, @tsql nvarchar(max) = N''''
DECLARE @objectid int;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @statsname nvarchar(130);
DECLARE @tableStat table (id int identity(1,1), obj_id int, name varchar(1000))
INSERT INTO @tableStat
SELECT [object_id], name
FROM sys.stats
WHERE OBJECTPROPERTY([object_id], ''IsUserTable'') = 1
                AND (auto_created = 1 or user_created = 1)

SELECT @cnt = COUNT(1) FROM @tableStat

WHILE @init <= @cnt
BEGIN
select @objectid = obj_id, @statsname = name from @tableStat where id = @init

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SET @tsql = N''UPDATE STATISTICS '' + @schemaname + N''.'' + @objectname + N'' '' + @statsname + N'' WITH FULLSCAN'';
--PRINT @tsql
EXEC sp_executesql @tsql
SET @init += 1
END',
  @database_name=N'DatabaseName',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Reorganize, Rebuild indexes and Update statistics.]    Script Date: 9/26/2017 12:28:54 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Reorganize, Rebuild indexes and Update statistics.',
  @step_id=2,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'--Index rebuild and reorg
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON -- 22/08/2015 added to avoid error due to xml and name identifier.
DECLARE @tsql NVARCHAR(MAX),@fillfactor INT=70,@init int = 1, @cnt int =0,@AvgFragmentationInPercent DECIMAL(6,2), @heap bit = 0
DECLARE @FragmentedIndexs TABLE (ID INT IDENTITY(1,1),IndexName VARCHAR(500),ObjectName VARCHAR(500),AvgFragmentationInPercent DECIMAL(6,2),FragmentCount INT,AvgFragmentSizeInPage DECIMAL(6,2),IndexDepth INT, index_id INT)

INSERT INTO @FragmentedIndexs
SELECT QUOTENAME(I.name) Name, QUOTENAME(DB_NAME())+''.''+QUOTENAME(OBJECT_SCHEMA_NAME(I.[object_id]))+''.''+QUOTENAME(OBJECT_NAME(I.[object_id])) ObjectName,PS.avg_fragmentation_in_percent,
PS.fragment_count,PS.avg_fragment_size_in_pages,PS.index_depth, i.index_id FROM sys.dm_db_index_physical_stats (DB_ID(), NULL ,NULL, NULL, ''LIMITED'') AS PS
INNER JOIN sys.indexes AS I ON PS.[object_id]= I.[object_id] AND PS.index_id = I.index_id WHERE PS.avg_fragmentation_in_percent > 10 --AND PS.index_type_desc = ''NONCLUSTERED INDEX''  
AND page_count > 0 AND page_count > 500 --AND i.index_id = 0 -- 22/08/2015 to exclude small tables having less than 500 pages for live database.
ORDER BY PS.avg_fragmentation_in_percent DESC

SELECT @cnt = COUNT(ID) FROM @FragmentedIndexs

WHILE @cnt >= @init
BEGIN
SELECT @AvgFragmentationInPercent=AvgFragmentationInPercent, @heap = CASE WHEN (IndexName IS NULL OR index_id = 0) THEN 1 ELSE 0 END FROM @FragmentedIndexs WHERE ID=@init

--Index rebuild
IF @AvgFragmentationInPercent<=30.00
BEGIN
 SELECT @tsql = STUFF((SELECT DISTINCT '';''+''ALTER INDEX ''+FI.IndexName+'' ON ''+FI.ObjectName+'' REORGANIZE ''
 FROM @FragmentedIndexs FI WHERE FI.AvgFragmentationInPercent <= 30 AND FI.ID = @init
 AND NOT EXISTS (SELECT * FROM @FragmentedIndexs X WHERE X.index_id = 0 AND FI.ObjectName = X.ObjectName)
 FOR XML PATH('''')), 1,1,'''')
END

ELSE IF @AvgFragmentationInPercent>30.00
BEGIN
 SELECT @tsql = STUFF((SELECT DISTINCT '';''+''ALTER INDEX ''+FI.IndexName+'' ON ''+FI.ObjectName+'' REBUILD WITH (FILLFACTOR = ''+CONVERT(VARCHAR(3),@fillfactor)+'') ''
 FROM @FragmentedIndexs FI WHERE FI.AvgFragmentationInPercent > 30 AND FI.ID = @init
 AND NOT EXISTS (SELECT * FROM @FragmentedIndexs X WHERE X.index_id = 0 AND FI.ObjectName = X.ObjectName)
 FOR XML PATH('''')), 1,1,'''')
END

--print @tsql
EXEC sp_executesql @tsql

--Update stats
IF @AvgFragmentationInPercent <= 30.00
BEGIN
SELECT @tsql = STUFF(( SELECT DISTINCT '';''+''UPDATE STATISTICS '' + FI.ObjectName + '' '' + FI.IndexName FROM @FragmentedIndexs FI
WHERE FI.AvgFragmentationInPercent <= 30 AND FI.ID = @init FOR XML PATH('''')), 1,1,'''')
--print @tsql
EXEC sp_executesql @tsql
END

--Table rebuild
IF @heap = 1
BEGIN
SELECT @tsql = STUFF(( SELECT DISTINCT '';''+''ALTER TABLE '' + FI.ObjectName + '' REBUILD'' FROM @FragmentedIndexs FI
WHERE FI.ID = @init AND index_id = 0 FOR XML PATH('''')), 1,1,'''')
--print @tsql
EXEC sp_executesql @tsql
END

SELECT @tsql = '''', @heap = 0
SET @init += 1
END
',
  @database_name=N'DatabaseName',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily',
  @enabled=1,
  @freq_type=4,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=0,
  @active_start_date=20140603,
  @active_end_date=99991231,
  @active_start_time=30000,
  @active_end_time=235959,
  @schedule_uid=N'b728c1ec-de6f-44bb-abee-dacc0049de00'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Happening

Upgrade from AX 2012 to Latest Dynamics 365 Finance and Operation

Below are the steps defined by sequence. 1. Create new Upgrade project in Dynamics LCS. 2. Create VSTS Project and connect it with L...

Trending now