Cuando trabajamos contra un SQL Server ‘on premise’ contamos con un motón de herramientas para averiguar que consultas están siendo más costosas y que índices podrían ayudarnos a mejorar el rendimiento de nuestra base de datos. Cuando trabajamos con SQL Azure, contamos un arsenal mucho más reducido de armas. Hoy quiero compartir dos consultas que me han sido de suma utilidad a la hora de mejorar el rendimiento de un importante proyecto que estamos realizando en Plain Concepts con SQL Azure.
La primera es una consulta que nos permite saber, a partir de la información que SQL Server almacena sobre los planes de ejecución qué consultas están siendo más costosas. Esta consulta extrae información de la vista dm_exec_query_stats que almacena estadísticas sobre la ejecución de consultas. En este caso la consulta muestra, por este orden, el texto de la consulta, el tiempo total consumido por todas las ejecuciones de cada consulta, el máximo tiempo que ha tardado en ejecutarse esa consultas, el tiempo mínimo que ha tardado alguna ejecución, el tiempo que ha tardado la última ejecución, el número de veces que se ha ejecutado la consulta, y la hora de la última ejecución. Todo esto ordenado por el tiempo total consumido por la consulta. Esta consulta nos permite ver de manera rápida que consultas están siendo más costosas para nuestro SQL Azure.
SELECT TOP (20)
q.text, s.total_elapsed_time, s.max_elapsed_time, s.min_elapsed_time,
s.last_elapsed_time, s.execution_count, last_execution_time, *
FROM sys.dm_exec_query_stats as s
cross apply sys.dm_exec_sql_text(plan_handle) AS q
WHERE s.last_execution_time > DateAdd(mi , –1500 , GetDate()) — solo las que se han ejecutado recientemente
AND text not like ‘%sys.%’ — eliminar esta propia consulta
ORDER BY s.total_elapsed_time DESC
La vista en la que se basa esta consulta, dm_exec_query_stats , contiene un motón de campos interesantes con información que os invito a explorar.
La segunda consulta que me ha sido de gran utilidad permite saber qué índices podrían ser beneficiosos en nuestra base de datos y cual sería en impacto (entendido como la mejora esperable si se crea el índice). Se basa en una capacidad de SQL Server llamada ‘missing indexes’.
SELECT TOP (20)
CAST(REPLACE(CAST(qp.query_plan AS NVARCHAR(MAX)),
‘xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"’,») AS XML),
qp.query_plan.value(
‘declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]’ , ‘decimal(18,4)’) * execution_count AS TotalImpact
FROM
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
WHERE
qp.query_plan.exist(
‘declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex[@Database!="m"]’) = 1
ORDER BY TotalImpact DESC
Estas consultas son perfectamente validas para un SQL Server ‘on premise’, pero resultan mucho más útiles cuando estamos trabajando contra un SQL Azure, pues carecemos de otras herramientas.