473,836 Members | 1,503 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

explain plan

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
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Jul 20 '05 #1
5 15285
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..syscach eobjects system
table.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2
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
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Jul 20 '05 #3
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 programmaticall y.

What other infotmation do you need?

--
David Portas
SQL Server MVP
--
Jul 20 '05 #4
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 programmaticall y.

What other infotmation do you need?

--
David Portas
SQL Server MVP
--

Jul 20 '05 #5
Bagieta (ba*******@pocz ta.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
2174
by: Greg Stark | last post by:
This query is odd, it seems to be taking over a second according to my log_duration logs and according to psql's \timing numbers. However explain analyze says it's running in about a third of a second. What would cause this? Is it some kind of postgresql.conf configuration failure? I have the same query running fine on a different machine. QUERY PLAN...
14
20405
by: Ina Schmitz | last post by:
Hello all, I don't succeed in displaying the explain plan. I use IBM DB2 Universal Database 8.2. I tried to do the example given in the online help for "Visual Explain". The tables EXPLAIN_STATEMENT and EXPLAIN_INSTANCE exist. With VESAMPL.DDL, I loaded the predefined execution plans. In the next step, I'ld like to display the loaded access plans. So, I right clicked on "Show Explained Statements History" and got the result:
5
3641
by: Jon Lapham | last post by:
I have been using the EXPLAIN ANALYZE command to debug some performance bottlenecks in my database. In doing so, I have found an oddity (to me anyway). The "19ms" total runtime reported below actually takes 25 seconds on my computer (no other CPU intensive processes running). Is this normal for EXPLAIN ANALYZE to report a total runtime so vastly different from wall clock time? During the "explain ANALYZE delete from msgid;" the CPU is...
4
12733
by: marklawford | last post by:
Not having earned my DBA badge from the scouts just yet I'm a little lost with an error I'm getting. We've just upgraded our development database from 7.2 to 8.2 as the first step in upgrading our wider environment. Of course, development doesn't stop so I'm running some explain plans over some new views. The problem is, when the view is accessed as part of the explain plan script, the following error is returned.
0
1913
by: JAW | last post by:
This plan seems like it should perform well. Does anyone see anything. SQL Statement Text: DECLARE MTR - RDG - EST - CSR CURSOR FOR
0
1859
by: jfnorris | last post by:
I'm having some difficulty with Visual Explain. When I explain reletively simple sql, VE will generate an access plan. If I try to explain sql with several joins both outer and inner, or anthing more complicated, the tool will act like it's processing the sql but does not generate the access plan. No errors or anything. Any Ideas?? Thanks in adavnce.
5
8121
by: kabotnet | last post by:
Hi, I'm new in db2, I'm trying to execute EXPLAIN command on some queries but i have error like: And message similar to: Token EXPLAIN is not valid, valid tokens ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER. I've created tables explain_* How can I start to find solution? Is it possible that my db2 doesn't support explain?
7
5790
by: skaushik | last post by:
Hi all, I was working on a SQL query where a history table is joined with a small table to get some information. There is an index on the history table column but the explain plan tells that there is a full table scan on both the tables. The query and the explain plan is below select * from history_table ht, small_table st where ht.columnA > st.columnB and sysdate between st.datetime1 and st.datetime2; Operation Object...
3
18625
by: raviva | last post by:
Hi, I want the explain plan. But when I clicked on the explain plan it says ORA-02404: specified plan table not found. I investigated on net and some books. I was asked to load the utlxplan.sql. I did that but of no use. I also changed the name of the plan table in TOAD, still no use. New thing I heard recently is to run TOADPREP.SQL. But this script is not available on our server($ORACLE_HOME/rdbms/admin). Can someone please suggest me what...
0
927
by: db2admin | last post by:
hello, I have compressed table 442992 rows and when i run explain plan on this table for some SQL using this table, i see table scan and cardinality on top of table node as 18458. I am new to compression and do not understand why cardinality is not the actual number of rows but 18458. here is the piece of explain plan. table ABC.XYZ is the table i am talking about ( right most in plan )
0
9816
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9668
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10840
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10546
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6978
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5647
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4448
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4013
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.