Query compiling time

In SQL2005 is there a way to find out just how much time CPU is using on
compiling a plan for a query and how much on executing that plan?
There is quite a lot of compiles/second (200-400/second) while running some
poorly written batches in our application. During that time, CPU is at 100%
and I would like to know if most of that CPU time is spent on compiling so I
can estimate if we can gain some speed by rewriting those queries.
Thanks.
TomHi Tom,
use this statement before running your batch:
SET STATISTICS TIME ON
after executing your commands, consider the message tab.
HTH,
Leila
"Tom" <tomman@.hotmail.com> wrote in message
news:OLr6XafgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> In SQL2005 is there a way to find out just how much time CPU is using on
> compiling a plan for a query and how much on executing that plan?
> There is quite a lot of compiles/second (200-400/second) while running
> some poorly written batches in our application. During that time, CPU is
> at 100% and I would like to know if most of that CPU time is spent on
> compiling so I can estimate if we can gain some speed by rewriting those
> queries.
>
> Thanks.
> Tom
>|||BTW, you may use: DBCC FreeProcCache to clean the procedure cache in order
to get your commands recompiled each time
"Tom" <tomman@.hotmail.com> wrote in message
news:OLr6XafgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> In SQL2005 is there a way to find out just how much time CPU is using on
> compiling a plan for a query and how much on executing that plan?
> There is quite a lot of compiles/second (200-400/second) while running
> some poorly written batches in our application. During that time, CPU is
> at 100% and I would like to know if most of that CPU time is spent on
> compiling so I can estimate if we can gain some speed by rewriting those
> queries.
>
> Thanks.
> Tom
>|||Do you have a lot of cursors, dynamic sql or ADO code? Each of those can
cause problems with compiles, especially the latter two.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tom" <tomman@.hotmail.com> wrote in message
news:OLr6XafgHHA.4284@.TK2MSFTNGP06.phx.gbl...
> In SQL2005 is there a way to find out just how much time CPU is using on
> compiling a plan for a query and how much on executing that plan?
> There is quite a lot of compiles/second (200-400/second) while running
> some poorly written batches in our application. During that time, CPU is
> at 100% and I would like to know if most of that CPU time is spent on
> compiling so I can estimate if we can gain some speed by rewriting those
> queries.
>
> Thanks.
> Tom
>