Solved

Know query time performance in "Query Analyzer"

Posted on 2001-09-04
12
450 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
  • 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Backing up an SQL Transaction Log 11 34
SQL Exceptions 3 37
sql server query from excel 3 57
MS SQL Pivot table help 4 12
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now