The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspxINSERT and EXEC: relational brothers in arms, helping you get your data and then put it somewhere. But like all brothers, their relationship has its ups and downs and sometimes you need to look just below the surface to see the real issues. In this postenCommunityServer 2.1 SP2 (Build: 61129.1)re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#14927Thu, 25 Jun 2009 20:47:06 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:14927Florian Reischl<p>Very nice Article, Adam!</p>
<p>Thanks</p>
<p>Flo</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#14938Fri, 26 Jun 2009 10:44:29 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:14938LeoPasta<p>Great article, Adam!</p>
<p>Well done.</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#14954Fri, 26 Jun 2009 20:10:59 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:14954jerryhung<p>1st try</p>
<p>CPU time - INSERT inside EXEC CPU time - INSERT EXEC</p>
<p>396.800000 43.800000</p>
<p>2nd &amp; 3rd aren't as bad</p>
<p>87.600000 34.400000</p>
<p>96.800000 31.400000</p>
<p>But something new learned today indeed</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#14989Sun, 28 Jun 2009 21:59:54 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:14989Bruce W Cassidy<p>Sometimes it's the things you take for granted that can turn around and bite you. &nbsp;I've always tended to veer to the INSERT... &nbsp;EXEC form myself, proabably for stylistic reasons. &nbsp;Now I will need to challenge that.</p>
<p>Cheers, good little article! &nbsp;Thought provoking as usual.</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#15034Wed, 01 Jul 2009 13:38:32 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15034ValeriyNY<p>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 &quot;it depends, test, test, test&quot;. </p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#15077Fri, 03 Jul 2009 11:47:16 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15077Nima<p>Hi Adam!</p>
<p>Great post :)</p>
<p>Why do I get different results from writes column in sys.dm_exec_requests?</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#15078Fri, 03 Jul 2009 11:51:27 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15078Nima<p>Sorry I mean: different result each time that I query sys.dm_exec_requests!</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#15079Fri, 03 Jul 2009 13:53:17 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15079Adam Machanic<p>Hi Nima,</p>
<p>I don't understand your question. Can you post a script that shows what you're seeing?</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#15086Sat, 04 Jul 2009 17:32:13 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15086Nima<p>Sorry for my poor explanation!</p>
<p>I mean when I query sys.dm_exec_requests, it returns 24, next time 26, next time 20 and ....</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#15093Sun, 05 Jul 2009 17:21:33 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15093Adam Machanic<p>Hi Nima,</p>
<p>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.</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#15102Mon, 06 Jul 2009 11:25:54 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15102Nima<p>Thanks Adam!</p>
<p>What about 'Reads' column? It is always 0 in my batch:</p>
<p>SELECT * FROM northwind..Orders</p>
<p>SELECT &nbsp; &nbsp;reads</p>
<p>FROM sys.dm_exec_requests</p>
<p>WHERE &nbsp; &nbsp; session_id = @@SPID</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#15140Tue, 07 Jul 2009 15:57:20 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15140Adam Machanic<p>Hi Nima,</p>
<p>The &quot;reads&quot; 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.</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#15155Wed, 08 Jul 2009 07:13:25 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15155Nima<p>Thanks indeed! :-)</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#15368Mon, 20 Jul 2009 16:07:21 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15368Roy Harvey<p>&gt;&gt;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.&lt;&lt;</p>
<p>I suspect the percentage would fall if the table has indexes, though the absolute effect would be as described.</p>
<p>The same idea as INSERT/EXEC is now being written widely with an INSERT/SELECT against a Table Valued Function. &nbsp;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. &nbsp;However a complex TVF has to be staged into a temporary table and probably has the same overhead as you describe for INSERT/EXEC.</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#15406Wed, 22 Jul 2009 10:06:18 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15406Arif<p>Great article, Adam!</p>
<p>i have one question</p>
<p>Does SQL Server cache the plan for dynamic queries ?</p>
<p>if yes then what is the disadvantage of a simple dynamic query</p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#15523Mon, 27 Jul 2009 15:06:32 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15523Adam Machanic<p>Hi Arif,</p>
<p>Yes, plans are cached for dynamic queries. I'm not sure what you mean when you ask about the &quot;disadvantage of a simple dynamic query.&quot; There are advantages and disadvantages, depending on what it is that you're doing. You might want to read Erland Sommarskog's article, &quot;The Curse and Blessings of Dynamic SQL&quot;:</p>
<p><a rel="nofollow" target="_new" href="http://sommarskog.se/dynamic_sql.html">http://sommarskog.se/dynamic_sql.html</a></p>
re: The Hidden Costs of INSERT EXEChttp://sqlblog.com/blogs/adam_machanic/archive/2009/06/25/the-hidden-costs-of-insert-exec.aspx#39143Tue, 18 Oct 2011 08:40:06 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:39143Ranu Mandan<p>Do it solves nested EXEC restriction ?</p>