July 16, 2008 at 2:47 pm
Based on the information below, is this Table/Lazy spool fine? The cost is 0% but 'Rowcount' and 'Number of executes' are high?
Physical operation: Table Spool
Logical Operation: Lazy Spool
Rowcount: 255,672
I/O Cost: 0.01
CPU Cost: 0.000001
Number of executes: 42612
Cost: 0%
Thank you.
July 17, 2008 at 5:17 am
Post your query first. I think you are writting a query like below:
select media_title from tbl_media m, tbl_Schedule s where media_category_id =34
AND '7/14/2007' BETWEEN s.start_date AND s.end_date
see if you write with a proper JOINing you may see No Lazy spool found there:
select media_title from dbo.tbl_media m inner join dbo.tbl_Schedule s on m.media_ID = s.Media_ID
where m.media_category_id =34
AND '7/14/2007' BETWEEN s.start_date AND s.end_date
Shamshad Ali.
July 17, 2008 at 5:22 am
Table spool/lazy spool is a temp storage of a interim resultset so that the query processor doesn't have to regenerate the resultset. Often found with sorts, aggregations in subquery, linked servers/xml or other expensive operations.
Providing the CPU usage is low (which in this case it is) and you don't see a hight number of reads for a work table (Run the query with SET STATISTICS IO ON) it shouldn't be a problem.
We'll need the query to say anything definitive about it, but usually they aren't a problem
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2008 at 1:03 pm
Thanks for replying to my post.
The Worktable reads are high.
Table 'Worktable'. Scan count 42611, logical reads 42619, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 42611, logical reads 42619, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 42611, logical reads 42617, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 42611, logical reads 1927313, physical reads 0, read-ahead reads 0.
Table '#temp1'. Scan count 0, logical reads 42613, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 47612, physical reads 0, read-ahead reads 0.
July 17, 2008 at 11:54 pm
It might be a concern, it might not. Without the query, that's about all that can be said.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply