Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

Know query time performance in "Query Analyzer"

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
bibarius
Asked:
bibarius
  • 3
  • 2
  • 2
  • +3
1 Solution
 
ibroCommented:
Hi bibarius,
when you execute the query on the botom (status bar) you can see how much time it runed.
0
 
bibariusAuthor Commented:
Yes but it not exect time.
I need it in Execution Plan , in miliseconds
0
 
ibroCommented:
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
mironCommented:
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
 
george74Commented:
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
 
bibariusAuthor Commented:
george74,

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

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

cheers,
george
0
 
bibariusAuthor Commented:
ok. i`am using MS SQL2000
0
 
mironCommented:
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
 
CleanupPingCommented:
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
 
arbertCommented:

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now