Hidden cost Analysis When executing insert EXEC _ database Other

The Hidden Costs of INSERT EXEC

INSERT and Exec:relational Brothers in arms, helping your get your data and then put it somewhere. But like all brothers, their relationship has its ups and downs and sometimes your need to look just below the surface to s EE the real issues. In this post I'll cover hidden penalties that a decision--seemingly purely aesthetic--can to the bring.

To illustrate the examples I would be using the following table:

Use tempdb
Go

CREATE TABLE dbo. MyTable
(
[SalesOrderID] [INT] Not NULL,
[Salesorderdetailid] [INT] Not NULL,
[Carriertrackingnumber] [nvarchar] (a) NULL,
[OrderQty] [smallint] Not NULL,
[ProductID] [INT] Not NULL,
[Specialofferid] [INT] Not NULL,
[UnitPrice] [Money] Not NULL,
[Unitpricediscount] [Money] Not NULL,
[LineTotal] [Money] Not NULL,
[ROWGUID]  [uniqueidentifier] Not NULL,
[ModifiedDate] [DateTime] Not NULL
)
Go

Consider the following queries, disregarding the fact that this isn ' t a good dynamic SQL example (I m trying to keep it si Mple), and take a moment to is about the differences between them from a behavioral point of view:

Exec
('
INSERT dbo. MyTable
SELECT *
From AdventureWorks.Sales.SalesOrderDetail
')
Go

INSERT dbo. MyTable

('
SELECT *
From AdventureWorks.Sales.SalesOrderDetail
')
Go

It ' s quite common to insert some data into a table from dynamic SQL, and for as long as I can remember, I ' ve believed the Choice between two forms of the query to is primarily an issue of style. My preference has been for the latter style, as I feel that it's a bit more readable. It also has the benefit of better supporting ownership chains, but that ' s something, and in most cases, we don ' t have to Worry about any longer in SQL Server or 2008. But Let's move beyond style and potential security issues and get to the substance. What actually happens when do we run these two queries?

In the ' Case--insert done inside of the exec--the of the ' The ' is ' is ' that outer statement--the EXEC Itself--is Pars Ed and validated. Now we are down one level of jump, into the string, which is passed to EXEC, and which is parsed and validated. The referenced objects are resolved, and the plan cache is checked. If There is no plan, the query is compiled. And then it ' s executed. The Data is streamed from SalesOrderDetail into MyTable and then the query inside of the EXEC is do control returns to the C Alling Context--exec--and Assuming there were no errors, the EXEC is now completed.

In the second case, something quite a bit different occurs. The insert is parsed and validated, and a insert EXEC plan is generated. This is does not involve SalesOrderDetail, as the query optimizer doesn ' t know at the the process where the DA Ta'll be inserted from, since, which is dynamic. So the plan references something called the "Parameter Table" (Plan truncated for simplicity):

|--table Insert (OBJECT: ([tempdb].[ DBO]. [MyTable])
|--top (rowcount est 0)
|--parameter Table Scan

Once This plan has been generated, we again jump down one level, and the inner string is parsed and validated, The referenced object resolved, the cache checked, and the compiled plan executed. But What is this Parameter Table thing?

Here's where things start really diverging. Data is not, at this point, streamed from SalesOrderDetail directly to MyTable. Rather, it is streamed from SalesOrderDetail to the Parameter Table. And the Parameter table, as it turns out, are in actuality a hidden temporary table. And not until all of the data has streamed to that hidden temporary the table are control resumed by the EXEC context, and on Ly then does the data start moving into its final home.

The natural question you might are asking yourself at this point are, just how much overhead does this Parameter Table in Troduce into the equation? The answer might just send you racing to check your stored procedures:the additional cost for the Parameter Table is Over 100% as compared with doing the insert inside of the EXEC. The fact that all of the data must is spooled to the Parameter Table before the insert can even begin tells us so this m UST be true, and it can is verified using a simple check against the sys.dm_exec_requests DMV, as in the following example :

Exec
('
INSERT dbo. MyTable
SELECT *
From AdventureWorks.Sales.SalesOrderDetail
')

SELECT
R.writes as [INSERT inside EXEC writes]
From Sys.dm_exec_requests R

r.session_id = @ @SPID
Go

INSERT dbo. MyTable
Exec
('
SELECT *
From AdventureWorks.Sales.SalesOrderDetail
')

SELECT
R.writes as [INSERT EXEC writes]
From Sys.dm_exec_requests R

r.session_id = @ @SPID
Go

That's that. We should avoid INSERT EXEC and try to does our inserts in the same context of which the SELECT is Running--right?

Yes and No. There is another element at the which I Haven ' t yet mentioned. What if we were only inserting a few rows, and the table we were inserting to looked like the something:

CREATE TABLE #MyTable
(
[SalesOrderID] [INT] Not NULL,
[Salesorderdetailid] [INT] Not NULL,
[Carriertrackingnumber] [nvarchar] (a) NULL,
[OrderQty] [smallint] Not NULL,
[ProductID] [INT] Not NULL,
[Specialofferid] [INT] Not NULL,
[UnitPrice] [Money] Not NULL,
[Unitpricediscount] [Money] Not NULL,
[LineTotal] [Money] Not NULL,
[ROWGUID]  [uniqueidentifier] Not NULL,
[ModifiedDate] [DateTime] Not NULL
)
Go

The only difference between this table and the previous one are that this is a temporary table and the other are not. But temporary tables have their own interesting little twists when it especially down to one of the key comes in A highly-transactional system:recompilation. As it turns out, doing the insert inside the EXEC would cause the internal statement to recompile every time a new temp tab Le is encountered. This means so if you have a stored procedure that creates a temp table, puts together a bit of dynamic SQL, and does a Insert inside of that dynamic SQL, and you'll now have yourself a recompilation problem.

To the illustrated, try the following script. Here's a single row are inserted as a result of the dynamic query, but it's complex enough the compile time more th An overshadows the overhead of the Parameter Table:

Use tempdb
Go

DBCC Freeproccache
Go

CREATE TABLE #AvgTimes
(
Cpu_time DECIMAL (19,4) not NULL,
Insert_type VARCHAR not NULL
)
Go

CREATE TABLE #MyTable
(
[SalesOrderID] [INT] Not NULL,
[Salesorderdetailid] [INT] Not NULL,
[Carriertrackingnumber] [nvarchar] (a) NULL,
[OrderQty] [smallint] Not NULL,
[ProductID] [INT] Not NULL,
[Specialofferid] [INT] Not NULL,
[UnitPrice] [Money] Not NULL,
[Unitpricediscount] [Money] Not NULL,
[LineTotal] [Money] Not NULL,
[ROWGUID]  [uniqueidentifier] Not NULL,
[ModifiedDate] [DateTime] Not NULL
)

Exec
('
INSERT #MyTable
SELECT Top (1)
sod.*
From AdventureWorks.Sales.SalesOrderDetail Sod
WHERE
Sod. UnitPrice > 10
and Sod. LineTotal > 100
and EXISTS
(
SELECT *
From AdventureWorks.Sales.SalesOrderHeader Soh
JOIN AdventureWorks.Person.Contact C on
C.contactid = Soh. CustomerID
WHERE
Soh. SalesOrderID = Sod. SalesOrderID
and c.lastname like ' ab% '
)
and EXISTS
(
SELECT *
From AdventureWorks.Production.Product P
WHERE
P.productid = Sod. ProductID
and P.color is NULL
)
And not EXISTS
(
SELECT *
From AdventureWorks.Sales.SalesOrderHeader Soh
JOIN AdventureWorks.Sales.SalesPerson SP on
Soh. SalesPersonID = sp. SalesPersonID
WHERE
Soh. SalesOrderID = Sod. SalesOrderID
and sp.commissionpct > 50
)
')

INSERT #AvgTimes
SELECT
R.cpu_time,
' INSERT inside EXEC '
From Sys.dm_exec_requests R
WHERE
r.session_id = @ @SPID

DROP TABLE #MyTable
Go 5

CREATE TABLE #MyTable
(
[SalesOrderID] [INT] Not NULL,
[Salesorderdetailid] [INT] Not NULL,
[Carriertrackingnumber] [nvarchar] (a) NULL,
[OrderQty] [smallint] Not NULL,
[ProductID] [INT] Not NULL,
[Specialofferid] [INT] Not NULL,
[UnitPrice] [Money] Not NULL,
[Unitpricediscount] [Money] Not NULL,
[LineTotal] [Money] Not NULL,
[ROWGUID]  [uniqueidentifier] Not NULL,
[ModifiedDate] [DateTime] Not NULL
)

INSERT #MyTable
Exec
('
SELECT Top (1)
sod.*
From AdventureWorks.Sales.SalesOrderDetail Sod
WHERE
Sod. UnitPrice > 10
and Sod. LineTotal > 100
and EXISTS
(
SELECT *
From AdventureWorks.Sales.SalesOrderHeader Soh
JOIN AdventureWorks.Person.Contact C on
C.contactid = Soh. CustomerID
WHERE
Soh. SalesOrderID = Sod. SalesOrderID
and c.lastname like ' ab% '
)
and EXISTS
(
SELECT *
From AdventureWorks.Production.Product P
WHERE
P.productid = Sod. ProductID
and P.color is NULL
)
And not EXISTS
(
SELECT *
From AdventureWorks.Sales.SalesOrderHeader Soh
JOIN AdventureWorks.Sales.SalesPerson SP on
Soh. SalesPersonID = sp. SalesPersonID
WHERE
Soh. SalesOrderID = Sod. SalesOrderID
and sp.commissionpct > 50
)
')

INSERT #AvgTimes
SELECT
R.cpu_time,
' INSERT EXEC '
From Sys.dm_exec_requests R
WHERE
r.session_id = @ @SPID

DROP TABLE #MyTable
Go 5

SELECT
Avg
(
Case A.insert_type
When ' INSERT inside EXEC ' THEN a.cpu_time
ELSE NULL
End
As [CPU Time-insert inside EXEC],
Avg
(
Case A.insert_type
When ' INSERT EXEC ' THEN a.cpu_time
ELSE NULL
End
) as [CPU Time-insert EXEC]
From #AvgTimes A
Go

DROP TABLE #AvgTimes
Go

So what have we are learned today? The choice between insert EXEC and insert inside of exec is not purely stylistic and has definite performance . Here are the rules I'll be following out of:

• when working with permanent tables, always avoid INSERT EXEC if possible. There are some cases where it won ' t be possible to avoid. I only showed the dynamic SQL in this post and But INSERT EXEC also applies to stored procedures. Can you safely rip apart all of the stored procedures in your system in order to avoid this issue? Maybe not quite as easily as can rip apart the dynamic SQL within stored.
• when working and temporary tables, evaluate the complexity of the operations, the amount of data to be Inserte D, and most importantly, test every solution! The more rows this are inserted as a result of the INSERT EXEC, the more the overhead you'll get from the Parameter Table. On the flip side, the more complex the dynamic statement, the more overhead your ' ll get from recompilations. Every scenario is bound to being different and you just learn something about your processes by doing this extra bit of a Nalysis.