Rendimiento

06
Mar
2024

Particionado de tablas en SQL Server (Introducción)

En este artículo vamos a hablar de un tema muy interesante y útil para los DBAs: el particionado de tablas en SQL Server. El particionado de tablas es una técnica que nos permite dividir una tabla grande en varias partes más pequeñas, llamadas particiones, que se almacenan en diferentes grupos de archivos. De esta forma, podemos mejorar el rendimiento de las consultas, las operaciones de mantenimiento y la administración de los datos.

¿Qué es el particionado de tablas?

El particionado de tablas en SQL Server es una técnica a la que podemos recurrir para dividir nuestras tablas grandes en particiones más pequeñas. Esto normalmente está asociado a un menor coste de recursos al tener que recuperar menos información, además nos abre un poco más el abanico de opciones a la hora de administrar pudiendo jugar con distintos grupos de archivos separados en discos, creando copias de sólo algunos grupos de archivos y haciendo planes de mantenimiento específicos para las particiones.

Tipos de particionado

Podemos encontrarnos con particionados de tipo horizontal y vertical en función de si separamos la tabla por filas o columnas respectivamente. 

Particionado vertical 

Este tipo de particionamiento consiste en dividir una tabla en varias particiones según las columnas que se quieren incluir o excluir. Cada partición contiene solo algunas columnas de la tabla original, pero todas las filas. El particionado vertical se suele usar para separar las columnas que se consultan con frecuencia de las que no, o para almacenar las columnas con tipos de datos especiales (como imágenes, documentos o textos grandes) en grupos de archivos diferentes. Dentro de este tipo de particionamiento podemos encontrar varias soluciones, desde dos tablas independientes con un identificador común para enlazar las columnas hasta una misma tabla con varias vistas indexadas sobre ella cada una con distintas columnas. 

Particionado horizontal

Este tipo de particionamiento es en el que todo el mundo piensa cuando se habla simplemente de particionamiento, sin especificar nada más. Consiste en partir una tabla en varias particiones con exactamente las mismas columnas y repartir las filas entre las distintas particiones en función del valor de una de las columnas. Así por ejemplo una tabla con datos de un log podría estar dividida en particiones por año. 

Ventajas de particionar las tablas

Como ya hemos comentado, si nos metemos en este lio del particionamiento no es por gusto, es porque tiene muchas ventajas frente a las tablas “normales”. Entre estas ventajas podemos destacar:

  • Reduce el tiempo de respuesta de las consultas, ya que podemos acceder solo a la partición o particiones que contienen los datos que necesitamos, en lugar de tener que escanear toda la tabla. Esto se conoce como eliminación de particiones o partition pruning.
  • Facilita el mantenimiento de los datos, ya que podemos realizar operaciones como reconstruir índices, actualizar estadísticas o realizar copias de seguridad por partición, en lugar de tener que hacerlo sobre toda la tabla. Esto reduce el tiempo y los recursos necesarios para estas tareas.
  • Mejora la disponibilidad de los datos, ya que podemos aislar las particiones que contienen datos críticos o de uso frecuente en grupos de archivos dedicados, con un nivel de redundancia y recuperación más alto. También podemos mover o restaurar particiones individuales sin afectar al resto de la tabla.
  • Permite una gestión más eficiente del espacio en disco, ya que podemos asignar diferentes niveles de compresión a cada partición, según la frecuencia y el tipo de acceso a los datos. También podemos liberar espacio eliminando o archivando particiones antiguas o poco usadas.

Implementando el particionamiento de tablas 

Los pasos para implementar el particionado de tablas variarán en función del tipo de particionado que queramos usar.

Implementar el particionado horizontal 

  1. Crear una función de partición, que define cómo se van a distribuir los datos entre las distintas particiones, según el valor de una columna o expresión. Por ejemplo, podemos crear una función de partición por año, mes o día, según la fecha de registro de los datos.
  2. Crear un esquema de partición, que asocia la función de partición con uno o varios grupos de archivos, donde se van a almacenar las particiones. Podemos especificar un grupo de archivos diferente para cada partición, o usar el mismo para todas.
  3. Crear o modificar la tabla que queremos particionar, indicando el esquema de partición y la columna o expresión que va a determinar el valor de la partición. Podemos crear una tabla nueva ya particionada, o modificar una tabla existente para aplicarle el particionado.
  4. Crear o modificar los índices sobre la tabla particionada, indicando también el esquema de partición y la columna o expresión que va a determinar el valor de la partición. Podemos crear índices alineados con la tabla, es decir, que siguen el mismo esquema y función de partición, o índices no alineados, que tienen su propio esquema y función de partición.

Implementar el particionado vertical 

Como hemos comentado antes para el particionado vertical podríamos optar por crear varias tablas con el mismo ID y distintas columnas en cada una de ellas. Otra opción será sobre una misma tabla con todas las columnas crear vistas indexadas, para ello los pasos serán:

  1. Crear vistas sobre la tabla original, seleccionando solo las columnas que queremos incluir en cada partición. Por ejemplo, podemos crear una vista con las columnas básicas y otra con las columnas avanzadas.
  2. Crear un índice agrupado sobre cada vista, indicando el grupo de archivos donde se va a almacenar la partición. De esta forma, se crea una copia física de los datos de la vista en el grupo de archivos especificado, y se mantiene sincronizada con la tabla original.
  3. Opcionalmente podremos crear índices no agrupados sobre cada vista, indicando también el grupo de archivos donde se va a almacenar la partición. De esta forma, se crea un índice que facilita el acceso a los datos de la vista, y se mantiene sincronizado con la tabla original.

Planificando el particionado de tablas

Ya hemos visto que el particionado de tablas es una herramienta muy poderosa. Sin embargo no hay que tomárselo a la ligera, requiere una planificación y un diseño adecuados para obtener los mejores resultados. Algunas consideraciones importantes son:

  • Elegir bien la columna y expresión que va a determinar el valor de la partición, ya que debe ser relevante para las consultas y operaciones más habituales sobre la tabla. Por ejemplo, si queremos consultar los datos por fecha, tiene sentido usar una columna que contenga la fecha como criterio de particionado.
  • Elegir bien el número y el tamaño de las particiones, ya que deben ser equilibradas y coherentes con el volumen y la distribución de los datos. Por ejemplo, si tenemos una tabla con muchos años de datos históricos, pero solo consultamos los datos del último año, no tiene sentido crear una partición por cada año, sino quizás una por cada mes o trimestre.
  • Elegir bien los grupos de archivos donde se van a almacenar las particiones, ya que deben tener suficiente espacio y capacidad para soportar la carga de trabajo. Por ejemplo, si tenemos una partición con datos muy consultados o críticos, podemos ubicarla en un grupo de archivos con discos más rápidos o con mayor redundancia.
  • Monitorizar y ajustar el particionado de tablas periódicamente, ya que los datos y las necesidades pueden cambiar con el tiempo. Por ejemplo, podemos usar herramientas como el asistente para el particionado de tablas o query store para analizar el rendimiento y la eficacia del particionado, y realizar cambios como agregar, eliminar, fusionar o dividir particiones, según sea necesario.

Conclusión

El particionado de tablas es una técnica muy útil para mejorar el rendimiento, el mantenimiento y la administración de las bases de datos en SQL Server. Sin embargo, requiere un diseño cuidadoso y un seguimiento constante para obtener los mejores resultados. En este artículo hemos visto los conceptos básicos del particionado horizontal y vertical de tablas, cómo se implementan en SQL Server y qué consideraciones hay que tener en cuenta al usarlos. Si quieres profundizar más en este tema, te recomendamos que leas nuestro anterior artículo sobre cuellos de botella de E/S en SQL Server, donde explicamos cómo el particionado de tablas puede ayudar a resolver este problema común.

Esperamos que te haya gustado este artículo y que te haya servido para aprender algo nuevo. Si tienes alguna duda o comentario, no dudes en dejarnos un mensaje en Twitter, por mail o dejarnos en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir. 

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 1 comentario
05
Mar
2024

¿Qué columnas añado a un índice y en qué orden?

Ya hicimos en este blog una serie de 7 artículos sobre índices que podéis consultar en la página índices dentro del menú de rendimiento. Sin embargo, nos faltaba algo muy importante y por lo que muchos me preguntáis: ¿Qué columnas añado a un índice y en qué orden?. Eso es lo que vamos a tratar de resolver hoy. Vaya por delante, antes de seguir leyendo, que esto de los índices no es algo exacto, seguramente la mayoría de las ocasiones deberéis hacer varias pruebas hasta dar con el resultado adecuado. ¿Os he hablado ya de la importancia de un entorno de pruebas? Pues eso, probad, fallad varias veces, pero no os resignéis que la solución está ahí y esto es así.

