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