sql server execution plan table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

sql server execution plan table

Author

Topic

swarup
Starting Member

3 Posts

Posted - 2004-05-20 : 00:31:13

hai all,
i am a newbee to sql server. i created "plan_table" in oracle by executing "utlxplan.sql" for my application. now, i shifted to sql server. can anyone please inform what is the name of the table in sql server i need to access (similar to plan_table in oracle)

thanks in advance,
motani

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-05-20 : 00:39:07

Query analyzer can do it for you, no need to create a plan table like in oracle. SQL profiler can also display the execution plan for you.

check http://www.sql-server-performance.com/query_execution_plan_analysis.asp for a good overview and details of how to use SQL profiler to display execution plans.

Also, check books online (BOL) for more information.

btw, BOL, query analyzer and SQL Profiler should all be installed on your workstation if you installed the SQL Server client tools.

-ec

swarup
Starting Member

3 Posts

Posted - 2004-05-20 : 13:44:21

thanks for the response.

the link u specified describes more of how to view the plans,
how they are executed, etc, rather than informing about a specific
table that stores about all these details.

let me just give in brief what my application does:

the application stores query execution plans in a database and gives
the plans on request(when a new query is fired), to reduce the query
optimization cost of the optimizer( as optimizer generates these
plans), inturn imroving the performance of the RDBMS. When it is
implemented on Oracle, we used to access the "plan_table", and get
the plans stored in a separate database (rather than repeating the
whole process of optimization) so, we need to access the
"plan_table"(in Oracle).
can u please inform the 'specific' table in SQL Server, which
i need to access.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-20 : 13:50:32

For SQL Server, use SET SHOWPLAN_ALL ON to view the execution plan. Here's an example:

set showplan_all on
go
select name from sysobjects
where name = 'dtproperties'
go
set showplan_all off
go
set showplan_all off
go

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-20 : 14:53:33

They call it a PLAN_TABLE in Oracle? That's DB2 to me...

Does it also use EXPLAIN?

EXPLAIN would put the results that look like SHOWPLAN (Although not graphically) in to that table with a PLAN_ID, then you select from that table to see what the optimizer will do.

Is that what you're talking about?

All the databases (DB2, SQL Server, Oracle) Manage their own Optimizers...it's not like you have control over them....except for hints...which I think I used once....

Brett

8-)

EDIT: And that was back in 6.5

swarup
Starting Member

3 Posts

Posted - 2004-05-21 : 01:30:24

you are correct, we use "explain".
Oracle provides the SQL Statement EXPLAIN PLAN to explore an execution plan. If we do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the INTO <table> part of the EXPLAIN PLAN command. If you omit the INTO <table> clause, Oracle fills a default table: PLAN_TABLE.

it is true, we use "plan hints" to influence the optimizer.

can i store the parameters (by executing SHOWPLAN) in a table in
SQL Server(like PLAN_TABLE and use plan hints to influence the
optimizer)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources