When working with SQL Server, it is important to understand the concept of compile time. Compile time refers to the time it takes for SQL Server to parse and compile a SQL command before executing it. In this article, we will explore different methods to measure and analyze compile time in SQL Server.

1. SET STATISTICS TIME ON

One way to measure compile time is by using the SET STATISTICS TIME ON command. Enabling this option not only provides the compile time of a SQL command but also shows the execution times. By enabling this option, you can troubleshoot and analyze the performance of individual SQL commands. Here is an example:

USE [AdventureWorks]
GO

SET STATISTICS TIME ON
GO

EXEC [uspGetManagerEmployees] 100
GO

SET STATISTICS TIME OFF
GO

When you run the above command, SQL Server will display the compile time in the output. If you run the same command again, you will notice that the compile time is reduced or even eliminated. This is because the execution plan has been cached, and no compilation is needed.

2. SQL Profiler (or SQL Trace)

Another method to measure compile time is by using SQL Profiler or SQL Trace. These tools allow you to monitor and capture the compile time of SQL commands in any client session. However, it is important to note that using SQL Profiler can have a performance impact, especially when compared to using the sp_trace_xxxx stored procedures.

There are different ways to measure compile time based on the type of SQL command:

a) SQL Batch

The compile time of a SQL batch can be calculated using the following formula:

Batch compile time = (SQL:StmtStarting StartTime of the first SQL Statement in the batch) - (SQL:BatchStarting StartTime)

You can enable the SQL Profiler event to capture the batch compile time. Here is an example:

USE [AdventureWorks]
GO

-- Enable the SQL Profiler event
-- SQL:StmtStarting captures the start time of each SQL statement in the batch

By running the above script and analyzing the captured event log, you can calculate the batch compile time.

b) Stored Procedure

The compile time of a stored procedure can be calculated using the following formula:

Store Procedure compile time = (SP:StmtStart StartTime of the first SQL Statement in the SP) - (SQL:StmtStarting StartTime of the caller SQL Statement)

By selecting the appropriate SQL Profiler event, you can capture the compile time of a stored procedure. Here is an example:

USE [AdventureWorks]
GO

DECLARE @return_value int

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

SELECT 'Return Value' = @return_value
GO

By running the above script and analyzing the captured event log, you can calculate the stored procedure compile time.

c) Dynamic T-SQL

The compile time of a dynamic query can be calculated using the following formula:

Compile time of Dynamic Query = (StartTime of StmtStarting of Dynamic Query) - (EXEC command StartTime of StmtStarting event in batch or SP)

By creating a dynamic query within a SQL batch and selecting the appropriate SQL Profiler event, you can capture the compile time of the dynamic query. Here is an example:

DECLARE @query varchar(max)
SET @query=' WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel])
-- CTE name and columns
AS
(
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 -- Get the initial list of Employees for Manager 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 -- Join recursive member to anchor
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] -- Outer select from the 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

By running the above script and analyzing the captured event log, you can calculate the compile time of the dynamic query.

3) DMV

Dynamic Management Views (DMV) can also be used to retrieve compile time information. However, it is important to note that DMVs have limitations, such as not being able to retrieve compile information for plans that are not in the cache. Additionally, the following query is only supported in SQL Server 2008 and higher versions:

-- Find high compile resource plans in the plan cache
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);

By running the above query, you can retrieve the compile time information for cached execution plans.

4. Extended Event

Extended Events can also be used to capture compile time information. However, this method is only available in SQL Server 2012 and higher versions. By capturing the Query_post_compilation_showplan event, you can retrieve the compile time of the execution plan. It is important to note that using Extended Events can have a significant performance impact. Here is an example:

-- Create an Extended Event trace for SQL dynamic query
-- Capture the Query_post_compilation_showplan event
-- The duration field of Query_post_compilation_showplan shows the compile time of the execution plan

By running the above Extended Event trace and analyzing the captured data, you can retrieve the compile time of the dynamic query.

Understanding compile time in SQL Server is crucial for optimizing query performance and troubleshooting performance issues. By utilizing the methods mentioned in this article, you can effectively measure and analyze compile time to improve the overall performance of your SQL Server environment.