Simple enough. First execution will be with the parameter value of 1. I\u2019m going to use Profiler to see what\u2019s happening. Events traced are SP:CacheInsert, T-SQL:StmtRecompile and the XML Plan for query compile, so I can see exactly what plan was generated. I\u2019m using the \u2018For Query Compile\u2019 event so that I can catch the plan at optimisation time, not at execution time.<\/p>\n
<\/p>\n With that trace running, I\u2019m going to run the proc once with a value of 1 for the parameter. Important to note is that there is no cached plan for the procedure at this point.<\/p>\n I have two plan for query compile events, one cache insert for the procedure and a batch completed. I want to focus on the two compile events.<\/p>\n The first one shows a table scan on tbl1. The second shows another table scan, on tbl2. So it appears that both branches of the IF statement went through the optimiser and had plans created for them.<\/p>\n Before jumping to any conclusions, I want to see what’s sitting in the plan cache for this procedure.<\/p>\n One plan for that procedure and the plan shows a conditional (the if) and query operators for both branches.<\/p>\n So far it looks like the plan that was generated on the first execution contains plans for all of the statements and is sufficient for any execution, but to be sure, let’s see what happens when I run the query again with a different parameter.<\/p>\n No query compile, no cache insert, no recompile statement. So in this case there was no recompile from the IF.<\/p>\n Before drawing any conclusions, I want to check a more complex procedure, as the plans here were Trivial. Maybe full optimisation will show a different result.<\/p>\n These queries are complex enough to go through full optimisation, but the profiler trace shows the same as before<\/p>\n Two showplan events prior to the completion of the first execution, one for each branch of the IF, one cache insert event and no showplan events, no recompile events when the second execution (with a different parameter value) began. (I’ll leave examining the plan in cache as an exercise to the reader)<\/p>\n One more, just to be thorough.<\/p>\n Much the same as we saw in the earlier tests. This time there are three showplan events, one for the subquery in the IF and one for each branch. Again there is one CacheInsert for the entire procedure and no recompile events on the second execution<\/p>\n So what is actually happening?<\/p>\n When the optimiser receives a batch or object to generate execution plans for it does not execute any portion of that code. That’s not its job, that’s what the Query Execution engine is there for. The optimiser’s job is to optimise each statement within the procedure or batch(1)<\/sup>, to generate execution plans that are good enough and, in most cases, are reusable.<\/p>\n When it comes across an IF statement, the optimiser is not going to execute the expression to see which branch will be taken on this execution(2)<\/sup>. It’s going to optimise both branches because it has no idea which one will be executed on this or subsequent executions.<\/p>\n For each statement within the procedure, the optimiser generates the best plan it can find based on the parameter values for the current execution. This is not necessarily ideal as it can end up optimising queries for parameter values that they will never be called with. As can be imagined, this can generate some not-so-optimal execution plans. I wrote about this problem some time back<\/a>, so I’m not going to touch on it again<\/p>\n So, in conclusion\u2026 Do IF statements cause recompiles? No. The optimiser processes all branches of a conditional no matter which branch will be taken during execution, even if the branch can never be executed. Use of IF statements can however result in some really awful plans and make you sincerely wish they had caused a recompile. Use with caution (or recompile hints) if plans can be radically different depending on parameter values on the first execution.<\/p>\n (1) There are cases where the optimiser won’t optimise a statement within a procedure during the initial optimisation and will leave the statement to be optimised later (called deferred compile), but this is not due to IF statements. The one thing that immediately comes to mind that does cause this behaviour is when the statement depends on an object that does not exist at the point that the first optimisation is done, such as when a table is created within the procedure and then used within the same procedure.<\/p>\n (2) As proof that the optimiser doesn’t evaluate conditional expressions, consider the following:<\/p>\n If that is executed, it fails with a divide by zero error (as expected). Requesting the estimated execution plan succeeds and returns a valid execution plan and executing it with the profiler trace that I used above (adding in the user error event) shows the Showplan for query compile (as the select within the IF is optimised), then a cache insert for that plan, and then only is the divide by zero error thrown, just before the SQL:BatchCompleted event.<\/p>\n","protected":false},"excerpt":{"rendered":" I heard this one over at SSC a while back. \u201cAvoid IF statements in stored procedures as they result in recompiles\u201d Ok, it sounds reasonable, if the optimiser optimises for the execution path taken on first execution it\u2019ll have to… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[23,15,16],"tags":[],"class_list":["post-775","post","type-post","status-publish","format-standard","hentry","category-execution-plans","category-sql-server","category-syndication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-cv","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/775","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=775"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/775\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=775"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"TraceEvents\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2010\/12\/TraceEvents.png\" "\\"TraceEvents\\"")
<\/a><\/p>\n<\/a><\/p>\n<\/a> <\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\nUSE Adventureworks\r\nGO\r\n\r\nCREATE PROCEDURE TestingRecompiles2 (@SomeParam INT)\r\nAS\r\nIF (@SomeParam = 1)\r\n SELECT p.[Title], p.[FirstName], p.[MiddleName], p.[LastName]\r\n FROM [HumanResources].[Employee] e\r\n INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]\r\n INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = e.[BusinessEntityID]\r\n INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]\r\nELSE\r\n SELECT s.[Name], a.[AddressLine1],a.[AddressLine2],a.[City],a.[PostalCode]\r\n FROM [Sales].[Store] s\r\n INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = s.[BusinessEntityID]\r\n INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]\r\nGO<\/pre>\n
<\/a><\/p>\nCREATE PROCEDURE TestingRecompiles3\r\nAS\r\nIF EXISTS (SELECT 1 FROM tbl1 WHERE ID>0)\r\n SELECT ID FROM tbl1\r\nELSE\r\n SELECT ID FROM tbl2\r\nGO\r\n\r\nINSERT INTO tbl1 (ID) -- Pre-populate with 1 row so that stats-based recompiles don't confuse the issue.\r\n VALUES (0)\r\nGO\r\n\r\nEXEC TestingRecompiles3\r\nGO\r\n\r\nINSERT INTO tbl1 (ID)\r\nVALUES (1)\r\nGO\r\n\r\nEXEC TestingRecompiles3\r\nGO<\/pre>\n
<\/a><\/p>\nIF (42\/0 = 137)\r\n SELECT name, sys.objects.type_desc FROM sys.objects<\/pre>\n