This option forces the stored procedure to be recompiled on every execution time. Keep in mind with version 15 of SQL Server, Microsoft introduced a feature called “Table Variable Deferred Compilation” that solves the problem mentioned here, without the use of option RECOMPILE. The WITH RECOMPILE option indicates that SQL Server does not cache a plan for the stored procedure. If you find your Table Variable queries are not performing well then you might consider adding the optimizer hint RECOMPILE to see if your query starts performing better. Once the optimizer has a better guess at the actual number of rows in a table variable, then it has a much better chance of picking an operator that works well for larger record sets, like a Hash Match for a join operation. This will allow the optimizer to know how many rows are in the table variable. Therefore, to improve the performance of your code that use table variables that contain lots of rows, you should consider adding the optimizer hint RECOMPILE as shown below: SELECT O., TV.īy adding the optimizer hint RECOMPILE, a statement that uses a table variable will be recompiled after the temporary table has been populated. The WITH RECOMPILE procedure option instructs the Database Engine does not cache a. ![]() Operators that perform well for small numbers of rows do not always scale when large numbers of rows are involved. The OPTION(RECOMPILE) is the preferred method when a recompile is needed. Having an estimated row count of 1 works well for operators that have small row counts, like a NESTED LOOP operator for a join operation. This occurs because table variables don’t have statistics. This is because when a batch is compiled the table variable has yet to be populated with any rows, and therefore the optimizer uses an estimated row count of 1 for table variables. Unfortunately that often goes hand-in-hand with making the query simpler, and I don't know how much control you have over that.Table Variables have been known to run slowly when the table variable contains lots of rows. What you often want to try and do in these cases is to work towards plan stability, so that no matter what happens (outside of changing the schema), then a good enough plan is always generated. This means that another execution of the same query will require to elaborate a. To answer why this suddenly changed one night - well there could be updated statistics, or a 'better' plan got pushed out of cache? I can envisage lots of scenarios where 'nothing' has changed, and yet the performance suddenly tanks. OPTION(RECOMPILE) tells the server not to cache the pan for given query. The slower plan does not have the early termination, which means that it thinks it has arrived at the best plan for the estimates and statistics that it has - clearly this is not the case as borne out by your experience. (see for more info) It's not unusual for this seemingly negative position to actually deliver you a plan that is good - just because it didn't evaluate every single possibility, doesn't mean it hasn't already got the best plan. We also see that the faster plan has a Reason for early termination: Time Out - which means that the optimiser reached a threshold and had to abandon looking for any further possible plans and go with the best it had so far. Unfortunately the plan and query are so complex it is difficult to pinpoint exactly why. However in your case the actual performance is improved by this 'wrong' plan, when compared to the 'better' plan. ![]() So the faster plan wrongly assumes it is going to be doing less work and so chooses a plan and operators based on that assumption. Conversely the '22 hour' plan (with option recompile) estimates a higher row count of 8,659. ![]() Looking at the plans (and yes they are fairly complex!) we can see that the '42 min' plan (without option recompile) has an estimated output row count of 2 rows, whereas the actual output is 21k. ![]() I think the first point I would make is that option(recompile) is not necessarily an optimisation, I'd view it as a 'change of behaviour', which can affect performance either way.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |