Post reply

Text version of Query Plan

  • February 27, 2007 at 4:50 pm

    #116458

    All

    I have a huge query that has an equally large query plan. 

    I like the graphical presentation to see how a query fits together, but it is taking me many minutes to seach the graphics for that one table scan that accounts for 75% of my processing.

    Is there a way to run the query, that looks in the system tables to get the information that SQL Server is using to to build my graphical query plan.    Then I can quickly locate the table scan.

    I have already looked at the following....but if I have a query that runs fast, the dynamic tables loose the data before I can hit enter. 

    sys.dm_exec_requests

    sys.dm_exec_query_stats

    sys.dm_exec_sql_text

    sys.dm_exec_requests

    sysprocesses

     

    Thanks in advance

    Eric

     

  • February 27, 2007 at 9:59 pm

    #692223

    SQL Server 2005 Books Online 
    Displaying Execution Plans by Using SQL Server Profiler Event Classes 

    http://msdn2.microsoft.com/en-us/library/ms190233.aspx

    Read the following blog "Obtaining Statement-Level Query Plans "

    http://blogs.msdn.com/sqlcat/Default.aspx?p=2

     

    MohammedU
    Microsoft SQL Server MVP

  • February 28, 2007 at 6:52 am

    #692286

    SET SHOWPLAN_ALL ON

    GO

    <your query here>

    You won't get results, just the plan in text format. Then you can use any search function (including the one in management studio/query analyser) to find the scan. Make sure you have Results to Text selected, not Results to Grid.

    You can also save the graphical plan as a .sqlplan file. It's a form of xml and can be opened and searched in your favorite text editor. I will admit, it's not easy to read.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 28, 2007 at 3:18 pm

    #692449

    The set showplan_all on command was exactly what I was looking for.

    I completely forgot about that one....

    Thanks again

    Eric Peterson

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply