Hola a todos.
Aquí les quiero compartir algunas recomendaciones en tareas de administración para un SQL Server en Failover Cluster que tengamos con ERP – MS Dynamics AX 2012 R2.
Como sabemos, la correcta configuración y administración de SQL Server nos apoyara en temas de rendimiento de nuestro ERP – MS Dynamics AX 2012 R2.
PS de Mantenimiento de Indices con SQL Enterprise 2012.
USE [MicrosoftDynamicsAX]
GO
/****** Object: StoredProcedure [dbo].[SP_INDEX_MAINTENANCE_ENTERPRISE] Script Date: 13/09/2016 0:02:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_INDEX_MAINTENANCE_ENTERPRISE]
AS
DECLARE @RowThreshold INT
DECLARE @PercentThreshold DECIMAL(38,10)
/***********************************************************************************************************************************
*************SET PARAMETERS*********************************************************************************************************/
SET @RowThreshold = 1000 –Threshold for the number of rows. In the number of rows in the index is less that the threshold
–then it will skip it completely.
SET @PercentThreshold = 50 –FOR THIS SCRIPT 50 = 50%
–Fragmentation threshold percentage. If greater than or equal to this amount them REBUILD the Index
–If less than this amount REORGANIZE the index
/************SET PARAMETERS*********************************************************************************************************
************************************************************************************************************************************/
SET NOCOUNT ON
–INDEXES THAT CAN BE REBUILT ONLINE
DECLARE @tablename SYSNAME;
DECLARE @index_name SYSNAME;
DECLARE @percentfrag DECIMAL(38,10);
DECLARE @command VARCHAR(8000);
DECLARE @command2 VARCHAR(8000);
DECLARE Index_Maint CURSOR FOR
SELECT
o.name,
i.name,
f.avg_fragmentation_in_percent
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL , NULL, ‘LIMITED’) f
JOIN sys.objects o on o.object_id = f.object_id
JOIN sys.indexes i on i.object_id = f.object_id and i.index_id = f.index_depth
WHERE f.index_id > 0
AND NOT EXISTS ( –eliminate indexes that can not be rebuilt ONLINE
SELECT 1 FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
AND ((c.system_type_id IN (34,35,99,241)) — image, text, ntext, xml
OR (c.system_type_id IN (167,231,165) — varchar, nvarchar, varbinary
AND max_length = -1))
WHERE ic.object_id = I.object_id
AND ic.index_id = I.index_id
)
OPEN Index_Maint
FETCH NEXT FROM Index_Maint INTO
@tablename, @index_name, @percentfrag
While @@FETCH_STATUS = 0
BEGIN
IF @percentfrag >= @PercentThreshold
BEGIN
SELECT @command2 = ‘ALTER INDEX ‘ + ‘[‘ + @index_name + ‘]’ + ‘ ON ‘ + ‘[‘ + @tablename + ‘]’ + ‘ REBUILD WITH(MAXDOP=0, ONLINE=ON)’; –Take advantage of parallelism and ONLINE INDEX REBUILDS
EXEC (@command2);
PRINT ‘Executed ‘ + @command2;
END;
ELSE
BEGIN
SELECT @command = ‘ALTER INDEX ‘ + ‘[‘ + @index_name + ‘]’ + ‘ ON ‘ + ‘[‘ + @tablename + ‘]’ + ‘ REORGANIZE’;
EXEC (@command);
PRINT ‘Executed ‘ + @command;
END;
FETCH NEXT FROM Index_Maint INTO
@tablename, @index_name, @percentfrag
END
CLOSE Index_Maint
DEALLOCATE Index_Maint
–INDEXES THAT CANNOT BE REBUILT ONLINE
DECLARE @tablename2 SYSNAME;
DECLARE @index_name2 SYSNAME;
DECLARE @percentfrag2 DECIMAL(38,10);
DECLARE @command3 VARCHAR(8000);
DECLARE @command2b VARCHAR(8000);
DECLARE Index_Maint2 CURSOR FOR
SELECT
o.name,
i.name,
f.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’) f
JOIN sys.objects o on o.object_id = f.object_id
JOIN sys.indexes i on i.object_id = f.object_id and i.index_id = f.index_depth
WHERE f.index_id > 0
AND EXISTS ( –Select only indexes that CANNOT be rebuilt ONLINE
SELECT 1 FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
AND ((c.system_type_id IN (34,35,99,241)) — image, text, ntext, xml
OR (c.system_type_id IN (167,231,165) — varchar, nvarchar, varbinary
AND max_length = -1))
WHERE ic.object_id = I.object_id
AND ic.index_id = I.index_id
)
OPEN Index_Maint2
FETCH NEXT FROM Index_Maint2 INTO
@tablename2, @index_name2, @percentfrag2
While @@FETCH_STATUS = 0
BEGIN
IF @percentfrag2 >= @PercentThreshold
BEGIN
SELECT @command2b = ‘ALTER INDEX ‘ + ‘[‘ + @index_name2 + ‘]’ + ‘ ON ‘ + ‘[‘ + @tablename2 + ‘]’ + ‘ REBUILD WITH(MAXDOP=0)’; –Take advantage of parallelism and ONLINE INDEX REBUILDS
EXEC (@command2b);
–PRINT ‘Executed ‘ + @command2b;
END;
ELSE
BEGIN
SELECT @command3 = ‘ALTER INDEX ‘ + ‘[‘ + @index_name2 + ‘]’ + ‘ ON ‘ + ‘[‘ + @tablename2 + ‘]’ + ‘ REORGANIZE’;
EXEC (@command3);
–PRINT ‘Executed ‘ + @command3;
END;
FETCH NEXT FROM Index_Maint2 INTO
@tablename2, @index_name2, @percentfrag2
END
CLOSE Index_Maint2
DEALLOCATE Index_Maint2
SP De Mantenimiento de STATISTICS
USE [MicrosoftDynamicsAX]
GO
/****** Object: StoredProcedure [dbo].[SP_STATISTICS_MAINTENANCE] Script Date: 13/09/2016 0:11:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[SP_STATISTICS_MAINTENANCE]
as
DECLARE @MAXDOP INT
CREATE TABLE #Config(
name VARCHAR(250),
minimum INT,
maximum INT,
config_value INT,
run_value INT
)
INSERT #Config
(name,minimum,maximum,config_value,run_value)
EXEC SP_CONFIGURE
SET @MAXDOP = (SELECT config_value FROM #Config WHERE name = ‘max degree of parallelism’)
EXEC SP_CONFIGURE ‘max degree of parallelism’, 0
RECONFIGURE WITH OVERRIDE
EXEC SP_UPDATESTATS
EXEC SP_CONFIGURE ‘max degree of parallelism’, @MAXDOP
RECONFIGURE WITH OVERRIDE
DROP TABLE #Config
Espero que les sea de utilidad
