| Author |
A few compilations/sec
|
|
| Hassan 2006-02-04, 8:23 pm |
| 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 ?
| |
| Andrew J. Kelly 2006-02-04, 8:23 pm |
| 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 ?
>
| |
| Hassan 2006-02-04, 8:23 pm |
| 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@shadh
awk.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...
>
>
| |
| Andrew J. Kelly 2006-02-05, 11:23 am |
| 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@shadh
awk.com> wrote in message
> news:eI5UedeKGHA.2628@TK2MSFTNGP15.phx.gbl...
>
>
| |
| Remus Rusanu [MSFT] 2006-02-05, 1:23 pm |
| 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@shadh
awk.com> wrote in message
> news:eI5UedeKGHA.2628@TK2MSFTNGP15.phx.gbl...
>
>
|
|
|
|