High CPU and Recompiles | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

High CPU and Recompiles

Can high CPU due to high recompiles feed off itself, meaning, the connections and activity on our server increases:
1. Which causes a higher number of recompiles.
2. Which causes higher CPU.
3. Which causes more plans being removed from cache.
3a. Which causes a higher number of recompiles
3b. Which causes higher CPU.
3c. Which causes more plans being removed from cache.
etc., etc., etc. Is the above a possible scenario?
Hi,
Question is little confusing. Do you think you can re-write the question again. Do you mean as below ?
"As connections and activity on our server increases, does it causes a higher number of recompiles" Thank you,
V
High recompiles might be one reason for HIG CPU but you need to use profiler to track it down what query/proc is causing this and why?
MohammedU.
Moderator
SQL-Server-Performance.com
I have used Profiler, which led to this thread. I am seeing Full Compiles and Recompiles on several of our most frequently called procedures. One in procedure in particular, that does a Full Compile some of the time, but not every execution, is due to a Set Option. The following is a sample of the beginning of the procedure: The procedure is quite complex
and contains about 2,000 lines. The beginning of the stored procedure starts
out with the following, where @Debug is an argument when the stored procedure
is executed: CREATE PROCEDURE dbo.sel_Main
[There are about 20 arguments including the following argument]
@Debug = 0
AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED IF @Debug > 0
BEGIN

SET NOCOUNT OFF
SET ANSI_WARNINGS ON

END
ELSE
BEGIN

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

END [The body of the procedure (approximately 2,000 lines) begins here] From what I see in Profiler, this procedure is called every time with @Debug = 0. And, like I said, the Full Compile due to a Set Option change does not occur every execution, but still quite often. So, if @Debug = 0 is not causing the Set Option change, what might be causing the plan to be flushed from cache?
Does the procedure use temporary table(s)?
The procedure does utilize temporary tables. Upon further analysis of Profiler, taken during the high CPU episode, it was not recompiles occurring, but full out compiles.
quote:Originally posted by mmarovic Does the procedure use temporary table(s)?

]]>