Excessive SQL Compilations

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.
Have a look at these:
http://support.microsoft.com/default...;en-us;Q243586
http://www.sql-server-performance.co...recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.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.