reducing compilations per second?

Does anybody have a good list or know of a good website that shows various
things you can do to reduce the compilations per second? I'm running SQL
Server 2000 and I'm getting anywhere from 114 to 200 compilations per
second, and obviously my CPU is pegged when it hits that higher end. What I
do know that is supposed to help:
1. upgrade to 2005
2. increase physical memory
3. increase minimum server memory setting
4. use a single database instead of several identical ones.
5. possibly change the code to use the full database path including the
owner for table names
Anything else?
thanks,
Coryhttp://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p1.aspx
SQL compilations of Transact-SQL code is a normal part of SQL Server's
operation. But because compilations chew up CPU and other resources, SQL
attempts to reuse as many execution plans in cache as possible (execution
plans are created when compilations occur). The more execution plans are
reused, the less overhead there is on the server, and the faster overall
performance there is.
To find out how many compilations SQL Server is doing, you can monitor the
SQLServer: SQL Statistics: SQL Compilations/Sec counter. As you would
expect, this measures how many compilations are performed by SQL Server per
second.
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. In some cases, you have
the power to reduce the number of unnecessary compilations. See this page
for tips on how to do this.
http://www.sql-server-performance.com/tips/stored_procedures.asp
If you find that your server is performing over 100 compilations per second,
you should take the time to investigate if the cause of this is something
that you can control. Too many compilations will hurt your SQL Server's
performance. [7.0, 2000] Updated 9-4-2006
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
> 2. increase physical memory
> 3. increase minimum server memory setting
> 4. use a single database instead of several identical ones.
> 5. possibly change the code to use the full database path including the
> owner for table names
>
> Anything else?
>
> thanks,
> Cory
>
>|||correct link
http://www.sql-server-performance.com/tips/stored_procedures_p1.aspx
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
> 2. increase physical memory
> 3. increase minimum server memory setting
> 4. use a single database instead of several identical ones.
> 5. possibly change the code to use the full database path including the
> owner for table names
>
> Anything else?
>
> thanks,
> Cory
>
>|||Also, did you run a trace and see where the compilations are coming from?
What queries are being executed and by whom?
--
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
> 2. increase physical memory
> 3. increase minimum server memory setting
> 4. use a single database instead of several identical ones.
> 5. possibly change the code to use the full database path including the
> owner for table names
>
> Anything else?
>
> thanks,
> Cory
>
>|||See in-line
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
This can help as it can compile at the statement level and if you have
lots of adhoc code (which it sounds like you do) you can use the FORCE
parameterization option.
> 2. increase physical memory
This may or may not help. If the code is simply not reusable this may
make matters worse.
> 3. increase minimum server memory setting
This will not help unless you have other apps on the same server. In
that case think about removing them.
> 4. use a single database instead of several identical ones.
Not a good way to go to fix compilation issues. Optimize the code first.
> 5. possibly change the code to use the full database path including the
> owner for table names
That is not the full path. FULL would be DB.Owner.Object. But you are
on the right track. ALWAYS owner qualify your objects.
> Anything else?
Yes, optimize the code so the queries can be reused. Have a look here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>
> thanks,
> Cory
>
>|||1) Are you running stored procs or ADO/ADONET code? Some quick tips for
sproc compilations:
a) reduce temp table usage (table variables maybe, or combine statements to
eliminate temp storage needs altogether)
b) create all temp objects at top of sproc
c) remove 'bad' SET statements from sprocs. see BOL/MS site for these.
Reducing compilations from ADO/ADONET code usually requires rewriting the
code to use more set based logic. As Andy Kelly said, on 2005 you can use
the FORCED PARAMETERIZATION database setting. I have a client that saw a
30-40% throughput improvement on their bad ADO code using that setting.
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
> Does anybody have a good list or know of a good website that shows various
> things you can do to reduce the compilations per second? I'm running SQL
> Server 2000 and I'm getting anywhere from 114 to 200 compilations per
> second, and obviously my CPU is pegged when it hits that higher end. What
> I do know that is supposed to help:
> 1. upgrade to 2005
> 2. increase physical memory
> 3. increase minimum server memory setting
> 4. use a single database instead of several identical ones.
> 5. possibly change the code to use the full database path including the
> owner for table names
>
> Anything else?
>
> thanks,
> Cory
>
>|||We are running nearly 100% ColdFusion code. We aren't using temp tables
even in the few stored procs that we have. We are currently rewriting the
most popular apps on our sites to use parameters on everything. We did look
into the cache on the server using some of the system stored procedures and
we realized that the ones that were parameterized are getting used over 200
times, while the ad-hoc queries are compiled separately with every hit, and
only have one hit on them.
Also, I posted this elsewhere:
One thing that we are still very confused about is how the initial lookup
actually works. We're mainly wondering if two similar queries that work
identically can share the same execution plan in the cache. For example, if
you ran this:
<cfquery>
Select field from table where ID = <CFQUERYPARAM value="1">
</cfquery>
Then on a different page that you wrote before you knew all about code reuse
in CFC's and stuff, you ran this:
<cfquery>
SELECT field
FROM table
WHERE id = <CFQUERYPARAM value="1">
</cfquery>
Same thing, but formatted differently. Will they both have to be compiled
into two different execution plans, or is SQL Server smart enough to use the
same plan in the cache? Is it a simple text lookup, or are formatting
characters stripped and it reduced down to bytecode or something? I have no
idea.
Thanks,
Cory
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13fkrvpprbubr9a@.corp.supernews.com...
> 1) Are you running stored procs or ADO/ADONET code? Some quick tips for
> sproc compilations:
> a) reduce temp table usage (table variables maybe, or combine statements
> to eliminate temp storage needs altogether)
> b) create all temp objects at top of sproc
> c) remove 'bad' SET statements from sprocs. see BOL/MS site for these.
> Reducing compilations from ADO/ADONET code usually requires rewriting the
> code to use more set based logic. As Andy Kelly said, on 2005 you can use
> the FORCED PARAMETERIZATION database setting. I have a client that saw a
> 30-40% throughput improvement on their bad ADO code using that setting.
>
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Does anybody have a good list or know of a good website that shows
>> various things you can do to reduce the compilations per second? I'm
>> running SQL Server 2000 and I'm getting anywhere from 114 to 200
>> compilations per second, and obviously my CPU is pegged when it hits that
>> higher end. What I do know that is supposed to help:
>> 1. upgrade to 2005
>> 2. increase physical memory
>> 3. increase minimum server memory setting
>> 4. use a single database instead of several identical ones.
>> 5. possibly change the code to use the full database path including the
>> owner for table names
>>
>> Anything else?
>>
>> thanks,
>> Cory
>>
>|||If you don't parameterize your queries (which can be identified using a profiler trace), then SQL
Server does the lookup using a checksum of the whole query string. i.e., it is
"everything"-sensitive.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OqRkKMRAIHA.4164@.TK2MSFTNGP06.phx.gbl...
> We are running nearly 100% ColdFusion code. We aren't using temp tables even in the few stored
> procs that we have. We are currently rewriting the most popular apps on our sites to use
> parameters on everything. We did look into the cache on the server using some of the system
> stored procedures and we realized that the ones that were parameterized are getting used over 200
> times, while the ad-hoc queries are compiled separately with every hit, and only have one hit on
> them.
>
> Also, I posted this elsewhere:
> One thing that we are still very confused about is how the initial lookup actually works. We're
> mainly wondering if two similar queries that work identically can share the same execution plan in
> the cache. For example, if you ran this:
>
> <cfquery>
> Select field from table where ID = <CFQUERYPARAM value="1">
> </cfquery>
>
> Then on a different page that you wrote before you knew all about code reuse in CFC's and stuff,
> you ran this:
>
>
> <cfquery>
> SELECT field
> FROM table
> WHERE id = <CFQUERYPARAM value="1">
> </cfquery>
>
>
>
> Same thing, but formatted differently. Will they both have to be compiled into two different
> execution plans, or is SQL Server smart enough to use the same plan in the cache? Is it a simple
> text lookup, or are formatting characters stripped and it reduced down to bytecode or something?
> I have no idea.
>
>
> Thanks,
> Cory
>
>
>
>
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13fkrvpprbubr9a@.corp.supernews.com...
>> 1) Are you running stored procs or ADO/ADONET code? Some quick tips for sproc compilations:
>> a) reduce temp table usage (table variables maybe, or combine statements to eliminate temp
>> storage needs altogether)
>> b) create all temp objects at top of sproc
>> c) remove 'bad' SET statements from sprocs. see BOL/MS site for these.
>> Reducing compilations from ADO/ADONET code usually requires rewriting the code to use more set
>> based logic. As Andy Kelly said, on 2005 you can use the FORCED PARAMETERIZATION database
>> setting. I have a client that saw a 30-40% throughput improvement on their bad ADO code using
>> that setting.
>>
>> "Cory Harrison" <charrison@.csiweb.com> wrote in message
>> news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Does anybody have a good list or know of a good website that shows various things you can do to
>> reduce the compilations per second? I'm running SQL Server 2000 and I'm getting anywhere from
>> 114 to 200 compilations per second, and obviously my CPU is pegged when it hits that higher end.
>> What I do know that is supposed to help:
>> 1. upgrade to 2005
>> 2. increase physical memory
>> 3. increase minimum server memory setting
>> 4. use a single database instead of several identical ones.
>> 5. possibly change the code to use the full database path including the owner for table names
>>
>> Anything else?
>>
>> thanks,
>> Cory
>>
>>
>|||Have a look at these to see how this works and how to address it.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
http://support.microsoft.com/kb/243588
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OqRkKMRAIHA.4164@.TK2MSFTNGP06.phx.gbl...
> We are running nearly 100% ColdFusion code. We aren't using temp tables
> even in the few stored procs that we have. We are currently rewriting the
> most popular apps on our sites to use parameters on everything. We did
> look into the cache on the server using some of the system stored
> procedures and we realized that the ones that were parameterized are
> getting used over 200 times, while the ad-hoc queries are compiled
> separately with every hit, and only have one hit on them.
>
> Also, I posted this elsewhere:
> One thing that we are still very confused about is how the initial lookup
> actually works. We're mainly wondering if two similar queries that work
> identically can share the same execution plan in the cache. For example,
> if you ran this:
>
> <cfquery>
> Select field from table where ID = <CFQUERYPARAM value="1">
> </cfquery>
>
> Then on a different page that you wrote before you knew all about code
> reuse in CFC's and stuff, you ran this:
>
>
> <cfquery>
> SELECT field
> FROM table
> WHERE id = <CFQUERYPARAM value="1">
> </cfquery>
>
>
>
> Same thing, but formatted differently. Will they both have to be compiled
> into two different execution plans, or is SQL Server smart enough to use
> the same plan in the cache? Is it a simple text lookup, or are formatting
> characters stripped and it reduced down to bytecode or something? I have
> no idea.
>
>
> Thanks,
> Cory
>
>
>
>
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13fkrvpprbubr9a@.corp.supernews.com...
>> 1) Are you running stored procs or ADO/ADONET code? Some quick tips for
>> sproc compilations:
>> a) reduce temp table usage (table variables maybe, or combine statements
>> to eliminate temp storage needs altogether)
>> b) create all temp objects at top of sproc
>> c) remove 'bad' SET statements from sprocs. see BOL/MS site for these.
>> Reducing compilations from ADO/ADONET code usually requires rewriting the
>> code to use more set based logic. As Andy Kelly said, on 2005 you can
>> use the FORCED PARAMETERIZATION database setting. I have a client that
>> saw a 30-40% throughput improvement on their bad ADO code using that
>> setting.
>>
>> "Cory Harrison" <charrison@.csiweb.com> wrote in message
>> news:OrU0Mv6$HHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Does anybody have a good list or know of a good website that shows
>> various things you can do to reduce the compilations per second? I'm
>> running SQL Server 2000 and I'm getting anywhere from 114 to 200
>> compilations per second, and obviously my CPU is pegged when it hits
>> that higher end. What I do know that is supposed to help:
>> 1. upgrade to 2005
>> 2. increase physical memory
>> 3. increase minimum server memory setting
>> 4. use a single database instead of several identical ones.
>> 5. possibly change the code to use the full database path including the
>> owner for table names
>>
>> Anything else?
>>
>> thanks,
>> Cory
>>
>>
>