Progress of SELECT INTO statement
I suspect that rows
in sys.partitions
is 0 due to not being committed yet. But this does not mean that SQL Server is unaware of what will go there if the Transaction does commit. The key is in remembering that all operations go through the Buffer Pool (i.e. memory) first, regardless of COMMIT or ROLLBACK of the operation. Hence, we can look in sys.dm_os_buffer_descriptors
for that info:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT --OBJECT_NAME(sp.[object_id]) AS [TableName], sdobd.*, '---', sp.*, '---', sau.*
SUM(sdobd.[row_count]) AS [BufferPoolRows],
SUM(sp.[rows]) AS [AllocatedRows],
COUNT(*) AS [DataPages]
FROM sys.dm_os_buffer_descriptors sdobd
INNER JOIN sys.allocation_units sau
ON sau.[allocation_unit_id] = sdobd.[allocation_unit_id]
INNER JOIN sys.partitions sp
ON ( sau.[type] = 1
AND sau.[container_id] = sp.[partition_id]) -- IN_ROW_DATA
OR ( sau.[type] = 2
AND sau.[container_id] = sp.[hobt_id]) -- LOB_DATA
OR ( sau.[type] = 3
AND sau.[container_id] = sp.[partition_id]) -- ROW_OVERFLOW_DATA
WHERE sdobd.[database_id] = DB_ID()
AND sdobd.[page_type] = N'DATA_PAGE'
AND sp.[object_id] = (SELECT so.[object_id]
FROM sys.objects so
WHERE so.[name] = 'TestDump')
If you want to see the details, uncomment the first row of items in the SELECT
list, comment out the remaining 3 lines.
I tested by running the following in one Session and then repeatedly running the query above in another.
SELECT so1.*
INTO dbo.TestDump
FROM sys.objects so1
CROSS JOIN sys.objects so2
CROSS JOIN sys.objects so3;
For monitoring purposes, we would like to get a rough idea of the progress of this statement, while it is executing.
One off or ongoing?
If this is a need that can be anticipated in advance* you could use sys.dm_exec_query_profiles
Connection 1 (session 55)
SET STATISTICS XML ON
SELECT so1.*
INTO dbo.TestDump
FROM sys.all_objects so1
CROSS JOIN sys.all_objects so2
CROSS JOIN sys.all_objects so3
CROSS JOIN sys.all_objects so4
CROSS JOIN sys.all_objects so5;
Connection 2
select row_count
from sys.dm_exec_query_profiles
WHERE physical_operator_name = 'Table Insert'
AND session_id = 55;
You may need to sum the rows counts returned if the SELECT INTO
is using parallelism.
* The session you want to monitor using this DMV must be enabled for statistics collection using SET STATISTICS PROFILE ON
or SET STATISTICS XML ON
. Requesting an "actual" execution plan from SSMS works as well (because it sets the latter option).
I don't think there's a way to get row counts, but you can estimate amount of data written by looking at:
SELECT writes
FROM sys.dm_exec_requests WHERE session_id = <x>;
SELECT COUNT(*) FROM sys.dm_db_database_page_allocations
(<dbid>, OBJECT_ID(N'dbo.newtablename'), 0, NULL, 'LIMITED');
If you have some kind of idea of how many pages the heap should take up when done, you should be able to work out % complete. The latter query won't be fast as the table gets bigger. And probably safest to run the above under READ UNCOMMITTED
(and it's not often I recommend that, for anything).