CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2005
Adam_B
Adam_B

Reputation: 83

Show how long a query has to run in SQL Server

Is there a way to see how far through a query is in SQL Server? I know how to do it in oracle but I can't work out if it's possible in SQL Server.

Cheers

Upvotes: 3

Views: 3002

Answers (2)

Diego
Diego

Reputation: 36126

not really. If you want a command that will say: this query is 35% done you wont have it. SQL Server cant tell how far it is to finish the query because it doesn't know in advance how many rows the query will satisfy.

What you can do is run sp_who2 'active' and look for your task. Again, it wont tell how long it will take to finish but you can see info in real time regard how long it's being running.

SET STATISTICS TIME ON or a trace file will show you info AFTER the query is ran

Upvotes: 0

devarc
devarc

Reputation: 1157

There is no straight way to do this, but if your query is more than just one select you can insert this "checkpoints":

RAISERROR ('Your message here', 0, 1) WITH NOWAIT

For example

SELECT ... FROM ...
RAISERROR ('33% .... DONE', 0, 1) WITH NOWAIT
SELECT ... FROM ...
RAISERROR ('66% .... DONE', 0, 1) WITH NOWAIT
...
...

You can see progress in Messages window.

Sometimes it's very helpful thing.

Upvotes: 1

Related Questions