high Compilations/sec value

Hi,
Would someone please explain what causes a high "Compilations/sec" count?
Is still caued by stored procedures not being cached and not enough memory
alloacted to SQL Server.
ThanksIf you have create SPs that are frequently used, with recompile option might
bring up compilations/sec high.
"mm" <postto@.news.com> wrote in message
news:u1p$2asuEHA.452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>|||Generally speaking, if this figure is over 100 compilations per second, then
you may be experiencing unnecessary compilation overhead. A high number such
as this might indicate that you server is just very busy, or it could mean
that unnecessary compilations are being performed. For example, compilations
can be forced by SQL Server if object schema changes, if previously
parallelized execution plans have to run serially, if statistics are
recomputed, or if a number of other things occur.
Also, it depends how your SPs are written. For example, if you have the
following SP:
CREATE PROCEDURE dbo.spTest (@.query bit) AS
IF @.query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO
Suppose I make my first call to this procedure with the @.query parameter set
to 0. The query-plan that SQL Server will generate will be optimized for the
first query ("SELECT * FROM authors"), because the path followed on the first
call will result in that query being executed.
Now, if I next call the stored procedure with @.query set to 1, the query
plan that SQL Server has in memory will not be of any use in executing the
second query, since the query-plan is optimized for the authors table, not
the publishers table. Result: SQL Server will have to compile a new query
plan, the one needed for the second query.
Ultimately, you should write the SP as follow:
CREATE PROCEDURE dbo.spTestDelegator (@.query bit) AS
IF @.query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
I hope this helps.
--
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"mm" wrote:
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>