Czy kolejność kolumn w indeksie PK ma znaczenie?

Mam kilka bardzo dużych stołów o tej samej podstawowej strukturze. Każdy z nich ma kolumnę RowNumber (bigint)i DataDate (date). Dane są ładowane przy użyciu SQLBulkImport każdej nocy i nigdy nie są ładowane „nowe” dane - jest to rekord historyczny (SQL Standard, nie Enterprise, więc brak partycjonowania).

Ponieważ każdy bit danych musi być powiązany z innymi systemami, a każda RowNumber/DataDatekombinacja jest unikalna, to jest mój Podstawowy klucz.

Zauważam, że ze względu na sposób, w jaki zdefiniowałem PK w SSMS Table Designer, RowNumberjest wymieniony jako pierwszy i DataDatedrugi.

Zauważam również, że moja fragmentacja jest zawsze BARDZO wysoka ~ 99%.

Teraz, ponieważ każdy z nich DataDatepojawia się tylko raz, spodziewałbym się, że indeksator będzie po prostu dodawał do stron każdego dnia, ale zastanawiam się, czy tak naprawdę indeksowanie opiera się na RowNumberpierwszym, a zatem czy trzeba zmieniać wszystko inne?

Rownumbernie jest kolumną tożsamości, to int generowany przez system zewnętrzny (niestety). Resetuje się na początku każdego DataDate.

Przykładowe dane

RowNumber | DataDate | a | b | c..... 
   1      |2013-08-01| x | y | z 
   2      |2013-08-01| x | y | z 
...
   1      |2013-08-02| x | y | z 
   2      |2013-08-02| x | y | z 
...

Dane są ładowane w RowNumberkolejności, po jednej DataDatena ładunek.

Proces importowania jest bcp - próbowałem załadować do tabeli tymczasowej, a następnie wybrać kolejno stamtąd ( ORDER BY RowNumber, DataDate), ale nadal pojawia się duża fragmentacja.

Czy kolejność kolumn w indeksie PK ma znaczenie?

Tak.

Domyślnie ograniczenie klucza podstawowego jest wymuszane w SQL Server przez unikalny indeks klastrowany. Indeks klastrowy określa logiczną kolejność wierszy w tabeli. Może być dodanych wiele dodatkowych stron indeksu, które reprezentują górne poziomy indeksu b-drzewa, ale najniższy poziom liścia indeksu klastrowego jest po prostu logiczną kolejnością samych danych.

Aby to wyjaśnić, wiersze na stronie niekoniecznie są fizycznie przechowywane w klastrowanej kolejności kluczy indeksu. Na stronie znajduje się osobna struktura pośrednictwa, która przechowuje wskaźnik do każdego wiersza. Ta struktura jest sortowana według klastrowanych kluczy indeksu. Ponadto każda strona ma wskaźnik do poprzedniej i następnej strony na tym samym poziomie w klastrowanej kolejności kluczy indeksu.

W przypadku klastrowego klucza głównego (RowNumber, DataDate)wiersze są logicznie sortowane najpierw według, RowNumbera następnie według DataDate- tak więc wszystkie wiersze, w których RowNumber = 1są logicznie pogrupowane, a następnie wiersze gdzie RowNumber = 2i tak dalej.

Po dodaniu nowych danych ( RowNumbersod 1 do n) nowe wiersze logicznie należą do istniejących stron, więc SQL Server prawdopodobnie będzie musiał wykonać wiele pracy, dzieląc strony, aby zrobić miejsce. Cała ta aktywność generuje dużo dodatkowej pracy (w tym rejestrowanie zmian) bez żadnego zysku.

Dzielone strony również zaczynają się w około 50% puste, więc nadmierne dzielenie może również powodować niską gęstość stron (mniej wierszy niż optymalna na stronę). To nie tylko zła wiadomość do odczytu z dysku (niższa gęstość = więcej stron do przeczytania), ale także strony o mniejszej gęstości zajmują więcej miejsca w pamięci po buforowaniu.

Zmiana indeksu klastrowego na (DataDate, RowNumber) oznacza, że ​​nowe dane (o prawdopodobnie wyższej DataDatesniż obecnie przechowywana) są dołączane do logicznego końca indeksu klastrowego na nowych stronach. Spowoduje to usunięcie niepotrzebnych kosztów podziału stron i skróci czas ładowania. Mniej pofragmentowane dane oznaczają również, że aktywność odczytu z wyprzedzeniem (odczytywanie stron z dysku tuż przed ich potrzebą do zapytania w toku) może być bardziej wydajna.

Jeśli nic więcej, Twoje zapytania będą znacznie częściej wyszukiwane DataDateniż RowNumber. Indeks klastrowy włączony (DataDate, RowNumber) obsługuje wyszukiwanie indeksowe DataDate(a następnie RowNumber). Istniejące ustawienie obsługuje tylko wyszukiwanie RowNumber(i tylko wtedy, być może, włączenie DataDate). Może się okazać, że można usunąć istniejący indeks nieklastrowany DataDatepo zmianie klucza podstawowego. Indeks klastrowy będzie szerszy niż indeks nieklastrowany, który zastępuje, dlatego należy przetestować, aby upewnić się, że wydajność pozostaje akceptowalna.

Podczas importowania nowych danych bcpmożesz uzyskać wyższą wydajność, jeśli dane w pliku importu zostaną posortowane według klastrowanych kluczy indeksu (najlepiej (DataDate, RowNumber) i określisz bcpopcję:

-h "ORDER(DataDate,RowNumber), TABLOCK"

Aby uzyskać najlepszą wydajność ładowania danych, możesz spróbować uzyskać minimalnie zarejestrowane wstawki. Aby uzyskać więcej informacji, zobacz:

• SQL Server Index Basics autorstwa Roberta Sheldona
• Efektywne indeksy klastrowe autorstwa Michelle Ufford
• Wkładki luzem przez TSQL autorstwa Roberta Sheldona
• Minimalne logowanie za pomocą INSERT… WYBIERZ w pustych tabelach klastrowych przeze mnie
• Minimalne logowanie za pomocą INSERT… SELECT i kontekst szybkiego ładowania przeze mnie

Tak, kolejność jest krytyczna. Bardzo wątpię, czy kiedykolwiek zapytałeś RowNumber (np WHERE RowNumber=1.). Przeważnie szeregi czasowe są sprawdzane według date ( WHERE DataDate BEWEEN @start AND @end), a takie zapytania wymagałyby organizacji klastrowej do DataDate.

Fragmentacja jest ogólnie śledźem czerwonym. Ograniczenie fragmentacji nie powinno być tutaj twoim celem, ale właściwa organizacja dla twoich zapytań powinna. Zmniejszenie fragmentacji jest dobrym pomysłem, ale nie jest celem samym w sobie. Jeśli masz odpowiednio zorganizowany model danych, który odpowiada twojemu obciążeniu (twoje zapytania są odpowiednio uwzględnione) i masz pomiary, które pokazują, że fragmentacja wpływa na wydajność, możemy o tym porozmawiać.