SQL Server Opciones de configuración

Opciones de Configuración

Hoy iniciare escribiendo acerca de las opciones de configuración de Microsoft SQL Server, cabe mencionar que muchas de estas opciones no cambian, además de que muchas opciones se han agregado y otras se han eliminado a lo largo del tiempo, las opciones de configuración que aquí se trataran, tienen que ver con las características de desempeño en un ambiente de producción. Es importante indicar que, a mi punto de vista, deben homologarse las opciones de configuración de Microsoft SQL Server en los distintos ambientes, si bien el ambiente de desarrollo es muy diferente a un ambiente de producción, las opciones de configuración establecidas en el ambiente de desarrollo de forma similar a las opciones de producción, permiten verificar el rendimiento que se presentará en producción, ya que las condiciones serán muy similares.

Actualmente, Microsoft SQL Server versión 2017, presenta 77 opciones de configuración de los cuales, 54 son identificadas como opciones avanzadas y 23 son no avanzadas, asimismo, se presentan 60 opciones como de efecto dinámico, esto significa que el cambio de valor en la configuración toma efecto inmediato y 17 de las opciones, cuando el valor cambia, requieren que se reinicien los servicios de Microsoft SQL Server para que el cambio tenga efecto. Esta información es fácilmente obtenida ejecutando la siguiente consulta:

SELECT COUNT([name]) as Total_Options,

(SELECT COUNT(is_advanced) FROM sys.configurations WHERE is_advanced=1) as Total_Advanced,

(SELECT COUNT(is_dynamic) FROM sys.configurations WHERE is_dynamic=1) as Total_Dynamic

FROM sys.configurations;

Obteniéndose la siguiente tabla:

Total_Options	Total_Advanced	Total_Dynamic
77	54	60

Las opciones de configuración son las siguientes:

Nombre de opción	Descripción
access check cache bucket count	Cuenta predeterminada de cubo hash para la caché de seguridad de resultado de verificación de acceso
access check cache quota	Cuota predeterminada para la caché de seguridad de resultados de verificación de acceso
Ad Hoc Distributed Queries	Habilitar o deshabilitar consultas distribuidas ad hoc
affinity I/O mask	máscara de E / S de afinidad
affinity mask	máscara de afinidad
affinity64 I/O mask	máscara de E / S affinity64
affinity64 mask	máscara affinity64
Agent XPs	Habilitar o deshabilitar Agent XPs
allow polybase export	Permitir INSERT en una tabla externa de Hadoop
allow updates	Permitir actualizaciones a las tablas del sistema
automatic soft-NUMA disabled	Automatic soft-NUMA está habilitado por defecto
backup checksum default	Habilitar suma de comprobación de copias de seguridad de forma predeterminada
backup compression default	Habilitar la compresión de copias de seguridad de forma predeterminada
blocked process threshold (s)	Umbral de informe de proceso bloqueado
c2 audit mode	modo de auditoría c2
clr enabled	Ejecución de código de usuario CLR habilitada en el servidor
clr strict security	Seguridad estricta CLR habilitada en el servidor
common criteria compliance enabled	Modo de cumplimiento Common Criteria habilitado
contained database authentication	Permite bases de datos contenidas y autenticación contenida
cost threshold for parallelism	umbral de costo para el paralelismo
cross db ownership chaining	Permitir el encadenamiento de propiedad cross db
cursor threshold	umbral del cursor
Database Mail XPs	Habilitar o deshabilitar Database Mail XPs
default full-text language	lenguaje de texto completo predeterminado
default language	idioma predeterminado
default trace enabled	Habilitar o deshabilitar la traza predeterminada
disallow results from triggers	No permitir devolver resultados de desencadenantes
EKM provider enabled	Habilitar o deshabilitar el proveedor de EKM
external scripts enabled	Permite la ejecución de scripts externos
filestream access level	Establece el nivel de acceso de FILESTREAM
fill factor (%)	Porcentaje de factor de relleno predeterminado
ft crawl bandwidth (max)	Número máximo de almacenamientos intermedios de rastreo de texto completo
ft crawl bandwidth (min)	ancho de banda de rastreo ft (min)
ft notify bandwidth (max)	Cantidad máxima de almacenamientos intermedios de notificaciones de texto completo
ft notify bandwidth (min)	Cantidad de almacenamientos intermedios de notificaciones de texto completo reservados
hadoop connectivity	Configurar SQL Server para conectarse a fuentes de datos de blobs de almacenamiento Hadoop o Microsoft Azure externas a través de PolyBase
index create memory (KB)	Memoria para tipos de creación de índice (kBytes)
in-doubt xact resolution	Política de recuperación para transacciones de DTC con resultados desconocidos
lightweight pooling	El planificador de modo de usuario usa la agrupación liviana
locks	Número de bloqueos para todos los usuarios
max degree of parallelism	grado máximo de paralelismo
max full-text crawl range	Rangos máximos de rastreo permitidos en la indexación de texto completo
max server memory (MB)	tamaño máximo de la memoria del servidor (MB)
max text repl size (B)	Tamaño máximo de un campo de texto en la replicación.
max worker threads	Máximo de hilos de trabajo
media retention	Periodo de retención de cinta en días
min memory per query (KB)	memoria mínima por consulta (kBytes)
min server memory (MB)	Tamaño mínimo de la memoria del servidor (MB)
nested triggers	Permitir que se invoquen triggers dentro de los disparadores
network packet size (B)	Tamaño del paquete de red
Ole Automation Procedures	Habilitar o deshabilitar los procedimientos de automatización de Ole
open objects	Número de objetos de base de datos abiertos
optimize for ad hoc workloads	Cuando se establece esta opción, el tamaño del caché del plan se reduce aún más para la carga de trabajo OLTP adhoc de un solo uso.
PH timeout (s)	Tiempo de espera de conexión de base de datos para controlador (es) de protocolo de texto completo
polybase network encryption	Configurar SQL Server para cifrar los canales de control y de datos al usar PolyBase
precompute rank	Usar rango precalculado para la consulta de texto completo
priority boost	Aumento de prioridad
query governor cost limit	Costo estimado máximo permitido por el gobernador de consulta
query wait (s)	tiempo máximo para esperar memoria  de consulta (s)
recovery interval (min)	Intervalo máximo de recuperación en minutos
remote access	Permitir acceso remoto
remote admin connections	Se permiten conexiones de administrador dedicadas desde clientes remotos
remote data archive	Permitir el uso del acceso a datos REMOTE_DATA_ARCHIVE para bases de datos
remote login timeout (s)	tiempo de espera de inicio de sesión remoto
remote proc trans	Crear transacción DTC para procedimientos remotos
remote query timeout (s)	tiempo de espera de consulta remota
Replication XPs	Habilitar o deshabilitar la replicación de XP
scan for startup procs	escanear para los procedimientos almacenados de inicio
server trigger recursion	Permitir recursividad para desencadenantes de nivel de servidor
set working set size	establecer el tamaño del conjunto de trabajo
show advanced options	mostrar opciones avanzadas
SMO and DMO XPs	Habilitar o deshabilitar SMO y DMO XPs
transform noise words	Transforma palabras de ruido para consulta de texto completo
two digit year cutoff	corte de dos dígitos del año
user connections	Número de conexiones de usuario permitidas
user options	opciones de usuario
xp_cmdshell	Activar o desactivar el shell del comando

Es importante mencionar que muchas opciones de configuración se establecen con valores binarios, esto es, se habilita o deshabilita la opción, por omisión, muchas de estas opciones se establecen deshabilitadas y solo algunas están habilitadas, estos valores se establecen desde que se instala el producto, por lo que si es necesario se puede cambiar su valor. Las opciones que caen en esta categoría son un total de 37, las cuales son:

