Solved

Know query time performance in "Query Analyzer"

Posted on 2001-09-04
12
453 Views
Last Modified: 2012-06-27
Hello!

How can i see query time performance in Query Analyzer(in miliseconds).
In Execution Plan may be it's a  "Cost" or "Subtreecost"
or somthing else.

Somebody can explane me?

Thank you!
0
Comment
Question by:bibarius
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 3

Expert Comment

by:ibro
ID: 6452544
Hi bibarius,
when you execute the query on the botom (status bar) you can see how much time it runed.
0
 

Author Comment

by:bibarius
ID: 6452566
Yes but it not exect time.
I need it in Execution Plan , in miliseconds
0
 
LVL 3

Expert Comment

by:ibro
ID: 6452820
i'm afraid you can not get this information. Execution plan gives you the estimate cost of each step in percentage of the exection plan. This is just a planed cost, but not the actual one. It may take more or less of the total execution plan. The goal of the execition plan is to show how the query will be splitted into pieces and what resources will be needed for each step (indexes, etc.).
If you use percentage together with total execution plan you may come to some rough time of the exection plan for the step.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 9

Accepted Solution

by:
miron earned 60 total points
ID: 6452944
did you try

SET STATISTICS TIME { ON | OFF }

to see the plan, no time use

SET SHOWPLAN_TEXT { ON | OFF }

but in case of former once it is "ON"
the query will not execute intill you set it "OFF"
instead SQL Server will show estimated plan.

To see the real plan and exceution time in milliseconds
use
profiler in SQL Server 2000
and
sqltrace in SQL Server 7.0
I suggest using graphycal interface. It is very intuitive.
Select RPC Completed and Batch completed from events tab,
and make sure duration and statement execution plan
coumns are choosen under columns tab.

0
 
LVL 1

Expert Comment

by:george74
ID: 6452994
bibarius,

to see the query time in milliseconds, in Query analyzer go to Query/Current Connection Options, General Tab, check "Show stats time". This will result that in the output area you will see such messages:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

Each block corresponds a step in the execution plan.

Cheers.
0
 

Author Comment

by:bibarius
ID: 6453310
george74,

In Query/Current Connection Options i have`t "Show stats time", i have "Connection Properties" tab and there not "Show stats time"
0
 
LVL 1

Expert Comment

by:george74
ID: 6453338
bibarius,

sorry than, but what version of mssql are you using? my previous comment should apply on mssql7.

cheers,
george
0
 

Author Comment

by:bibarius
ID: 6453466
ok. i`am using MS SQL2000
0
 
LVL 9

Expert Comment

by:miron
ID: 6454199
well even simplier,
( Ctlr + Shift + T )
run query
and check the trace.
make sure query to output to tabular format. ( Ctrl + D ) before running a query.

...personally, I would prefer the text based command, no need to use mouse, all those clicks...
did you try to execute

SET STATISTICS TIME ON

and then run some queries?
0
 

Expert Comment

by:CleanupPing
ID: 9281674
bibarius:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 34

Expert Comment

by:arbert
ID: 9544792

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept miron's answer

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Arbert
EE Cleanup Volunteer
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question