Hi,

I'm fairly new to SQL Server, but using version 2005 and I have SQL
Server Management Studio. When I run a query, I can right click to
display an execution plan. However, for certain queries, this graphic
becomes unruly (really, really big). Is there a way I can have a more
text-based summary of what is happening -- indexes used, table scans
performed, and the like?

Thanks, - Dave

Yes, there are several - depending on how much detail you want and what
format you want it in. There are three different options you can turn on.
ShowPlan_Text (which gives you a short version of the plan), ShowPlan_All
(which gives you a much more detailed version of the plan), and ShowPlan_XML
(which gives you the most information - it's in the form of a XML document).

One thing to remember about all of these options is when any one of them is
turned on for a connection and you run a query, SQL doesn't actually run the
query and return results, instead it creates an exeecution plan and returns
that plan.

You can turn any of these options on or off by using a SET statement. Try,
for example, the following code to see the different types of information
you can recieve.

You can also turn on or off ShowPlan_Text in SSMS by choosing Query from the
menu, the Query Options... That will bring up a dialog, in the box on the
left, choose Execution Advanced. Then check or uncheck SET SHOWPLAN_TEXT.

Sample code showing the results of each of the options.

Select COUNT(*) From sys.objects;
go
Set ShowPlan_Text On;
go
Select COUNT(*) From sys.objects;
go
Set ShowPlan_Text Off;
go
Set ShowPlan_All On;
go
Select COUNT(*) From sys.objects;
go
Set ShowPlan_All Off;
go
Set ShowPlan_XML On;
go
Select COUNT(*) From sys.objects;
go
Set ShowPlan_XML Off;
go

Tom

"laredotornado (AT) zipmail (DOT) com" <laredotornado (AT) gmail (DOT) com> wrote

laredotornado (AT) zipmail (DOT) com (laredotornado (AT) gmail (DOT) com) writes:
Well, complex plans are that: complex. I don't think there is an
"quick summary of a plan". Which may be just as well, because it's
not always trivial to find what is the bottleneck in a plan. (Although
in a grpahical plan it's a good idea to look for thick arrows.)

On my web site there is a tool that provids an alternate representation
of a plan, submitted by Ivan Arjentinski:
http://www.sommarskog.se/sqlutil/query_plan_tree.html.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Aug 4, 1:37*pm, "Tom Cooper" <tomcoo... (AT) comcast (DOT) net> wrote:
Hi,

The suggestion of "set showtext on" worked well. A follow up I had
from this is where do I find cumulative statistics about the query?
This is what I'm seeing right now in my tool -- http://screencast.com/t/NGZjY2Q4Yjc
..

Thanks - Dave

ShowPlan_Text gives you only a only a brief overview of the plan. If you
want the statistics, use ShowPlan_All or ShowPlan_XML. I think for what you
want, ShowPlan_All would be best. If you run ShowPlan_All in SSMS, set the
option to return the results in grid format. Otherwise, all the interesting
statistical stuff is very far off to the right and you need to scroll the
result window to the right to see it.

Tom

"laredotornado" <laredotornado (AT) zipmail (DOT) com> wrote

On Aug 4, 1:37 pm, "Tom Cooper" <tomcoo... (AT) comcast (DOT) net> wrote:
Hi,

The suggestion of "set showtext on" worked well. A follow up I had
from this is where do I find cumulative statistics about the query?
This is what I'm seeing right now in my tool --
http://screencast.com/t/NGZjY2Q4Yjc
..

Thanks - Dave