I am trouble shooting a long running query and usually it won't take long to get an estimated execution plan, but when I click the 'Display Estimate execution plan' button on the SQL mgmt tool, it takes over 50 secs.. I ran into a lot of complex long running queries in the past but their execution plans are usually very fast to get < 5 secs. Does anyone know why sometimes it would take a long time to get an estimated execution plan? W
Hmm, don't seem to be a problem but how complex is this query to show the plan? DO you see similar behaviour when accessing the query Exec.plan from another machine?
It is not that complex. I saw others even more complex than this one. I observed the same behavior on a different computer. When I tried to trouble shoot the problem the first time and got the estimated execution plan instantly but after that, it takes forever. We are using SQL 2008 standard on a Windows 2008 enterprise x64 box.
Can you run through the execution plan and see where exactly it is taking time, I mean try to post the text based plan here. Before doing that ensure to switch on STATISTICS ON from query window.
Execute your query and then execute this query on another query analyzer is select st.text,sp.* from sys.sysprocesses sp cross apply sys.dm_exec_sql_text(sp.sql_handle) sp order by sp.cpu desc then checl what is the bottleneck,this query will help you
It depends on the Data also,in the past the data was low and in the present data is high ,that might be an issue of time taking