Tareas de Mantenimiento de SQL Server (Failover Cluster) con MS Dynamics AX 2012 R2

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

Primero reunión FY17–SQL PASS LIMA

Hola a todos.

Para este FY17 tenemos grandes planes a ejecutar como comunidad PASS de LIMA – PERU, entre ellas tendremos próximos eventos sobre SQL Server 2016 y apertura de hasta 2 cursos online sobre SQL Server 2016 (para certificación Sonrisa) y sobre MS Dynamics CRM.

Agradezco mucho al team que conforman el core de SQL PASS LIMA, Cecilia Gonzales, Frank Chambillo, MVP Nicolas Nakasone, Alberto de la Rossi y  Jorge Muchaypiña.

20160907_212445

20160907_212440

Saludos,

ExpoTIC 2016–EL poder de Microsoft HoloLens

Hola a todos.

En el ultimo ExpoTIC 2016,  la empresa donde laboro tuvo la oportunidad de participar como una de las empresas innovadoras en tecnologia y dimos una charla sobre “Construir un Imperio sin morir en el intento” , donde mostramos el poder de Microsoft HoloLens.

 

image

 

 

Previo al evento en el Expotic 2016, tuvimos una sesion interna donde pudimos ver las capacidades del HoloLens para el desarrollo de software y entrenamiento.

20160817_155149

20160817_155236

Saludos,

Participación en el II CORESIC 2016

Hola a todos.

Después de varios meses vuelvo a escribir en mi blog, realmente ha sido un año muy retador con mucho trabajo y eventos de comunidad que he venido desarrollando y otros proyectos por ejecutar aun mas interesantes.

Bueno detallando el titulo del post, bueno en esta ocasión tendré la oportunidad de participar en el segundo CORESIC en la Merced – Perú como ponente hablando sobre mi pasión MS Dynamics AX y SQL Server.

image

Detalles de todas las sesiones y demás de este evento provincial en la siguiente liga http://www.coresic.net/

 

image

image

Ahí nos vemos.

Saludos,