Table Spool should alert you to a potential problem


	Table Spool should alert you to a potential problem

Osolage

3-26-2007 10:54 AM

2127 views

tags:

sqlserver, sql, dba, database administration, table spool, worktables, query tuning

Osolage says:

I was looking at a query plan today and saw a Table Spool icon. I had never seen one of those before. I thought that it might not be so great.

I found this little bit of text on spooling data to work tables and thought it was interesting.

Add a Comment

Login to Comment. Not a member yet? Sign up

Today's Top Clips

visit the Top Clips page

View the Top Clips from March 26, 2007

Embed This Clip In Your Site...

<div style="margin: 12px 0px; font-family: arial; color: #333333; background: #ffffff; border: solid 4px #e5e5e5; width: 100%; clear: left;"><div class="CM_CTB_Content_Wrap" style="margin: 0px; padding: 0px;background-color: #ffffff;"><div style="border-bottom: solid 1px #dcdcdc; white-space: nowrap; margin-bottom: 8px; background-color: #eeeeee ;background-image: url(http://clipmarks.com/images/source-bg.gif); background-repeat: repeat-x; height: 24px; line-height: 24px; vertical-align: middle; padding-bottom: 4px; color: #666666; font-size: 10px;" ><a href="http://clipmarks.com/clip-to-blog/" title="see clips that are hot right now"><img src="http://content.clipmarks.com/blog_embed/9f7f7518-0ea2-4f66-8f72-d5c848f91193/AEFC4B0F-7ADD-4FBD-9A38-940FA83DBC1F/" alt="" width="19" height="19" border="0" style="vertical-align: middle; margin: 0px 4px; display: inline; border: none; float:none;" /></a>clipped from <a title="http://www.sql-server-performance.com/query_execution_plan_analysis.asp" href="http://www.sql-server-performance.com/query_execution_plan_analysis.asp" style="font-size: 11px;">www.sql-server-performance.com</a></div><blockquote style="text-align: left; padding: 0px 8px; margin: 4px 0px 8px 0px; background: transparent; border: none;" cite="http://www.sql-server-performance.com/query_execution_plan_analysis.asp"><P nd="56"><STRONG>Sometimes, the Query Optimizer will need to create a temporary worktable in the tempdb database</STRONG>. If this is the case, it will be indicated in the graphical query execution plan with an icon labeled like this: Index Spool, Row Count Spool, or Table Spool.</P></blockquote><div style="height: 2px; font-size: 2px; background: #dcdcdc; border-bottom: solid 1px #f5f5f5; margin: 2px 4px;"></div><blockquote style="text-align: left; padding: 0px 8px; margin: 4px 0px 8px 0px; background: transparent; border: none;" cite="http://www.sql-server-performance.com/query_execution_plan_analysis.asp"><P nd="57">Anytime a worktable is used, performance is generally hurt because of the extra I/O generated when maintaining a worktable. Ideally, there should be no worktables. Unfortunately, they cannot always be avoided. And sometimes their use can actually boost performance because using a worktable is more efficient that the alternatives.</P></blockquote><div style="height: 2px; font-size: 2px; background: #dcdcdc; border-bottom: solid 1px #f5f5f5; margin: 2px 4px;"></div><blockquote style="text-align: left; padding: 0px 8px; margin: 4px 0px 8px 0px; background: transparent; border: none;" cite="http://www.sql-server-performance.com/query_execution_plan_analysis.asp"><P nd="58">In any event, the use of a worktable in a graphical query execution plan should raise an alert with you. Take a careful look at such a query and see if there is anyway it can be rewritten to avoid the worktable. There may not be. But if there is, you are one step closer to boosting the performance of the query. [7.0, 2000] <EM>Updated 7-10-2006</EM></P></blockquote></div><div style="margin: 0px 6px 6px 4px;"><table style="font-size: 11px;border-spacing: 0px;padding: 0px;" cellpadding="0" cellspacing="0" width="100%"><tr><td style="background:transparent;border-width:0px;padding:0px;">&nbsp;</td><td align="right" style="background:transparent;border-width:0px;padding:0px;width:107px" width="107"><a href="http://clipmarks.com/share/AEFC4B0F-7ADD-4FBD-9A38-940FA83DBC1F/blog/" title="blog or email this clip"><img src="http://content8.clipmarks.com/images/c2b-foot.png" border="0" alt="blog it" width="107" height="17" style="border-width:0px;padding:0px;margin:0px;" /></a></td></tr></table></div></div>

New from the makers of Clipmarks: Amplify.com - Don't just share the news...Amplify it!

Clipmarks: Home; New Clips; Top Clips; Dashboard

Popular Topics: News; Life; Science; Technology; Entertainment

Get Started: Sign Up; Install Clipping Tool; How Clipping Works; Clip-to-Blog™; ClipSearch

Tools and Resources: FAQ; ClipWeek; Top Clippers; Top Tags; Site Map

About Clipmarks: About Us; Contact; Copyright; Privacy; EULA

Search Options

Today's Top Clips