El Blog de Juan Rafael

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