.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Table Spool takes up too much processing in execution plan

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server
 

I have a query that is taking over 2 hours to run. When I look at the execution plan for a major segment of it, 75% of the cost is on three instances of "Table Spool" (25% each). What is "Table Spool" and how do you address it? The execution plan lists this as

 Table Spool
(Eager Spool)

There are actually 2 queries involved. One loops through a series of dates and calls the second one. All joining fields have indexes in their corresponding tables:

Calling Query:

    -- Process all quarters
        declare @lastQtrStart datetime
        declare @currentQtrStart datetime
        declare @currentQtrEnd datetime

        set @currentQtrStart = '1/1/2008'
        set @lastQtrStart = '1/1/2020'

        while @currentQtrStart < @LastQtrStart
            begin
                set @currentQtrEnd = dateadd(d , -1 , dateadd(MM , 3 , @currentQtrStart))
              


View Complete Post


More Related Resource Links

Improper execution plan with scalar function

  
Run the following sql in an empty db(in my case, sql 2008): create table t (id int) go declare @i int set @i = 1 while @i <= 1000 begin insert t values (@i) set @i = @i + 1 end go create function f(@i int) returns int as begin return (select count(*) - 10000+@i from t) end go Then run the following sql a, it's slow, used 6 seconds, yet sql b is very fast. Their execution plans are totally different. Can sql a run as fast as sql b? If not, why? -- sql a declare @i int set @i = 500 select * from t where id = (select dbo.f(@i)) -- sql b select * from t where id = (select dbo.f(500)) Thanks!

CPU killer when showing execution plan when having a missing index

  
Hi you all! Do you have problems with flickering and CPU getting high when you show the execution plan for a SQL Statement AND there is a missing index suggestion? See related connect id: https://connect.microsoft.com/SQLServer/feedback/details/360457/hovering-mouse-over-execution-plans-query-line-is-cpu-killer I really hope someone from Microsoft can take a look at this a give us a status update, over TWO years since I reported it and still an issue. Have you experienced the same? Please vote the bug up to "raise" the flag.Best regards Arild Røksund, Norwegian developer (SQL & .net), Omega AS (please Vote as helpful if helpful)

Execution Plan in Activity Monitor (Recent Expensive queries)

  
If we see the execution plan of a query in Activity Monitor > Recent Expensive Queries is it Estimated Execution Plan or Actual Execution Plan ?

Is the saved execution plan for a stored procedure really relevant?

  
The execution plan of a stored proc is saved for future use in the compilation process - but sometime in the future it could be irrelevant... for example: my assumption is that the heuristics of the optimizer are for example available indices and table size. So: 1. If an index is dropped then the execution plan can't be done... BUT when I checked this case, I created a simple stored proc with a simple select and a where clause, and ran it - the execution plan (which i assume is the saved one) contained an index seek. Now I dropped the index and got a table scan instead - so what happened? the saved execution plan (which was there from the time I run "create proc") was NOT performed unless the actual execution plan checks for existence of index (obviously 2. If an index is added then we'd have a better execution plan than the compiled one. BUT - when checking this.... before adding the index I had a table scan... after adding the index i still expected a table scan (because of the saved execution plan)... BUT i got an index scan - THIS LEADS ME TO THE CONCLUSION THAT after adding the index the proc was automatically recompiled by the sql server (I never read such a thing anywhere - so either i misundertand the entire theory or my conclusion is right). 3. Table size... first - is table size really  the optimizer heuristics? meaning for exa

Is the saved execution plan for a stored procedure really relevant?

  
The execution plan of a stored proc is saved for future use in the compilation process - but sometime in the future it could be irrelevant... for example: my assumption is that the heuristics of the optimizer are for example available indices and table size. So: 1. If an index is dropped then the execution plan can't be done... BUT when I checked this case, I created a simple stored proc with a simple select and a where clause, and ran it - the execution plan (which i assume is the saved one) contained an index seek. Now I dropped the index and got a table scan instead - so what happened? the saved execution plan (which was there from the time I run "create proc") was NOT performed unless the actual execution plan checks for existence of index (obviously 2. If an index is added then we'd have a better execution plan than the compiled one. BUT - when checking this.... before adding the index I had a table scan... after adding the index i still expected a table scan (because of the saved execution plan)... BUT i got an index scan - THIS LEADS ME TO THE CONCLUSION THAT after adding the index the proc was automatically recompiled by the sql server (I never read such a thing anywhere - so either i misundertand the entire theory or my conclusion is right). 3. Table size... first - is table size really  the optimizer heuristics? meaning for exa

Query execution plan problem

  
Hi, I have encountered a problem with a query execution plan on MS SQL Server 2008. It is a simple query on a single table. The table has a primary key RNUM (number(10)) with a clustered index. The query is executed via ODBC using fast forward cursors and is constructed like this: select [field_list_here] from table_name where RNUM>@P and TYPE='A' order by RNUM. The field TYPE has 2 possible values and is not indexed. The table has about 2 000 000 rows of static data (only reads, no inserts and updates). For some time my query executes using the efficient query execution plan. Below a copy from Management Studio from an ad-hoc query: SELECT (0%) <- Clustered Index Seek  (100%) But after 2 days of executing other type of queries SQL Server starts to use other execution plan (live copy):                             Fetch query (0%) <- Clustered Index Seek [CWT_PrimaryKey] (0%)                            |                           \/ Fast forward (0%) <- Population quer

