SQL Server Query Progress – Indicating Running Query Progress in SQL Server

monitoringsql serversql-server-2005

I have a query:

SELECT *
FROM OPENQUERY
(
    oracle,
    'SELECT *
    FROM [some_table]'
)

It runs for about 7 hours. I only need to run this query once to import data into a new system when we change system.

I want to know is there some table in SQL Server 2005 that tracks how much data has been imported or bytes sent and received? So I can try and monitor this or check how this is going?

Best Answer

Is there some table in MS SQL 2005 that tracks how much data has been imported or bytes sent and received? So I can try and monitor this or check how this is going?

The simplest way to do this is the query the management views associated with the currently-executing request, the session, or the connection:

The first two expose a row_count attribute that contains the number of rows returned by the query so far. If you know the total number of rows that will be returned (even roughly) this can provide a simple progress indication.

The third view provides num_reads and num_writes indicating how many network packets have been sent and received by the connection. The size of the network packet in use for that connection is given in the net_packet_size attribute. The reads and writes include protocol overhead, and are not reset to zero at the start of each query or statement, so you would need to record the values before the query of interest starts executing.

All three views allow you to specify the session_id you are interested in, for example:

SELECT row_count 
FROM sys.dm_exec_requests AS DER 
WHERE session_id = 52;
Related Question