Excessive SQL Compilations

28 Sep 2006 11:41 PM

Frank Rizzo

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).

I have the following questions:
1. What is causing excessive compilations?
2. How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?

Thanks.

29 Sep 2006 2:15 AM

Andrew J. Kelly

Have a look at these:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586
http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp

--
Andrew J. Kelly SQL MVP

Show quoteHide quote

"Frank Rizzo" <n***@none.com> wrote in message
news:uYnoWe14GHA.3604@TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
>
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
>
> Thanks.

Thread options