explain plan

[Bagieta]

Hello.
Does someone of you know if is there a 'explain plan' like function in SQL
Server (similar to explain plan in Oracle or DB2)
If so then how it works, where stores data and how it can be retrieved?

Best regards
Bagieta
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
dbDeveloper - Multiple databases editor

http://prominentus.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

[David Portas]

Query Analyzer will show you the execution plan for SQL statements (choose
Query / Display Estimated Execution Plan) or you can use the SET
SHOWPLAN_TEXT ON statement to return the text of the plan when you execute a
query.

The actual cached plans aren't exposed to be queried directly but you can
see what objects have been cached in the master..syscacheobjects system
table.

--
David Portas
SQL Server MVP
--

[Bagieta]

Thank you.
Unfortunately I need those data stored somewhere and something more than
object names.
I'm developing a software that can manages SQL Server so I must do it in
code using sql statements.
Do you know is the Microsoft going to put that functionality in future
releases?
Regards
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
dbDeveloper - Multiple databases editor

http://prominentus.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

[David Portas]

SET SHOWPLAN_ALL presents the same info in a form that may make it easier
for you to extract the various elements of the plan programmatically.

What other infotmation do you need?

--
David Portas
SQL Server MVP
--

[Bagieta]

Yes I know but I DONT want to use any of microsoft tools.
Those tools parse results or something and presents them to user.
I want to make the same thing in my application. But no matter what
connection controls I use I cannot use SHOWPLAN function.
That's why I cannot get the necessary information.

SET SHOWPLAN_ALL presents the same info in a form that may make it easier
for you to extract the various elements of the plan programmatically.

What other infotmation do you need?

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Bagieta (ba*******@poczta.onet.pl) writes:

Yes I know but I DONT want to use any of microsoft tools.
Those tools parse results or something and presents them to user.
I want to make the same thing in my application. But no matter what
connection controls I use I cannot use SHOWPLAN function.
That's why I cannot get the necessary information.

In such case, I guess you have to cancel the project.

Seroiusly, SET SHOWPLAN_ALL or SET STATISTICS PROFILE are the tools
you can use to get data back about execution plans to SQL Server, and
that is the data you can work on.

One caveate with SHOWPLAN_ALL is that it comes with an implicit
SET NOEXEC ON. If you also want the statement to be executed, you
should use SET STATISTICS PROFILE.

Or could you be more specific why SHOWPLAN/STATISTICS PROFILE is not
good enough for you? There are not much alternatives.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp