473,242 Members | 1,726 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 software developers and data experts.

"Show Execution Plan" and misleading query costs...

Hi All,

I'm a relative newbie to SQL Server, so please forgive me if this is a
daft question...

When I set "Show Execution Plan" on in Query Analyzer, and execute a
(fairly complex) sproc, I note that a particular query is reported as
having a query cost of "71% relative to the batch" - however, this is
nowhere near the slowest executing query in the batch - other queries
which take over twice as long are reported as having costs in the
order of a few percent each.

Am I misreading the execution plan? Note that I'm looking at the
graphical plan, and am not reading the 'estimated' plan - I'm using
the one generated from executing the sproc. My expectation was that
this would be based on the execution times of the queries within the
sproc, however, this does not appear to be the case. (Note - I
determined execution times from PRINT statements, using GETDATE() to
determine the current time, down to milliseconds).

Any feedback would be of great assistance... I may well have to
change the way I approach optimizing queries based on these findings.

Thanks,

LemonSmasher.
Jul 20 '05 #1
3 5342
This is just me, others may differ but I never use the graphical outupt.
I run set statistics profile on and set statistics io on and examine
that output for high physical then logical IO's. Examine the actual
query plan for row operations, executions, and eliminate table scans
where they contribute to high row operations and reduce io with
appropriate indexes, subqueries or alternate joins.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #2
[posted and mailed, please reply in news]

Will Atkinson (Le**********@hotmail.com) writes:
When I set "Show Execution Plan" on in Query Analyzer, and execute a
(fairly complex) sproc, I note that a particular query is reported as
having a query cost of "71% relative to the batch" - however, this is
nowhere near the slowest executing query in the batch - other queries
which take over twice as long are reported as having costs in the
order of a few percent each.


While you are looking at the actual plan, all numbers you see are
estimates from the optimizer. To present the graphical plan, QA sends
the command SET STATISTICS PROFILE ON and this output does include any
statistics about actual execution time.

If you want to see execution times per statement, you can use
SET STATISTICS TIME ON, or run a Profiler trace and include the
events SP:StmtCompleted and SQL:StmtCompleted and the Duration
column.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
In article <Xn**********************@127.0.0.1>, es****@sommarskog.se
says...
[posted and mailed, please reply in news]

Will Atkinson (Le**********@hotmail.com) writes:
When I set "Show Execution Plan" on in Query Analyzer, and execute a
(fairly complex) sproc, I note that a particular query is reported as
having a query cost of "71% relative to the batch" - however, this is
nowhere near the slowest executing query in the batch - other queries
which take over twice as long are reported as having costs in the
order of a few percent each.


While you are looking at the actual plan, all numbers you see are
estimates from the optimizer. To present the graphical plan, QA sends
the command SET STATISTICS PROFILE ON and this output does include any
statistics about actual execution time.

If you want to see execution times per statement, you can use
SET STATISTICS TIME ON, or run a Profiler trace and include the
events SP:StmtCompleted and SQL:StmtCompleted and the Duration
column.


You also need to clear the cache if you want a true test. When you run a
query and then again run it, you may not see any real benefits if the
result/plan is cached from the previous run.

--
--
sp*********@rrohio.com
(Remove 999 to reply to me)
Jul 20 '05 #4

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

Similar topics

2
by: Vic Spainhower | last post by:
Hello, I am new to php and MySQL and I'm attempting to run a select query on a MySQL database which is working fine except prior to displaying the table with the results from the select query it...
0
by: eli | last post by:
Hi again, I know that "Show Column" query shows the name of all variables with their characteristics. I want to know if there is a command that ONLY shows the name of the varibales of a...
6
by: John | last post by:
Hi I have this code as below that first runs a report and then an update query to set a flag so these records are not included in the report next time. Private Sub Command2_Click()...
2
by: xpcer | last post by:
hi friend, can you tell me, why i can't execute the "show databases" query using vb?, i use a myProv
2
by: -Lost | last post by:
I have been watching code execution in various situations and I have noticed something. On the first test my example gave me obvious results. One method was far faster than the other. However,...
1
by: hgarcia | last post by:
Hi everybody, I need to modify the "execution path" in orther to instal a new library. How I can do this, actually I don't even know what an execution path is? Thanks, H
1
by: 4.spam | last post by:
Hello, all. DB2 v8.2.7, DB2 v9.1.3, Windows XP. When I choose this menu item (this menu appears when I right click a database) in db2cc nothing is happening on both versions of db2cc. When I...
1
by: RN1 | last post by:
Sub Page_Load(........) If Not Page.IsPostBack Then Call LoadData() End If End Sub Sub LoadData() Dim dSet As DataSet Dim sqlConn As SqlConnection Dim sqlDapter As SqlDataAdapter
4
by: WolverYanks | last post by:
SELECT Dates., Sum(1) AS FROM Dates LEFT JOIN ON Dates. = . WHERE (((.)<)) GROUP BY Dates.; I'm doing a sports database, for entertainment and to learn Access, so hopefully this is an easy...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.