Execution Plan takes > 50 secs to show | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Execution Plan takes > 50 secs to show

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

Do you see lot of WAIT stats from Activity Monitor?

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
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |