Perfmon/Profiler confusion

I am trying to troubleshoot crazy values for SQL Statistics/SQL
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.

> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a pro
c
> is recompiled before it even starts executing.
>|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.