Duncan McC


When running large Store Procedures that take several hours, does SQL 05 have a way of monitoring the progress by telling you what it has actually completed so far rather than having to wait till the query has finished to find out.




Re: SQL Query Progress

Arnie Rowland


No.







Re: SQL Query Progress

zuomin

For a single query. NO. OR there will be too much overload on the server.

For multiple statements in sproc. You can add print statement to trace the progress.

Thanks,
Zuomin






Re: SQL Query Progress

Duncan McC

Thanks Zuomin,

I have tried the print statement as suggested already, but this still only comes back once the queries have finished

Print('Query starting')

WAITFOR DELAY '00:00:10'

Print('Query finished')





Re: SQL Query Progress

Chris Howarth

You could create an activity log table and insert rows into it at specific points in your stored proc. You're then free to query the activity log table at your leisure to determine progress.

Note, though, that any rows inserted into the table within a transaction (I'm not sure whether or not you're using transactions in your stored proc) will be removed if the transaction is rolled back. If you are using a transaction in the stored proc then queries against the activity log table during the stored proc's execution should be executed with the READ UNCOMMITTED transaction isolation level.

Chris






Re: SQL Query Progress

zuomin

Yes. SQL commands are executed one by one.

So you can add the such print trace statement to wanted place OR insert it into some other log tracking table.

Both will hurt performance, So do it only if progress trace is necessary.

thanks,

Zuomin





Re: SQL Query Progress

AngBT

If SQL commands are executed one by one, then I do not understand how I cannot get the following to work: Results are only returned after 22 secs. I was expecting the first result after 10 secs, the second result after 17 secs.

Can you explain why this doesn't work

CREATE PROCEDURE [dbo].[usp_Test1]

AS

print ('STARTING TEST1')

WAITFOR DELAY '00:00:10'

CREATE PROCEDURE [dbo].[usp_Test2]

AS

print('STARTING TEST2')

WAITFOR DELAY '00:00:07'

CREATE PROCEDURE [dbo].[usp_Test3]

AS

print('STARTING TEST3')

WAITFOR DELAY '00:00:05'

EXEC usp_Test1

EXEC usp_Test2

EXEC usp_Test3





Re: SQL Query Progress

Arnie Rowland

SQL Server completes a 'batch' of commands before 'reporting' back to the client.

The three EXECUTE command constitute a single batch.