SQL Server

01
Mar
2024

Configurando el paralelismo en SQL Server: Guía Completa Parte 2

En este artículo, vamos a explicar cómo configurar el paralelismo en SQL Server, qué opciones tienes a tu disposición y qué factores debes tener en cuenta para optimizar el rendimiento de tus consultas. Para poder hacer esto es muy importante tener las bases teóricas sobre el paralelismo, coste de consultas y planes paralelos que vimos en el pasado artículo. Si aún no lo has leído, te recomiendo encarecidamente que pares aquí, vayas a leerlo y luego continúes con este.

Configuración del paralelismo en SQL Server

Ahora que ya todos estamos en el mismo punto y entremos en materia. SQL Server dispone de varios parámetros que nos permiten controlar el comportamiento del paralelismo. Estos parámetros se pueden establecer a nivel de servidor, de base de datos o de consulta. Estos parámetros son principalmente dos, el nivel de coste y el máximo grado de paralelismo.

Cost Threshold for Parallelism 

Este parámetro define el umbral de coste estimado a partir del cual SQL Server considera usar el paralelismo para ejecutar una consulta. El coste estimado es una medida relativa que depende de varios factores, como el tamaño de los datos, el tipo de operación, el índice utilizado, etc. El valor por defecto de este parámetro es 5, lo que significa que SQL Server usará el paralelismo para las consultas que tengan un coste estimado mayor o igual a 5.

Este valor se puede modificar a nivel de servidor con el comando sp_configure o con el asistente de configuración de SQL Server. Un valor demasiado bajo puede provocar que SQL Server use el paralelismo para consultas que no lo necesitan, consumiendo recursos innecesariamente. Un valor demasiado alto puede provocar que SQL Server no use el paralelismo para consultas que podrían beneficiarse de él, perdiendo rendimiento. Por eso, es recomendable ajustar este valor según el tipo de consultas que se ejecuten en el servidor y el rendimiento que se quiera obtener.

Max Degree of Parallelism

Este parámetro define el número máximo de procesadores o núcleos que SQL Server puede usar para ejecutar una consulta en paralelo. El valor por defecto de este parámetro es 0, lo que significa que SQL Server puede usar todos los procesadores o núcleos disponibles en el servidor. Este valor se puede modificar a nivel de servidor con el comando sp_configure o con el asistente de configuración de SQL Server. También se puede modificar a nivel de base de datos con el comando ALTER DATABASE o a nivel de consulta con la opción MAXDOP.

Hay que mencionar aquí que el paralelismo en SQL Server es si o no, si una consulta cumple con los requisitos del umbral de paralelismo usará todos los núcleos configurados para paralelizar. No hay opción intermedia. Un valor demasiado alto puede provocar que SQL Server use más procesadores o núcleos de los necesarios, generando una sobrecarga de coordinación y reduciendo el rendimiento. Un valor demasiado bajo puede provocar que SQL Server use menos procesadores o núcleos de los disponibles, desaprovechando los recursos y limitando el rendimiento. Por eso, es recomendable ajustar este valor según la capacidad del servidor y la complejidad de las consultas que normalmente se ejecutan.

¿Cómo configurar el paralelismo en SQL Server?

Como ya hemos comentado, el paralelismo en SQL Server se puede configurar a varios niveles: a nivel de servidor, a nivel de base de datos y a nivel de consulta. Cada nivel tiene sus propias opciones y parámetros que afectan al comportamiento del paralelismo. A continuación, te explicamos cada uno de ellos y cómo puedes modificarlos.

MAXDOP a nivel de servidor

Para configurar MAXDOP a nivel de servidor, tenemos las dos opciones que hemos visto antes para controlar el paralelismo: el grado máximo de paralelismo (MAXDOP del inglés MAX Degree Of Parallelism) y el umbral de coste para el paralelismo (cost threshold for parallelism).

Podemos modificar el valor del MAXDOP y el umbral de coste de paralismo mediante el comando sp_configure o mediante las opciones disponibles en las configuraciones de la instancia en SSMS.

MAXDOP a nivel de base de datos

A nivel de base de datos, hay una opción que puedes configurar para controlar el paralelismo: el grado máximo de paralelismo (MAXDOP) por base de datos. Esta opción te permite especificar un valor diferente del MAXDOP para cada base de datos, lo que te da más flexibilidad para adaptar el paralelismo a las características de cada base de datos. El valor por defecto es 0, lo que significa que se utiliza el valor del MAXDOP a nivel de servidor. Puedes modificar el valor del MAXDOP por base de datos mediante el comando ALTER DATABASE o mediante las opciones en las propiedades de la base de base de datos en SSMS.

MAXDOP a nivel de consulta

A nivel de consulta, podemos usar la opción de consulta OPTION (MAXDOP) para controlar el paralelismo. La opción de consulta OPTION (MAXDOP) te permite especificar un valor diferente del MAXDOP para cada consulta, lo que te da más control para optimizar el rendimiento de cada consulta. El valor por defecto es 0, lo que significa que se utiliza el valor del MAXDOP a nivel de base de datos o a nivel de servidor. Puedes modificar el valor de la opción de consulta OPTION (MAXDOP) mediante la cláusula OPTION al final de la consulta.

Otras formas de modificar el MAXDOP

Existen otros métodos además de los principales que hemos comentado para controlar el nivel de paralelismo como vamos a ver a continuación:

  • Establecer un umbral de coste de paralelismo demasiado alto: de esta manera nos garantizamos de que casi ninguna consulta se va a paralelizar.
  • Traza 8649 tanto a nivel de consulta como de servidor: Porque en ningún momento os había dicho que estas formas eran buenas ideas, podemos usar esta traza para que TODAS las consultas (que se pueda) se ejecuten con un plan de ejecución paralelo.
  • Usar inhibidores de paralelismo: Ya vimos en el anterior artículo que hay varias razones por las que una consulta no va a paralelizar.
  • Usar inhibidores en columnas calculadas: Si nuestra tabla tiene una columna calculada que usa una función escalar, como es un inhibidor de paralelismo ninguna consulta sobre esa tabla se paralelizará. Ni siquiera los chequeos de integridad o las reconstrucciones de índices.
  • Opcion MAXDOP en indices: A la hora de crear, reorganizar o reconstruir un índice podremos usar el hint MAXDOP para establecer un nivel de paralelismo diferente al del servidor.
  • Resource Governor: Con resource governor podremos establecer distintas configuraciones de MAXDOP para cada grupo de cargas de trabajo.

Evaluación del impacto del paralelismo en el rendimiento de las consultas

El paralelismo es una característica de SQL Server que puede mejorar el rendimiento de las consultas que involucran grandes cantidades de datos o que requieren operaciones complejas. Sin embargo, el paralelismo también tiene sus inconvenientes, como el consumo de recursos, la sobrecarga de coordinación y la posibilidad de generar bloqueos o esperas. Por eso, es importante configurar el paralelismo de forma adecuada para cada escenario y evitar los problemas que puede causar.

Aun así, evaluar el impacto de los cambios de configuración es de vital importancia. No será la primera vez que una configuración que, a priori, creemos beneficiosa empeore el comportamiento final del servidor. Para saber si el paralelismo está ayudando o perjudicando al rendimiento de nuestras consultas, podemos usar varias herramientas y métodos, como consultar los planes de ejecución de las consultas, consultar las gráficas del monitor de actividad de nuestro SSMS, usar las vistas de administración dinámica (como las que vimos aquí) o el monitor de rendimiento de windows (perfmon).

Conclusión

En este artículo, hemos explicado cómo configurar el paralelismo en SQL Server, qué parámetros debemos tener en cuenta y cómo evaluar el impacto del paralelismo en el rendimiento de nuestras consultas. 

Espero que este artículo te haya sido útil y que te ayude a optimizar el rendimiento de tus consultas en SQL Server. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir. ¡Hasta la próxima!

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 0 comentarios
29
Feb
2024

Paralelismo en SQL Server: Guía completa parte 1

El paralelismo es una característica de SQL Server que permite dividir una consulta en varias tareas y ejecutarlas simultáneamente en diferentes procesadores o núcleos. Esto puede mejorar el rendimiento de las consultas que involucran grandes cantidades de datos o que requieren operaciones complejas. Sin embargo, el paralelismo también tiene sus inconvenientes, como el consumo de recursos, la sobrecarga de coordinación y la posibilidad de generar bloqueos o esperas. Por eso, es importante configurar el paralelismo de forma adecuada para cada escenario y evitar los problemas que puede causar.

En este artículo, vamos a explicarte qué es el paralelismo en SQL Server, y por qué es importante para el rendimiento de tus consultas. En un próximo artículo veremos cómo configurarlo, qué opciones tienes a tu disposición y qué factores debes tener en cuenta. Sin embargo, hoy nos vamos a centrar en esa teoría, a mi parecer imprescindible conocer antes de empezar a cambiar configuraciones. Tampoco vamos a hablar hoy de problemas de rendimiento, si quieres saber más sobre los problemas de uso excesivo de CPU en SQL Server, te recomiendo que leas nuestro anterior artículo al respecto.

¿Qué es el paralelismo en SQL Server?

El paralelismo en SQL Server es la capacidad de ejecutar una consulta en varios hilos o procesos paralelos, cada uno de los cuales se asigna a un procesador o núcleo diferente. Esto nos permite aprovechar la potencia de los sistemas multiprocesador de hoy en día y reducir el tiempo de ejecución de las consultas que implican grandes volúmenes de datos o que realizan operaciones costosas, como joins, ordenaciones o agregados.

El paralelismo se activa cuando el optimizador de consultas de SQL Server estima que el coste de ejecutar una consulta en paralelo es menor que el de ejecutarla en serie. El coste se mide en unidades de tiempo y depende de varios factores, como el tamaño de los datos, el tipo de operación, el índice utilizado, la estadística disponible, etc. El optimizador de consultas genera un plan de ejecución que indica cómo se va a ejecutar la consulta, qué operadores se van a utilizar y cuántos hilos se van a emplear.

Coste de los planes de ejecución, la clave del paralelismo

Hemos hablado de que el motor de SQL Server calcula el coste de ejecución de una consulta y en base a eso decide si usar o no un plan de varios hilos. ¿Pero qué es el coste de ejecución de una consulta? El coste de ejecución de una consulta es una medida que indica el esfuerzo que tiene que hacer SQL Server para ejecutar una consulta. El coste de ejecución se expresa en unidades arbitrarias que no tienen un significado absoluto, sino relativo. Es decir, el coste de ejecución sirve para comparar el rendimiento de diferentes consultas o de diferentes planes de ejecución para la misma consulta.

Para ver el coste de ejecución de una consulta, podemos usar los planes de ejecución, que son representaciones gráficas de cómo SQL Server ejecuta una consulta. Los planes de ejecución se pueden mostrar de forma estimada o real. Un plan de ejecución estimado se genera sin ejecutar la consulta, basándose en las estadísticas y los índices disponibles. Un plan de ejecución real se genera después de ejecutar la consulta, basándose en los datos reales y los recursos consumidos. Los planes de ejecución se pueden mostrar usando el menú de SQL Server Management Studio, las opciones SET SHOWPLAN o las vistas de gestión dinámica.

El coste de ejecución de una consulta es una herramienta útil para optimizar el rendimiento de las consultas en SQL Server. Al comparar el coste de ejecución de diferentes consultas o de diferentes planes de ejecución para la misma consulta, podemos identificar los operadores más costosos y buscar formas de reducir su coste. Por ejemplo, podemos crear o modificar índices, simplificar las consultas, usar funciones o variables adecuadas, cambiar el nivel de aislamiento, etc.

Los planes de ejecución

Los planes de ejecución se componen de varios operadores, que son las acciones que realiza el motor de SQL Server para ejecutar una consulta. Cada operador tiene un coste asociado, que se muestra como un porcentaje del coste total de la consulta. El coste total de la consulta se muestra en la raíz del plan de ejecución. Cada operador tendrá un coste que dependerá de la complejidad del mismo, del número de filas que procesa, del tamaño de las filas, del uso de índices, de la memoria, del disco, etc. El coste de cada operador se puede ver al pasar el cursor sobre el icono del mismo o al ver las propiedades del mismo.

