SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Re-compilation and Its effects on Performance


Re-compilation and Its effects on Performance

Author
Message
at.yazdani
at.yazdani
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 15
Comments posted to this topic are about the item Re-compilation and Its effects on Performance
admin-499013
admin-499013
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 114
Simply stating a known issue (recompilation) has , I suppose some merit.

However it would be much more interesting to see some quantitative analysis of the effects or otherwise of recompilation, perhaps in relation to size and complexity of query. Some comparisons across SQL Server versions?
doofledorfer
doofledorfer
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 135
There is value in being aware of the many reasons queries are fast or slow. What would begin making this useful would be a. A discussion about relative magnitude of the consequences, and b. where to look to determine whether this issue is affecting my query, and how much?

Question: what has been, in your experience, the longest amount of time it has taken to compile a query?

How much difference has it ever made, in your experience, to alter the default behavior of the query cache?

It would be an interesting exercise to turn off the cache, recompile every statement every time and make note of in which circumstances, if any, a perceptible difference was made.
David Data
David Data
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1559 Visits: 828
I use WITH RECOMPILE or OPTION(RECOMPILE) quite often; the latter is what you use when you only want to recompile an individual query within a procedure. You put it at the end of the query - e.g.
SELECT A, B, C
INTO [MyNewtable]
FROM [MyTable]
WHERE Whatever
OPTION(RECOMPILE);



I have an ETL application which runs a lot of SQL procedures to process the data that has been loaded. When the procedures are created - and in some cases when they first run - the tables are empty. The next time they run there may be millions of records in each table. The initial plan may be a very bad one by that time.

I had one proc that ran in seconds under test, but sometimes took minutes or hours in production. Eventually I realised that it was sometimes trying to do a triangular join without using a critical index, even though the index was there. In that case the original plan was based on the statistics for the table just BEFORE it was loaded with a lot of data, when the index probably would not have been needed. The solution was to
UPDATE STATISTICS [MyTable];
before the query, so the
OPTION(RECOMPILE);
then produced a good plan.

Another case where RECOMPILE seems to help are procedures which use IF ... ELSE to run different queries depending on a parameter. I'm not sure how clever SQL Server is about these, but a plan where one query runs would not be good if next time a different one did.

As to doofledorfer's question about compile time - I don't know how long it takes but I assume milliseconds. Not good if you're running a fast transaction thousands of times, but no problem at all when you're doing a data load which takes seconds with RECOMPILE and minutes without it.
peter-757102
peter-757102
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3065 Visits: 2559
In SQL Server 2008, there is a query hint that circumvents optimization for specific values:

option( optimize for unknown )

This results in plans that are value agnostic and deliver more constant performance.
jpenniman
jpenniman
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 31
I do agree... understanding the effects of compilation/recompilation on application performance is a worth while investigation. The moral, however, should be "avoid compilation if possible." Speaking anecdotally from experience, I've seen compile times as much as sever hundred milliseconds. 200 milliseconds might no seem like much, but scale it out to 10,000 concurrent users, and you've just tanked you application and caused your phone to ring with the dreaded "the app is slow" call.

I would argue that if you're turning to recompile as a solution to consistent timings, you need to step back and reconsider the design. You can almost always eliminate the need for a recompile with proper tuning. There are always exceptions, of course, but in your standard OLTP application it's generally not necessary.
jfogel
jfogel
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1744 Visits: 1197
I've only had the need for WITH RECOMPILE once. I had a stored procedure that would gather data for a report and it was always returning something other than what was expected. I made sure there were not multiples like dbo.stored proc and user name.proc and thus executing the wrong one, ran trace sessions on it and anything else I could throw at it. It was only after adding recompile to it that the thing started to act right. Even drop/create didn't solve the issue. I didn't have a lot of time to spend on the issue so I never found out exactly why this was an issue.

Cheers
Raj Gujar
Raj Gujar
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 175
I have added some more information about this on My Blog , Hope this helps.

[url=http://sqlsrv.blogspot.com][/url]
R A J
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6343 Visits: 3648
peter-757102 (6/7/2012)
In SQL Server 2008, there is a query hint that circumvents optimization for specific values:

option( optimize for unknown )

This results in plans that are value agnostic and deliver more constant performance.

Peter, Thanks for the tip. That looks useful.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search