February 27, 2007 at 4:50 pm
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
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
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
February 28, 2007 at 3:18 pm
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