A few compilations/sec

Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
times. But the recompilations/sec could average around 0 or 1..
How can i find out what sprocs are triggering the compiles ?And why would
they ?
I tried to look at sysprocesses at the waitresource column for values that
have a TAB%[COMPILE] in them and found some sprocs but they look very simple
and straighforward. So whats causing a compile ? Could it be the code ?
A compile is when you execute a statement / procedure and there is no plan
for it in the procedure cache. This usually occurs when you have adhoc sql
or poorly formatted batches. Take a look at sysproccache table and order by
USECOUNTS DESC. You will see all the ones at the top that are reused and
the ones near the bottom are not.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
> times. But the recompilations/sec could average around 0 or 1..
> How can i find out what sprocs are triggering the compiles ?And why would
> they ?
> I tried to look at sysprocesses at the waitresource column for values that
> have a TAB%[COMPILE] in them and found some sprocs but they look very
> simple and straighforward. So whats causing a compile ? Could it be the
> code ?
>
|||There are so many sprocs with usecounts of 1.. There must be a better way to
narrow it down , is there not ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>A compile is when you execute a statement / procedure and there is no plan
>for it in the procedure cache. This usually occurs when you have adhoc sql
>or poorly formatted batches. Take a look at sysproccache table and order
>by USECOUNTS DESC. You will see all the ones at the top that are reused
>and the ones near the bottom are not.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
|||Well then you have a lot that don't get reused. You can use trace along with
the SP events that track cache hits and such.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eh273SfKGHA.208@.tk2msftngp13.phx.gbl...
> There are so many sprocs with usecounts of 1.. There must be a better way
> to narrow it down , is there not ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>
|||Use the plan_handle to get the T-SQL batches that are use donly once. For
SQL 2005 the query looks like:
select *, (select [text] from sys.dm_exec_sql_text(p.plan_handle))
from sys.dm_exec_cached_plans p
where usecounts = 1
order by size_in_bytes desc
Also, go ahead and read the 'Execution Plan Caching and Reuse' topic in BOL
http://msdn2.microsoft.com/en-us/library/ms181055(en-US,SQL.90).aspx .
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eh273SfKGHA.208@.tk2msftngp13.phx.gbl...
> There are so many sprocs with usecounts of 1.. There must be a better way
> to narrow it down , is there not ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>