How to see total time my query took in executing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to see total time my query took in executing

Author

Topic

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-07-05 : 19:04:03

Hello Guys,
Is there any way to write one query to see total time it took in executing the query. Right now i am using select getdate before and after the query i.e;
Select GetDate()
My Query????????
Select GetDate()

Any help will be appreciated. Thanks

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-05 : 20:10:18

you can run sql profiler and watch the query there. remember to select start and end time.

Your approach also works with a simple query, even with a complicated sproc that approach would work. I sometimes write stuff to tables inside a sproc for quick and dirty debugging. inserting start/end time into a summary table would be pretty trivial to implement.

-ec

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-07-05 : 20:15:40

Hello,
Thank you so much for your reply but to be very honest i do not know anything about SQL Profiler so would you mind telling me what steps do i need in Profiler.
I just need to see the total time of one sql query on two different instances so your help will be greatly appreciated.Thanks

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-05 : 20:59:38

here is a good intro

http://www.developer.com/db/article.php/3482216

-ec

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-07-05 : 21:03:51

Thank You! I really appreciate your help and it is a good topic for a novice like me on SQL Profiler.Thanks

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-07-05 : 21:22:04

Hello,
I just read the topic you sent me and i noticed "By default, SQL Profiler is set not to capture the SQL statements that it sends to the server itself"
So how am i going to use Profiler to capture a single SQL Statement.
Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-05 : 21:26:30

That just means that Profiler doesn't capture the statements that Profiler itself generates. It will capture everything else.

quote:
Originally posted by wshtrue

Hello,
I just read the topic you sent me and i noticed "By default, SQL Profiler is set not to capture the SQL statements that it sends to the server itself"
So how am i going to use Profiler to capture a single SQL Statement.
Thanks

CODO ERGO SUM

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-07-05 : 21:32:10

Got It.Thank You, but Are you Guys saying that there is no single statement which can be used in Query Analyzer to capture the total time and i need to use Profiler only to capture the total time of one single SQL Statement.Thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-07-05 : 22:54:37

You might be looking for something like this:


SET STATISTICS TIME ON
SET STATISTICS PROFILE ON

EXEC proc_name @variable

SET STATISTICS TIME OFF
SET STATISTICS PROFILE OFF

The SET STATISTICS TIME ON will show times for each statment. The SET STATISTICS PROFILE ON will let you see what statements it was for.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-07-05 : 22:59:00

Hi,
Definitely i was looking for something like this only.Thank you so much so inplace of stored procedure name i can write my SQL Statement over there Right? Thanks

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-07-05 : 23:06:20

Hello,
I can write a SP also but problem is i don't have permission to write sp, only permission i have is to run SQL Statements.So that is why i had asked that in place of writing SP can i just write that SQL statement in between the code which you suggested me.Soemthing like this:
[code]
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON

Select column1,column2,column3 from tablename where column4='12345'and column5='Hailu'

SET STATISTICS TIME OFF
SET STATISTICS PROFILE OFF

[code]

Thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-07-05 : 23:11:49

Yes. Although, if you are actually just writing a single statement, you probably don't need to have the PROFILE option.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-07-05 : 23:22:53

Thank You so much! Yes, i need to run a single query only but on different instances like on Unit, QA, Prod etc.I just need to compare the performance.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources