April 19, 2011 at 12:03 pm
Gotta question about a possible plan guide scenario.
I have a sproc that executes a sequence of statements. After reviewing the trace, one of the sql statements is being recompiled every time it's run, so it's something like:
create procedure dbo.mystoredprocedure
@a int,
@aWAID int,
@OpID int
as
begin
...bunches of logic
then in the middle is:
IF @a= 1
BEGIN
INSERT INTO #ZLI SELECT SecID,
ModID,
WAID
FROM CA
WHERE WAID = @aWAID
AND OpID = @aOpID
END
ELSE IF @a = 2
BEGIN
INSERT INTO #ZLI SELECT SecID,
ModID,
WAID
FROM CA
WHERE OpID = @aOpID
END
...bunches of logic
end
In the trace file I can see an SP:Recompile entry and the text data is for the 'INSERT INTO #ZLI' statement, which I think makes sense as the way it's written. My question is, can I create a plan guide for each of the 'INSERT INTO #ZLI' statements and will that prevent the constant recompiling?
April 20, 2011 at 5:01 am
Nope, a plan guide won't help. Temporary tables have statistics. As the data changes, statistics get updated. When statistics updates meets certain thresholds, you get recompile events firing. If the statement level recompiles are hurting performance, you might (big word, MIGHT), consider using table variables instead. They don't have statistics, so recompiles are eliminated, but, because they don't have statistics, they can negatively impact performance.
----------------------------------------------------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
April 20, 2011 at 5:29 am
Here's why it's just a might... I converted a big proc from using ±10 table variables to temp tables. Here are the stats after I had done all the index tuning I could on the extraction queries :
#baseline (CPU, READS, WRITES, MS Duration)
21219 4 107 095 822 28616
#fnsplit into #temp tables
12125 1 866 311 789 20470
#items
6312 622 135 786 10276
#v2
6469 254 344 818 11489
#v2 test 2
6109 254 108 825 9078
#all else
8033 254 097 831 9204
April 21, 2011 at 7:33 am
You have TWO sections of "bunches of logic". The question here is if those two sections take much work. If they do, then the cost of a recompile (unless iterated a LOT) is probably a very small part of the overall cost/duration of the sproc execution. And please note what Grant said - if you switch to and then join to those new table variables you can get REALLY bad query plans some times. If that isn't the case for your data and you are seeing perf degredation from recompiles then the switch to table vars could be helpful.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply