These Days: Un día en la vida de una consulta...
Es de sobra conocida por todos la frase que nos enseña que aquel que no conoce la historia está condenado a repetirla. En mi caso, a pesar de ser un apasionado de la historia, debo ser tonto como un cepillo, porque voy a repetir un error garrafal: voy a abrir múltiples frentes. Con esto no me refiero a que vaya a adoptar un modo berserker y vaya a salir a la calle a liarme a manotazo limpio con el primero que me encuentre hasta abrirle la frente, sino que me he decidido a empezar una nueva serie de artículos, en este caso dedicados a la identificación y resolución de problemas de rendimiento en SQL Server.
Espero que esto no me quite demasiado tiempo para la serie sobre depuración, pero creo que es la mejor forma de abordar los artículos acerca del rendimiento en SQL Server. Estuve dándole unas vueltas y creo que organizándolo de este modo podré darle la continuidad y el trasfondo teórico que quiero, algo que no podría hacer si lo organizara como artículos sueltos de 'tips and tricks'.
Así que, sin más preámbulos, y como dirían 'Les Luthiers', vamos a 'principiar' por el principio...
El lenguaje SQL
Aquí conviene recordar un detalle que, a pesar de ser evidente, muchas veces no tenemos en cuenta. El lenguaje SQL es un lenguaje declarativo: el usuario solicita al servidor unos datos determinados, pero no le indica cómo se deben obtener estos datos, ni que algoritmos se emplearán para su extracción, etc... Eso lo decidirá el propio motor de la base de datos que, tal como veremos en éste post, tiene a su disposición una serie de metadatos y estructuras (índices, estadísticas, etc...) que le ayudan a obtener estos datos solicitados del modo más eficiente posible.
Si en lugar de usar un lenguaje como TSQL empleáramos un lenguaje imperativo, como C++ o C#, nosotros tendríamos que especificar paso a paso las operaciones para extraer los datos; el algoritmo empleado para obtener los datos que queremos. Tendríamos que recorrer listas, árboles, aplicar nuestros amados Quick Sort y demás...
¿Cómo se procesan las consultas?
En el siguiente diagrama trataré de representar una simplificación del ciclo de vida de una consulta de SQL Server, desde el momento en que la consulta llega al servidor hasta el momento en que se retorna el resultado al cliente:
Como podéis ver por la simplicidad del diagrama, no vamos a entrar a estudiar las internalidades del procesado de consultas; mi objetivo aquí es establecer una base que luego vayamos extendiendo en sucesivas entradas de este blog. Para poder realizar esta simplificación, me tomaré algunas licencias por lo que lo que voy a comentar aquí debajo no es correcto al 100%, pero la idea de funcionamiento es la misma.
Dicho esto, vamos a ver que sucede en cada una de las etapas descritas en el diagrama:
En esta fase se evalúa la sentencia T-SQL que se pretende ejecutar y se comprueba que no tenga errores de sintaxis, que la semántica sea correcta (es decir, que si intenta acceder a una tabla llamada Contactos, esa tabla exista y se tenga acceso a ella), etc.. Como salida del parser, se obtiene una versión de la consulta 'limpia' y normalizada, que describe, de modo no ambiguo, QUE datos quiere obtener el usuario.
Bien, ese 'qué' ya está decidido y comprendido por SQL Server; ahora lo que necesita es el CÓMO obtener los datos. Esto vendrá definido por el Plan de Ejecución. ¡Oh, el Plan de Ejecución! Ésta palabra será clave para nosotros en tantos y tantos escenarios de optimización, sobre todo desde un punto de vista del desarrollo. En resumen, el plan de ejecución será una especie de lista que describirá paso a paso como obtener la información solicitada: que tablas iremos a buscar, que índices emplearemos, como uniremos los datos de unas tablas con otras, etc.
Bien, hecha pues la introducción a los planes de ejecución, os puedo comentar que en éste punto se comprueba también si esa consulta ya se ejecutó anteriormente y si se almacenó un plan de ejecución para ella. Si es así, SQL Server ejecuta directamente los pasos dictados por ese plan de ejecución y devuelve los resultados al cliente.
Si no hubiera un plan de ejecución cacheado, pasaríamos a la siguiente etapa...
Aquí es donde empieza lo divertido. Recordemos, por enésima vez, que SQL es un lenguaje declarativo; eso quiere decir que al contrario de los lenguajes imperativos, como C++, donde le suministramos al ordenador una serie de pasos para obtener un resultado, en SQL le pedimos a la máquina unos resultados (e.j: dame todos los contactos que vivan en España y tengan mas de 30 años), pero la manera de obtener estos resultados es cosa del servidor. Si va a iterar por todos los elementos de la tabla, si va a usar un índice u otro, o ninguno, si primero va a filtrar por nacionalidad o por edad, esas son decisiones que el SQL Server va a tomar por nosotros.
Por ello SQL Server compila muchas veces cada consulta, generando con cada compilación un plan de ejecución. Al final, el compilador se decidirá por el más eficiente de los múltiples planes generados, y pasará a la siguiente etapa.
¿Y cómo sabe SQL Server cual es el plan mas eficiente? Pues porque los planes de ejecución estiman determinados parámetros, cómo el número de filas que va a devolver cada operación, el coste estimado de CPU, lecturas y escrituras estimadas, etc. para cada una de las operaciones de lo componen. Sumando estas estimaciones puede obtener un índice que le sirve para identificar que planes son mejores que otros, con lo que la elección se reduce (casi siempre) a seleccionar el plan con el índice más bajo.
Cuando el SQL Server se ha decidido por el plan mas eficiente, se suele proceder a almacenar este plan de ejecución en una caché de planes de ejecución para que la próxima vez que se lance la consulta no se tenga que recompilar.
Esta caché se almacena en una zona especial del Buffer Pool de SQL Server, y evidentemente es un recurso finito. Hay que evitar tener demasiados planes de ejecución cacheados para que no se nos sature y SQL Server tenga que eliminar planes 'validos' para hacer sitio a planes de ejecución nuevos... pero esa es otra historia ;)
Un poco de práctica: procedimientos almacenados vs. consultas ad hoc
Para ver todo esto en la práctica, me vais a permitir que me ponga mi disfraz de Cazadores de Mitos (¡¡me pido a Jamie!! ¡De mayor quiero dejarme un bigote como el suyo!) y rebusque en el folklore de SQL Server... un segundito...
Un segundito más....
¡Aquí está! Mito número 208: "Las consultas ad-hoc son menos eficientes que los procedimientos almacenados"... Según el mito, las consultas ad-hoc son, por un motivo u otro, menos eficientes que la misma consulta encapsulada dentro de un procedimiento almacenado. ¿será verdad? ¿será mentira? Vamos a verlo...
NOTA: Este pequeño ejemplo práctico esta orientado a SQL Server 2005 y 2008; en versiones anteriores no funcionará debido a la ausencia de las DMV's (vistas de administración dinámicas) que vamos e emplear.
Vamos a empezar creando unos datos de prueba:
CREATE DATABASE PruebaPlanesEjecucion
GO
USE PruebaPlanesEjecucion
GO
CREATE TABLE dbo.Persona
(
ID INT IDENTITY PRIMARY KEY NOT NULL,
Nombre NVARCHAR(40) NOT NULL
)
INSERT INTO dbo.Persona VALUES ('Juan')
INSERT INTO dbo.Persona VALUES ('Pepe')
INSERT INTO dbo.Persona VALUES ('Arturo')
INSERT INTO dbo.Persona VALUES ('Wenceslao')
INSERT INTO dbo.Persona VALUES ('Miguel Angel')
En el artículo hablábamos de los planes de ejecución para las consultas SQL. Pues bien, vamos a aprovechar para ver el plan de ejecución de una consulta sobre nuestros datos de pruebas. Para ello escribimos la siguiente consulta:
SELECT * FROM dbo.Persona WHERE id = 2
Seleccionamos la consulta y, en lugar de ejecutarla con CTRL+E o F5, pulsamos CTRL+L; esto nos mostrará el plan de ejecución estimado para la consulta; otro día hablaremos de las diferencias entre los planes estimados y reales. El resultado que obtendremos será algo parecido a este:
Como se aprecia en al captura, desde el SQL Server Management Studio (SSMS) podemos ver gráficamente el plan de ejecución de nuestra consulta. Hay más maneras de verlo, pero generalmente ésta es la más práctica.
Comentábamos que SQL Server tiene una caché de los planes de ejecución, que podemos consultar mediante una select sobre la DMV sys.dm_exec_cached_plans. En mi máquina de pruebas me devuelve 193 filas, esto es, 193 planes de ejecución cacheados, lo que hace muy complicado identificar rápidamente que plan corresponde a la consulta que hemos lanzado.
Para ayudarnos a identificar el plan de ejecución en esta prueba, vamos a hacer algo que NO debemos hacer en producción; vamos a limpiar la caché de planes de ejecución. Así será fácil identificar la consulta que acabamos de lanzar, pero repito que éstos experimentos deben quedarse para nuestras máquinas de pruebas.
DBCC FREEPROCCACHE
GO
SELECT * FROM dbo.Persona WHERE ID = 2
SELECT usecounts, cacheobjtype,objtype FROM sys.dm_exec_cached_plans
Podemos ver que en la cache tenemos tres planes: el nuestro es el que tiene de objtype 'Adhoc' y que, como podemos ver en su columna usecounts, solo se ha ejecutado una única vez.
Vamos a hacer una prueba: vamos a volver a eliminar la cache (DBCC FREEPORCCACHE) y vamos a ejecutar 10 veces seguidas la misma consulta(nada avanzado aquí, simplemente seleccionamos la query y pulsamos F5 10 veces... que estoy vago hasta para hacer un bucle ;)).
Comprobamos como, efectivamente, aparece un plan de ejecución que se ha reutilizado 10 veces: ese es nuestro plan!
Hablando de nuestro plan... ¿os acordáis de él? Era un plan muy, pero que muy simple. Por tanto, cabría suponer que si cambiamos el parámetro que pasamos a la where (el ID) por otro valor diferente, el plan de ejecución debería ser el mismo. De hecho, os animo a que lo comprobéis, cambiando la consulta y mirando su plan de ejecución; veréis que es idéntico.
Ok, siendo el plan de ejecución es el mismo, si ahora ejecutáramos 10 veces deberíamos ver el plan con un usecounts de 20, ¿no? ¡Pues no! Vamos a hacer la prueba:
DBCC FREEPROCCACHE
GO
-- Ejecutamos esta 10 veces
SELECT * FROM dbo.Persona WHERE ID = 2
-- Ejecutamos esta otra 10 veces
SELECT * FROM dbo.Persona WHERE ID = 4
-- Miramos a ver que tenemos...
SELECT usecounts, cacheobjtype,objtype FROM sys.dm_exec_cached_plans
¿Que resultado obtenemos? Pues como se ve en la siguiente captura, no se esta reaprovechando el plan de ejecución, en su lugar tenemos dos planes de ejecución diferentes (aunque virtualmente idénticos), cada uno con 10 ejecuciones.
Esto es significativo: si cada vez que cambiamos de parámetro a nuestras consultas tiene que recompilar el plan y generar uno nuevo, tenemos al menos dos problemas: el coste adicional de CPU asociado a las recompilaciones, y el incremento de planes en la cache, que podrá alcanzar su limite de tamaño y tener que sacar planes de ejecución para hacer sitio a los nuevos.
Vamos a ver que sucede si implementamos esta consulta como un procedimiento almacenado:
-- Vamos a implementar esta consulta como un procedimiento almacenado
CREATE PROCEDURE testProc
@id INT
AS
BEGIN
SELECT * FROM dbo.Persona WHERE ID = @id
END
DBCC FREEPROCCACHE
GO
-- Ejecutamos esta 10 veces
testProc 2
-- Ejecutamos esta otra 10 veces
testProc 4
SELECT usecounts, cacheobjtype,objtype FROM sys.dm_exec_cached_plans
Como podemos ver, finalmente conseguimos que se reutilice el plan de ejecución, ya que el numero de ejecuciones total es 20.
Volviendo al mito... ¿son las consultas ad-hoc menos eficientes que los procedimientos almacenados? Visto lo visto, podemos concluir que el mito es... ¡CIERTO! :) ¿Quiere esto decir que debemos usar procedimientos almacenados para todo? No, ni mucho menos; no todo en esta vida es rendimiento :) Pero esa también es otra historia...
Por cierto, para que no tengáis que andar copiando y pegando del blog, os adjunto un demoscript con toda demostración.
Conclusiones:
Si habéis llegado hasta aquí, mi más sincera enhorabuena :) Ha sido una entrada bastante extensa, con bastante chicha si los conceptos son desconocidos. Hemos revisado el ciclo de vida de la consulta, hemos introducido el concepto de plan de ejecución y hemos hecho un ejercicio sobre reutilización de planes de ejecución bastante interesante... ¡casi nada!
Estoy convencido de que la mayor parte de vosotros ya estabais familiarizados con todos los conceptos descritos en éste articulo. De todos modos, para mi es importante tener un artículo de introducción al que poder hacer referencia desde otros posts de contenido más avanzado que sin duda aparecerán en este blog en breves.
Por tanto, si sois nuevos a SQL Server y su optimización, ¡gracias por haber llegado hasta aquí! Espero vuestros comentarios y dudas al respecto. Y si ya teníais experiencia en SQL Server y en la optimización, ¡espero poder contentaros en pocas semanas con entradas más interesantes y enriquecedoras!
¡Hasta pronto!
Rock Tip:
La razón por la que he elegido el tema 'These Days' (de mis Bon Jovi de toda la vida) para éste post es que estos días, últimamente, he explicado el funcionamiento básico del Query Processor y el Query Optimizer en muchas ocasiones; parece que es mi tema de moda, y es lo que me ha impulsado a iniciar esta sesión respecto a la optimización de consultas.
Respecto a la canción en si, os diré que le tengo un gran cariño: el segundo concierto al que fui en mi vida fue precisamente un concierto de Bon Jovi, en la gira de presentación del These Days allá por el 96. Fue, sin duda, uno de los mejores días de mi vida, y uno de los mejores conciertos que nadie pueda soñar con ver. No se trata, ni de lejos, de mi tema favorito de ellos; es más, muchos estaréis de acuerdo conmigo en que éste el albúm marca el comienzo de su declive. Sin embargo le tengo mucho cariño, quizá por aquel concierto. Haber visto a Jon y Richie cantando las canciones de cowboy en acústico te marca para toda la vida; quizá a ese momento le debo mi afición a las botas camperas :)