Calculate The Time To Run SP

3 Apr 2006 6:46 AM

Taha

Hi All

There is Any Way To Calculate The Time To Run SP Or Select Statement Before
Run It
For Example

SELECT     *
FROM         stores
WHERE     (state = 'CA')

How long Time Take This Query to Run

Thanks

3 Apr 2006 7:11 AM

Omnibuzz

try using
SET STATISTICS TIME ON

3 Apr 2006 7:31 AM

Tibor Karaszi

No, there are no such facilities in SQL Server. One of the reasons is that the optimize can pick
different executing plans, and any estimates based on one execution plan will be totally off if some
other execution plan is selected. You also have the probability of blocking.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quoteHide quote

"Taha" <taha***@hotmail.com> wrote in message news:efD9epuVGHA.5364@tk2msftngp13.phx.gbl...
> Hi All
>
> There is Any Way To Calculate The Time To Run SP Or Select Statement Before Run It
> For Example
>
> SELECT     *
> FROM         stores
> WHERE     (state = 'CA')
>
> How long Time Take This Query to Run
>
> Thanks
>
>

3 Apr 2006 7:47 AM

Taha

Thank You Fro Reply
But What I Looking For UDF Or SP That Return Elapsed time For Select
Statement I Send To This SP Or UDF Whit out Run it Men Not Need Data Return
Just The Time To Calculate This Statement
Because I Have Large data and I want say to the user how many this query
take time
Thanks

Show quoteHide quote

"Taha" <taha***@hotmail.com> wrote in message
news:efD9epuVGHA.5364@tk2msftngp13.phx.gbl...
> Hi All
>
> There is Any Way To Calculate The Time To Run SP Or Select Statement
> Before Run It
> For Example
>
> SELECT     *
> FROM         stores
> WHERE     (state = 'CA')
>
> How long Time Take This Query to Run
>
> Thanks
>
>

3 Apr 2006 8:02 AM

Enric

As a maximum you could say them an approximation

--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)

Show quoteHide quote

"Taha" wrote:

> Thank You Fro Reply
> But What I Looking For UDF Or SP That Return Elapsed time For Select
> Statement I Send To This SP Or UDF Whit out Run it Men Not Need Data Return
> Just The Time To Calculate This Statement
> Because I Have Large data and I want say to the user how many this query
> take time
> Thanks
>
>
> "Taha" <taha***@hotmail.com> wrote in message
> news:efD9epuVGHA.5364@tk2msftngp13.phx.gbl...
> > Hi All
> >
> > There is Any Way To Calculate The Time To Run SP Or Select Statement
> > Before Run It
> > For Example
> >
> > SELECT     *
> > FROM         stores
> > WHERE     (state = 'CA')
> >
> > How long Time Take This Query to Run
> >
> > Thanks
> >
> >
>
>
>

3 Apr 2006 9:23 AM

Omnibuzz

There is no way to find an accurate time for an SP to run. As Tibor Karaszi
had said, the same SP can run for different periods depending on the server
load, configuration, database size and the mood of the SQL Engine :)

You can only find the time it took for the current execution.

3 Apr 2006 11:41 AM

Taha

Ok How find the time it took for the current execution Please
Only Time return Parameter I need
Thanks
Show quoteHide quote

"Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message
news:8BA9246F-EC38-491C-B1B6-E7B9B3ADE53D@microsoft.com...
> There is no way to find an accurate time for an SP to run. As Tibor
> Karaszi
> had said, the same SP can run for different periods depending on the
> server
> load, configuration, database size and the mood of the SQL Engine :)
>
> You can only find the time it took for the current execution.
>

3 Apr 2006 12:11 PM

Tibor Karaszi

You have to do this yourself. Either in the client application (declare a variable, set it to
current time before execution and after execution check number of ms or s elapsed), or in the stored
procedure with same basic logic and have an output parm of the procedure where you send out the
number of ms.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quoteHide quote

"Taha" <taha***@hotmail.com> wrote in message news:eRATWOxVGHA.440@TK2MSFTNGP10.phx.gbl...
> Ok How find the time it took for the current execution Please
> Only Time return Parameter I need
> Thanks
> "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message
> news:8BA9246F-EC38-491C-B1B6-E7B9B3ADE53D@microsoft.com...
>> There is no way to find an accurate time for an SP to run. As Tibor Karaszi
>> had said, the same SP can run for different periods depending on the server
>> load, configuration, database size and the mood of the SQL Engine :)
>>
>> You can only find the time it took for the current execution.
>>
>
>

3 Apr 2006 12:19 PM

Taha

Thanks
Show quoteHide quote

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
message news:OguNqexVGHA.1536@TK2MSFTNGP15.phx.gbl...
> You have to do this yourself. Either in the client application (declare a
> variable, set it to current time before execution and after execution
> check number of ms or s elapsed), or in the stored procedure with same
> basic logic and have an output parm of the procedure where you send out
> the number of ms.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "Taha" <taha***@hotmail.com> wrote in message
> news:eRATWOxVGHA.440@TK2MSFTNGP10.phx.gbl...
>> Ok How find the time it took for the current execution Please
>> Only Time return Parameter I need
>> Thanks
>> "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message
>> news:8BA9246F-EC38-491C-B1B6-E7B9B3ADE53D@microsoft.com...
>>> There is no way to find an accurate time for an SP to run. As Tibor
>>> Karaszi
>>> had said, the same SP can run for different periods depending on the
>>> server
>>> load, configuration, database size and the mood of the SQL Engine :)
>>>
>>> You can only find the time it took for the current execution.
>>>
>>
>>
>

3 Apr 2006 4:09 PM

amish

You can use profiler also.

Regards
Amish shah