query progress indicator

having sql2000 on win2000
when running long lasting queries [30 minutes f.e.], and running them async,
is there some method to periodicaly check the percentage of completness?
i know there is a status indicating when the query is done, but is it
possible to know intermediate status?
since execution plan has a good knowledge about what have to be done, i
think it is not technicaly impossible mission to have some estimation of
remaining time to run
also, when running sp, supposing sp is composed of complex [many]
subqueries, is there some clever method sp can indicate to calling process
which part of code is currently being executed [some kind of semaphores
between subqueries f.e.]
any comments?
thnx.
On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + getdate()
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
getdate()
Exec my_proc2 etc...
|||On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + convert(varchar(20),getdate(),
109)
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
convert(varchar(20),getdate(),109)
Exec my_proc2
etc...
|||> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
You can use RAISERROR...WITH NOWAIT to send informational progress messages.
RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
SELECT or PRINT will wailt until the out buffer is full.
RAISERROR('Start message', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('End message', 0, 1) WITH NOWAIT
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
> async, is there some method to periodicaly check the percentage of
> completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
>
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
> You can use RAISERROR...WITH NOWAIT to send informational progress
> messages. RAISERROR...WITH NOWAIT will flush the output buffer
> immediately, where SELECT or PRINT will wailt until the out buffer is
> full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
thnx, looks good.
so, inside sp, on convenient points, to place raiseerror construct, and
later, catch error event inside client app, and filter out custom errors.
nice!
but, what with first part of problem: how to monitor query execution
progress on long lasting monolith queries?
is there some events sql may fire inside query, to tell me "now, i am on 40%
on table scan"
thnx
|||> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
Sorry, but there is no query progress event mechanism built into SQL Server.
Execution plans can involve many operators that make it problematic to
predict overall progress. I've heard that some have played around with
query governor cost limit to predict elapsed time but without much success.
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:u8Ohis20HHA.5408@.TK2MSFTNGP02.phx.gbl...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
> interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
>
> thnx, looks good.
> so, inside sp, on convenient points, to place raiseerror construct, and
> later, catch error event inside client app, and filter out custom errors.
> nice!
> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
> thnx
>
|||Another method to feedback on the progress of a multi-SQL-statement
long-running script/stored procedure is to insert current date time values
into a table, and monitor that table for progress.
Linchi
"Dan Guzman" wrote:

> You can use RAISERROR...WITH NOWAIT to send informational progress messages.
> RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
> SELECT or PRINT will wailt until the out buffer is full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sali" <sali@.euroherc.hr> wrote in message
> news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
>