The Hidden Costs of INSERT EXEC

re: The Hidden Costs of INSERT EXEC

Florian Reischl — Thu, 25 Jun 2009 20:47:06 GMT

Very nice Article, Adam!

Thanks

Flo

re: The Hidden Costs of INSERT EXEC

LeoPasta — Fri, 26 Jun 2009 10:44:29 GMT

Great article, Adam!

Well done.

re: The Hidden Costs of INSERT EXEC

jerryhung — Fri, 26 Jun 2009 20:10:59 GMT

1st try

CPU time - INSERT inside EXEC CPU time - INSERT EXEC

396.800000 43.800000

2nd & 3rd aren't as bad

87.600000 34.400000

96.800000 31.400000

But something new learned today indeed

re: The Hidden Costs of INSERT EXEC

Bruce W Cassidy — Sun, 28 Jun 2009 21:59:54 GMT

Sometimes it's the things you take for granted that can turn around and bite you. I've always tended to veer to the INSERT... EXEC form myself, proabably for stylistic reasons. Now I will need to challenge that.

Cheers, good little article! Thought provoking as usual.

re: The Hidden Costs of INSERT EXEC

ValeriyNY — Wed, 01 Jul 2009 13:38:32 GMT

Great post, thought provoking! I usually prefer direct INSERT SELECT, but recently realized the overhead of recompilation if temp table is used. Thus, switching to INSERT EXEC made perfect sense in my case. Of course, as usual, YMMV and the answer is "it depends, test, test, test".

re: The Hidden Costs of INSERT EXEC

Nima — Fri, 03 Jul 2009 11:47:16 GMT

Hi Adam!

Great post :)

Why do I get different results from writes column in sys.dm_exec_requests?

re: The Hidden Costs of INSERT EXEC

Nima — Fri, 03 Jul 2009 11:51:27 GMT

Sorry I mean: different result each time that I query sys.dm_exec_requests!

re: The Hidden Costs of INSERT EXEC

Adam Machanic — Fri, 03 Jul 2009 13:53:17 GMT

Hi Nima,

I don't understand your question. Can you post a script that shows what you're seeing?

re: The Hidden Costs of INSERT EXEC

Nima — Sat, 04 Jul 2009 17:32:13 GMT

Sorry for my poor explanation!

I mean when I query sys.dm_exec_requests, it returns 24, next time 26, next time 20 and ....

re: The Hidden Costs of INSERT EXEC

Adam Machanic — Sun, 05 Jul 2009 17:21:33 GMT

Hi Nima,

Great question! What seems to be causing this is the partially-filled pages that remain from previous inserts. Sometimes more pages have to be allocated to make the insert work and other times some pages that were allocated on the previous run can be reused. This may translate into fewer writes for those attempts since not as many allocations need to be logged.

re: The Hidden Costs of INSERT EXEC

Nima — Mon, 06 Jul 2009 11:25:54 GMT

Thanks Adam!

What about 'Reads' column? It is always 0 in my batch:

SELECT * FROM northwind..Orders

SELECT reads

FROM sys.dm_exec_requests

WHERE session_id = @@SPID

re: The Hidden Costs of INSERT EXEC

Adam Machanic — Tue, 07 Jul 2009 15:57:20 GMT

Hi Nima,

The "reads" column in sys.dm_exec_requests appears to refer to physical reads (it's not documented in exactly that way, but that's the best I've been able to determine). Try the logical_reads column instead.

re: The Hidden Costs of INSERT EXEC

Nima — Wed, 08 Jul 2009 07:13:25 GMT

Thanks indeed! :-)

re: The Hidden Costs of INSERT EXEC

Roy Harvey — Mon, 20 Jul 2009 16:07:21 GMT

>>The answer might just send you racing to check your stored procedures: the additional cost for the Parameter Table is well over 100% as compared with doing the insert inside of the EXEC.<<

I suspect the percentage would fall if the table has indexes, though the absolute effect would be as described.

The same idea as INSERT/EXEC is now being written widely with an INSERT/SELECT against a Table Valued Function. An in-line TVF would be fine, since it is just seen as a parameterized view and compiled into the base query as a view would be. However a complex TVF has to be staged into a temporary table and probably has the same overhead as you describe for INSERT/EXEC.

re: The Hidden Costs of INSERT EXEC

Arif — Wed, 22 Jul 2009 10:06:18 GMT

Great article, Adam!

i have one question

Does SQL Server cache the plan for dynamic queries ?

if yes then what is the disadvantage of a simple dynamic query

re: The Hidden Costs of INSERT EXEC

Adam Machanic — Mon, 27 Jul 2009 15:06:32 GMT

Hi Arif,

Yes, plans are cached for dynamic queries. I'm not sure what you mean when you ask about the "disadvantage of a simple dynamic query." There are advantages and disadvantages, depending on what it is that you're doing. You might want to read Erland Sommarskog's article, "The Curse and Blessings of Dynamic SQL":

http://sommarskog.se/dynamic_sql.html

re: The Hidden Costs of INSERT EXEC

Ranu Mandan — Tue, 18 Oct 2011 08:40:06 GMT

Do it solves nested EXEC restriction ?