Execution plan help

  
When doing a query execution plan, I see the following: Clustered Index Scan Predicate [FinanceDW].[dbo].[fact_temp] [segment_key =2] which is Cost at 55% This is what im doing: where segment_key = 2 Any ideas what this means? an how I can improve it?

SQl 2008 execution plan problem

  
Hi We are in the middle of upgrading a PeopleSoft database from SQL 2000 to SQL 2008. After the upgrade a select from a view takes 8 min compared to the 1 second it took in SQL 2000. I can see that the execution plan is the problem. What is causing 2008 to be worse than 2000. Is there a patch to fix this 2008 problem or should we stick with 2000 for now. Fixing the system with new indexes and trouble shooting the query is out off the quetion as the people lost there faith in SQL as a system and is saying if this happens now it will keep on happening with other dev and to much time is spent on trouble shooting the SQL 2008 shortcomings.

SQl 2008 execution plan problem

  
Hi We are in the middle of upgrading a PeopleSoft database from SQL 2000 to SQL 2008. After the upgrade a select from a view takes 8 min compared to the 1 second it took in SQL 2000. I can see that the execution plan is the problem. What is causing 2008 to be worse than 2000. Is there a patch to fix this 2008 problem or should we stick with 2000 for now. Fixing the system with new indexes and trouble shooting the query is out off the quetion as the people lost there faith in SQL as a system and is saying if this happens now it will keep on happening with other dev and to much time is spent on trouble shooting the SQL 2008 shortcomings.

Execution Log table shows only one user

  
Hi,     Im trying to get a list of users who have accessed and run reports. Im using executionlog table to get this information but strangely all the reports are being run by a single username which same as the windows service login for reporting services. Is user logging when report is accessed and executed from Report manager different from running it through report server? Does this have any impact on users being entered in execution log table? One basic question: Does the service running SSRS need access rights to datasource for running a report? Any pointers to how the execution flows from a users desktop to the logging to that user in execution log table would be very useful..   Thanks Rakesh    

Save Execution plan option disabled

  
You cannot vote on your own post
0

Hi,

 

I am using Management studio. I want to save the Execution plan on a stored procedure, but the Save Execution plan option is disabled. Even on the File menu the Save Execution plan option is disabled. Is this some security issue or Is it because of Express addition. Is there any way to get around this problem.

Thanks in advance.

-Jaya


Save Execution plan option disabled

  

Hi,

 

I am using Management studio. I want to save the Execution plan on a stored procedure, but the Save Execution plan option is disabled. Even on the File menu the Save Execution plan option is disabled. Is this some security issue or Is it because of Express addition. Is there any way to get around this problem.

Thanks in advance.

-Jaya

Save Execution plan option disabled

  

Hi,

 

I am using Management studio. I want to save the Execution plan on a stored procedure, but the Save Execution plan option is disabled. Even on the File menu the Save Execution plan option is disabled. Is this some security issue or Is it because of Express addition. Is there any way to get around this problem.

Thanks in advance.

-Jaya


How to estimate "Minimum query plan threshold for considering queries for parallel execution(cost es

  

Hi,

We have 4 QUAD core processors and we have set max degree of parellelism to 4(as suggested by Microsoft). As of now we are using the default value for "Minimum query plan threshold for considering queries for parallel execution(cost estimate)".

Please suggest how to estimate the best value for for "Minimum query plan threshold for considering queries for parallel execution(cost estimate)"?

Thanks in advance.

 

 


How to Open Xml at Graphical Execution Plan?

  

This was working until a few days ago. I dont know if I change some config.

Exectuing:

SELECT *
FROM sys.dm_exec_query_stats as EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle)
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle)
GO

Return a Xml "<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver......"

When I click at cell, I want to see Graphical Execution Plan, not the XML.


http://blog.fujiy.net/ - MCPD em .NET 2.0 MCTS em .NET 4

How to Open Xml at Graphical Execution Plan?

  

This was working until a few days ago. I dont know if I change some config.

Exectuing:

SELECT *
FROM sys.dm_exec_query_stats as EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle)
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle)
GO

Return a Xml "<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver......"

When I click at cell, I want to see Graphical Execution Plan, not the XML.


http://blog.fujiy.net/ - MCPD em .NET 2.0 MCTS em .NET 4

Why a Clustered table takes more space than a Heap Table

  

I'm using SQL Server 2005 buld 3080.

I have a table with no clustered index that return the followings results on sp_spaceused:

Rows: 47841318      Reserved :18015392 KB      Data : 7801184 KB      Index : 2489536 KB      Unused :7724672 KB

Tha data takes about 8 gb and there is a space lost of about 7.5 gb on the 17 gb reserved. I understans that because this is a HEAP table it cannot been defrag when running a rebuild index on it. Then I created a clustered index on that table to release unused space. Here is the new result of the sp_spaceused:

Rows: 47841318      Reserved: 15865744 KB      Data : 13140608 KB      Index: 2725000 KB      Unused: 136 KB

Now we can see that the unused space as been released with the defragmentation.

I don't understand why Data space has almost double it's value. The index size has grown up a little bit due to my new clustered index (Probably to store the root levels of the index). I expected that data remain approximatly the same.

If somebody could explain this I would really appreciate to understand.


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend