Our ETL flow has a long-running SELECT INTO-statement, that's creating a table on the fly, and populating it with several hundred million records.
The statement looks something like
SELECT ... INTO DestTable FROM SrcTable
For monitoring purposes, we would like to get a rough idea of the progress of this statement, while it is executing (approx. rowcount, written number of bytes, or similar).
We tried the following to no avail:
-- Is blocked by the SELECT INTO statement: select count(*) from DestTable with (nolock) -- Returns 0, 0: select rows, rowmodctr from sysindexes with (nolock) where id = object_id('DestTable') -- Returns 0: select rows from sys.partitions where object_id = object_id('DestTable')
Furthermore, we can see the transaction in
sys.dm_tran_active_transactions, but I was not able to find a way to get the count of affected rows on a given
transaction_id (something similar to
@@ROWCOUNT perhaps, but with the
transaction_id as argument).
I understand that on SQL Server the SELECT INTO-statement is both a DDL and a DML statement in one, and as such, the implicit table creation will be a locking operation. I still think there must be some clever way to obtain some kind of progress information while the statement is running.