Cómo ajustar Microsoft SQL Server para el rendimiento

Para retener a sus usuarios, cualquier aplicación o sitio web debe funcionar rápido. Para entornos de misión crítica, un retraso de un par de milisegundos en la obtención de información puede crear grandes problemas. A medida que el tamaño de la base de datos crece día a día, necesitamos recuperar los datos lo más rápido posible y volver a escribirlos en la base de datos lo más rápido posible. Para asegurarnos de que todas las operaciones se ejecuten sin problemas, debemos ajustar el rendimiento de nuestro servidor de base de datos.

En este artículo, describiré un procedimiento paso a paso para ajustar el rendimiento básico en uno de los principales servidores de bases de datos del mercado: Microsoft SQL Server (SQL Server, para abreviar).

#1 Encontrar a los culpables

Al igual que con cualquier otro software, debemos comprender que SQL Server es un programa informático complejo. Si tenemos un problema con él, debemos descubrir por qué no funciona como esperábamos.

Desde SQL Server necesitamos extraer y enviar datos de la forma más rápida y precisa posible. Si hay problemas, un par de razones básicas y las dos primeras cosas que debe verificar son:

• La configuración del hardware y la instalación, que puede ser necesario corregir ya que las necesidades de SQL Server son específicas
• Si hemos proporcionado el código T-SQL correcto para que SQL Server lo implemente

Aunque SQL Server es un software propietario, Microsoft ha proporcionado muchas formas de entenderlo y usarlo de manera eficiente.

Si el hardware está bien y la instalación se ha realizado correctamente, pero SQL Server sigue funcionando lentamente, primero debemos averiguar si hay algún error relacionado con el software. Para verificar lo que está sucediendo, debemos observar cómo se están desempeñando los diferentes subprocesos. Esto se logra calculando las estadísticas de espera de diferentes subprocesos. El servidor SQL usa subprocesos para cada solicitud de usuario, y el subproceso no es más que otro programa dentro de nuestro complejo programa llamado SQL Server. Es importante tener en cuenta que este subproceso no es un subproceso del sistema operativo en el que está instalado el servidor SQL; está relacionado con el subproceso SQLOS, que es un pseudo sistema operativo para SQL Server.

Las estadísticas de espera se pueden calcular mediante sys.dm_os_wait_stats Dynamic Management View (DMV), que brinda información adicional sobre su estado actual. Hay muchos scripts en línea para consultar esta vista, pero mi favorito es el script de Paul Randal porque es fácil de entender y tiene todos los parámetros importantes para observar las estadísticas de espera:

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

Cuando ejecutamos este script, debemos concentrarnos en las filas superiores del resultado porque se configuran primero y representan el tipo de espera máxima.

Necesitamos comprender los tipos de espera para poder tomar las decisiones correctas. Para conocer los diferentes tipos de espera, podemos acudir a la excelente documentación de Microsoft.

Tomemos un ejemplo en el que tenemos demasiado PAGEIOLATCH_XX . Esto significa que un subproceso está esperando lecturas de páginas de datos del disco al búfer, que no es más que un bloque de memoria. Debemos estar seguros de que entendemos lo que está pasando. Esto no significa necesariamente un subsistema de E/S deficiente o memoria insuficiente, y aumentar el subsistema de E/S y la memoria resolverá el problema, pero solo temporalmente. Para encontrar una solución permanente, necesitamos ver por qué se leen tantos datos del disco: ¿Qué tipos de comandos SQL están causando esto? ¿Estamos leyendo demasiados datos en lugar de leer menos datos usando filtros, como las cláusulas where ? ¿Están ocurriendo demasiadas lecturas de datos debido a los escaneos de tablas o escaneos de índices? ¿Podemos convertirlos en búsquedas de índice implementando o modificando los índices existentes? ¿Estamos escribiendo consultas SQL que SQL Optimizer (otro programa dentro de nuestro programa de servidor SQL) no comprende?

Necesitamos pensar desde diferentes ángulos y usar diferentes casos de prueba para encontrar soluciones. Cada uno de los tipos de espera anteriores necesita una solución diferente. Un administrador de base de datos debe investigarlos a fondo antes de tomar cualquier medida. Pero la mayoría de las veces, encontrar consultas T-SQL problemáticas y ajustarlas resolverá del 60 al 70 por ciento de los problemas.

#2 Encontrar consultas problemáticas

Como se mencionó anteriormente, lo primero que podemos hacer es buscar consultas problemáticas. El siguiente código T-SQL encontrará las 20 consultas con peor rendimiento:

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

