Cuando se trabaja con SQL Server, es importante comprender el concepto de tiempo de compilación. El tiempo de compilación se refiere al tiempo que SQL Server tarda en analizar y compilar un comando SQL antes de ejecutarlo. En este artículo, exploraremos diferentes métodos para medir y analizar el tiempo de compilación en SQL Server.

1. SET STATISTICS TIME ON

Una forma de medir el tiempo de compilación es utilizando el comando SET STATISTICS TIME ON. Al habilitar esta opción, no solo se proporciona el tiempo de compilación de un comando SQL, sino que también se muestran los tiempos de ejecución. Al habilitar esta opción, puede solucionar problemas y analizar el rendimiento de comandos SQL individuales. Aquí hay un ejemplo:

USE [AdventureWorks]
GO

SET STATISTICS TIME ON
GO

EXEC [uspGetManagerEmployees] 100
GO

SET STATISTICS TIME OFF
GO

Cuando ejecuta el comando anterior, SQL Server mostrará el tiempo de compilación en la salida. Si ejecuta el mismo comando nuevamente, notará que el tiempo de compilación se reduce o incluso se elimina. Esto se debe a que el plan de ejecución se ha almacenado en caché y no se necesita ninguna compilación.

2. SQL Profiler (o SQL Trace)

Otro método para medir el tiempo de compilación es utilizando SQL Profiler o SQL Trace. Estas herramientas le permiten monitorear y capturar el tiempo de compilación de comandos SQL en cualquier sesión de cliente. Sin embargo, es importante tener en cuenta que el uso de SQL Profiler puede tener un impacto en el rendimiento, especialmente en comparación con el uso de los procedimientos almacenados sp_trace_xxxx.

Existen diferentes formas de medir el tiempo de compilación según el tipo de comando SQL:

a) Lote SQL

El tiempo de compilación de un lote SQL se puede calcular utilizando la siguiente fórmula:

Tiempo de compilación del lote = (SQL:StmtStarting Hora de inicio de la primera instrucción SQL en el lote) - (SQL:BatchStarting Hora de inicio)

Puede habilitar el evento SQL Profiler para capturar el tiempo de compilación del lote. Aquí hay un ejemplo:

USE [AdventureWorks]
GO

-- Habilitar el evento SQL Profiler
-- SQL:StmtStarting captura la hora de inicio de cada instrucción SQL en el lote

Al ejecutar el script anterior y analizar el registro de eventos capturado, puede calcular el tiempo de compilación del lote.

b) Procedimiento almacenado

El tiempo de compilación de un procedimiento almacenado se puede calcular utilizando la siguiente fórmula:

Tiempo de compilación del procedimiento almacenado = (SP:StmtStart Hora de inicio de la primera instrucción SQL en el procedimiento almacenado) - (SQL:StmtStarting Hora de inicio de la instrucción SQL del llamador)

Seleccionando el evento SQL Profiler adecuado, puede capturar el tiempo de compilación de un procedimiento almacenado. Aquí hay un ejemplo:

USE [AdventureWorks]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 100

SELECT 'Valor de retorno' = @return_value
GO

Al ejecutar el script anterior y analizar el registro de eventos capturado, puede calcular el tiempo de compilación del procedimiento almacenado.

c) T-SQL dinámico

El tiempo de compilación de una consulta dinámica se puede calcular utilizando la siguiente fórmula:

Tiempo de compilación de la consulta dinámica = (Hora de inicio de StmtStarting de la consulta dinámica) - (Hora de inicio del comando EXEC de StmtStarting en el lote o procedimiento almacenado)

Al crear una consulta dinámica dentro de un lote SQL y seleccionar el evento SQL Profiler adecuado, puede capturar el tiempo de compilación de la consulta dinámica. Aquí hay un ejemplo:

DECLARE @query varchar(max)
SET @query=' WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel])
-- Nombre y columnas de CTE
AS
(
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 -- Obtener la lista inicial de empleados para el gerente n
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = 100
UNION ALL
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Unir miembro recursivo al ancla
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte] ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
)
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS ''ManagerFirstName'', p.[LastName] AS ''ManagerLastName'', [EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Selección externa de CTE
FROM [EMP_cte]
INNER JOIN [HumanResources].[Employee] e ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
OPTION (MAXRECURSION 25) '

EXEC (@query)
GO

Al ejecutar el script anterior y analizar el registro de eventos capturado, puede calcular el tiempo de compilación de la consulta dinámica.

3) DMV

Las vistas de administración dinámica (DMV) también se pueden utilizar para recuperar información sobre el tiempo de compilación. Sin embargo, es importante tener en cuenta que las DMV tienen limitaciones, como no poder recuperar información de compilación para planes que no están en la caché. Además, la siguiente consulta solo es compatible con SQL Server 2008 y versiones superiores:

-- Encontrar planes de recursos de compilación alta en la caché de planes
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP 10 CompileTime_ms, CompileCPU_ms, CompileMemory_KB, qs.execution_count, qs.total_elapsed_time/1000 AS duration_ms, qs.total_worker_time/1000 as cputime_ms, (qs.total_elapsed_time/qs.execution_count)/1000 AS avg_duration_ms, (qs.total_worker_time/qs.execution_count)/1000 AS avg_cputime_ms, qs.max_elapsed_time/1000 AS max_duration_ms, qs.max_worker_time/1000 AS max_cputime_ms, 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 StmtText, query_hash, query_plan_hash
FROM (
    SELECT c.value('xs:hexBinary(substring((@QueryHash)[1],3))', 'varbinary(max)') AS QueryHash,
           c.value('xs:hexBinary(substring((@QueryPlanHash)[1],3))', 'varbinary(max)') AS QueryPlanHash,
           c.value('(QueryPlan/@CompileTime)[1]', 'int') AS CompileTime_ms,
           c.value('(QueryPlan/@CompileCPU)[1]', 'int') AS CompileCPU_ms,
           c.value('(QueryPlan/@CompileMemory)[1]', 'int') AS CompileMemory_KB,
           qp.query_plan
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY qp.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS n(c)
) AS tab
JOIN sys.dm_exec_query_stats AS qs ON tab.QueryHash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY CompileTime_ms DESC
OPTION(RECOMPILE, MAXDOP 1);

Al ejecutar la consulta anterior, puede recuperar la información sobre el tiempo de compilación de los planes de ejecución en caché.

4. Evento extendido

Los eventos extendidos también se pueden utilizar para capturar información sobre el tiempo de compilación. Sin embargo, este método solo está disponible en SQL Server 2012 y versiones superiores. Al capturar el evento Query_post_compilation_showplan, puede recuperar el tiempo de compilación del plan de ejecución. Es importante tener en cuenta que el uso de eventos extendidos puede tener un impacto significativo en el rendimiento. Aquí hay un ejemplo:

-- Crear un rastro de eventos extendidos para consulta SQL dinámica
-- Capturar el evento Query_post_compilation_showplan
-- El campo de duración de Query_post_compilation_showplan muestra el tiempo de compilación del plan de ejecución

Al ejecutar el rastro de eventos extendidos anterior y analizar los datos capturados, puede recuperar el tiempo de compilación de la consulta dinámica.

Comprender el tiempo de compilación en SQL Server es crucial para optimizar el rendimiento de las consultas y solucionar problemas de rendimiento. Al utilizar los métodos mencionados en este artículo, puede medir y analizar de manera efectiva el tiempo de compilación para mejorar el rendimiento general de su entorno de SQL Server.