June 6, 2012 at 10:12 pm
Comments posted to this topic are about the item Re-compilation and Its effects on Performance
June 7, 2012 at 12:45 am
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?
Pete
June 7, 2012 at 1:55 am
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.
June 7, 2012 at 3:22 am
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.
June 7, 2012 at 4:30 am
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.
June 7, 2012 at 6:09 am
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.
June 7, 2012 at 7:36 am
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
June 7, 2012 at 9:52 am
June 7, 2012 at 10:16 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply