Recomendaciones para SQL Server con Dynamics NAV

Max Server Memory

MAX_SERVER_MEMORY = TOTAL_SERVER_MEMORY - [OS/Apps Memory] - [Threads * Thread Size]
TOTAL_SERVER_MEMORY = 24GB
OS/Apps Memory = 4 GB (systems with > 16GB of RAM)
Threads = 576
Thread Size = 2MB (x64)
Recomendado MAX_SERVER_MEMORY = 18 GB

Auto - Create Statistics

Se recomienda fuertemente activar "Auto Create Statistics" ya que es una de las formas que utiliza el propio SQL Server para mejorar el rendimiento. El coste para ejecutar esta opción es mínimo en comparación con el problema de rendimiento que puede experimentar si una estadística necesaria falta y SQL Server no la puede crear. Las estadísticas son un factor muy importante en el plan de consultas eficientes que viene con SQL Server.

Auto - Update Statistics

Se recomienda fuertemente activar "Auto Update Statistics" ya que es una de las formas que utiliza el propio SQL Server para mejorar el rendimiento. El coste para ejecutar esta opción es mínimo en comparación con el problema de rendimiento que puede experimentar si una estadística no está actualizada y SQL Server la utiliza para compilar la consulta y crear el plan de ejecución. Esto es muy importante con C/AL FINDFIRST,  FINDLAST, FIND('-'), FIND('+'), y  FINDSET para [Entrada No.] porque si la estadística en [N º Entrada] está fuera de fecha llevará a SQL Server más tiempo para encontrar el primer y último valor, bloqueando  el recurso durante mucho tiempo y causando un impacto negativo en la concurrencia del sistema y, posiblemente, resultando en tiempos muertos de bloqueo (Lock Time Outs).

Auto-Grow

Auto-Growths (Auto-crecimientos) de la base de datos o del registro de transacciones en entornos de producción puede degradar el rendimiento pues todas las transacciones deben hacer cola y esperar al SQL Server para hacer crecer el archivo de registro o de datos antes de que pueda comenzar a procesar las transacciones de nuevo. Esto puede crear un cuello de botella. Recomendamos fuertemente el crecimiento de los datos y del archivo de registro durante las horas pico entre un 10 a 25% del tamaño actual. No recomendamos desactivar Auto-Grow pues en casos de emergencia es mejor tenerlo activado que quedarse sin espacio y poner la base de datos fuera de servicio.

Trace Flag 4119

Si está ejecutando SQL Server 2005, le recomendamos que active el indicador de traza 4119 por razones de rendimiento. (Esto no es necesario en SQL 2008)

TempDB

El número de archivos de datos de TempDB debe ser igual al número de núcleos de CPU utilizada por SQL Server, hasta un máximo de 8. Esto evita los cuellos de botella en el proceso de asignación de página SGAM (Shared Global Allocation Map) cuando los nuevos objetos temporales son creados. Los archivos de TempDB deben estar en una unidad separada de los datos y los archivos de registro.

Esta recomendación viene dada ya que cada objeto temporal que se crea en TempDB necesita páginas asignadas para ello. Estas páginas deben ser asignados a través de páginas SGAM y sólo hay una página SGAM por archivo de datos (no realmente, pero lo suficientemente cerca). Así que si tienes un solo archivo de datos y se está creando una gran cantidad de objetos temporales, como es posible con SSRS y RCSI (u otras bases de datos en el servidor, ya que todos utilizan la misma TempDB) puede crearse un cuello de botella pues está tratando de manejar las asignaciones de muchas peticiones al mismo tiempo. Es por eso que existe la  recomendación de crear un archivo de datos TempDB por núcleo de la CPU o hilos activos para aliviar la posibilidad de un cuello de botella en la asignación. Esto es raro pero puede suceder.

NOTA: Cada archivo de TempDB no necesita estar en su propia unidad de disco, sólo tienen que estar separados de todos los datos de SQL y archivos de registro.

Disk Alignment

La alineación del disco puede tener un impacto significativo en el rendimiento del disco como un bloque puede abarcar dos discos físicos. Puesto que la configuración adecuada puede variar mucho según el fabricante, consulte a su fabricante de hardware para verificar la alineación del disco y la configuración adecuada. La asignación o el tamaño de banda debe ser> = 8k para evitar páginas rasgadas se recomienda 64k para que coincida con el tamaño de la extensión de SQL Server (8 - 8K páginas).

Para comprobar la alineación del disco:

  1. Click "Start" - "Run" and type MSINFO32
  2. Click "Components" - "Storage" - "Disks"
  3. Look at the value for "Partition Starting Offset" for the drive you wish to check

Si el resultado de dividir el valor de Partition Starting Offset por (64 * 1024) es un número entero entonces los discos están alineados en esa unidad, si por el contrario es un número decimal entonces los discos no están alineados.

