Identificando planes de ejecuciòn ineficientes

El tema de optimizar nuestra Base de datos es bastante amplio, esta entrada será solo una de una serie que pretendo subir tocando diversos puntos para identificar y solucionar problemas en nuestra Base de Datos.
Recuerdo haber tenido contacto con un administrador de Base de datos al que se le cuestionaba el rendimiento tan pobre de la Base de datos, su respuesta era mostrarnos una grafica del Monitor de performance en donde se veían picos en el servidor y cuellos de botella en el procesador. ¿Pero eso es suficiente? La respuesta es no, los cuellos de botella en nuestro procesador se pueden deber a diversas causas, es decir eso solo es lo que se ve por fuera, pero tenemos que investigar que es lo que exactamente hizo que el procesador estuviera tan demandado.
Para hacerlo una opción es ejecutar una traza y analizarla pero también debemos siempre buscar apoyarnos en las DMVs que SQL desde la versión 2005 nos proporciona, estas son vistas y funciones que nos proporcionan detalle del comportamiento de nuestra Base de datos.
En esta ocasión las usaremos para obtener un script que nos permita identificar las consultas más costosas en términos de procesador, el resultado se debe analizar a fondo, pues un escenario posible es que tengamos una consulta sumamente costosa pero que se ejecuta solo esporádicamente y quizá otra menos costosa pero que se ejecuta cientos o miles de veces, entonces debemos nosotros saber a dónde apuntar nuestra atención.
Select Top 10
total_worker_time/execution_count as avg_cpu_cost,plan_handle,
execution_count,(Select substring(text,statement_start_offset/2+1,
(Case when Statement_end_offset=-1
Then len(convert(nvarchar(max),text))*2
Else Statement_end_offset
End - statement_start_offset)/2)
From sys.dm_exec_sql_text(sql_handle)) as query_text
From sys.dm_exec_query_stats
Order by [avg_cpu_cost] desc

Lo que nos regresa esta consulta es el porcentaje promedio de CPU que es utilizado cada vez que se ejecuta una consulta , el número de veces que es invocada esa consulta , el Id del plan de ejecución y por último la consulta en sí, nuestro trabajo es una vez identificadas poder aislar el porqué de ese costo.

Nota: La ejecuciòn de esta consulta unicamente nos traera los planes que SQL mantiene en el cache, es decir pudieran existir otros que no nos arroje la consulta . Para resolver este problema la recomendaciòn de Microsoft es que ejecutemos periodicamente esta consulta para obtener informaciòn mas completa.

Por ahora con esto termino este post, esto solo es una pequeña parte de las cosas que tenemos que considerar cuando buscamos optimizar nuestra Base de datos, saludos y ojala les sea de utilidad.