How badly do SQL Compilations impact the performance of SQL Server?

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.).

There are three relevant counters that should be recorded using PerfMon (or another 3rd party solution). The key point is to record these stats somehow.

• SQL Statistics\Batch Requests/sec
• SQL Statistics\SQL Compilations/sec
• SQL Statistics\SQL Re-Compilations/sec

As Thomas Stringer mentioned, it's good to keep an eye on the ratio of compilations/batch request. Obviously, lower is better, but there are only guidelines for what is "good", and only you can decide what is acceptable. The absolute amount of perf gain you'll see by reducing the number of compilations depends on many factors.

I also like to look at the ratio of recompilations/compilation, to get a sense of the amount of query plan reuse. Again, lower is better. In this case, however, you do want to have recompiles happening in the system as statistics change (if the DB is read-only and you have recompiles... something may be wrong). Same as I said previously, there are only guidelines for what is "good."

What you really want to do is trend these numbers over time, so if you see a huge spike in either of the ratios, something got deployed that isn't using query plans correctly (ideally, this gets caught during testing) -- use Shark's analysis queries to find the culprits. In addition, here is one to find frequently recompiled queries:

SELECT TOP 50
    qs.plan_generation_num,
    qs.execution_count,
    qs.statement_start_offset,
    qs.statement_end_offset,
    st.text
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    WHERE qs.plan_generation_num > 1
    ORDER BY qs.plan_generation_num DESC

If you're also recording stats for CPU usage, all the stats can be correlated together to find out just how much it hurts, and how much your fixes help. In practice, I've found that even just a single bad query plan strategy on a core sproc can bring a server to its knees; obviously YMMV.