SQL Server Performance

Execution Plan takes > 50 secs to show

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by WingSzeto, Apr 7, 2011.

  1. WingSzeto Member

    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
  2. satya Moderator

    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?
  3. WingSzeto Member

    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.
  4. satya Moderator

    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.
  5. Jahanzaib Member

    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
  6. satya Moderator

    Do you see lot of WAIT stats from Activity Monitor?
  7. Jahanzaib Member

    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

Share This Page