SQL Server: Jak śledzić postęp polecenia CREATE INDEX?

SQL Server 2014, Std Ed

Czytałem, że percent_complete w dm_exec_requests nie działa dla CREATE INDEX, aw praktyce procent_complete utrzymuje się na poziomie 0. Więc to nie pomaga.

Obecnie używam poniższej metody, która przynajmniej pokazuje mi ruch (że tworzenie indeksu nie jest zablokowane). Ale nie mam pojęcia, czy mam% 10 poprzez proces, czy% 99.

Wypróbowałem metodę opisaną tutaj: /dba//a/102545/6229, ale pokazuje ona wyraźnie nieprawidłowy szacowany czas ukończenia (w zasadzie pokazuje „teraz” dla trwającego ponad 60 minut procesu, w który mam 10 minut )

Jak mogę uzyskać wskazówkę?

SELECT percent_complete, estimated_completion_time, reads, writes, logical_reads, text_size, *
FROM
sys.dm_exec_requests AS r
WHERE
r.session_id <> @@SPID
AND r.session_id = 58

Myślę, że poniższe zapytanie przynajmniej zbliży cię do siebie. Wykorzystuje DMV, który został wprowadzony w SQL Server 2014: sys.dm_exec_query_profiles (i dzięki Martinowi Smithowi za wprowadzenie go do mnie za pośrednictwem powiązanej odpowiedzi DBA.StackExchange: Postęp instrukcji SELECT INTO :-).

Proszę zanotować:

• !! Trzeba będzie dodać SET STATISTICS PROFILE ON;lub SET STATISTICS XML ON;w partii, która jest zapytań robi CREATE INDEX(i umieszczone przed tym CREATE INDEXstwierdzeniem, czy to nie było oczywiste), w przeciwnym wypadku nie wiersze pojawią się w tym DMV dla tego SPID / session_id !!

• INOperator jest stosowany do odfiltrowania Index Insertwierszu, jeśli występują, będą zwiększać się TotalRowswartości, które skośnej obliczenia ponieważ rząd nie wykazuje żadnych rzędy przetwarzane.

• Wyświetlana tutaj liczba wierszy (tj. TotalRows) Jest dwa razy większa niż liczba wierszy w tabeli ze względu na operację wykonującą dwa kroki, z których każdy działa na wszystkich wierszach: pierwszy to „skanowanie tabeli” lub „skanowanie indeksu klastrowanego”, a drugi to rodzaj". Podczas tworzenia indeksu klastrowego lub tworzenia indeksu nieklastrowanego na stosie zobaczysz „Skanowanie tabeli”. Podczas tworzenia indeksu nieklastrowanego na indeksie klastrowym zobaczysz „Skanowanie indeksu klastrowanego”.

• To zapytanie nie działa podczas tworzenia indeksów filtrowanych. Z jakiegoś powodu indeksy filtrowane a) nie mają kroku „Sortuj”, i b) row_countpole nigdy nie wzrasta od 0.
  Nie jestem pewien, co testowałem wcześniej, ale moje testy wskazują teraz, że przefiltrowane indeksy są przechwytywane przez to zapytanie. Słodkie. Uważaj jednak, że liczba wierszy może być wyłączona (zobaczę, czy kiedyś to naprawię).

• Podczas tworzenia indeksu klastrowanego na stosie, który ma już indeksy nieklastrowane, należy odbudować indeksy nieklastrowane (aby zamienić RID - RowID - dla kluczy indeksowanych klastrowanych), a każda przebudowa indeksu klastrowanego zostanie być oddzielną operacją i dlatego nie ma odzwierciedlenia w statystykach zwracanych przez to zapytanie podczas tworzenia indeksu klastrowanego.

• To zapytanie zostało przetestowane przeciwko:

  • Tworzenie:
    • Indeksy nieklastrowane na stosie
    • Indeks klastrowany (nie istnieją indeksy nieklastrowane)
    • Indeksy nieklastrowane w indeksie / tabeli klastrowanej
    • Indeks klastrowany, gdy indeksy nieklastrowane już istnieją
    • Unikalne indeksy nieklastrowane w indeksie / tabeli klastrowanej
  • Przebudowa (tabela z indeksem klastrowanym i jednym indeksem nieklastrowanym; przetestowana na SQL Server 2014, 2016, 2017 i 2019) poprzez:
    • ALTER TABLE [schema_name].[table_name] REBUILD;( tylko przy użyciu tej metody pojawia się indeks klastrowany )
    • ALTER INDEX ALL ON [schema_name].[table_name] REBUILD;
    • ALTER INDEX [index_name] ON [schema_name].[table_name] REBUILD;

DECLARE @SPID INT = 51;

;WITH agg AS
(
     SELECT SUM(qp.[row_count]) AS [RowsProcessed],
            SUM(qp.[estimate_row_count]) AS [TotalRows],
            MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS],
            MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0,
                    [physical_operator_name],
                    N'<Transition>')) AS [CurrentStep]
     FROM sys.dm_exec_query_profiles qp
     WHERE qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan',
                                           N'Index Scan',  N'Sort')
     AND   qp.[session_id] = @SPID
), comp AS
(
     SELECT *,
            ([TotalRows] - [RowsProcessed]) AS [RowsLeft],
            ([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
     FROM   agg
)
SELECT [CurrentStep],
       [TotalRows],
       [RowsProcessed],
       [RowsLeft],
       CONVERT(DECIMAL(5, 2),
               (([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete],
       [ElapsedSeconds],
       (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft],
       DATEADD(SECOND,
               (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]),
               GETDATE()) AS [EstimatedCompletionTime]
FROM   comp;

Przykładowe dane wyjściowe:

                        Rows                 Percent   Elapsed  Estimated    Estimated
CurrentStep  TotalRows  Processed  RowsLeft  Complete  Seconds  SecondsLeft  CompletionTime
-----------  ---------  ---------  --------  --------  -------  -----------  --------------
Clustered    11248640   4786937    6461703   42.56     4.89400  6.606223     2016-05-23
Index Scan                                                                   14:32:40.547