Przyśpieszanie importu danych poprzez wyłączenie indeksów tabel

6-mar-2011

Podczas masowego importu danych komendą BULK INSERT lub bcp lub innych operacji typu ETL indeksy tabel są na bieżąco aktualizowane. Oczywiście spowalnia to porces importu danych.

Indeksy można na czas importu wyłączyć, należy jednak pamiętać, aby po zakończeniu importu je na nowo przebudować.

Najpierw sprawdźmy jakie indeksy ma tabela HumanResources.Shift:

SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID('HumanResources.Shift')

W kolumnie is_disabled zobaczysz informację o tym czy indeks jest włączony, a w kolumnie name jego nazwę. Wyłączenie indeksu może wsytąpić poprzez:

ALTER INDEX AK_Shift_Name ON HumanResources.Shift DISABLE

Zobacz wynik:

SELECT name, is_disabled FROM sys.indexes
WHERE object_id = OBJECT_ID('HumanResources.Shift')

Aby po imporcie na nowo włączyć indeks musisz go przebudować. Nie można go jakoś po prostu włączyć, bo przecież i tak byłby nieaktuaalny! Wykorzystaj do tego komendę:

ALTER INDEX AK_Shift_Name ON HumanResources.Shift REBUILD

Przebudowanie indeksu dużej tabeli może być długotrwałe, ale od czego są dodatkowe opcje! Opcja

ALTER INDEX AK_Shift_Name ON HumanResources.Shift REBUILD
WITH (online=on, sort_in_tempdb=on)

Oto znaczenie tych opcji:

  • online – pozwala wielu uzytkownikom korzystać jednocześnie z indeksów podczas ich modyfikacji
  • sort_in_tempdb – jeżeli powstają pomocniczo obiekty podczas sortowania danych to domyślnie powstają one w tym samym pliku co dany obiekt. Jeżeli preferowałbyś tworzenie takich obiektów w bazie daych tymczasowj, przełacz ten parametr na on.

 Wydając komendę wyłączenia indeksów, możesz także wskazać, że chcesz wyłączyć wszystkie indeksy używając słowa ALL:

ALTER INDEX ALL ON HumanResources.Shift DISABLE

Podobnie można przebudować indeksy:

ALTER INDEX ALL ON HumanResources.Shift REBUILD

Uwaga!

Wykonując komendę:

ALTER INDEX ALL ON HumanResources.Shift DISABLE

Wyłączysz także indeks klustrowany (jeżeli tabela go ma), więc niemożliwe będzie w ogóle korzystanie z tej tabeli. Tego raczej nie chcieliśmy 🙂 Jeżeli coś takiego zrobiłeś możesz śmiało przebudować ten indeks, tak jak opisałem to powyżej.

Komentarze:

  1. Gość napisał,

    Jak wyłączyłem wszystkie indeksy, w tym klastrowy, to SQL Server 2012 odmówił mi importu masowego bo stwierdził że nie mógł utworzyć planu. Czyli przynajmniej jeden indeks, klastrowy, trzeba zachować aby możliwy był import danych.

  2. admin napisał,

    Dokładnie tak. Jeśli tabela ma indeks clustrowy, to tak właściwie na dysku ta tabela jest tym indeksem. Co za tym idzie ten indeks musi być włączony. Nie znalazłem jeszcze praktycznego zastosowania wyłącznego indeksu clustrowanego 🙂

Autor: Rafał Kraik