SQL Server – How Badly Do SQL Compilations Impact Performance?

perfmonperformancesql serversql-server-2005

I am profiling an instance of a SQL Server 2005 and I, via PerfMon's SQLServer:SQL Statistics - SQL Compilations/sec metric, I see that the average is about 170 or so.

I whipped out SQL Profiler and looked for SP:Compile or SQL:Compile events. Apparently they do not exist. I did find Stored Procedure/SP:Recompile and TSQL/SQL:StmtRecompile events. The amount of data I see in the Profiler suggests that these are the wrong events to look at, though I am not sure.

So my questions. Answers to any of these would be great.

  1. How can I see what exactly is compiling in SQL Server?
  2. Did I pick the wrong metrics to look at? In either Perfmon or SQL Profiler?
  3. With regards to Stored Procedure/SP:Recompile and TSQL/SQL:StmtRecompile events in SQL Profiler…they do not include the Duration metric. How can I gauge the impact of these events to the system if they provide no way to see the timing impact to the system.

Best Answer

SQL Compilations/sec is a good metric, but only when coupled with Batch Requests/sec. By itself, compilations per sec doesn't really tell you much.

You are seeing 170. If batch req per sec is only 200 (a little exaggerated for effect) then yes, you need to get down to the bottom of the cause (most likely an overuse of ad hoc querying and single-use plans). But if your batch req per sec is measuring about 5000 then 170 compilations per sec is not bad at all. It's a general rule of thumb that Compilations/sec should be at 10% or less than total Batch Requests/sec.

If you really want to drill down into what's being cached, run the following query that utilizes the appropriate DMVs:

select
    db_name(st.dbid) as database_name,
    cp.bucketid,
    cp.usecounts,
    cp.size_in_bytes,
    cp.objtype,
    st.text
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st

To get all single-use plans (a count):

;with PlanCacheCte as 
(
    select
        db_name(st.dbid) as database_name,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes,
        cp.objtype,
        st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
)
select count(*)
from PlanCacheCte
where usecounts = 1

To get a ratio of how many single-use count plans you have compared to all cached plans:

declare @single_use_counts int, @multi_use_counts int

;with PlanCacheCte as 
(
    select
        db_name(st.dbid) as database_name,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes,
        cp.objtype,
        st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
    where cp.cacheobjtype = 'Compiled Plan'
)
select @single_use_counts = count(*)
from PlanCacheCte
where usecounts = 1

;with PlanCacheCte as 
(
    select
        db_name(st.dbid) as database_name,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes,
        cp.objtype,
        st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
    where cp.cacheobjtype = 'Compiled Plan'
)
select @multi_use_counts = count(*)
from PlanCacheCte
where usecounts > 1

select
    @single_use_counts as single_use_counts,
    @multi_use_counts as multi_use_counts,
    @single_use_counts * 1.0 / (@single_use_counts + @multi_use_counts) * 100
        as percent_single_use_counts

As for durations captured through a SQL Server Trace, it is not available for the Recompile events. It isn't so significant to see the duration or pain that plan compilation is causing, as there's not much you can do for a case-by-case situation. The solution is to attempt to limit compilations and recompilations through plan re-use (parameterized queries, stored procedures, etc.).

Related Question