Índices faltantes

En los últimos post sobre problemas de rendimiento por alto consumo de RAM y CPU vimos que los índices jugaban un papel fundamental en esto del rendimiento. Si no tenemos los índices correctos nuestro SQL Server va a tener que hacer más lecturas de las necesarias provocando sobrecarga de E/S de disco además de sobrecargar RAM y CPU. Así lo hablamos, también, en los comentarios a uno de estos post en LinkedIn, donde mi buen amigo Óscar nos señalaba este hecho como el causante de nuestros problemas. Por suerte, SQL Server es capaz de detectar este problema y, aunque no siempre acierta, es un buen punto de partida.

Característica índice faltante de SQL Server

Cuando estamos ejecutando una consulta que podría mejorar con la creación de un índice, SQL Server nos mostrará un aviso (warning) de que nos falta un índice (missing index) en el componente raíz del plan de ejecución. Además, nos sugerirá el script de creación del mismo. Estos datos, además, se persisten y podemos ver todas estas sugerencias de creación de índices juntas en unas vistas de administración dinámica (DMV). El problema de estas sugerencias es que son para consultas individuales, es decir SQL dice que le falta un índice para optimizar el rendimiento de la consulta que está ejecutando sin medir el impacto en el resto de consultas.

Además tampoco sugiere de forma correcta el orden de las columnas ni analiza el coste/beneficio de las columnas que recomienda como incluidas. Tiene más limitaciones, como que solo propone índices nonclustered básicos, ni columnstore ni índices filtrados ni únicos, solo muestra un índice faltante por consulta y sobre todo, una de las mayores carencias para mi es que no nos muestra ni la consulta ni el plan de ejecución que ha generado la alerta. Esto último lo podemos compensar si tenemos activado el almacén de consultas (Query Store), en ese caso sí que podremos verlo. Pero bueno, ahora entendéis lo que os decía de que es un buen punto de partida, simplemente eso, no es una solución mágica que seguir al pie de la letra.

Mi guía definitiva para la creación de un buen índice

Disclaimer: Igual me he venido un poco arriba con el título y es simplemente una lista de recomendaciones, pero espero que seáis benévolos y me lo sepáis perdonar. 

Como decía, partiremos de la base de las sugerencias de creación de índices para detectar las debilidades de nuestra base de datos en este sentido y a partir de ahí empezaremos a trabajar.

Paso 1 Triaje

Es importante conocer las consultas que hacen un mayor uso de los recursos para poder empezar a optimizar por ahí y valorar si es o no necesario ese índice. Por lo general yo reviso juntas todas las sugerencias de índices de una misma tabla para con el mínimo número de índices cubrir la mayoría de escenarios. Recordad aquí las recomendaciones generales, una tabla no debería tener más de 5 o 10 índices.

Paso 2 Filtros de equidad y no equidad

Existen dos tipos de filtros en SQL Server, los filtros de equidad (un valor igual a otro con el que se quiere comparar) y los de inequidad (no igual, mayor que, menor que, etc…). En nuestro caso buscaremos primero los filtros (en el where o joins) de equidad. Esas columnas serán las primeras claves de nuestros índices. Las columnas sobre las que se realicen filtros de inequidad irán después.

Paso 3 Agrupados y ordenaciones

Otra de las cosas que tenemos que tener muy en cuenta son las columnas por las que se ordena o agrupa. En el primer caso, si siempre ordenamos por la misma columna igual es interesante que sea el índice clustered de la tabla y que nuestra PK sea nonclustered. Si realizamos muchas agrupaciones por la misma consulta valoraremos el uso de índices columnares, que nos darán mejor rendimiento en estas situaciones. Si no cumple con los requisitos para crear un índice de otro tipo propio pero aún así supone un coste alto en nuestras consultas valoraremos incluir estas columnas en el INCLUDE de nuestro índice.

Paso 4 Lecturas

Como ya vimos en la serie de artículos sobre los índices, SQL Server usará nuestro índice para filtrar y después se irá a la tabla a buscar el resto de campos que necesite para cubrir lo que le hemos pedido en el SELECT. Esto, por supuesto, no es lo más eficiente y un índice que cubra completamente la consulta es lo ideal. En una situación ideal los usuarios de la base de datos no usarían SELECT * y podríamos plantearnos crear índices que cubran completamente la consulta entre las columnas clave y las incluidas pero si no es el caso cuidado. Muchos datos en las columnas include de nuestro índice puede hacer que tenga un coste tan elevado de lectura que termine no usándose.

Paso 5 Orden

Llegamos a uno de los puntos clave a la hora del rendimiento de nuestros índices. SQL Server empezará a usar la estructura de árbol B de nuestro índice desde la primera columna en orden hasta la última. Si no filtramos por una de las columnas anteriores del índice, entonces ya no podremos hacer uso de esa SARGabilidad y nos encontraremos con un escaneo del nivel hoja del índice. Es decir, si nuestro índice incluye las columnas A, B y C en este orden y nuestra consulta no usa, por ejemplo la A siempre nos vamos a encontrar con un scan, lo mismo si filtramos por la A y la C pero no la B. No pasaría esto si filtramos por la columna A, la A y B o todas ellas.

Teniendo en mente esta capacidad de lectura a través de la estructura de árbol invertido podemos concluir que para un mejor rendimiento primero pondremos las columnas con mayor cantidad de datos distintos (que serán las más selectivas). 

Paso 6 Unicidad y filtrado

Es el momento de decidir aspectos extra de nuestro índice como si es además una restricción de unicidad o si es recomendable que esté filtrado. Este último caso es muy común en entornos donde en vez de borrarse los registros se usa un check de registro activo. En este caso, normalmente se filtra por registros activos y añadir este filtro al índice escribiendo solo los registros vivos puede reducir su tamaño sin afectar al rendimiento.

Conclusión

Las recomendaciones de missing indexes son una petición de socorro de nuestro SQL Server que nos dice que algo no va bien pero no debemos tomarlas al pie de la letra. Es nuestra responsabilidad analizar, validar y modificar estas sugerencias para adaptarlos a nuestro escenario. No tenemos que olvidar tampoco las vistas indexadas para tener índices que nos cubran consultas más complejas con uniones entre tablas. 

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 Índices, Rendimiento, SQL Server, 2 comentarios
04
Mar
2024

SQL lento por cuellos de botella de E/S ¿Y ahora qué?

Volvemos al ataque con otro artículo sobre problemas de rendimiento. Como buenos DBAs ya hemos luchado contra los temibles problemas con el uso de RAM y los picos de uso de CPU y ahora es el turno de librar una batalla contra los problemas de E/S en los discos duros. Los problemas de velocidad de E/S en disco no son tan fáciles de localizar, sin embargo están ahí, en la sombra, saboteando el buen rendimiento de SQL Server. Los discos duros son el hardware más lento de nuestros servidores y, sin embargo, los que almacenan toda la información. No es extraño que se conviertan en uno de los principales cuellos de botella para nuestras consultas.

¿Qué es un cuello de botella por E/S de disco?

En informática, llamamos cuello de botella a la situación resultante cuando un componente de un sistema es más lento que el resto y por tanto frena todo. Es como un atasco en la carretera de los datos. ¿Habéis escuchado el dicho de que una cadena es tan fuerte como su eslabón más débil? Pues, en nuestro caso, un sistema es tan rápido como su componente más lento. En el caso de SQL Server esto tiene graves consecuencias, como ya hemos dicho, el hardware más lento es el que almacena toda la información.

Causas comunes de los cuellos de botella de E/S

Vale, afrontamos esta batalla con una mano atada a la espalda, los discos son hardware lento. Pero eso no significa que nos tengamos que rendir, existen una serie de optimizaciones que podremos llevar a cabo para paliar este problema en la medida de lo posible.

Velocidad de E/S de discos

Lo primero y lo más obvio será elegir un hardware de calidad acorde a nuestras necesidades. La velocidad de los discos se mide en E/S y no es igual para un disco SATA que para uno SAS (que duplica la velocidad de SATA) que para un disco SSD. Por no hablar de los SSD M2 y NVMe que han supuesto un verdadero avance en este sentido. El problema es que los discos duros, o son rápidos, o son grandes o son económicos, pero las tres cosas a la vez son, a día de hoy, imposibles. Esto hace que tengamos que priorizar, eligiendo discos más rápidos para las bases de datos con más volumen de operaciones como la TempDB y para los ficheros de log.

