quinta-feira, 12 de dezembro de 2019

Monitorar Processo Criação de Index (SQL Server)

Dale galera hoje vamos falar um pouco de uma rotina para monitorar o processo de criação de Index

Quem que nunca precisou de criar um index em horário de produção e ficou se cagando todo !!!

Bom a menos que a versão do SQL Server seja 2016 ou superior e Enterprise, que possibilita utilizar a opção de criar um index ONLINE ( dai já é outra historia ).

Para criar um index em horário de operação tem que acima de tudo entender a regra de negócio aplicada assim como validar os impactos que esse pode causar com a alocação da tabela. Para possibilitar a analise do tempo de criação e progresso criei a view VW_PROGRESS_SESSION.

Para que seja possível acompanhar o progresso na criação de um Index, é preciso setar o parâmetro SET STATISTICS PROFILE ON, e executar junto com o comando de Create do Index , como no exemplo abaixo:

SET STATISTICS PROFILE ON

GO

CREATE INDEX [IX_nome_index] ON [Nome_Database].[dbo].[Nome_Tabela] ([Coluna_index])
INCLUDE ([Colunas_include])
WITH(FILLFACTOR= 80, ONLINE=ON); -- Hint ONLINE disponível somente na versão Enterprise do SQL Server

Após rodar o script de create do index, abra uma nova aba do Management Studio e rode a consulta da View, como no exemplo abaixo:

SELECT
*
FROM VW_PROGRESS_SESSION
WHERE COMMAND = 'CREATE INDEX'

Será retornado um resultado do progresso com as colunas abaixo:

command,  -- Comando que esta sen executado No caso ( 'CREATE INDEX')
session_id, --  Id da Seção
start_time,  --  Data hora que iniciou a execução
text,            -- Script que está rodando na seção
Ds_Operador_Atual,
Qt_Linhas_Total,
Qt_Linhas_Processadas,
Qt_Linhas_Restantes,
Vl_Percentual_Completado,
Qt_Segundos_Decorridos,
Qt_Segundos_Restantes,
Dt_Prevista,
host_name,
program_name

Imagem de exemplo:


Bom saber que a estimativa prevista do percentual completado, e Data previsa para termino é calculado conforme o total de linhas processadas, ou seja, é uma estimativa


Script de criação da view VW_PROGRESS_SESSION:
/*
SELECT
 * 
FROM VW_PROGRESS_SESSION
WHERE COMMAND = 'CREATE INDEX'
--WHERE COMMAND = 'UPDATE'
--'CREATE INDEX'
--'ALTER INDEX'

*/

CREATE VIEW VW_PROGRESS_SESSION
AS
WITH CTE_SESSION AS 
(
    SELECT
        B.session_id,
  C.command ,
        B.login_time,
        B.[host_name],
        B.[program_name],
        B.nt_user_name,
        B.original_login_name,
        D.[text],
        MIN(C.start_time) AS start_time,
        SUM(A.[row_count]) AS Qt_Linhas_Processadas,
        SUM(A.[estimate_row_count]) AS [Qt_Linhas_Total],
        MAX(A.last_active_time) - MIN(A.first_active_time) AS [Qt_Tempo_Decorrido_MS],
        MAX(IIF(A.[close_time] = 0 AND A.[first_row_time] > 0, [physical_operator_name], N'<Transition>')) AS [Ds_Operador_Atual]
    FROM
        sys.dm_exec_query_profiles A
        JOIN sys.dm_exec_sessions B ON B.session_id = A.session_id
        JOIN sys.dm_exec_requests AS C WITH (NOLOCK) ON B.session_id = C.session_id
        CROSS APPLY sys.dm_exec_sql_text(C.[sql_handle]) D
    --WHERE    C.command = 'CREATE INDEX'
    GROUP BY
        B.session_id,
  C.command ,
        B.login_time,
        B.[host_name],
        B.[program_name],
        B.nt_user_name,
        B.original_login_name,
        D.[text]
),
CTE_RESULT AS 
(
    SELECT
         *,
         ( [Qt_Linhas_Total] - Qt_Linhas_Processadas ) AS [Qt_Linhas_Restantes],
         ( [Qt_Tempo_Decorrido_MS] / 1000.0 ) AS [Qt_Segundos_Decorridos]
     FROM
         CTE_SESSION
)
SELECT
 command ,
    session_id,
    start_time,
    [text],
    [Ds_Operador_Atual],
    [Qt_Linhas_Total],
    Qt_Linhas_Processadas,
    [Qt_Linhas_Restantes],
    CONVERT(DECIMAL(5, 2), (( Qt_Linhas_Processadas * 1.0 ) / [Qt_Linhas_Total] ) * 100) AS [Vl_Percentual_Completado],
    [Qt_Segundos_Decorridos],
    (( [Qt_Segundos_Decorridos] / Qt_Linhas_Processadas ) * [Qt_Linhas_Restantes] ) AS [Qt_Segundos_Restantes],
    DATEADD(SECOND, (( [Qt_Segundos_Decorridos] / Qt_Linhas_Processadas ) * [Qt_Linhas_Restantes] ), GETDATE()) AS [Dt_Prevista],
    [host_name],
    [program_name],
    nt_user_name,
    original_login_name
FROM
    CTE_RESULT

Nenhum comentário:

Postar um comentário

Migrando Blog https://luizflsilva.wordpress.com/

Curtindo cada vez mais a criar conteudo que possa levar aprendizado a amantes e profissionais da area de banco de dados, estou migrando o bl...