Postęp instrukcji SELECT INTO

Nasz przepływ ETL ma długo działającą instrukcję SELECT INTO, która tworzy tabelę w locie i zapełnia ją setkami milionów rekordów.

Oświadczenie wygląda mniej więcej tak SELECT ... INTO DestTable FROM SrcTable

Do celów monitorowania chcielibyśmy uzyskać ogólne pojęcie o postępie wykonywania tej instrukcji podczas jej wykonywania (przybliżona liczba wierszy, liczba zapisanych bajtów lub podobna).

Wypróbowaliśmy następujące bezskutecznie:

-- 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')

Co więcej, widzimy transakcję sys.dm_tran_active_transactions, ale nie byłem w stanie znaleźć sposobu, aby uzyskać liczbę dotkniętych wierszy w danym transaction_id(może coś podobnego do @@ROWCOUNT, ale z transaction_idargumentem as).

Rozumiem, że na SQL Server instrukcja SELECT INTO jest zarówno instrukcją DDL, jak i DML w jednym, i jako takie, niejawne tworzenie tabeli będzie operacją blokującą. Nadal uważam, że musi istnieć jakiś sprytny sposób na uzyskanie pewnego rodzaju informacji o postępie podczas działania instrukcji.

Podejrzewam, że rowsin sys.partitionsma wartość 0, ponieważ nie zostało jeszcze popełnione. Ale to nie znaczy, że SQL Server nie wie, co się tam stanie, jeśli transakcja się zatwierdzi. Kluczem jest zapamiętywanie, że wszystkie operacje najpierw przechodzą przez pulę buforów (tj. Pamięć), bez względu na COMMIT lub ROLLBACK operacji. Dlatego możemy poszukać sys.dm_os_buffer_descriptorstych informacji:

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')

Jeśli chcesz zobaczyć szczegóły, usuń komentarz z pierwszego wiersza pozycji na SELECTliście, skomentuj pozostałe 3 wiersze.

Testowałem, uruchamiając poniższe w jednej sesji, a następnie wielokrotnie uruchamiając powyższe zapytanie w innej.

SELECT so1.*
INTO   dbo.TestDump
FROM   sys.objects so1
CROSS JOIN sys.objects so2
CROSS JOIN sys.objects so3;

Do celów monitorowania chcielibyśmy z grubsza zorientować się w postępach tego polecenia podczas jego wykonywania.

Jednorazowy czy trwa?

Jeśli jest to potrzeba, którą można przewidzieć z wyprzedzeniem *, możesz skorzystać sys.dm_exec_query_profiles

Połączenie 1 (sesja 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;

Połączenie 2

select row_count
from sys.dm_exec_query_profiles
WHERE physical_operator_name = 'Table Insert' 
    AND session_id = 55;

Być może trzeba zsumować liczbę zwracanych wierszy, jeżeli SELECT INTOjest za pomocą równoległości .

* Sesja, którą chcesz monitorować za pomocą tego DMV, musi być włączona do zbierania statystyk za pomocą SET STATISTICS PROFILE ONlub SET STATISTICS XML ON. Żądanie „rzeczywistego” planu wykonania z SSMS również działa (ponieważ ustawia tę drugą opcję).

Nie sądzę, że istnieje sposób na uzyskanie liczby wierszy, ale można oszacować ilość danych zapisanych na podstawie:

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');

Jeśli masz jakieś pojęcie o tym, ile stron sterty powinno zająć po zakończeniu, powinieneś być w stanie wypracować% ukończenia. To ostatnie zapytanie nie będzie szybkie, gdy tabela się powiększy. I prawdopodobnie najbezpieczniej jest uruchomić powyższe pod READ UNCOMMITTED(i nie zawsze tak polecam, na cokolwiek).

Jeśli możesz zmienić INSERTz

SELECT ... INTO DestTable FROM SrcTable

do

INSERT DestTable SELECT ... FROM SrcTable

wtedy twoje select count(*) from DestTable with (nolock)zapytanie zadziałałoby.

Jeśli nie jest to możliwe, możesz użyć sp_WhoIsActive (lub zagłębić się w DMV) do monitorowania liczby zapisów zapytania. Byłby to dość szorstki miernik, ale może być użyteczny, jeśli podstawa wyrówna liczbę zapisów, co zwykle.

Jeśli dodasz, powinieneś być w stanie uzyskać minimalne logowanie przy użyciu INSERTpowyższego WITH (TABLOCK).