Kiedy lepiej jest tworzyć STATYSTYKI zamiast tworzenia Indeksu?

Znalazłem wiele informacji na temat STATISTICS : jak są one utrzymywane, jak można je tworzyć ręcznie lub automatycznie na podstawie zapytań lub indeksów i tak dalej. Ale nie byłem w stanie znaleźć żadnych wskazówek ani informacji o „najlepszych praktykach” dotyczących tego, kiedyaby je utworzyć: jakie sytuacje odnoszą większe korzyści z ręcznie utworzonego obiektu STATISTICS niż z indeksu. Widziałem ręcznie tworzone filtrowane statystyki pomagające w zapytaniach dotyczących tabel podzielonych na partycje (ponieważ statystyki utworzone dla indeksów obejmują całą tabelę i nie dotyczą poszczególnych partycji - brillaint!), Ale z pewnością muszą istnieć inne scenariusze, które skorzystałyby z obiektu statystycznego podczas gdy nie potrzebuje szczegółów indeksu, ani nie jest wart kosztów utrzymania indeksu lub zwiększenia szans na blokowanie / martwe blokady.

@JathanathanFite w komentarzu wspomniał o rozróżnieniu między indeksami a statystykami:

Indeksy pomogą SQL w szybszym znajdowaniu danych, tworząc odnośniki sortowane inaczej niż sama tabela. Statystyki pomagają SQL określić, ile pamięci / wysiłku będzie wymagało wypełnienie zapytania.

To świetna informacja, głównie dlatego, że pomaga mi wyjaśnić moje pytanie:

Jak wiedząc o tym (lub innego informacji technicznych na co S i jak y związane z zachowaniami i charakteru STATISTICS) pomagają określić , kiedy wybrać CREATE STATISTICSsię CREATE INDEX, zwłaszcza podczas tworzenia indeksu stworzy powiązany STATISTICSobiekt? Który scenariusz byłby lepszy, gdyby posiadał tylko dane STATYSTYCZNE, a nie posiadał Indeksu?

Byłoby superduper, jeśli to możliwe, mieć działający przykład scenariusza, w którym STATISTICSobiekt jest lepiej dopasowany niż INDEX.

Ponieważ jestem wzrokowcem / myśliciel, myślałem, że to może pomóc, aby zobaczyć różnice między STATISTICSi INDEXES, side-by-side, jako możliwych sposobów pomaga określić, kiedy STATISTICSsą lepszym wyborem.

Thingy           PROs                             CONs
-------          ----------                       -------------------
INDEX            * Can help sorts.                * Takes up space.
                 * Contains data (can             * Needs to be maintained (extra I/O).
                   "cover" a query).              * More chances for blocking / dead-locks.

STATISTICS       * Takes up very little space.    * Cannot help sorts.
                 * Lighter maintenance / won't    * Cannot "cover" queries.
                   slow down DML operations.
                 * Does not increase chances
                   of blocking / dead-locks.

Oto niektóre zasoby, które znalazłem, szukając tego, takie, które nawet zadają to samo pytanie, ale na które nie udzielono odpowiedzi:

Indeks programu SQL Server a statystyki

Pytania dotyczące statystyk programu SQL Server Byliśmy zbyt nieśmiali, by zadawać pytania

Statystyka. Czy możliwe są histogramy wielokolumnowe?

** Żeby było jasne, nie mam na to odpowiedzi i naprawdę szukam informacji zwrotnych od, mam nadzieję, kilku osób, które dostarczą dziwnie brakujących informacji tutaj w interwebach.

Pytanie obraca się wokół - Kiedy dobrze jest po prostu tworzyć statystyki w porównaniu do tworzenia indeksu (który tworzy statystyki).

Z moich notatek o wewnętrznych serwerach SQL (klasa SQLSkills - IE1 i IE2) oraz książki o wewnętrznych serwerach SQL Server , poniżej mam ograniczone rozumienie:

Statystyki SQL Server to nic innego jak obiekty systemowe, które zawierają istotne informacje na temat wartości kluczy indeksu i regularnych wartości kolumn.

