Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Know query time performance in "Query Analyzer"

Posted on 2001-09-04
12
Medium Priority
?
464 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Accepted Solution

by:
miron earned 240 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

636 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