May 25, 2011 at 10:20 am
Hey everyone, need some help.
We have recently started seeing a huge number of recompiles on our system. At the same time we are seeing these recompiles, our CPU maxes out at 100%. This makes sense to me as recompiling a plan requires server resources. What I don't understand is why some of these items are getting recompiled at all.
Here are some samples of the statements that are getting recompiled:
SELECT @UseOrOperator = UseOrOperator FROM #Promotions WHERE ID = @ParentID
IF EXISTS (SELECT 'x' FROM #TempInventory WHERE OnlineOrderDetailID IS NOT NULL)
IF EXISTS (SELECT 'x' FROM #TempInventory WHERE OrderDetailID IS NOT NULL)
DELETE TMP
FROM #TMPDownlineReport TMP
INNER JOIN BC WITH (NOLOCK) ON BC.BCKey = TMP.SubjectID
WHERE NOT EXISTS (
SELECT 'x'
FROM dbo.fn_SplitStrings(@Ranks, '|') AS TMP2
WHERE TMP2.String = BC.RankTypeID
)
AND TMP.SubjectTypeID = 2
INSERT INTO #TMPDownlineReport (SubjectID, SubjectTypeID, DepthLevel)
SELECT BC.BCKey, 2, (BCL.IndexLevel - BCL2.IndexLevel)
FROM BC AS BC WITH (NOLOCK)
INNER JOIN BCIndexLinks BCL WITH (NOLOCK) ON BCL.BCKey = BC.BCKey
INNER JOIN BCIndexLinks BCL2 WITH (NOLOCK) ON BCL.IndexStart >= BCL2.IndexStart
WHERE BCL.IndexStart < BCL2.IndexStop
AND BCL.IndexLevel > BCL2.IndexLevel --Dont include himself AND BCL2.BCKey = @BCKey
go
Each of these has either a Schema Changed or Temp Table Change EventSubClass Type as a reason for the recompile. However, I don't see how I can be getting Schema Changes or Temp Table Changes. Can anyone help me understand this?
Something I did notice was that I had a huge amount of auto statisitc updating going on in tempdb. I turned this off an the queries above where all pulled AFTER the autostats were turned off.
I have attached an excel spreadsheet which shows an overview of the top 20 statements that caused a recompile during a 12 hour period and the reason that they compiled.
Thanks for the assistance everone.
Fraggle.
May 25, 2011 at 12:59 pm
Here are a couple of questions I have:
How do you know that this is a huge number of recompiles? What metrics do you have to compare this to?
Has there been any other change to the server or application that may be causing the CPU spikes (update to anti-virus perhaps?)?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 25, 2011 at 1:09 pm
As I'm reading this whitepaper, http://msdn.microsoft.com/en-us/library/ee343986.aspx, I'm wondering if all the connections have the same SET options. Several SET options cause re-compiles.
This same whitepaper also mentions that using non-qualified object names can cause re-compiles and I noticed that a couple of the queries you include have non-qualified objects in them.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 25, 2011 at 2:06 pm
Jack Corbett (5/25/2011)
Here are a couple of questions I have:How do you know that this is a huge number of recompiles? What metrics do you have to compare this to?
Has there been any other change to the server or application that may be causing the CPU spikes (update to anti-virus perhaps?)?
1) We have 3 servers and this server is our medium sized server. I checked against our most utilized server, and se roughly 2000% more compiles and recompiles on the server we are having issues on.
2) I have numbers form 3 monhts ago from a different issue. They were lower than our busiest server.
As for changes to the application, no, there wasn't any. No changes in the server hardware or software that I am aware of either.
Fraggle.
May 25, 2011 at 2:16 pm
Jack Corbett (5/25/2011)
As I'm reading this whitepaper, http://msdn.microsoft.com/en-us/library/ee343986.aspx, I'm wondering if all the connections have the same SET options. Several SET options cause re-compiles.This same whitepaper also mentions that using non-qualified object names can cause re-compiles and I noticed that a couple of the queries you include have non-qualified objects in them.
Jack, question here. Would not using the qualified names cause either a Schema Change or a Temp Table Change? Those are the 2 primary reason that I am getting the recompiles.
Thanks,
Fraggle
May 25, 2011 at 2:55 pm
Don't know. I also can't reproduce the behavior. I'm seeing anything referencing a temp table getting a recompile the first time I run sproc with a subclass of Deferred Compile, but that's 2008 R2.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 26, 2011 at 4:58 am
When you create temporary tables, you get a recompile. When you add a row to temporary tables, you get a recompile because of the stats updates. Then, statistics can get updated based on, I believe 600 rows + 10% of the table if it's greater than 600 rows, and that's, added, updated or removed rows. Each time stats get updated, you get a recompile. If the plan is really complicated and it takes it a while to recompile, then you get lots of blocking, which just exacerbates the problem. To fix it... it's a tough fix. In places where you can, use table variables instead of temp tables. These places include places where the temp table is NOT searched on (requires stats) or joined (requires stats). Otherwise, reduce the size of the procedure so it compiles faster, reduce the number of rows you're manipulating in temp tables so you get fewer recompiles, and that's about it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 26, 2011 at 6:29 am
Grant Fritchey (5/26/2011)
When you create temporary tables, you get a recompile. When you add a row to temporary tables, you get a recompile because of the stats updates. Then, statistics can get updated based on, I believe 600 rows + 10% of the table if it's greater than 600 rows, and that's, added, updated or removed rows. Each time stats get updated, you get a recompile. If the plan is really complicated and it takes it a while to recompile, then you get lots of blocking, which just exacerbates the problem. To fix it... it's a tough fix. In places where you can, use table variables instead of temp tables. These places include places where the temp table is NOT searched on (requires stats) or joined (requires stats). Otherwise, reduce the size of the procedure so it compiles faster, reduce the number of rows you're manipulating in temp tables so you get fewer recompiles, and that's about it.
Grant,
I am a bit confused here. I understand what you are saying about the statistics updating and causing recompiles and a deferred recompile. However, a Deferred Compile and Statistic changes both have their own EventSubClass in profile, so wouldn't it read that way?
The reasons for recompiles that I am seeing from an EventSubClass perspecitive are "Temp Table Changes" and "Schema Changes"
Thank for the clarification.
Fraggle.
May 26, 2011 at 6:39 am
You're still guaranteed a recompile each time a temp table is created and first referenced.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 26, 2011 at 6:53 am
Grant,
Isn't the compile time for large procedures why they changed to statement level compile with 2005? In theory the whole procedure should not need to be recompiled only those short statements which should reduce the CPU time needed for those recompiles.
I understand the recompiles being caused by the temp tables, but it shouldn't take long to recompile because only the specific statements should be recompiled not the entire procedure.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 26, 2011 at 7:06 am
Jack Corbett (5/26/2011)
Grant,Isn't the compile time for large procedures why they changed to statement level compile with 2005? In theory the whole procedure should not need to be recompiled only those short statements which should reduce the CPU time needed for those recompiles.
I understand the recompiles being caused by the temp tables, but it shouldn't take long to recompile because only the specific statements should be recompiled not the entire procedure.
Yeah, sorry if I wasn't clear, it is statement level, but the size of the proc matters because you'll still get some blocking while that recompile occurs and if the proc is big and has multiple temp tables, performance degrades. I've seen it, unfortunately.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 26, 2011 at 12:09 pm
Grant Fritchey (5/26/2011)
You're still guaranteed a recompile each time a temp table is created and first referenced.
Grant,
I understand that I will get recompile each time a temp table is created and first referenced due to a "Deferred Compile". However, I am not seeing "Deferred Compiles" for the statements. I am seeing "Schema Changes" and "Temp Table Changes".
Is my information incorrect? When a temp table is created or first referenced, would this be a "Schema Change" or a "Temp Table Change" rather than a "Deferred Compile"? If this is the case, then how does a "Deferred Compile" differ?
Thank you very much for the clarification.
Fraggle.
May 26, 2011 at 12:11 pm
Grant Fritchey (5/26/2011)
Jack Corbett (5/26/2011)
Grant,Isn't the compile time for large procedures why they changed to statement level compile with 2005? In theory the whole procedure should not need to be recompiled only those short statements which should reduce the CPU time needed for those recompiles.
I understand the recompiles being caused by the temp tables, but it shouldn't take long to recompile because only the specific statements should be recompiled not the entire procedure.
Yeah, sorry if I wasn't clear, it is statement level, but the size of the proc matters because you'll still get some blocking while that recompile occurs and if the proc is big and has multiple temp tables, performance degrades. I've seen it, unfortunately.
Thanks for the clarification. So the more complex the query is, the longer the plan will take to generate, which has potential for blocking other process. Does that sum it up?
Fraggle.
May 26, 2011 at 1:32 pm
Fraggle-805517 (5/26/2011)
Grant Fritchey (5/26/2011)
You're still guaranteed a recompile each time a temp table is created and first referenced.Grant,
I understand that I will get recompile each time a temp table is created and first referenced due to a "Deferred Compile". However, I am not seeing "Deferred Compiles" for the statements. I am seeing "Schema Changes" and "Temp Table Changes".
Is my information incorrect? When a temp table is created or first referenced, would this be a "Schema Change" or a "Temp Table Change" rather than a "Deferred Compile"? If this is the case, then how does a "Deferred Compile" differ?
Thank you very much for the clarification.
Fraggle.
My memory is very faulty so I looked up the eventsubclass for REcompiles. There isn't a deferred compile property. That might be an older event. I would expect to see the values you're seeing for the changes you're doing.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 26, 2011 at 1:36 pm
Fraggle-805517 (5/26/2011)
Grant Fritchey (5/26/2011)
You're still guaranteed a recompile each time a temp table is created and first referenced.Grant,
I understand that I will get recompile each time a temp table is created and first referenced due to a "Deferred Compile". However, I am not seeing "Deferred Compiles" for the statements. I am seeing "Schema Changes" and "Temp Table Changes".
Is my information incorrect? When a temp table is created or first referenced, would this be a "Schema Change" or a "Temp Table Change" rather than a "Deferred Compile"? If this is the case, then how does a "Deferred Compile" differ?
Thank you very much for the clarification.
Fraggle.
I'm slow but I get there eventually. You're capturing the sprecompile event, not the stmtrecompile event. If you capture that latter you should see the deferred compile. Sorry. Like I said, I can't go off of memory, I have to look everything up.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply