Compiles / Sec on 2005

Hi All
I read somewhere that the compiles / sec performance counter should be used
to evaluate the risk involved in upgrating a SQL 2000 Server to a SQL 2005
server. That SQL 2005 is more CPU intensive on these tasks? Does anyone
have more documentation / information on the subject ?
Thanks
Elrond BishopWell since 2005 can compile at the statement level vs. the entire batch or
proc there can be considerable savings in recompile costs. It they are all
compiles that means none of the plans are being reused. In that case you may
be able to get some help with the Force Parameterization option or the use
of plan guides. See here for more details:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Elrond Bishop" <billgates@.microsoft.com> wrote in message
news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi All
> I read somewhere that the compiles / sec performance counter should be
> used to evaluate the risk involved in upgrating a SQL 2000 Server to a SQL
> 2005 server. That SQL 2005 is more CPU intensive on these tasks? Does
> anyone have more documentation / information on the subject ?
> Thanks
> Elrond Bishop|||The database has a lot of "Dynamic" SQL coming into the database from VB
strings executed through the conneciton object.. Bad programming practice I
know but very little we can do about it at the moment.
Should I be worried about a higher CPU usage in that case ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Well since 2005 can compile at the statement level vs. the entire batch or
> proc there can be considerable savings in recompile costs. It they are all
> compiles that means none of the plans are being reused. In that case you
> may be able to get some help with the Force Parameterization option or the
> use of plan guides. See here for more details:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>|||Not only CPU, but also RAM and Network...
You may want to analyse that using SQL Server Profiler (make a workload file
and trace conditions of your queries) and Database Tuning Advisor for
getting some useful recommendation on your query commands (using that
workload file)?
From your message I understand that you are not with your current querying
solution but I just wanted to stress that it would be much much better using
SPs for quering against your database from your VB apps. Using SPs, you'll
not be able to change your hard-coded SPs for minor code changings (which
does not affect datasets that return to your app) and it would be more
secure and fast (SPs are much faster and better than ad hocs)
--
Ekrem Önsoy
"Elrond Bishop" <billgates@.microsoft.com> wrote in message
news:ORurkmpBIHA.3900@.TK2MSFTNGP02.phx.gbl...
> The database has a lot of "Dynamic" SQL coming into the database from VB
> strings executed through the conneciton object.. Bad programming practice
> I know but very little we can do about it at the moment.
> Should I be worried about a higher CPU usage in that case ?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Well since 2005 can compile at the statement level vs. the entire batch
>> or proc there can be considerable savings in recompile costs. It they are
>> all compiles that means none of the plans are being reused. In that case
>> you may be able to get some help with the Force Parameterization option
>> or the use of plan guides. See here for more details:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
>> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>|||I understand the importance of stored procedures. just dont have a choice in
the matter at the moment. there are +- 80 different VB applicaitons
countless lines of VBA and other methods of accessing the data. a bit of a
mess, but it was inherited. There is a project underway to move the
applications to SP's but thats going to take a while. there are also loads
and loads of SP based queries, so compiles may have been coming from that as
well. looking at an average of 3000 - 8000 connections during the day, 500
gig database.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:E474511D-11F6-4CA3-B459-A4793192154A@.microsoft.com...
> Not only CPU, but also RAM and Network...
> You may want to analyse that using SQL Server Profiler (make a workload
> file and trace conditions of your queries) and Database Tuning Advisor for
> getting some useful recommendation on your query commands (using that
> workload file)?
> From your message I understand that you are not with your current querying
> solution but I just wanted to stress that it would be much much better
> using SPs for quering against your database from your VB apps. Using SPs,
> you'll not be able to change your hard-coded SPs for minor code changings
> (which does not affect datasets that return to your app) and it would be
> more secure and fast (SPs are much faster and better than ad hocs)
> --
> Ekrem Önsoy
>
> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
> news:ORurkmpBIHA.3900@.TK2MSFTNGP02.phx.gbl...
>> The database has a lot of "Dynamic" SQL coming into the database from VB
>> strings executed through the conneciton object.. Bad programming practice
>> I know but very little we can do about it at the moment.
>> Should I be worried about a higher CPU usage in that case ?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Well since 2005 can compile at the statement level vs. the entire batch
>> or proc there can be considerable savings in recompile costs. It they
>> are all compiles that means none of the plans are being reused. In that
>> case you may be able to get some help with the Force Parameterization
>> option or the use of plan guides. See here for more details:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
>> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>>
>|||SQL 2005 will not increase the compile rates if they are already not reusing
the plans in 2000. It may in fact decrease it especially if you utilize some
of the features I mentioned earlier. But having said that the engine is
always evolving and the only way to know for sure is to test it under your
real life conditions. But the solution is really to fix the way the app
makes the calls so that you either use sps or format the calls so that they
can be reused via autoparameterizaion or sp_executesql.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Elrond Bishop" <billgates@.microsoft.com> wrote in message
news:ORurkmpBIHA.3900@.TK2MSFTNGP02.phx.gbl...
> The database has a lot of "Dynamic" SQL coming into the database from VB
> strings executed through the conneciton object.. Bad programming practice
> I know but very little we can do about it at the moment.
> Should I be worried about a higher CPU usage in that case ?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Well since 2005 can compile at the statement level vs. the entire batch
>> or proc there can be considerable savings in recompile costs. It they are
>> all compiles that means none of the plans are being reused. In that case
>> you may be able to get some help with the Force Parameterization option
>> or the use of plan guides. See here for more details:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
>> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>|||With a bunch of dynamic sql it is quite possible you could very much benefit
from FORCED PARAMETERIZATION setting in 2005, as Andrew mentioned. Dynamic
sql that does lots of statement calls where it simply changes the values in
one or more WHERE clause lines is a great example of code that would
benefit. The first execution would be parameterized and the (hopefully
index seek/bookmark lookup) query plan cached. then as the code looped
through the remaining where clause values (such as EmpID = 1, EmpID = 2 ...)
the cached query plan will be reused - saving a lot of effort on compiles.
I have seen this very thing result in 30-40% throughput inprovement at one
of my clients.
"Elrond Bishop" <billgates@.microsoft.com> wrote in message
news:ORurkmpBIHA.3900@.TK2MSFTNGP02.phx.gbl...
> The database has a lot of "Dynamic" SQL coming into the database from VB
> strings executed through the conneciton object.. Bad programming practice
> I know but very little we can do about it at the moment.
> Should I be worried about a higher CPU usage in that case ?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23ZmiGEpBIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Well since 2005 can compile at the statement level vs. the entire batch
>> or proc there can be considerable savings in recompile costs. It they are
>> all compiles that means none of the plans are being reused. In that case
>> you may be able to get some help with the Force Parameterization option
>> or the use of plan guides. See here for more details:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Elrond Bishop" <billgates@.microsoft.com> wrote in message
>> news:ODJKGvoBIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Hi All
>> I read somewhere that the compiles / sec performance counter should be
>> used to evaluate the risk involved in upgrating a SQL 2000 Server to a
>> SQL 2005 server. That SQL 2005 is more CPU intensive on these tasks?
>> Does anyone have more documentation / information on the subject ?
>> Thanks
>> Elrond Bishop
>

Compile Blocking Issues

We've recently been experiencing problems locking issues seemingly caused by
compiles.. ..cpu max's out at 100%, query duration get longer, and we see a
large number of LCK_M_X in sysprocesses with coupled with something like TAB:
5:736291420:0 [COMPILE].. ..what could be causing this issue?
K1) What is the table referenced in the lock?
2) It could be caused by lots of compiles' :-)) Seriously, do you do a
lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
temptable useage (it is SCARY how many recompiles can be caused by this!!)?
SQL 2000 or 2005'
TheSQLGuru
President
Indicium Resources, Inc.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||Have you read this:
"Description of SQL Server blocking caused by compile locks"
http://support.microsoft.com/kb/263889
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||On Jun 13, 11:49 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) What is the table referenced in the lock?
> 2) It could be caused by lots of compiles' :-)) Seriously, do you do a
> lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
> temptable useage (it is SCARY how many recompiles can be caused by this!!)?
> SQL 2000 or 2005'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Ben UK" <B...@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>
>
> > We've recently been experiencing problems locking issues seemingly caused
> > by
> > compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> > a
> > large number of LCK_M_X in sysprocesses with coupled with something like
> > TAB:
> > 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> > K- Hide quoted text -
> - Show quoted text -
I've observed this behavior with sprocs that are called very often and
make use of temp tables. As data is inserted into the temp table one
or more sp-recompile events will happen. If many concurrent spids are
calling this sproc at the same time, SQL appears to allow only one
spid at a time to perform the recompile - this will reduce the level
of concurrency in the system and you will see blocking spids that are
marked with [COMPILE]. Try to use profiler to trace stored procedure
recompiles, and RPC:Completed events to identify what is being
recomplied, then ideally try to tune the code to reduce recompiles.|||Thanks for the responses, we're SQL 2005, SP1, the objects referenced in the
lock are primarily 2 sp's and 1 udf.. ..when querying sysprocesses we can see
around 30 occurances of this lock from around 600 connections.
The problems *seem*to have started since we changed the schema and removed a
table containing denormalized data and replaced it with a view. Both the
sp's that have compile issues reference the new view (as do around 50-60
more), the udf doesn't reference any new tables... ...the udf uses a table
variable, but neither sp uses temp tables of any kind.
http://support.microsoft.com/kb/263889
^ I did read the article earlier today.. ..it was kinda useful, but I didn't
see anything in there that would indicate the cause of our issue. The only
thing it made me question was some of the table with the sp's weren't fully
qualified.. ..but this has always been the case so it would be strange for
this to only just start causing a problem..
Again thanks for the responses.. ..any help is greatly appreciated
K
Unfortunately I can't analyse new traces, as currently our frontend is being
redirected..|||Is 736291420 an object id for a stored proc? It sounds like what MS calls
"rolling block". Does the blocking head spid(s) constantly changing?
Did you run profiler trace to see where the SP:Recompile event occurs and
what Event Subclass it falls into?
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||Yep it's a sproc and the blocking head does constantly change.. ..what causes
this behaviour?
Thanks in advance
K
"YPD" wrote:
> Is 736291420 an object id for a stored proc? It sounds like what MS calls
> "rolling block". Does the blocking head spid(s) constantly changing?
> Did you run profiler trace to see where the SP:Recompile event occurs and
> what Event Subclass it falls into?
>
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> >
> > We've recently been experiencing problems locking issues seemingly caused
> > by
> > compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> > a
> > large number of LCK_M_X in sysprocesses with coupled with something like
> > TAB:
> > 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> >
> > K
>
>|||The culprit for the performance problem should be stored procedure
recompilations. What happens is the sprocs are recompiled everytime they get
called. The recompliation doesn't happen in a timely fasion so that client
connections calling the sprocs have to be queued up waiting to be
recompiled.
Profiler is your friend. Please run a profiler trace to capture a series of
events to determine what caused the recomplications. The link
http://support.microsoft.com/kb/243586/ referenced in Kalen's post is a good
place to start.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:AA4556BF-995D-4201-B887-36C04FEB9E84@.microsoft.com...
> Yep it's a sproc and the blocking head does constantly change.. ..what
> causes
> this behaviour?
> Thanks in advance
> K
> "YPD" wrote:
>> Is 736291420 an object id for a stored proc? It sounds like what MS calls
>> "rolling block". Does the blocking head spid(s) constantly changing?
>> Did you run profiler trace to see where the SP:Recompile event occurs and
>> what Event Subclass it falls into?
>>
>> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
>> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>> >
>> > We've recently been experiencing problems locking issues seemingly
>> > caused
>> > by
>> > compiles.. ..cpu max's out at 100%, query duration get longer, and we
>> > see
>> > a
>> > large number of LCK_M_X in sysprocesses with coupled with something
>> > like
>> > TAB:
>> > 5:736291420:0 [COMPILE].. ..what could be causing this issue?
>> >
>> > K
>>

compile

Anyone have an idea why a program that compiles in
developer will give a 'not responding' message when being
compiled to create an executable?Does this have anything to do with SQL Server? I suspect you posted to the
wrong newsgroup...
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Syl" <clemnsyl@.netscape.net> wrote in message
news:f58801c43dcc$89775b60$a501280a@.phx.gbl...
> Anyone have an idea why a program that compiles in
> developer will give a 'not responding' message when being
> compiled to create an executable?

compile

Anyone have an idea why a program that compiles in
developer will give a 'not responding' message when being
compiled to create an executable?
Does this have anything to do with SQL Server? I suspect you posted to the
wrong newsgroup...
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Syl" <clemnsyl@.netscape.net> wrote in message
news:f58801c43dcc$89775b60$a501280a@.phx.gbl...
> Anyone have an idea why a program that compiles in
> developer will give a 'not responding' message when being
> compiled to create an executable?