Surviving the Night

Out of Time: A vueltas con los planes compilados en SQL Server 2008

Dicen por ahí que rectificar es de sabios... así que aquí va mi momento de sabiduría del día: en la entrada anterior compartía con vosotros algunas de las sorpresas que me he ido encontrado en la RC0 de SQL Server 2008, y también os decía que debido al poquito tiempo que me queda libre estas semanas, seguramente no podría postear mucho más sobre el producto a corto plazo. Pues bien, heme aquí, humillándome y escribiendo una entrada sobre una nueva característica de SQL Server 2008 :)

La verdad es que no he podido evitarlo, realmente me parece que este va a ser otro de esos detalles 'pequeños' pero que pueden marcar una diferencia importante; en este caso a nivel de rendimiento. Vamos para allá...

Optimización para consultas Ad Hoc

El otro día, mientras leía los Books Online (la documentación del producto) de SQL Server 2008 RC0 me tropecé, como quien no quiere la cosa, con la característica que os voy a comentar ahora. Nada mas leerla me vino a la mente el artículo sobre el ciclo de vida de las consultas y el pequeño y bonito debate que se abrió en los comentarios, y me parece que complementa muy bien lo comentado en su momento en aquella entrada, por lo que no puedo resistirme a comentarla brevemente por aquí.

Haciendo un poco de memoria...

¿Recordáis el problema de las consultas ad hoc en SQL Server? Comentábamos que éstas, al contrario que los procedimientos almacenados y que las consultas parametrizadas, no tenían en cuenta los parámetros a la hora de almacenar y reutilizar su plan de ejecución (para una explicación detallada, echádle un vistazo al post antes referido).

Vamos a ver un ejemplo sobre la base de datos AdventureWorks:

USE AdventureWorks
GO

-- Limpiamos la Cache de planes de ejecucion
DBCC FREEPROCCACHE
GO

-- Ejecutamos esta consulta 5 veces
SELECT *
FROM Sales.SalesOrderDetail
WHERE UnitPrice = 5.70
GO

-- Ejecutamos esta consulta 5 veces
SELECT *
FROM Sales.SalesOrderDetail
WHERE UnitPrice = 21.98
GO

-- Miramos nuestros planes de ejecucion en la cache de planes
SELECT
    usecounts,
    size_in_bytes,
    cacheobjtype,
    objtype
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'

Como podéis ver, la demo se compone de los siguientes pasos:

1. Limpiamos la Caché de Procedimientos Almacenados:
   
   Inicialmente limpiamos la caché de planes de ejecución mediante el comando DBCC FREEPROCCACHE, con el único objetivo de facilitarnos luego la búsqueda de los planes que se han generado por las consultas de la demo. Por razones evidentes, no debemos ejecutar este comando en un servidor de producción.
   
2. Ejecutamos una consulta con dos parámetros diferentes:
   
   A continuación lanzamos una consulta sobre AdventureWorks. Como podéis ver, lanzamos la misma consulta con dos parámetros, ejecutando 5 veces cada una de ella.
   
   En un mundo ideal SQL Server sería lo suficientemente inteligente como para detectar que son la misma consulta pero con diferentes parámetros, y que su plan de ejecución no va a variar ya que es trivial. ¡¡Vamos a ver si es así!!
   
   