NOTA: En todas las unidades formateadas con Windows Server 2008 o posteriores se alinearán automáticamente los discos.

Read Committed Snapshot Isolation (RCSI)

Con el lanzamiento de SQL Server 2005, un nuevo nivel de aislamiento se introdujo llamado Read Committed Snapshot. Este nivel de aislamiento imita el "Nivel 1" de lectura confirmada, que es el predeterminado de SQL Server, pero permite una mayor concurrencia y menos bloqueo ya que utiliza un principio de versiones en los registros bloqueados. Los procesos de lectura y escritura reciben diferentes "versiones" del registro para que no bloquee cada vez que mantiene la coherencia transaccional. Estas versiones de filas se ayudan del almacén de versiones que se mantiene en TempDB. Desde la perspectiva de Microsoft Dynamics NAV no es muy útil como NAV envía sugerencias de nivel de aislamiento con la mayoría de los estados que anularán RCSI, sino que tendrá un beneficio cuando las aplicaciones externas accedan a los registros en la base de datos de producción NAV tales como informes, opiniones, y aplicaciones secundarias . (SSRS, los procesos ETL, etc.)
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK_INMEDIATE
ALTER DATABASE DatabaseName
SET READ_COMMITTED_SNAPSHOT ON
GO
NOTA: TempDB debe ser configurado según las recomendaciones o la asignación de páginas pueden producir cuellos de botella.

Max Degree of Parallelism

Cuando SQL Server se ejecuta en un equipo con más de un microprocesador o CPU, detecta el mejor grado de paralelismo, es decir, el número de procesadores que se emplea para ejecutar una única instrucción en cada ejecución de planes en paralelo. Puede utilizar la opción max degree of parallelism para limitar el número de procesadores utilizados en la ejecución de planes paralelos. Para que el servidor pueda determinar el Grado máximo de paralelismo, establezca esta opción en 0, que es el valor predeterminado. Si se establece el grado máximo de paralelismo en 0, SQL Server puede utilizar todos los procesadores disponibles hasta un número de 64. Para suprimir la generación del plan paralelo, establezca max degree of parallelism en 1.

Microsoft Dynamics NAV se comunica con el servidor SQL a través de ODBC. El C / AL se convierte en simples declaraciones TSQL a través de la librería NDBCS.DLL. Estas declaraciones son muy simples Select, Update, Insert y Delete de SQL y no requieren de la ejecución en paralelo en SQL Server. Esto no impide que el compilador a veces trate de utilizar la ejecución en paralelo en la planificación de las consultas y esto, a veces, puede afectar negativamente al rendimiento. Es por eso que se recomienda establecer el  grado máximo de paralelismo en SQL Server en 1 para evitarlo.
sp_configure 'max degree of parallelism', 1
RECONFIGURE
GO
NOTA: Si está ejecutando los trabajos de mantenimiento de SQL Server Enterprise Edition estos son multi-hilo, pero si usted pone como grado máximo de paralelismo 1 estos se ejecutarán en un solo subproceso. Le recomendamos que en el trabajo de mantenimiento regrese el grado máximo de paralelismo a 0 y al ejecutar el trabajo de mantenimiento volver a 1. Esto se puede hacer con el código de TSQL dentro del propio trabajo de mantenimiento.

Dynamics NAV Default Isolation Level

Con el lanzamiento de Microsoft Dynamics NAV 5.00 SP1 (Build 30482 / KB 979135) y posteriores tenemos la opción de cambiar el nivel de aislamiento predeterminado de NAV de SERIALIZABLE a REPEATABLEREAD.

SERIALIZABLE: Este es el nivel de aislamiento más restrictivo. Cuando se utiliza, los valores fantasmas no pueden ocurrir pues impide que otros usuarios actualicen o inserten filas en el conjunto de datos hasta que la transacción se complete.

REPEATABLEREAD: Cuando se utiliza, las lecturas ‘sucias’ y no repetibles no pueden ocurrir. Esto significa que los bloqueos se colocarán en todos los datos que se utilizan en una consulta y otra transacción no puede actualizarlos. En resumen SERIALIZABLE es más restrictiva y bloquea más registros que REPEATABLEREAD que puede causar más tiempos de espera de bloqueos y desbloqueos. REPEATABLEREAD es menos restrictiva y permite una mayor concurrencia en general.

Configuración por defecto del nivel de aislamiento en NAV de SERIALIZABLE a REPEATABLEREAD
UPDATE [$ndo$dbproperty] SET diagnostics = 4194304

Microsoft Dynamics NAV "Lock Timeout"

