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
>