question

javier_cepa avatar image
javier_cepa asked

SQL Server 2005 vs SQL Server 2008 plan with Lazy Spool

Hi, I have a query that in sql server 2005 doesn't use lazy spool and it retrieve data inmediately but the same query on sql server 2008 uses in his execution plan the lazy spool operator and it uses intensively tempdb. With the sql server 2008 the performance is to bad. Could anyone explain it? Thanks in advance and sorry for my english...
sql-server-2008sql-server-2005lazy-spool
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

javier_cepa avatar image javier_cepa commented ·
More DATA Same Database, same data. Database restored from sql server 2005 (32 bits) on SQL Server 2008 (64 bits).
0 Likes 0 ·
javier_cepa avatar image javier_cepa commented ·
After doing a full statistics update, rebuild/reorganize index, setting 4199 trace flag i see that the sql server doesn't use tempdb intensively but the query execution time is 3:30min when the time execution on sql 2005 returns data immediately. I attached the query plan graph. During the query i can't see a intensive use of cpu. My proccesors are living in lazy town. :-( Attached sql server 2005/2008 query plans. ![alt text][1] ![alt text][2]![alt text][3]![alt text][4] [1]: /storage/temp/488-sql2008planok.png [2]: /storage/temp/488-sql2008planok.png [3]: /storage/temp/489-sql2005planok.png [4]: /storage/temp/488-sql2008planok.png
0 Likes 0 ·
sql2008planok.png (14.4 KiB)
sql2005planok.png (8.3 KiB)
Grant Fritchey avatar image
Grant Fritchey answered
Every version of SQL Server has a different query optimizer and the optimizer makes different choices. For most queries, with a consistent set of statistics, the choices are usually the same. But for some queries, especially very complex queries, the choices can change. Your choices here are fairly limited. First, make sure your statistics are up to date on the 2008 box. If the statistics have aged because of changes to the data that could explain the changes in plans. Also, if the statistics are just different between the 2008 and 2005 boxes, you could be seeing differences in the plans generated. Use [DBCC SHOW_STATISTICS][1] on each box to compare the statistics between them. One or both are likely to be out of date or possibly sampled differently than the other. Usually this comes from a very complex query that needs to be tuned, even in the 2005 version. Try tuning the query to get better performance. I can't make suggestions beyond this here because I don't know what you're working with. Finally, you can try some of the more obscure stuff to see if it will help. Check the state of Traceflag 4199. If it's set to on in your 2008 instance, you're using all the latest optimization techniques. You could experiment with setting it to off. I have a blog post [about that here][2] for more information. You could also try making the optimizer work harder by setting Traceflag 2301 to on. This will open up more choices to the optimizer. But, this can be a very dangerous thing to do. So be careful. I've got another [blog post on that here][3]. The most likely solutions are statistics or tuning the query. [1]: http://msdn.microsoft.com/en-us/library/ms174384.aspx [2]: http://www.scarydba.com/2011/03/07/execution-plan-stability/ [3]: http://www.scarydba.com/2011/06/20/make-the-optimizer-work-harder/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

javier_cepa avatar image javier_cepa commented ·
I'll test some of your recommendations in my development server. Thank you for you quick answer. I'll post my results here.
1 Like 1 ·
ThomasRushton avatar image
ThomasRushton answered
Given that you're going from SQL2005 to SQL2008, you might need to run a couple of maintenance routines before allowing your database to go live. @Grant Fritchey has already suggested updating statistics. You might also want to run DBCC UPDATEUSAGE just to check your data is good in the new environment. (Although that shouldn't affect the optimizer...)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
Just posting pictures of execution plans doesn't allow for enough information to understand why something is the way it is. I can make a few guesses at other things to look at. One plan is parallel and the other is not. What is the cost threshold for parallelism on each server? Also, what is the max degree of parallelism on each server? If these are different that could explain why you're seeing differences in the plans. Other than that, I can't help based on what you've provided. At minimum we would need to see the execution plan itself. You can save them as a .sqlplan file and then upload that to here. But, it will show your query too, but that's also needed to understand what's going on. Be very certain that traceflag 4199 is helpful to you. It's a server-wide setting. It could have far reaching impact.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
You say that the SQL2008 database was created from a Restore of the SQL2005 database; however, the index being used by the SQL2008 query isn't the same name as that being used by the SQL2005 query. So, yes. @Grant is right - we need to see more than you've provided. Please upload the .sqlplan files and the full schemas for the Action2 & Proxy2 tables (including indexes & keys) from both servers.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

javier_cepa avatar image
javier_cepa answered
Trace 4199 doesn't change anything... in my enviroment. I detected a problem with sql server with "IS NOT NULL OR" and a conjunction of LEFT OUTER JOIN. Changing "IS NOT NULL" to "<> ''" and LEFT OUTER JOIN with CROSS JOIN seems to correct the query plan on sql 2008... ¿? In any case, thank you very much for all your effort and pacience
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.