3. Monitorizamos la Cache de planes de ejecución:
   
   ¿Vivimos en ese mundo ideal? ¿Cómo podemos comprobarlo? Lo primero que se me ha ocurrido es dar un par de palmadas al aire a ver si por arte de magia aparecía una cerveza fresquita aquí la lado, pero no ha funcionado. Mmmm.. me tomaré eso como un primer indicio de que no vivimos en un mundo ideal :(
   
   El segundo indicio lo sacamos de la vista de administración dinámica sys.dm_exec_cached_plans, en la que podremos observar como, al contrario de lo que sucede con los procedimientos almacenados o las consultas parametrizadas, los planes de ejecución no se han reutilizado. Como se puede ver en la siguiente captura de pantalla, tenemos dos planes de ejecución con 5 ejecuciones cada uno.

Bien, hasta aquí nada nuevo bajo el sol, ya que se trata del comportamiento por defecto de SQL Server 2005 y SQL Server 2008. Sin embargo os prometí que os traería novedades, y aqui vienen...

Let's Get Rocked!! Optimize for Ad Hoc Workload

Los planes de ejecución consumen memoria... ¡y no poca! Como podemos ver en la captura de pantalla, en el caso los planes de ejecución del ejemplo anterior cada uno de los planes se lleva 24576Kb... ¡24 Kb, 3 páginazas por plan, nada más y nada menos! Y eso que son planes pequeñitos...

Esto puede plantear problemas en un escenario lamentablemente muy común; aplicaciones que lanzan consultas ad-hoc directamente contra nuestro servidor. A no ser que siempre se repitan las consultas con los mismos parámetros, cosa bastante improbable, el número de planes de ejecución en la caché crecerá vertiginosamente, dando lugar a problemas de presión de memoria, que a su vez obligará a sacar planes 'antiguos', que en ultima instancia (y para terminar con nuestra cadena de males) implicará que se tengan que recompilar con mucha mayor frecuencia los planes de ejecución, con el aumento de coste de CPU asociado.

En SQL Server 2008 aparece una nueva opción de configuración del servidor, llamada 'optimize for ad hoc workloads', que viene a mejorar el comportamiento del servidor en este tipo de escenarios. La idea principal es que, cuando se lance una consulta ad hoc no parametrizada contra el servidor, éste no almacene el plan de ejecución completo. En su lugar, almacenara un pequeño 'stub', muy liviano.

Este stub sirve para poder almacenar una entrada en la caché de planes de ejecución, de modo que el servidor sepa que esa consulta ya se ha ejecutado alguna vez en el sistema. Lo bueno de este mecanismo es que el stub ocupa muy poca memoria, lo malo es que el stub no es un plan de ejecución. Por tanto en su siguiente ejecución, hay que volver a compilar la consulta.

Como optimización adicional, siempre que una consulta se ejecuta por segunda vez, SQL Server supone que esta consulta va a ejecutarse más veces con el mismo parámetro, por lo que, de algún modo, promociona su plan de ejecución, y en lugar del stub almacena su plan compilado real.

Vamos a ver un pequeño ejemplo:

-- Habilitamos la opcion de 'optimize for ad hoc workloads'
sp_configure 'show advanced options',1
GO
sp_configure 'optimize for ad hoc workloads',1
RECONFIGURE
GO

-- Limpiamos la cache de planes de ejecucion
DBCC FREEPROCCACHE
GO

-- Vamos a hacer las demos sobre AdventureWorks
USE AdventureWorks
GO

-- Vamos a ver una consulta auto parametrizada
SELECT *
FROM Sales.SalesOrderDetail
WHERE UnitPrice = 5.70
GO

-- Vemos ahi el plan stub para la consulta
SELECT
    usecounts,
    size_in_bytes,
    cacheobjtype,
    objtype
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'

-- Vamos a ver como, para la consulta autoparametriazada, no se nos
-- crea un plan real: vemos como la consulta tiene como query_plan NULL
SELECT sql.text, p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE text NOT LIKE '%sys_dm_exec%' AND text NOT LIKE '%msparam_0%'
ORDER BY qs.EXECUTION_COUNT DESC
GO

-- Deshabilitamos la opcion y volvemos a hacer las pruebas para ver
-- como no se generan los stubs
sp_configure 'show advanced options',1
GO
sp_configure 'optimize for ad hoc workloads',0
RECONFIGURE
GO

Como vemos en la demo, al mirar la caché de planes de ejecución nos encontramos con lo siguiente:

Es interesante resaltar que el stub del plan de ejecución ocupa solamente 336 bytes ( 1 página)... ¡una gran diferencia respecto al tamaño del plan de ejecución real!

Si además tratamos de ver cual es el plan de ejecución almacenado para esa entrada en la caché, veremos que nos devuelve NULL:

Como vemos, de cara a minimizar el consumo de CPU, de memoria (en la caché de procedimientos), incluso de un modo indirecto, de E/S (menos paginas, menos fallos de página), este mecanismo tiene un aspecto genial. Eso si, se recomienda emplearlo solo en los servidores que sean atacados por aplicaciones que hagan uso masivo de consultas ad-hoc, y no empleen tantos procedimientos almacenados ni queries parametrizadas.

Y ahora, los deberes ;)

Para terminar, os animo a que hagais pruebas en vuestros entornos o aplicaciones. Yo aún no he podido sacar tiempo para probarlo en condiciones y estudiar su rendimiento sobre ningún producto, pero en el momento que pueda hacer una comparativa os lo haré saber.

Si me gustaría que, si os animáis a hacer las pruebas, me comentéis por aquí vuestros resultados. Realmente ha sido una característica que me ha gustado mucho, y tengo interés por ver su adopción y resultados :)

Rock Tip:

Es la primera vez que escojo un tema de los noruegos Wig Wam para acompañar una entrada de mi blog; en este caso se trata de 'Out of Time', un medio tiempo bestial, de impecable factura, y que deja claro el buen nivel instrumental y compositivo de esta banda, a pesar de la continua autoparodia que hacen de si mismos sus componentes.

Lo cierto es que todos los chicos de Wig Wam (Glam, Teeny, Flash y Sporty) ya tenían bastante experiencia antes de decidirse a montar un grupo a medio camino entre el homenaje y la parodia de las grandes bandas de hard rock, AOR y glam de los años ochenta y primeros noventa, ¡y eso se nota! Sin embargo, no saltaron a la luz pública principalmente gracias a su participación en Eurovision en 2005, con su temazo 'In My Dreams'. A partir de entonces su vida se ha convertido en un no-parar de estadios con decenas de miles de asistentes, y de hoteles con cientos de grouppies dispuestas cada noche... o eso dicen ellos! :) Sea como sea, esta claro que son un grupo muy divertido y recomendable!