Este ajuste significa básicamente que si un usuario intenta acceder a un recurso y está bloqueado por otro usuario espere los segundos que usted ha especificado y vuelva a intentarlo antes de emitir un mensaje de error. Le recomendamos que esta opción no se utilice en absoluto o que se establezca entre unos 10 a 20 segundos, un valor muy alto para el bloqueo estático de tiempo de espera puede causar problemas. Por lo general, cuando un cliente tiene un gran valor que para esto es porque hay un problema de rendimiento subyacente. Esto realmente depende de cada configuración y puede ser mayor si hay circunstancias específicas.

Para cambiar el valor por defecto en Microsoft Dynamics NAV, click File, click Database, y luego click Alter para abrir la ventana de Alter Database. Click en la pestaña Advanced y marque o desmarque la casilla de Lock timeout field.

Microsoft Dynamics NAV "Always Rowlock"

Microsoft Dynamics NAV siempre bloquea las filas por una compatibilidad con sistemas de 32 bits y sobre SQL Server 2000. En el 99% de los casos en SQL Server 2005 y posteriores esto se puede desactivar liberando la memoria de bloqueo que SQL puede utilizar para otros fines sin ninguna pérdida de rendimiento.

NOTA: En algunas pruebas de rendimiento sin tener esta opción habilitada la concurrencia y el rendimiento aumentaron.

Maintenance Jobs

Statistics

Incluso con la opción de actualizar estadísticas automáticamente activada se recomienda ejecutar un trabajo de mantenimiento periódico para actualizar las mismas. Esto se debe a que la actualización automática de estadísticas sólo se activa cuando el 5% de los datos de esta estadística ha cambiado y en las tablas como Value Entry, Item Ledger Entry, G/L Entry, y aquellas que pueden tener entre los 10 de millones de registros un pequeño porcentaje de los datos en una estadística dada como [N º de entrada] puede cambiar con un efecto significativo sobre la distribución general de los datos de esta estadística causando planes de ejecución de consultas ineficientes mucho antes de que el umbral del 5% se alcance provocando como resultado un rendimiento degradado. Se recomienda el uso de sp_updatestats para realizar esta tarea, ya que sólo se actualizarán las estadísticas donde los datos se han cambiado. Le recomendamos la creación de un trabajo de SQL que se ejecute diaria o semanal mente (dependiendo del volumen de transacciones) en las horas pico para actualizar todas las estadísticas donde los datos han cambiado.

NOTA: No use "FULLSCAN", ya que es una pérdida de tiempo pues se sobrescribirá en el momento en que la estadística se auto actualiza.
USE AdventureWorks2008R2;
GO
EXEC sp_updatestats

Index defragmentation

Los índices se fragmentan al igual que los discos duros y pueden tener un impacto negativo en el rendimiento general. Se recomienda utilizar la siguiente secuencia de comandos que reconstruye y reorganiza las funciones de desfragmentación de índices. Le recomendamos la creación de un trabajo de SQL que se ejecute diaria o semanalmente (dependiendo del volumen de transacciones) durante las horas pico para actualizar todos los índices.

Instant File initialization

En SQL Server 2005 y versiones posteriores, los archivos de datos se pueden inicializar de forma instantánea, lo que permite la ejecución rápida de la base de datos o grupo de archivos de restauración. La inicialización instantánea de archivos utiliza espacio de disco sin necesidad de rellenar ese espacio con ceros. En cambio, el contenido del disco se sobrescribe según los nuevos datos se escriben en los archivos. El registro del archivo de inicialización aún requiere rellenarse con ceros pero esto sucede en paralelo con la transferencia de los datos de la copia de seguridad. El siguiente paso de la restauración no se iniciará hasta que todos los datos han sido transferidos y todo el registro se ha inicializado.

Para utilizar la inicialización instantánea de archivos se debe ejecutar el servicio MSSQLSERVER en una cuenta de Windows y asignar el privilegio especial de Windows SE_MANAGE_VOLUME_NAME a dicha cuenta. Este privilegio se asigna al grupo de administradores de Windows por defecto. Si tiene derechos de administrador del sistema puede asignar este privilegio mediante la adición de la cuenta de Windows a la política de seguridad para Realizar tareas de mantenimiento del volumen.

"Page Verify" - Checksum vs. Torn Page Detection

Se recomienda para verificar la paginación de la base de datos (Database Page Verify) el uso de la opción suma de comprobación (Checksum) pues es mucho más robusto en la detección de la corrupción de base de datos física.
ALTER DATABASE DatabaseName
SET PAGE_VERIFY CHECKSUM;
GO

Lock Pages in Memory

Le recomendamos activar la opción para bloquear páginas en memoria y de esta forma el sistema operativo no interfiere en la página de SQL Server. Sin embargo en 64 bits sólo tendrá que conceder el derecho de bloqueo de páginas en memoria a la cuenta de SQL para que SQL Server pueda utilizar esta característica.

Comentarios