Tenemos que tener cuidado con los resultados; aunque una consulta puede tener un tiempo de ejecución promedio máximo, si se ejecuta solo una vez, el efecto total en el servidor es bajo en comparación con una consulta que tiene un tiempo de ejecución promedio medio y se ejecuta muchas veces en un día.

#3 Consultas de ajuste fino

El ajuste fino de una consulta T-SQL es un concepto importante. Lo fundamental que hay que entender es qué tan bien podemos escribir consultas T-SQL e implementar índices, de modo que el optimizador de SQL pueda encontrar un plan optimizado para hacer lo que queríamos que hiciera. Con cada nueva versión de SQL Server, obtenemos un optimizador más sofisticado que cubrirá nuestros errores al escribir consultas SQL no optimizadas y también corregirá cualquier error relacionado con el optimizador anterior. Pero, no importa cuán inteligente pueda ser el optimizador, si no podemos decirle lo que queremos (escribiendo consultas T-SQL adecuadas), el optimizador SQL no podrá hacer su trabajo.

SQL Server utiliza algoritmos avanzados de búsqueda y clasificación. Si somos buenos en los algoritmos de búsqueda y clasificación, la mayoría de las veces podemos adivinar por qué SQL Server está tomando una acción particular. El mejor libro para aprender más y comprender tales algoritmos es The Art of Computer Programming de Donald Knuth.

Cuando examinamos las consultas que deben ajustarse, debemos usar el plan de ejecución de esas consultas para que podamos averiguar cómo las interpreta el servidor SQL.

No puedo cubrir todos los aspectos del plan de ejecución aquí, pero en un nivel básico puedo explicar las cosas que debemos considerar.

• Primero, debemos averiguar qué operadores se llevan la mayor parte del costo de la consulta.
• Si el operador está asumiendo muchos costos, debemos saber por qué. La mayoría de las veces, los escaneos costarán más de lo que buscan. Necesitamos examinar por qué se está realizando una exploración en particular (exploración de tabla o exploración de índice) en lugar de una búsqueda de índice. Podemos resolver este problema implementando índices adecuados en las columnas de la tabla, pero como ocurre con cualquier programa complejo, no existe una solución fija. Por ejemplo, si la tabla es pequeña, los escaneos son más rápidos que las búsquedas.
• Hay aproximadamente 78 operadores, que representan las diversas acciones y decisiones del plan de ejecución de SQL Server. Necesitamos estudiarlos en profundidad consultando la documentación de Microsoft, para que podamos entenderlos mejor y tomar las medidas adecuadas.

#4 Reutilización del plan de ejecución

Incluso si implementamos índices adecuados en las tablas y escribimos un buen código T-SQL, si no se reutiliza el plan de ejecución, tendremos problemas de rendimiento. Después de ajustar las consultas, debemos asegurarnos de que el plan de ejecución pueda reutilizarse cuando sea necesario. La mayor parte del tiempo de la CPU se dedicará a calcular el plan de ejecución que puede eliminarse si reutilizamos el plan.

Podemos usar la consulta a continuación para averiguar cuántas veces se reutiliza el plan de ejecución, donde usecounts representa cuántas veces se reutiliza el plan:

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

La mejor manera de reutilizar el plan de ejecución es implementando procedimientos almacenados parametrizados. Cuando no estamos en condiciones de implementar procedimientos almacenados, podemos usar sp_executesql , que se puede usar en su lugar para ejecutar declaraciones T-SQL cuando el único cambio en las declaraciones SQL son los valores de los parámetros. Lo más probable es que SQL Server reutilice el plan de ejecución que generó en la primera ejecución.

Una vez más, como ocurre con cualquier programa informático complejo, no existe una solución fija. A veces es mejor compilar el plan nuevamente.

Examinemos las siguientes dos consultas de ejemplo:

• select name from table where name = 'sri';
• select name from table where name = 'pal';

Supongamos que tenemos un índice no agrupado en la columna de name y la mitad de la tabla tiene el valor sri y pocas filas tienen pal en la columna de name . Para la primera consulta, SQL Server usará la exploración de la tabla porque la mitad de la tabla tiene los mismos valores. Pero para la segunda consulta, es mejor usar el escaneo de índice porque solo unas pocas filas tienen valor pal .

Aunque las consultas son similares, el mismo plan de ejecución puede no ser una buena solución. La mayoría de las veces será un caso diferente, por lo que debemos analizar todo cuidadosamente antes de decidir. Si no queremos reutilizar el plan de ejecución, siempre podemos usar la opción “recompilar” en los procedimientos almacenados.

Tenga en cuenta que incluso después de usar procedimientos almacenados o sp_executesql , hay ocasiones en las que el plan de ejecución no se reutilizará. Ellos son:

• Cuando los índices utilizados por la consulta cambian o se eliminan
• Cuando cambian las estadísticas, la estructura o el esquema de una tabla utilizada por la consulta
• Cuando usamos la opción “recompilar”
• Cuando hay un gran número de inserciones, actualizaciones o eliminaciones
• Cuando mezclamos DDL y DML en una sola consulta

#5 Eliminación de índices innecesarios

Después de ajustar las consultas, debemos verificar cómo se usan los índices. El mantenimiento de índices requiere mucha CPU y E/S. Cada vez que insertamos datos en una base de datos, SQL Server también necesita actualizar los índices, por lo que es mejor eliminarlos si no se utilizan.

El servidor SQL nos proporciona dm_db_index_usage_stats DMV para encontrar estadísticas de índice. Cuando ejecutamos el código T-SQL a continuación, obtenemos estadísticas de uso para diferentes índices. Si encontramos índices que no se usan en absoluto, o que se usan con poca frecuencia, podemos eliminarlos para mejorar el rendimiento.

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

#6 Instalación de SQL Server y configuración de la base de datos

Al configurar una base de datos, debemos mantener los datos y los archivos de registro por separado. La razón principal de esto es que escribir y acceder a los archivos de datos no es secuencial, mientras que escribir y acceder a los archivos de registro es secuencial. Si los ponemos en el mismo disco, no podemos usarlos de forma optimizada.

Cuando compramos Storage Area Network (SAN), un proveedor puede darnos algunas recomendaciones sobre cómo configurarlo, pero esta información no siempre es útil. Necesitamos tener una discusión detallada con nuestros chicos de hardware y redes sobre cómo mantener los datos y los archivos de registro por separado y de manera optimizada.

#7 No sobrecargue SQL Server

La tarea principal de cualquier administrador de base de datos es asegurarse de que el servidor de producción funcione sin problemas y sirva a los clientes lo mejor posible. Para que esto suceda, necesitamos mantener bases de datos separadas (si es posible, en máquinas separadas) para los siguientes entornos:

• Producción
• Desarrollo
• Pruebas
• Analítico

Para una base de datos de producción necesitamos una base de datos con modo de recuperación completo, y para otras bases de datos, un modo de recuperación simple es suficiente.

La prueba en una base de datos de producción pondrá mucha carga en el registro de transacciones, índices, CPU y E/S. Es por eso que necesitamos usar bases de datos separadas para producción, desarrollo, prueba y análisis. Si es posible, use máquinas separadas para cada base de datos, ya que disminuirá la carga en la CPU y la E/S.

#8 Registro de transacciones, tempdb y memoria

El archivo de registro debe tener suficiente espacio libre para las operaciones normales porque una operación de crecimiento automático en un archivo de registro requiere mucho tiempo y podría obligar a otras operaciones a esperar hasta que se complete. Para averiguar el tamaño del archivo de registro para cada base de datos y cuánto se usa, podemos usar DBCC SQLPERF(logspace) .

La mejor manera de configurar tempdb es ponerlo en un disco separado. Necesitamos mantener el tamaño inicial tan grande como podamos porque cuando llegue a una situación de crecimiento automático, el rendimiento disminuirá.

Como se mencionó anteriormente, debemos asegurarnos de que el servidor SQL se ejecute en una máquina separada, preferiblemente una sin ninguna otra aplicación. Necesitamos mantener algo de memoria para el sistema operativo, y algo más si es parte de un clúster, por lo que en la mayoría de los casos, alrededor de 2 GB deberían ser suficientes.

Conclusión:

Los procedimientos y sugerencias discutidos aquí son solo para el ajuste de rendimiento básico. Si seguimos estos pasos, podemos, en promedio, obtener una mejora del 40 al 50 por ciento en el rendimiento. Para realizar un ajuste avanzado del rendimiento de SQL Server, tendríamos que profundizar mucho más en cada uno de los pasos que se tratan aquí.

Lecturas adicionales en el blog de ingeniería de Toptal:

• Solucionar cuellos de botella con índices SQL y particiones
• Guía de migración de Oracle a SQL Server y de SQL Server a Oracle