Início / dba / Perguntas / 129090
Accepted
Dan
Asked: 2016-02-13 04:31:43 +0800 CST

Progresso da instrução SELECT INTO

  • 772

Nosso fluxo ETL tem uma instrução SELECT INTO de longa duração, que cria uma tabela em tempo real e a preenche com várias centenas de milhões de registros.

A declaração é algo comoSELECT ... INTO DestTable FROM SrcTable

Para fins de monitoramento, gostaríamos de ter uma ideia aproximada do andamento desta instrução, enquanto ela está sendo executada (aprox. rowcount, número de bytes gravados ou similar).

Tentamos o seguinte sem sucesso:

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

Além disso, podemos ver a transação em sys.dm_tran_active_transactions, mas não consegui encontrar uma maneira de obter a contagem de linhas afetadas em um determinado transaction_id(algo semelhante a @@ROWCOUNTtalvez, mas com o transaction_idargumento as).

Entendo que no SQL Server a instrução SELECT INTO é uma instrução DDL e DML em uma e, como tal, a criação implícita da tabela será uma operação de bloqueio. Ainda acho que deve haver alguma maneira inteligente de obter algum tipo de informação de progresso enquanto a instrução está em execução.

sql-server sql-server-2014

4 respostas

  1. Best Answer

    Eu suspeito que rowsin sys.partitionsé 0 devido a não ter sido confirmado ainda. Mas isso não significa que o SQL Server não saiba o que acontecerá se a transação for confirmada. A chave está em lembrar que todas as operações passam primeiro pelo Buffer Pool (ou seja, memória), independentemente de COMMIT ou ROLLBACK da operação. Portanto, podemos procurar sys.dm_os_buffer_descriptorsessas informações:

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

    Se você quiser ver os detalhes, remova o comentário da primeira linha de itens da SELECTlista e comente as 3 linhas restantes.

    Testei executando o seguinte em uma sessão e, em seguida, executando repetidamente a consulta acima em outra.

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

  2. Para fins de monitoramento, gostaríamos de ter uma ideia aproximada do andamento dessa declaração, enquanto ela está sendo executada.

    Um fora ou em curso?

    Se esta for uma necessidade que pode ser antecipada* você pode usarsys.dm_exec_query_profiles

    Conexão 1 (sessão 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;

    Conexão 2

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

    Você pode precisar somar as contagens de linhas retornadas se SELECT INTOestiver usando paralelismo .

    * A sessão que você deseja monitorar usando este DMV deve ser habilitada para coleta de estatísticas usando SET STATISTICS PROFILE ONou SET STATISTICS XML ON. Solicitar um plano de execução "real" do SSMS também funciona (porque define a última opção).

    • 7

  3. Não acho que haja uma maneira de obter contagens de linhas, mas você pode estimar a quantidade de dados gravados observando:

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

    Se você tiver alguma ideia de quantas páginas o heap deve ocupar quando terminar, poderá calcular a % concluída. A última consulta não será rápida conforme a tabela fica maior. E provavelmente é mais seguro executar o procedimento acima READ UNCOMMITTED(e não é sempre que eu recomendo isso, para qualquer coisa).

    • 6

  4. Se você pudesse mudar o INSERTde um

    SELECT ... INTO DestTable FROM SrcTable

    para um

    INSERT DestTable SELECT ... FROM SrcTable

    então sua select count(*) from DestTable with (nolock)consulta funcionaria.

    Se isso não for possível, você pode usar sp_WhoIsActive (ou mergulhar nos DMVs) para monitorar quantas gravações a consulta faz. Isso seria um medidor bastante aproximado, mas poderia ser útil se você baseasse o número de gravações que normalmente faz.

    Você deve conseguir obter um registro mínimo com o INSERTacima, se adicionar WITH (TABLOCK).

    • 5

relate perguntas