Query Optimizer and Data Definition Language Queries
Query Optimizer and Data Definition Language Queries
Are you SURE that data definition language (DDL) queries aren't run through your database's optimizer? It turns out that the tests aren't so conclusive.
Join the DZone community and get the full member experience.Join For Free
Find out how Database DevOps helps your team deliver value quicker while keeping your data safe and your organization compliant. Align DevOps for your applications with DevOps for your SQL Server databases to discover the advantages of true Database DevOps, brought to you in partnership with Redgate
Data Definition Language queries don’t go through the optimizer, right? 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:
|search 1 tasks||2||117|
|search 1 time||2||0|
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:
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:
|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.
After creating our plan, there are a couple of takeaways. 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.
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.
Published at DZone with permission of Grant Fritchey , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.