Por otro lado, los planes de ejecución se dividen en dos partes: el plan serial y el plan paralelo. El plan serial contiene las operaciones que se ejecutan en un solo hilo, como las lecturas o las escrituras de datos. El plan paralelo contiene las operaciones que se ejecutan en varios hilos, como los joins, las ordenaciones o los agregados. 

Fases de los planes de ejecución paralelos

Tenemos que pensar en estos planes paralelos como cuando hacíamos un trabajo de grupo en clase. En esos casos, nos repartíamos las tareas, cada uno hacía su parte y, al final, lo poníamos en conjunto y salía un trabajo final excelente. Los planes paralelos hacen exactamente lo mismo y para ello se componen de tres fases: la distribución, la ejecución y la recolección.

Fase de distribución

En esta primera fase se reparten los datos entre los hilos que van a ejecutar el plan paralelo. Para ello, se utiliza un operador llamado exchange, que puede ser de dos tipos: repartition o redistribute. El operador repartition divide los datos en fragmentos iguales y los asigna a los hilos de forma aleatoria. El operador redistribute divide los datos en función de una clave de partición y los asigna a los hilos de forma que cada uno reciba los datos que corresponden a una o varias particiones.

Fase de ejecución 

Durante esta fase se realizan las operaciones sobre los datos distribuidos de manera paralela en varios hilos. Cada hilo ejecuta el mismo plan paralelo sobre los datos que le han sido asignados. Los operadores que se utilizan en esta fase son los mismos que se utilizan en el plan serial, pero con la diferencia de que se ejecutan en paralelo.

Fase de recolección 

Para finalizar, en esta última fase, se juntan los resultados de los hilos que han ejecutado el plan paralelo. Para ello, se utiliza otro operador exchange, que puede ser de dos tipos: concat o gather streams. El operador concat concatena los resultados de los hilos sin ordenarlos ni agruparlos. El operador gather streams, sin embargo, ordena o agrupa los resultados de los hilos según el criterio especificado.

Inhibidores de paralelismo

No todas las consultas que ejecutamos en SQL Server son candidatas a usar un plan de ejecución paralelo. Existen una serie de factores que pueden impedir o limitar el uso del paralelismo por parte de SQL Server. A estos factores los llamamos inhibidores de paralelismo. Algunos de estos factores son:

  • El nivel de aislamiento de la transacción: si el nivel de aislamiento es SERIALIZABLE o REPEATABLE READ, SQL Server no usará el paralelismo para evitar problemas de consistencia de los datos.
  • El uso de cursores: si una consulta usa un cursor, SQL Server no usará el paralelismo para mantener el orden de las filas devueltas por el cursor.
  • El uso de funciones escalares o tabulares: si una consulta usa una función escalar o tabular definida por el usuario, SQL Server no usará el paralelismo para ejecutar la función, lo que puede afectar al rendimiento de la consulta.
  • El uso de tablas temporales o variables: si una consulta usa una tabla temporal o una variable de tabla, SQL Server no usará el paralelismo para acceder a dicha tabla o variable, lo que puede afectar al rendimiento de la consulta.

Conclusión

Muchos estabais esperando leer cómo configurar el nivel de paralelismo y qué tener en cuenta. Eso lo veremos en el próximo artículo mañana. En este artículo, hemos explicado a fondo el paralelismo en SQL Server y lo que implica, hemos explicado qué es el coste de ejecución de una consulta y cómo se calcula.

También hemos visto cómo mostrar el coste de ejecución de una consulta usando los planes de ejecución. El paralelismo es una característica de SQL Server que puede mejorar el rendimiento de las consultas que involucran grandes cantidades de datos o que requieren operaciones complejas. Sin embargo, el paralelismo también tiene sus inconvenientes, como el consumo de recursos, la sobrecarga de coordinación y la posibilidad de generar bloqueos o esperas. Por eso, es importante configurar el paralelismo de forma adecuada para cada escenario y evitar los problemas que puede causar como ya comentamos en el pasado artículo sobre problemas de uso excesivo de CPU en SQL Server.

Esperamos que este artículo os haya sido útil y os animamos a seguir aprendiendo sobre SQL Server con nosotros. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir. ¡Hasta la próxima!

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 1 comentario
28
Feb
2024

Descubriendo qué llena la TempDB con xEvents

Hace un par de semanas, os preparé un artículo sobre la TempDB en el que pudimos ver, qué es, para qué sirve, por qué se llena y cómo optimizarla. Sin embargo, nos dejamos una cosa en el tintero y es algo que muchos me habéis señalado. Aunque hablamos también de cómo vaciar la TempDB no profundizamos en cómo saber qué consultas son las causantes del llenado de la TempDB. Hoy vamos a ver justo eso, y para ello nos vamos a apoyar en una herramienta de la que también os he hablado, los eventos extendidos de SQL Server.

Un pequeño repaso de la TempDB

No es el objetivo de este artículo profundizar en la teoría de la TempDB, para eso tenemos el artículo del pasado 13/02/2024. Pero, si que tenemos que repasar algunos de esos conceptos para no perdernos ahora. La TempDB es la base de datos de sistema con ID número 2 que se crea cada vez que se inicia el servicio de SQL Server y se elimina al pararlo. 

En esta base de datos se almacenan datos de intercambio de las consultas en curso ya sean datos de paginación de memoria para ordenar u operar con registros como datos almacenados por petición de los usuarios en tablas temporales (de una sesión o globales). Estas funcionalidades son las que hacen esta base de datos susceptible de crecer muy rápido e incluso de terminar llenándose completamente. Como comprenderás, llenar completamente una base de datos que se usa para prácticamente todo puede tener consecuencias fatales, llegando a dejar inoperativo nuestro servidor.

Otro pequeño repaso a xEevents

Como ya vimos hace un par de días, los eventos extendidos son una poderosa herramienta de SQL Server para los DBAs. No solo nos van a ayudar, como hoy, a detectar errores en la TempDB, sino también a monitorizar completamente el estado de nuestro servidor. Sin ir más lejos, ayer pudimos ver como usar xEvents para localizar consultas con mucho consumo de CPU.

Errores de TempDB 

Para nuestro ejercicio de hoy vamos a partir de la base de los errores de falta de espacio en el archivo de datos de SQL Server y vamos a crear una sesión de xEvents que nos almacene la consulta que los ha provocado cuando se den en la base de datos TempDB. Estos errores son el 1101 y el 1105

Error 1101 - Could not allocate a new page for database 'tempdb' because the 'PRIMARY' filegroup is full due to lack of storage space or database files reaching the maximum allowed size. Note that UNLIMITED files are still limited to 16TB. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Error 1105 - Could not allocate space for object '%.*ls'%.*ls in database 'tempdb' because the 'PRIMARY' filegroup is full due to lack of storage space or database files reaching the maximum allowed size. Note that UNLIMITED files are still limited to 16TB. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Como podéis ver, ambos errores son muy parecidos y la solución para ambos es la misma: revisar que el crecimiento sea el correcto, añadir espacio para que pueda crecer el fichero o liberar espacio en el fichero.

Sesión de xEvents

Nosotros nos vamos a centrar hoy en detectar las consultas causantes del problema para poder trabajar sobre ellas y no necesitar tanto espacio en TempDB. Para ello, vamos a hacer uso de una sesión de xEvents.

No me lio más, vamos a ponernos manos a la obra. Para nuestra sesión de XE vamos a capturar los eventos sqlserver.error_reported. Cómo vimos en el artículo de los eventos extendidos, vamos a poder usar las acciones para capturar información extra. En este caso vamos a capturar la base de datos y toda la información relevante sobre la consulta que ha disparado el error. Con esto, estaríamos capturando todas las consultas que generen un error en SQL Server pero como lo que nosotros queremos son solo los errores de espacio en TempDB vamos a hacer uso de los filtros. En concreto vamos a crear 3 filtros, el primero será que la base de datos sea la de id número 2 que ya hemos visto que es la que nos interesa, crearemos también un filtro por los errores 1101 y 1105 que nos interesan y terminaremos con un filtro por id de sesión superior a 50 para quitarnos las consultas del sistema de la captura. El resto de configuraciones son las genéricas que ya vimos.

Este sería el script de nuestra sesión de xEvents:

CREATE EVENT SESSION [TempDB_error] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack, sqlserver.session_nt_username,sqlserver.plan_handle)
WHERE ([database_id] = (2) AND [session_id] > (50) AND ([error_number]=(1101) OR [error_number]=(1105))))
ADD TARGET package0.event_file(SET filename=N’TempDB_error.xel’,max_file_size=(100),max_rollover_files=(10)),
ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

Revisando los resultados de xEvents:

Una vez creada e inicializada nuestra sesión podremos ver los datos en tiempo real desde la interfaz gráfica de nuestro SSMS:

Error_TempDB_xEvents

Otra opción será consultar el fichero de la sesión con una consulta T-SQL.

