Solved

Know query time performance in "Query Analyzer"

Posted on 2001-09-04
12
452 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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 …
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

827 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