Separación de discos

Los discos están limitados en velocidad, vale, pero podemos jugar con lo que se llama un escalado horizontal, haciendo crecer nuestro servidor con más discos y aprovechando la ventaja de lectura y escritura simultánea entre ellos. En la era de la virtualización y las redes de almacenamiento NAS y SAN es aún más importante esta separación y tendremos que tener en cuenta más niveles que solo los discos duros. Entrarán en juego conceptos como el uso de varios agregados, LUNs, controladoras y hasta distintas interfaces físicas. 

RAID

Elegir un nivel correcto de RAID para nuestros duros también va  a marcar la diferencia de velocidad. No nos vamos a extender mucho en este tema pues ya le hemos dedicado un artículo completo en el blog hace pocos días. Os recomiendo su lectura si aún no lo habéis hecho.

Optimizaciones en SQL para reducir la E/S

Pero Roberto, mi servidor ya está montado y como DBA no tengo la posibilidad de modificar configuraciones de arquitectura de infraestructura. Tranquilos, jóvenes aprendices, no hace falta una herramienta descomunal para un resultado satisfactorio. Solo hay que saber usar bien lo que tenemos. Y sigo hablando de SQL, mal pensados. Esto que os voy a decir os va a parecer una obviedad, pero es la clave del éxito. Si tienes problemas con las lecturas en disco, haz menos lecturas en disco. Ahora te lo explico, no te preocupes. Menos lecturas en disco no significa que tengas que empezar a cortar el acceso a los usuarios o limitar las consultas, esto lo vamos a lograr con una serie de optimizaciones. 

Limpieza de datos

Esto es clave, todos tendemos a almacenar datos con una especie de síndrome de diógenes digital que de vez en cuando tenemos que evaluar. ¿Realmente necesitamos toda la información que estamos almacenando? En ocasiones estamos sometidos a unas regulaciones que nos implican una retención de los datos por varios años pero, si solo tenemos que almacenarlos y no se consultan, ¿qué hacen en la base de datos de producción? ¿Podrían irse a un backup y eliminarlos de la base de datos? Consulta con el responsable de los datos y el departamento legal si esto es posible.

Bases de datos histórico

Si los datos antiguos tienen pocas lecturas pero las suficientes como para no poder borrarse y almacenarse sólo en una copia de seguridad siempre podremos crear una base de datos de datos históricos y sacarlos de la base de datos principal. Dependiendo de la frecuencia de actualización de estos datos históricos, incluso podremos sacarlos del plan de mantenimiento de índices y de copias de seguridad diarios ahorrando tiempo y espacio y destinando esos recursos a los datos que realmente necesitamos. 

Particionado

El particionado de las tablas es otro potente recurso que tenemos a nuestra disposición para reducir el uso de lecturas en disco. Separando una tabla en distintas particiones, por ejemplo por fecha podremos reducir el número de páginas que SQL Server necesita consultar. Profundizaremos sobre este tema en un futuro artículo.

Índices

Después de ver que unos buenos índices son la solución a los problemas de alto consumo de RAM y de CPU si ahora os digo que también lo son a las lecturas en disco os va a parecer que los uso como un comodín para todo. Pero es que realmente unos buenos índices que cubran las consultas que ejecutamos nos ayudará a reducir el número de páginas leídas, reduciendo enormemente las necesidades de E/S. Y ese es el motivo subyacente por el que se cargan menos páginas en RAM y el procesador trabaja menos.

Conclusión

Los discos duros de nuestros servidores son el hardware más lento y donde almacenamos toda la información, eso los hace propensos de ser uno de los causantes más comunes de cuellos de botella para el rendimiento de nuestras consultas. Con unas buenas prácticas de implementación tanto de infraestructura como de arquitectura y modelado de los datos podemos reducir al mínimo este problema. Si te ha gustado esta serie de artículos dedicados a reducir incidencias de rendimiento por falta de recursos permanece atento al blog que mañana vamos a publicar la guía definitiva de creación de índices para solucionar los problemas.

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, 1 comentario
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