SELECT TOP 100 * FROM (
SELECT DISTINCT
n.value('@name', 'VARCHAR(100)') AS EventName,
n.value('@timestamp', 'DATETIME') AS Timestamp,
n.value('(data[@name="error_number"]/value)[1]', 'INT') AS ErrorNumber,
n.value('(data[@name="severity"]/value)[1]', 'INT') AS Severity,
n.value('(data[@name="state"]/value)[1]', 'INT') AS State,
n.value('(data[@name="user_defined"]/value)[1]', 'BIT') AS UserDefined,
n.value('(data[@name="category"]/text)[1]', 'VARCHAR(100)') AS Category,
n.value('(data[@name="destination"]/text)[1]', 'VARCHAR(100)') AS Destination,
n.value('(data[@name="is_intercepted"]/value)[1]', 'BIT') AS IsIntercepted,
n.value('(data[@name="message"]/value)[1]', 'VARCHAR(MAX)') AS ErrorMessage,
n.value('(action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS SqlText,
n.value('(action[@name="session_id"]/value)[1]', 'INT') AS SessionID,
n.value('(action[@name="database_id"]/value)[1]', 'INT') AS DatabaseID,
n.value('(action[@name="session_nt_username"]/value)[1]', 'VARCHAR(MAX)') AS UserName,
n.value('(action[@name="plan_handle"]/value)[1]', 'VARCHAR(4000)') AS PlanHandle
FROM (select cast(event_data as XML) as event_data
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\TempDB_error*.xel', null, null, null)) ed
CROSS APPLY ed.event_data.nodes('event') as q(n)
) t
WHERE EventName = 'error_reported'
ORDER BY TimeStamp DESC

En cualquiera de los casos podremos ver todas las consultas que han dado error por falta de espacio en TempDB y cómo lo vamos a ver ordenado por fecha solo nos tendremos que ir a la primera para descubrir al causante de la incidencia y poder reeducarle (siempre respetando los derechos humanos, ya sabéis).

Conclusión

Enfrentarse a un problema de consumo de TempDB puede ser un verdadero dolor de cabeza (por no decir otra parte de la anatomía masculina) pero, con las herramientas adecuadas podremos afrontarlo de manera sencilla. Para luego es tarde, no esperes a tener un problema en TempDB y echar de menos esta sesión de xEvents, ponla en tu servidor de pruebas (puedes jugar con el tamaño máximo del fichero de TempDB para reproducir el error) y, si te convence, aplicalo en tus servidores más críticos.

Esperamos que este artículo os haya sido útil y os animamos a seguir aprendiendo sobre SQL Server con nosotros. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir. ¡Hasta la próxima!

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 1 comentario
27
Feb
2024

SQL consume mucha CPU. Solución a problemas de procesador

La semana pasada hablamos sobre el uso excesivo de RAM en SQL Server. Hoy, vamos a abordar otro problema común que puede afectar el rendimiento de nuestros servidores: el consumo elevado de CPU. El consumo excesivo de CPU en SQL Server puede afectar significativamente el rendimiento y la estabilidad del sistema. En este artículo, compartiremos estrategias efectivas para identificar y abordar las causas subyacentes de este problema.

Identificando el problema

Como resumía Don Castelino en uno de los mejores artículos que existen sobre este tipo de incidentes, lo primero que debemos determinar es de donde viene el problema. Si recordáis, en el caso de la RAM, teníamos una cantidad de memoria reservada para SQL y sobre eso, podíamos ver el consumo. En el caso de la CPU la cosa se complica porque la CPU es compartida con el sistema operativo y todos los demás procesos que haya en el servidor. Por este motivo, tendremos muchas más variables que analizar. La siguiente imagen resume la lista de comprobaciones que debemos realizar.

ALTO-CONSUMO-DE-CPU

Vamos a ponernos en situación: Estamos ante un problema de rendimiento, nos dirigimos al administrador de tareas de Windows en nuestro servidor y vemos un consumo de CPU del 100% o cercano a esa cifra de manera continuada. Lo primero que debemos hacer será, en el mismo administrador de tareas, determinar si el mayor consumo de CPU viene de SQL Server o de otros procesos. En caso de que el consumo de SQL Server no supere, de manera constante, el 70% del total deberemos ver con los administradores de sistemas que otras cosas están causando el incidente en nuestro servidor. 

Como eso sería muy fácil, y ya habriamos terminado aquí el artículo, vamos a suponer que vemos que el problema de CPU sí que viene de SQL Server. Analicemos los siguientes pasos.

Batch Request por segundo

Una de las medidas que debemos tomar junto con el consumo de CPU es la cantidad de procesos batch por segundo que está ejecutando nuestro servidor SQL Server. Podemos encontrar este dato de forma sencilla en el monitor de rendimiento de nuestro SSMS o en el Performance Monitor de Windows. De esta manera veremos, de forma clara si nuestro SQL está ejecutando muchos procesos de manera simultánea o si, por el contrario, con pocos procesos generamos ese alto consumo de CPU. 

Debemos conocer y familiarizarnos con esta medición pues, no hay un valor estándar para todos los SQL Server. Depende mucho del procesador que tengamos, sus núcleos y su velocidad así como de los procesos que normalmente se ejecutan. Yo he visto, desde servidores donde un valor cercano a 1000 batch por segundo suponían un problema, como servidores con 15 o 20 veces ese valor que funcionaban perfectamente. 

Vista sys.dm_exec_query_stats

Una de las vistas de administración dinámica de SQL Server que nos va a ayudar a solucionar problemas de rendimiento de CPU es sys.dm_exec_query_stats. En ella vamos a poder ver las consultas con mayor consumo de CPU que tenemos en SQL Server. Si además la combinamos con la función sys.dm_exec_sql_text podremos ver el texto de las consultas. Volviendo a la vista, esta nos va a aportar gran cantidad de información entre la que nos vamos a quedar con el conteo de ejecuciones, y los tiempos de ejecución máximo, mínimo y total. Además, os recomiendo ordenar por este último, pues el problema puede ser la ejecución repetida de una consulta ligera y no tanto la ejecución única de una consulta pesada.

SELECT TOP 10
 total_worker_time/1000 AS [Total Worker Time (ms)],
 (total_worker_time/1000)/execution_count AS [Avg Worker Time (ms)],
 last_worker_time/1000 AS [Last Worker Time (ms)],
 max_worker_time/1000 AS [Max Worker Time (ms)],
 min_worker_time/1000 AS [Min Worker Time (ms)],
 execution_count,
 SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
 ((CASE qs.statement_end_offset
 WHEN -1 THEN DATALENGTH(st.text)
 ELSE qs.statement_end_offset
 END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time DESC;

Eventos Extendidos

La vista que hemos visto antes, nos puede dar las consultas recientes más pesadas, sin embargo, no nos será de ayuda ante un problema de consumo de CPU que no sea reciente. Para esto podemos usar otra de las formas de monitorización más potentes que incluye SQL Server, los eventos extendidos. Vamos a usar el evento query_post_execution_showplan que aunque ya dijimos que es pesado es lo más potente para este tipo de situaciones. Además vamos a capturar algunas acciones extra como el identificador del proceso, la base de datos, el usuario y el texto de la consulta. Para terminar, yo he añadido un filtro para solo capturar las consultas de más de 20 segundos de duración (recordad que el valor se muestra siempre en microsegundos). Este sería el script de mi sesión de xEvents:

CREATE EVENT SESSION [ConsumoCPU] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(
ACTION(package0.process_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text)
WHERE ([duration]>=(20000000)))
ADD TARGET package0.event_file(SET filename=N'C:\SQL2022\ConsumoCPU.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
xEvents_ALTO-CONSUMO-DE-CPU

Conclusión

Espero que a partir de ahora estéis más pereparados para afrontar problemas de rendimiento de CPU. Las herramientas que os he facilitado pueden ser de gran utilidad pero no son las únicas. En este sentido, QueryStore también puede ser un gran aliado, aunque aún no lo hayamos visto en este blog (espero solucionar esto pronto) os recomiendo que busquéis información y probéis la herramienta. 

Los más entendidos, habréis notado que no hemos mencionado en ningún momento las configuraciones de paralelismo de la instancia SQL Server. Esto es porque en este artículo hemos partido de la base de solucionar un problema de rendimiento en un servidor correctamente configurado. Le dedicaremos un artículo a estas configuraciones más adelante.

Espero que este artículo te haya resultado útil e interesante. Si tienes alguna duda o comentario, no dudes en contactarnos en Twitter o por mail o dejarnos un mensaje en los comentarios de aquí abajo. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir.

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 3 comentarios
26
Feb
2024

Eventos Extendidos en SQL Server: Una Guía Completa

Bienvenidos a este emocionante viaje al corazón de SQL Server. Hoy, nos sumergimos en el fascinante mundo de los eventos extendidos (también conocidas como Extended Events, xEvents o simplemente XE). Si aún estás utilizando el antiguo SQL Profiler/SQL Trace, es hora de dar la bienvenida a una herramienta más moderna y poderosa. En este artículo, explicaremos cómo crear sesiones de eventos, analizar datos y obtener información valiosa sobre el motor interno de SQL Server.

¿Qué son los eventos extendidos?

Los eventos extendidos son una herramienta poderosa para supervisar y solucionar problemas de rendimiento en SQL Server, Azure SQL Database y Azure SQL Managed Instance. A diferencia de las características obsoletas de Seguimiento de SQL y SQL Server Profiler, los xEvents ofrecen una mayor flexibilidad y escalabilidad.

Los Extended Events son una característica ligera de monitoreo de rendimiento que nos permite recopilar datos para supervisar y solucionar problemas. A diferencia del antiguo SQL Trace, xEvents ofrecen una visión escalable del comportamiento de SQL Server con un impacto mínimo en el rendimiento.

Ventajas de los Eventos Extendidos

Como ya hemos señalado, una de las mayores virtudes de los Eventos Extendidos es su ligereza. Los xEvents utilizan recursos mínimos del sistema. Todo esto además proporcionándonos un grado de precisión y detalles como ninguna otra herramienta. Son capaces de proporcionarnos una vista detallada del motor de base de datos a través de una interfaz gráfica. Y es que esta es otra de sus ventajas, nuestro SQL Server Management Studio (SSMS) ofrece una interfaz gráfica para crear, modificar y analizar sesiones de eventos.

Conceptos Clave

Antes de empezar a crear nuestra primera sesión de eventos extendidos tenemos que conocer una serie de conceptos que vamos a necesitar.Si habéis trabajado antes con el seguimiento de eventos para Windows esto os va a sonar, xEvents se basa en estos conceptos para llevar esta monitorización a SQL Server. Sin embargo, necesita de conceptos nuevos que son específicos de SQL Server. Vamos a ver los principales.

Motor de Eventos Extendidos

Es la base de todo esto, el motor de xEvents implementa y administra las sesiones de eventos extendidos. Es responsable de recopilar y relacionar los datos.

Sesiones de Eventos Extendidos

Una sesión se crea en el proceso del motor de base de datos donde se hospeda el motor de eventos extendidos. Proporciona el contexto necesario para comprender la infraestructura y el procesamiento que ocurre.

Paquetes de Eventos Extendidos

Los paquetes son contenedores de objetos que las sesiones de eventos extendidos utilizan para obtener y procesar datos durante su ejecución.

Destinos de Eventos Extendidos

Los consumidores de eventos pueden recibir datos durante una sesión. Llamamos consumidores de eventos al destino que va a recibir la información que extraemos de nuestra sesión de XE. Pueden ser archivos, tablas o incluso aplicaciones externas.

Arquitectura de Eventos Extendidos

Ahora que ya tenemos claro los conceptos básicos de los eventos extendidos podemos profundizar más técnicamente. xEvents es un sistema de control de eventos para servidores que permite la correlación de datos del motor de base de datos y, en ciertas condiciones, de las bases de datos y del sistema operativo. Los datos de eventos se pueden utilizar dentro y fuera de una aplicación, proporcionando información útil para la supervisión y el rendimiento. Los datos se pueden consumir o analizar mediante diversas herramientas, como SQL Server Management Studio, XEvent Profiler o el Monitor de rendimiento.

El diseño de Eventos Extendidos es flexible e independiente del evento, permitiendo que cualquier evento se enlace con cualquier destino. Es decir, los eventos están separados de los consumidores de eventos, lo que significa que cualquier destino puede recibir cualquier evento. Además, se pueden asociar acciones a cualquier evento y se pueden filtrar dinámicamente los datos del evento. XE puede generar datos de eventos de forma sincrónica y proporciona un enfoque unificado para controlar los eventos en todo el sistema del servidor, integrándose con las herramientas de ETW existentes y ofreciendo un mecanismo configurable de control de eventos que utiliza T-SQL.

Crea tu sesión de Eventos Extendidos

Ya hemos dicho que una de las ventajas de los xEvents es su interfaz gráfica. Desde SSMS, conectados a una instancia de bases de datos encontraremos la opción Eventos Extendidos en el apartado Administración. Tenemos además un asistente para facilitarnos la tarea de creación de sesiones que nos irá guiando por los pasos necesarios además de poder elegir entre diferentes plantillas ya predefinidas.

Explorando los Datos

Una vez que tengamos nuestra sesión de eventos, podremos ver los datos que nos proporciona. Uno de los eventos más interesantes es query_post_execution_showplan, que nos ofrece el plan de ejecución real después de que se haya ejecutado una consulta. Esto incluye estadísticas de rendimiento en tiempo real. 

Sin embargo, este evento tiene un alto costo Por suerte, existen alternativas más ligeras para obtener planes de ejecución individuales como query_thread_profile. Disponible desde SQL Server 2014 SP2 y SQL Server 2016, este evento muestra un perfil detallado por operador e hilo de ejecución en un plan real. Aunque puede ser muy detallado, es útil para análisis a gran escala.

Conclusión

En resumen, los eventos extendidos son una herramienta esencial para cualquier administrador o desarrollador que desee profundizar en el rendimiento y la optimización en entornos SQL Server. Son una herramienta poderosa para comprender el comportamiento interno de SQL Server. Al adoptarlas, obtendrás información valiosa sin afectar significativamente el rendimiento del sistema. 

¡Espero que esta guía te ayude a aprovechar al máximo los eventos extendidos! Sigue atento al blog porque a lo largo de esta semana vamos a publicar artículos más prácticos donde usaremos estos eventos extendidos para solucionar problemas de rendimiento de nuestro SQL Server.

Si tienes alguna duda o comentario, no dudes en contactarnos en Twitter o por mail o dejarnos un mensaje en los comentarios de aquí abajo. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir.

Publicado por Roberto Carrancio en SQL Server, 4 comentarios
23
Feb
2024

SQL consume mucha RAM. Solución a problemas de memoria

Bienvenidos a este tercer artículo sobre el uso que hace SQL Server de la memoria RAM. En los últimos días ya hemos visto cómo se gestiona la RAM en SQL y la configuración especial LPIM para optimizar el rendimiento en caso de presión de memoria. Pero si recordáis, en este último artículo, os decía que antes de tocar LPIM lo mejor era afrontar el problema. Por ello vamos a dedicar este artículo a ver cómo podemos detectar y afrontar problemas de consumo excesivo de memoria RAM.

Determinando el consumo excesivo de memoria

¿Realmente tenemos un problema? Esta es la principal pregunta, normalmente, a ojos del que no conoce el funcionamiento de SQL Server puede ser preocupante la gran cantidad de memoria que SQL Server suele tener reservada. Sin embargo, nosotros que somos buenos DBAs (y hemos leído el artículo sobre la RAM del otro día) sabemos que no nos podemos fiar de eso pues SQL Server siempre va a reservar toda la RAM que tenga a su disposición hasta el máximo que le hayamos asignado. Esto no quiere decir que la esté usando así que vamos a ver cómo podemos realmente darnos cuenta. 

Analizando el consumo de memoria RAM de SQL Server

SQL Server pone a nuestra disposición unas vistas de administración dinámica (dm views) que nos pueden dar cierta información sobre la RAM. Estas vistas son, según la documentación oficial, sys.dm_os_process_memory y sys.dm_os_sys_memory. Aunque a mi me gusta también consultar sys.dm_os_sys_info y sys.dm_os_performance_counters.

Vista sys.dm_os_process_memory

En esta vista podemos encontrar datos sobre la cantidad de memoria en uso y disponible en nuestro SQL Server. Podéis consultar la documentación oficial de la vista aquí. Yo os recomiendo estas:

En esta consulta de Pinal Dave, muy interesante podemos ver la memoria en uso y un par de avisos sobre presión de memoria. Si los dos avisos están en 0 es que todo está bien.

SELECT physical_memory_in_use_kb/1024 [Physical Memory Used in MB],
process_physical_memory_low [Physical Memory Low],
process_virtual_memory_low [Virtual Memory Low]
FROM sys.dm_os_process_memory;
sys.dm_os_process_memory

Esta otra consulta nos da un poco más de información, en ella vamos a poder ver el detalle de lo que está consumiendo la memoria RAM por parte de SQL Server:

SELECT
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
   large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
   locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
   virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
   virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
   virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
   page_fault_count AS sql_page_fault_count,
   memory_utilization_percentage AS sql_memory_utilization_percentage,
   process_physical_memory_low AS sql_process_physical_memory_low,
   process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;
sys.dm_os_process_memory

Vista sys.dm_os_sys_memory

Esta vista de sistema nos va a dar menos variedad de información pero no por ello menos útil. A diferencia de la anterior aquí veremos los datos de memoria de nuestro servidor no solo de SQL Server. Podéis ver todas las columnas disponibles en la vista aquí, aunque yo os recomiendo esta consulta.

SELECT total_physical_memory_kb/1024 [Total Physical Memory in MB],
available_physical_memory_kb/1024 [Physical Memory Available in MB],
system_memory_state_desc
FROM sys.dm_os_sys_memory;
sys.dm_os_sys_memory

Vista sys.dm_os_sys_info;

Esta vista es muy interesante pues además de la memoria en uso por SQL Server nos va a mostrar una estimación de lo que SQL Server espera necesitar. Si combinado con las anteriores vemos que el valor necesitado es menor que el disponible podemos respirar tranquilos. Os dejo un ejemplo de uso sencillo:

SELECT committed_kb/1024 [SQL Server Committed Memory in MB],
committed_target_kb/1024 [SQL Server Target Committed Memory in MB]
FROM sys.dm_os_sys_info;
sys.dm_os_sys_info

Vista sys.dm_os_performance_counters

Esta vista nos va a mostrar todos los contadores de rendimiento, incluidos los de SQL Server. Con ella podremos tener una visión más completa de lo que está pasando. Para el caso que nos ocupa os recomiendo filtrar por la palabra memoria:

SELECT object_name, counter_name, instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name like '%Memory%'
sys.dm_os_performance_counters

Bonus 

Además de todas estas vistas de sistema, SQL pone a nuestra disposición el comando DBCC MEMORYSTATUS. Este comando nos va a dar mucha información (demasiada) sobre todo el estado de la memoria. Está diseñado para ser una herramienta de diagnóstico para los ingenieros de soporte de Microsoft por lo que, a nosotros, simples mortales, nos va a confundir más que otra cosa. Si tienes curiosidad sobre este comando puedes pasarte por su página en los books online de SQL Server dónde lo explican en detalle.

DBCC_MEMORYSTATUS

¿Qué hago si realmente hay un problema con la memoria?

Si llegados a este punto has detectado que si que tenias un problema real con el consumo de memoria RAM, te compadezco. Estás en una situación en la que a ninguno nos gustaría estar, pero no te preocupes, vivimos de esto y es lo que nos da dinero. Sigue leyendo que aún hay más artículo para que puedas salir airoso de este marronazo.

Lo primero que tendrás que determinar es si el problema está ocurriendo ahora mismo o ya ha pasado. Si estás en el primer caso puedes hacer uso de procedimientos almacenados como sp_who3, sp_whoisactive o sp_blitzwho para determinar el consumo de memoria de cada una de las consultas en ejecución y tirar de las orejas a alguien.

Si, como me pasa a mi, en tu país también está mal visto (y hasta penado por ley) el castigo físico, analiza las consultas que más memoria consumen y trata de optimizarlas. Quizá solo te falta un índice. O a lo mejor es al revés y te sobran. O están bien definidos pero sin mantenimiento. Te aseguro que con esto de los índices vas a resolver el 70% de las incidencias que te puedas encontrar. El resto de casos puede ser debido a ordenaciones sin miedo a lo que pueda pasar o al uso de operadores como UNION, DISTINCT, etc… que cargan el resultado en memoria para eliminar registros duplicados.

Si el problema ya ha pasado y no tienes tanta suerte de acceder a las consultas en ejecución, revisa los índices que te faltan y los que no se usan que es probable que con eso lo soluciones. Te dejo por aquí todo lo que se ha hablado en este blog sobre índices.

Conclusión

Afrontar una incidencia sobre el consumo de memoria en SQL Server puede ser un desafío. Espero que con estas herramientas de diagnóstico, nativas y de terceros, podáis enfrentaros mejor a ellas. Tampoco está de más ser proactivos y revisar de vez en cuando el uso de índices para ahorrarnos problemas. Un buen mantenimiento de índices y estadísticas nos evitará más de un disgusto. Recordad que un diagnóstico y una resolución rápida son fundamentales para mantener un entorno SQL Server saludable y eficiente. La superación de estos retos garantiza un funcionamiento fluido de la base de datos y contribuye a la eficacia general de la empresa, a la satisfacción del cliente y, en última instancia, a la cuenta de resultados.

Espero que este artículo te haya resultado útil e interesante. Si tienes alguna duda o comentario, no dudes en contactarnos en Twitter o por mail o dejarnos un mensaje en los comentarios de aquí abajo. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir.

CRÉDITOS: Varias de las consultas de este post son originales de Pinal Dave de SQLAuthority y del blog SQLHack. 

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 5 comentarios
22
Feb
2024

Optimizando SQL Server con Lock Pages in Memory (LPIM)

Continuamos con la serie de artículos sobre la optimización de la RAM de nuestro SQL Server que empezamos ayer. Hoy vamos a hablar de una característica muy interesante de SQL Server que puede ayudar a mejorar el rendimiento de nuestras bases de datos: el bloqueo de páginas de memoria o Lock Pages in Memory (LPIM). No es una característica de SQL Server sino una configuración de Windows Server que nos permitirá controlar lo que pasa cuando el sistema tiene presión de RAM y tiene que paginar a disco.

¿Qué es Lock Pages in Memory?

El bloqueo de páginas en memoria (LPIM) es una política de Windows que evita que el sistema realice la paginación de los datos a la memoria virtual del disco durante la presión de memoria. En otras palabras, LPIM bloquea los datos en la memoria RAM. No importa que otros procesos o el propio sistema operativo necesiten esa memoria, nuestro SQL va a decir “tu problema no es mi problema” y no las va a liberar. Esto puede mejorar el rendimiento del sistema y puede ser muy útil para SQL Server aunque también es muy peligroso.

¿Cómo funciona LPIM en SQL Server?

Cuando SQL Server se ejecuta en una cuenta que tiene el privilegio LPIM, puede bloquear las páginas de memoria en la RAM física y evitar que el sistema operativo las pagine a la memoria virtual en el disco duro. Esto significa que SQL Server usará la memoria tanto como sea necesario, y no la liberará al sistema operativo en circunstancias normales. Este comportamiento puede aumentar el rendimiento del sistema y de SQL Server si hay presión de memoria en el sistema.

¿Cómo habilitar LPIM en SQL Server?

Como hemos comentado, para habilitar LPIM en SQL Server, debemos conceder el privilegio a la cuenta de servicio de SQL Server. Para hacerlo debemos seguir los siguientes pasos:

  1. En el menú Inicio, seleccionamos Ejecutar.
  2. En el cuadro Abrir, escribimos gpedit.msc.
  3. En la consola Directiva de grupo local, expandimos Configuración del equipo.
  4. Expandimos Configuración de Windows.
  5. Expandimos Configuración de seguridad.
  6. Expandimos Directivas locales.
  7. Seleccionamos la carpeta Asignación de derechos de usuario.
  8. En el panel, vamos a la directiva Bloquear páginas en la memoria y hacemos doble clic en ella.
  9. En el cuadro de diálogo Configuración de la directiva de seguridad local, seleccionamos **Agregar usuario o grupo…*.
  10. Agregamos la cuenta de servicio de SQL Server.
  11. Seleccionamos Aceptar.
  12. Reiniciamos la instancia SQL Server para que se aplique la configuración.

Ventajas y desventajas de LPIM

Según lo que hemos visto en la teoría, con LPIM mejoraremos el rendimiento al evitar que las páginas se muevan entre RAM y disco, reducimos la latencia. Además ganamos estabilidad al asignar memoria física específicamente para SQL Server, evitando conflictos con otros procesos o aplicaciones. Por último, ganamos un mayor control sobre cómo se utiliza la memoria en nuestra instancia. 

Sin embargo, estamos hablando de una configuración a bastante bajo nivel que interfiere en la gestión de memoria del sistema operativo. Cualquier mala decisión por nuestra parte podría acarrear resultados no deseados. En este sentido, es importante tener en cuenta que si se concede el privilegio LPIM, se recomienda encarecidamente establecer la memoria máxima de SQL Server en un valor específico, en lugar de dejar el valor predeterminado de 2 PB. Esto se debe a que si el valor de Memoria de servidor máxima se establece en una cifra demasiado alta, puede hacer que una única la instancia de SQL Server compita por la memoria con otras instancias de SQL Server hospedadas en el mismo host, otras aplicaciones o incluso con el propio sistema operativo.

¿Cuándo habilitar LPIM?

Llegamos a un punto clave, internet está dividido sobre este tema. Incluso la propia documentación oficial de Microsoft parece recomendarlo en algunos apartados y desaconsejarlo en otras páginas. Así que, aquí está SoyDBA para intentar aclarar las dudas. A priori, parece que ante un escenario de presión de memoria podría ser recomendable activar esta característica. Si a menudo nos encontramos con escenarios en los que SQL Server sufre de problemas de memoria y vemos el típico error 17890  en el log deberíamos por lo menos plantearnos esta solución. Quizá soy yo muy conservador pero, personalmente, no creo que sea recomendable en ningún otro escenario. Si no es para solucionar un problema no metería mano en configuraciones de este calado en el sistema operativo. Y por supuesto, nunca lo pondría en producción sin una buena batería de pruebas en un entorno para ese fin. En resumen, yo no lo haría si no hay un problema, y si lo hay sólo activaría LPIM si no puedo aumentar el hardware de la máquina que sería el segundo paso (el primero es tratar de reducir el consumo, obviamente).

LPIM en máquinas virtuales

No quería cerrar este artículo sin comentar un pequeño detalle y es que esta configuración no deberíamos tocarla en servidores virtuales. Todos los principales proveedores de máquinas virtuales tienen su propia gestión de la memoria de las máquinas virtuales y configurar LPIM en una máquina virtual nos va a causar problemas graves de rendimiento, tanto de nuestro propio servidor como en el resto de máquinas alojadas en el mismo host e, incluso, en el propio sistema del host.

Conclusión

LPIM puede ser una herramienta valiosa para mejorar el rendimiento de SQL Server. Sin embargo, como con cualquier herramienta, es importante entender cómo funciona y cómo usarla correctamente para obtener los mejores resultados. Me vienen a la cabeza aquellas sabias palabras “Un gran poder conlleva una gran responsabilidad” y es que, en este contexto, tienen incluso más sentido que cuando el Tio Ben (a punto de morir) se las dijo a Spiderman.

Espero que este artículo te haya resultado útil e interesante. Si tienes alguna duda o comentario, no dudes en contactarnos en Twitter o por mail o dejarnos un mensaje en los comentarios de aquí abajo. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir.

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 0 comentarios