Opción de Configuración	Descripción	Valor por Omisión
Ad Hoc Distributed Queries	Habilitar o deshabilitar consultas distribuidas ad hoc	0
Agent XPs	Habilitar o deshabilitar Agent XPs	0
allow polybase export	Permitir INSERT en una tabla externa de Hadoop	0
allow updates	Permitir actualizaciones a las tablas del sistema	0
automatic soft-NUMA disabled	Automatic soft-NUMA está habilitado por defecto	0
backup checksum default	Habilitar suma de comprobación de copias de seguridad de forma predeterminada	0
backup compression default	Habilitar la compresión de copias de seguridad de forma predeterminada	0
c2 audit mode	modo de auditoría c2	0
clr enabled	Ejecución de código de usuario CLR habilitada en el servidor	0
clr strict security	Seguridad estricta CLR habilitada en el servidor	1
common criteria compliance enabled	Modo de cumplimiento Common Criteria habilitado	0
contained database authentication	Permite bases de datos contenidas y autenticación contenida	0
cross db ownership chaining	Permitir el encadenamiento de propiedad cross db	0
Database Mail XPs	Habilitar o deshabilitar Database Mail XPs	0
default trace enabled	Habilitar o deshabilitar la traza predeterminada	1
disallow results from triggers	No permitir devolver resultados de desencadenantes	0
EKM provider enabled	Habilitar o deshabilitar el proveedor de EKM	0
external scripts enabled	Permite la ejecución de scripts externos	1
lightweight pooling	El planificador de modo de usuario usa la agrupación liviana	0
nested triggers	Permitir que se invoquen triggers dentro de los disparadores	1
Ole Automation Procedures	Habilitar o deshabilitar los procedimientos de automatización de Ole	0
optimize for ad hoc workloads	Cuando se establece esta opción, el tamaño del caché del plan se reduce aún más para la carga de trabajo OLTP adhoc de un solo uso.	0
polybase network encryption	Configurar SQL Server para cifrar los canales de control y de datos al usar PolyBase	1
precompute rank	Usar rango precalculado para la consulta de texto completo	0
priority boost	Aumento de prioridad	0
remote access	Permitir acceso remoto	1
remote admin connections	Se permiten conexiones de administrador dedicadas desde clientes remotos	0
remote data archive	Permitir el uso del acceso a datos REMOTE_DATA_ARCHIVE para bases de datos	0
remote proc trans	Crear transacción DTC para procedimientos remotos	0
Replication XPs	Habilitar o deshabilitar la replicación de XP	0
scan for startup procs	escanear para los procedimientos almacenados de inicio	0
server trigger recursion	Permitir recursividad para desencadenantes de nivel de servidor	1
set working set size	establecer el tamaño del conjunto de trabajo	0
show advanced options	mostrar opciones avanzadas	0
SMO and DMO XPs	Habilitar o deshabilitar SMO y DMO XPs	1
transform noise words	Transforma palabras de ruido para consulta de texto completo	0
xp_cmdshell	Activar o desactivar el shell del comando	0

Ahora bien, de las 40 opciones de configuración que no se listaron anteriormente, algunas son relacionadas con las conexiones, otras afectan el rendimiento de las consultas y otras son para especificar el lenguaje que se maneja en el motor de base de datos.

Es importante indicar que las opciones de configuración que requieren que se reinicien los servicios del motor de base de datos son las siguientes:

Opción de Configuración	Descripción	Valor por Omisión
affinity I/O mask	máscara de E / S de afinidad	0
affinity64 I/O mask	máscara de E / S affinity64	0
automatic soft-NUMA disabled	Automatic soft-NUMA está habilitado por defecto	0
c2 audit mode	modo de auditoría c2	0
common criteria compliance enabled	Modo de cumplimiento Common Criteria habilitado	0
external scripts enabled	Permite la ejecución de scripts externos	1
fill factor (%)	Porcentaje de factor de relleno predeterminado	0
hadoop connectivity	Configurar SQL Server para conectarse a fuentes de datos de blobs de almacenamiento Hadoop o Microsoft Azure externas a través de PolyBase	0
lightweight pooling	El planificador de modo de usuario usa la agrupación liviana	0
locks	Número de bloqueos para todos los usuarios	0
open objects	Número de objetos de base de datos abiertos	0
polybase network encryption	Configurar SQL Server para cifrar los canales de control y de datos al usar PolyBase	1
priority boost	Aumento de prioridad	0
remote access	Permitir acceso remoto	1
scan for startup procs	escanear para los procedimientos almacenados de inicio	0
set working set size	establecer el tamaño del conjunto de trabajo	0
user connections	Número de conexiones de usuario permitidas	0

Estas opciones de configuración se pueden modificar en cualquier momento, sin embargo, el valor correspondiente solo se hará valido cuando se reinicien los servicios de Microsoft SQL Server.

En siguientes entregas estaré indicando las características de cada una de las opciones de configuración que se tienen, asimismo indicare cuando se trate de opciones que se han incluido en las distintas versiones de Microsoft SQL Server, por lo que no todas las opciones que se han indicado aquí son válidas en todas las versiones anteriores a Microsoft SQL Server 2017.

Conclusión

Se han indicado las opciones de configuración de Microsoft SQL Server, como se puede observar, estas se han desarrollado para modificar el comportamiento del motor de base de datos. Es importante indicar que se recomienda solo modificar las opciones cuando se conoce bien el uso y las características de operación que se asocian.

Particularmente, recomiendo que solo se modifiquen las opciones que pueden incidir de forma directa con el rendimiento de las consultas y posteriormente, después de haber analizado las consecuencias de otras opciones. Asimismo, recomiendo que se genere un laboratorio para validar el comportamiento del motor de base de datos, cuando se modifica algún valor y entonces generar el cambio ya probado en una instancia de producción, para mejorar el rendimiento.