{"id":2496,"date":"2021-01-19T16:30:00","date_gmt":"2021-01-19T14:30:00","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=2496"},"modified":"2021-01-18T00:19:22","modified_gmt":"2021-01-17T22:19:22","slug":"what-is-deferred-compilation","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2021\/01\/19\/what-is-deferred-compilation\/","title":{"rendered":"What is deferred compilation?"},"content":{"rendered":"\n
When I talked about row estimations for table variables, I mentioned ‘deferred compile’, but didn’t give a whole lot of details. What, then, is a deferred compilation? Let’s start with how batches work normally.<\/p>\n\n\n\n
T-SQL is an interpreted language. While we talk about compiles, they’re not compilations in the sense of what happens to C++. There’s no conversion of the script to a machine language or intermediate language which is used from that point onwards. Every time a batch executes, it has to be parsed, bound and have an execution plan generated or fetched from cache.<\/p>\n\n\n\n
When a batch is parsed, the entire batch is parsed.<\/p>\n\n\n
\nSET STATISTICS TIME ON\nGO\n\nSELECT * FROM dbo.Clients\n\nSELECT * FROM dbo.StarSystems ss \nINNER JOIN dbo.Stations s ON s.StarSystemID = ss.StarSystemID\n\nSELECT GETDATE()\nGO\n<\/pre><\/div>\n\n\n<\/figure>\n\n\n\n
Ignoring the SET STATISTICS, we’ve got there one batch with two queries in it. Two queries, two execution times but only one parse and compile time. The entire batch was compiled and then the batch was executed.<\/p>\n\n\n\n
If we set up an XE session to track compiles, it shows similar<\/p>\n\n\n\n<\/figure>\n\n\n\n
The XE session shows that the compilations for both queries were completed before either started execution.<\/p>\n\n\n\n
But what happens if we reference a table that doesn’t exist?<\/p>\n\n\n
\nSET STATISTICS TIME ON\nGO\n\nSELECT * FROM dbo.Clients\n\nSELECT * FROM dbo.StarSystems_DoesNotExist ss \nINNER JOIN dbo.Stations s ON s.StarSystemID = ss.StarSystemID\n\nSELECT GETDATE()\nGO\n<\/pre><\/div>\n\n\n<\/figure>\n\n\n\n
The first of the queries is still compiled, but not the second. The second can’t be compiled because it references an object that doesn’t exist. The first query then executes, and right before when the second query would execute, it’s sent back to be bound and optimised. In this case, the object still doesn’t exist and so we get an error.<\/p>\n\n\n\n
Msg 208, Level 16, State 1, Line 6 Invalid object name 'dbo.StarSystems_DoesNotExist'<\/span><\/code>.<\/p>\n\n\n\n
But if the object was created between the start of the batch and the query that uses it, we get a still different result<\/p>\n\n\n
\nSET STATISTICS TIME ON\nGO\n\nSELECT * FROM dbo.Clients\n\nSELECT * INTO #StarSystems FROM dbo.StarSystems\n\nSELECT * FROM #StarSystems ss \nINNER JOIN dbo.Stations s ON s.StarSystemID = ss.StarSystemID\n\nSELECT GETDATE()\nGO\n<\/pre><\/div>\n\n\n<\/figure>\n\n\n\n
This time we have three queries. Two of them get an execution plan generated when the batch starts, but the third can’t, because the table it references doesn’t exist. Instead, the statement starts executing but can’t execute because there’s no plan. It gets sent back to the optimiser to be compiled, then the query executes.<\/p>\n\n\n\n
This is a deferred compile (also called deferred resolution). A compile that does not happen when the batch starts, but is rather deferred until the point that the query itself executes, usually because the table does not exist at the point the batch starts..<\/p>\n","protected":false},"excerpt":{"rendered":"
![\"\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2021\/01\/2021-01-13_23-02-34.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2021\/01\/2021-01-14_00-57-38-1024x239.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2021\/01\/2021-01-17_22-16-19.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2021\/01\/2021-01-18_00-13-31-1024x332.png\")
<\/figure>\n\n\n\n