Query Optimizer and Data Definition Language Queries

Data Definition Language queries don’t go through the optimizer, right? While normally, my short answer to this question in the past would have been, yes. However, with testing comes knowledge and I want to be sure about the statement.

I’m working with a team of people to completely rewrite the SQL Server Execution Plans book. We’ll probably be published in April or May. It’s going to be amazing. The structure will be completely different and the book will represent five years of additional knowledge in how execution plans work and how to read and interpret them since the last book was written. However, enough on that. Let’s answer the question about Data Definition Language.

First of all, we need to quickly define our terms. Data Definition Language (DDL) represents the syntax for queries that build your databases and the data structures within them. As a result, if you read the documentation at MSDN, you’ll see that this includes CREATE, ALTER, DROP, ENABLE/DISABLE TRIGGER, TRUNCATE TABLE and UPDATE STATISTICS. Consequently, these command are separated from the Data Manipulation Language (DML) queries that are used to SELECT, UPDATE, DELETE and INSERT your data (amongst other things).

Testing the Data Definition Language Query

Here is an example of one of the simplest possible DDL queries:

CREATE TABLE dbo.MyNewTable (  MyNewTableID INT PRIMARY KEY IDENTITY(1, 1),
                               MyNewValue NVARCHAR(50)
                            );
GO

We can check whether or not you’re going to get an execution plan for this in two easy ways. You can capture the estimated plan, or run the query and capture the actual plan. Since most of the time, it’s preferable to work with the added runtime information that an actual plan provides, let’s start there. However, if you execute the query above and try to capture an actual plan, you will not get one. So, let’s capture the estimated plan instead. Here’s what we see:

Not much of a plan really. The devil is in the details though, so let’s open the Properties page (always use the Properties page, the ToolTip is just too unreliable in the amount of information displayed):

That is ALL the properties that are available. The implication is simple, SQL Server doesn’t create execution plans for DDL statements.

Further, the lack of a plan suggests that these queries must not go through the query optimization process. We can check this. There is a Dynamic Management View (DMV), sys.dm_exec_query_optimizer_info, that shows aggregate values for the work done by the optimizer. It’s a little tricky to capture the metrics of a single query, but it is possible (NOTE: don’t run this on production, please):

DBCC FREEPROCCACHE();
GO

SELECT *
INTO OpInfoAfter
FROM sys.dm_exec_query_optimizer_info AS deqoi;
GO

DROP TABLE OpInfoAfter;
GO

--gather the existing optimizer information
SELECT *
INTO OpInfoBefore
FROM sys.dm_exec_query_optimizer_info AS deqoi;
GO

--run a query
CREATE TABLE dbo.MyNewTable (  MyNewTableID INT PRIMARY KEY IDENTITY(1, 1),
                               MyNewValue NVARCHAR(50)
                            );
GO

SELECT *
INTO OpInfoAfter
FROM sys.dm_exec_query_optimizer_info AS deqoi;
GO

--display the data that has changed
SELECT oia.counter,
       (oia.occurrence - oib.occurrence) AS ActualOccurence,
       (oia.occurrence * oia.value - oib.occurrence * oib.value) AS ActualValue
FROM OpInfoBefore AS oib
JOIN OpInfoAfter AS oia
   ON oib.counter = oia.counter
WHERE oia.occurrence <> oib.occurrence;
GO

DROP TABLE OpInfoBefore;
DROP TABLE OpInfoAfter;
GO

If we run this, we won’t see any values. This is because this CREATE TABLE statement doesn’t go through the optimizer. There’s no optimization possible, so you won’t see an execution plan of any kind.

Does this mean that my first answer is correct then? Does the optimizer skip making execution plans for DDL statements?

Changing the Data Definition Language Query

Let’s modify the query so that we’re doing an ALTER table instead of a CREATE:

ALTER TABLE Sales.Customer  WITH CHECK ADD  CONSTRAINT SomeTest FOREIGN KEY(CustomerID)
REFERENCES [dbo].[Agent] ([AgentId]);

If we check the Estimated and Actual plans using the same methods above, we don’t get an execution plan (well, the estimated plan is the same T-SQL place holder that we saw). What happens if we see what the optimizer did? This time, we get results:

counter	ActualOccurence	ActualValue
elapsed time	2	0
final cost	2	0.139429282
insert stmt	1	1
maximum DOP	2	0
optimizations	2	2
search 1	2	2
search 1 tasks	2	117
search 1 time	2	0
tables	2	2
tasks	2	117
view reference	1	1

I’ll tell you right now, I don’t know what this represents. I suspect I’d need to run the debugger to see it. Maybe it’s a plan for the CHECK process of the check constraint. The optimizer is involved in this process, twice. Two different statements were optimized. One involved inserting data and one involved referencing a view. However, at the end of the process, we still don’t see an execution plan displayed.

How About Extended Events

Can we capture the events to see what’s happening? We can try. I’ll spin up a Session and capture the following events:

query_post_execution_showplan
sql_statement_completed
sp_statement_completed

In addition, I’ll enable event correlation so that we can see the order in which events occurred. Now, I’ll try running the ALTER TABLE command to see what we can find out:

name	statement	attach_activity_id.guid	attach_activity_id.seq
query_post_execution_showplan	NULL	65A0A74B-E5D5-460C-846D-87808C334283	1
sp_statement_completed	SELECT * FROM [Sales].[Customer]	65A0A74B-E5D5-460C-846D-87808C334283	2
sql_statement_completed	ALTER TABLE Sales.Customer  WITH CHECK ADD  CONSTRAINT SomeTest FOREIGN KEY(CustomerID)  REFERENCES [dbo].[Agent] ([AgentId])	65A0A74B-E5D5-460C-846D-87808C334283	3

And there we have it. We can see that there was a plan compiled, first, then a procedure call was made with a single statement: SELECT * FROM Sales.Customer. Finally our SQL Batch statement that ran the ALTER TABLE was created. Everything is grouped by the activity GUID and we have them in the sequence determined by attach_activity_id.seq. Here’s the execution plan:

In short, there was a plan generated here, but it is, as I guessed at, the plan for the check constraint process. There is no plan for the Data Definition Language statement.

Conclusion

Just saying something doesn’t make it true. You have to be able to prove it. From every evidence that Microsoft supplies us, the DDL statements do not generate an execution plan or go through the query optimization process. Because we tested this theory by attempting to create a plan, by observation of the optimizer through the sys.dm_exec_query_optimizer_info DMV, and through Extended Events, we can therefore safely say that none of the statements tested created any kind of plan for the DDL statement.

Have I tested every variation on every possible DDL statement? No. Is it possible that other statements could have execution plans associated with them? I won’t say that’s impossible, because nothing is impossible. Therefore, I’ll simply say, that’s unlikely.