SQL Server używa modelu opartego na kosztach, aby jak najszybciej wybrać „wystarczająco dobry” plan wykonania. Oszacowanie zdolności (oszacowanie liczby wierszy do przetworzenia na każdym etapie wykonywania zapytania) jest najważniejszym czynnikiem optymalizacji zapytania, którego wpływ wpływa na strategię łączenia, wymaganie przyznania pamięci, wybór wątku roboczego oraz wybór indeksów podczas uzyskiwania dostępu do danych .

SQL Server nie będzie używać indeksów nieklastrowanych, gdy szacuje, że duże nie. wymaganych operacji zapętlenia klucza lub RID, więc będzie utrzymywał statystyki dotyczące indeksów (i kolumn), które pomogą w takich oszacowaniach.

Istnieją 2 ważne rzeczy dotyczące statystyk:

1. Histogram przechowuje informacje o dystrybucji danych TYLKO w kolumnie statystyk po lewej stronie (indeks). Przechowuje również informacje o gęstości kluczowych wartości w wielu kolumnach. Zasadniczo histogram przechowuje dystrybucję danych tylko dla kolumny statystyk skrajnie lewej.

2. SQL Server zachowa co najwyżej 200 kroków w histogramie, niezależnie od wielkości tabeli. Przedziały objęte każdym krokiem histogramu rosną wraz ze wzrostem tabeli, co prowadzi do „mniej dokładnych” statystyk dla dużych tabel.

   Pamiętaj, że selektywność indeksu jest miarą odwrotnie proporcjonalną do gęstości, tzn. Im więcej unikalnych wartości ma kolumna, tym wyższa jest jej selektywność.

Gdy określone zapytania nie są uruchamiane zbyt często, możesz wybrać tworzenie statystyk na poziomie kolumny zamiast indeksu. Statystyki na poziomie kolumny pomagają Optymalizatorowi kwerend znaleźć lepsze plany wykonania, mimo że te plany wykonania są nieoptymalne ze względu na skanowane indeksy. Jednocześnie statystyki nie dodają narzutu podczas operacji modyfikacji danych i pomagają uniknąć konserwacji indeksu. To podejście działa tylko w przypadku rzadko wykonywanych zapytań.

Patrz:

• Statystyka kolumn Daj przewagę optymalizatorowi autorstwa Kalena Delaneya
• Statystyki, przeklęte kłamstwa i statystyki - co to jest Statman? Conor Cunningham

Uwaga: ktoś taki jak Paul White lub Aaron Bertrand może wtrącić się, aby zapewnić więcej koloru dla twojego dobrego pytania .

Powiedziałbym, że potrzebujesz indeksu, gdy potrzebujesz być w stanie ograniczyć ilość danych / szybko dotrzeć do poprawnych danych w oparciu o pola (pola).

Potrzebujesz statystyk, gdy potrzebujesz optymalizatora, aby zrozumieć naturę danych, aby móc wykonywać operacje w najlepszy możliwy sposób.

To, co wymyśliłem, filtrowane statystyki pomagają, gdy masz skośne dane, które mają duży wpływ na plan, na przykład w przypadku przepełnienia stosu niewielu użytkowników ma ogromną liczbę postów, więc użycie tylko przeciętnych postów na użytkownika nie jest najlepszym oszacowaniem. Możesz więc utworzyć filtrowane statystyki dla userId na podstawie nazwy użytkownika, a następnie SQL Server powinien wiedzieć, że gdy ta nazwa użytkownika znajdzie się w zapytaniu, otrzyma to identyfikator użytkownika i powinien być w stanie dowiedzieć się, że indeksowane pole w tabeli postów będzie miało ogromną liczbę wierszy o tym identyfikatorze, ponieważ istnieje tam histogram. W przypadku średnich nie można tego zrobić.

Od 70-461 Książka szkoleniowa Itzika Ben-Gana

Istnieje tylko kilka możliwych powodów ręcznego tworzenia statystyk. Jednym z przykładów jest sytuacja, gdy predykat zapytania zawiera wiele kolumn, które mają relacje między kolumnami; statystyki wielu kolumn mogą pomóc ulepszyć plan zapytań. Statystyki dla wielu kolumn zawierają gęstości międzykolumnowe, które nie są dostępne w statystykach dla jednej kolumny. Jeśli jednak kolumny są już w tym samym indeksie, obiekt statystyk wielokolumnowych już istnieje, więc nie należy ręcznie tworzyć dodatkowego.