Bieganie, sport, który stał się bardzo popularny, nie ma, co się dziwić. Dostarcza sporą dawkę endorfin. Dzięki temu paradoksalnie mimo zmęczenia czujemy się pełni energii. To uczucie wielu ludzi nakręcą, biegają i częściej. Takie podejście może powodować pewne kłopoty bo więcej nie znaczy lepiej.
Pewnie czytasz i się zastanawiasz, co ma bieganie do baz danych? Bo raczej nie o bieganiu wpisu się spodziewasz. Odpowiadam, z bazami jest trochę jak z bieganiem. Przesadzając z ilością treningów możemy doprowadzić do gorszych rezultatów i samopoczucia, przesadzając z indeksami możemy doprowadzić do spadku wydajności. Żeby móc to jednak stwierdzić, niezbędne są dane, które trzeba zbierać i analizować.
Pomiary i ich gromadzenie
W sporcie kwestią pomiaru i ich zbierania musimy zająć się sami, z SQL Server jest prościej, bo pewne rzeczy robi za nas. Wszelkie statystki użycia dostarczane są przez dynamiczny widok sys.dm_db_index_usage_stats , którym gromadzi na prawdę sporą ilość danych. Jednak w tym wpisie skupie się na metrykach związanych z operacjami:
- seeks – przeszukiwanie indeksów w celu pobrania wierszy z klastrowych lub nieklastrowych indeksów. W bazach OLTP oczekujemy jak największej liczby wyszukań na indeksach z jednego prostego powodu, wyszukiwanie jest mniej kosztowne.
- scans – skanowanie wykonuję pobranie wszystkich wierszy z indeksu w celu zwrócenie wyniku. Skanowanie jest efektywne głównie dla małych tabel.
- lookups – występuję w sytuacji gdy zapytanie korzysta z indeksu nieklastrowego które nie zawiera wszystkich danych z klazuli SELECT. SQL Server zmuszony jest sięgnąć do indeksu klastrowego po tzw KeyID lub do sterty po tzw Row ID, w takiej sytuacji wykonywana jest w operacja pętli Lookup. Tego typu operacje nie są porządane i jednym z sposobów jest tworzenie indeksu pokrywającego.
- updates – wstawianie nowych danych, modyfikowanie istniejących
Te cztery metryki gromadzone są dla operacji systemowych (system) i użytkownika (user). Jak można domyślić się, wartości dla users związane są z operacjami na indeksach niezbędnych to wykonania poleceń użytkownika: pobranie, modyfikacje, wstawianie danych. Natomiast wartości dla system_seeks, system_scans i tak dalej, zliczane są przy operacjach na indeksach związanych z ich utrzymaniem, np. przebudowa indeksów oraz odświeżania statystyk.
Zobaczmy jak to wygląda w praktyce. W celach demonstracji ograniczyłem się tylko do indeksów jednej tabeli z bazy AdventureWorks2012. Poniższe zapytanie pozwala wykonać migawkę widoku przed i po wykonanym zapytaniu. To po to, aby móc wyłapać zmiany liczników dla poszczególnych operacji.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
--Migawka przed SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, s.user_seeks, s.last_user_seek, s.user_scans, s.user_updates, s.user_lookups, s.system_seeks, s.system_scans, s.system_updates, s.system_lookups INTO #temp1 FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats s ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID WHERE OBJECT_NAME(i.object_id) = '<nazwa tabeli'; --Początek polecenia <tutaj> --Koniec polecenia --Migawka po SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, s.user_seeks, s.user_scans, s.user_updates, s.user_lookups, s.system_seeks, s.system_scans, s.system_updates, s.system_lookups INTO #temp2 FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats s ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID WHERE OBJECT_NAME(i.object_id) = '<nazwa tabeli>'; --Różnica pomiędzy migawkami SELECT t1.table_name, t1.index_name, t2.user_seeks - t1.user_seeks AS user_seeks, t2.user_scans - t1.user_scans AS user_scans, t2.user_updates - t1.user_updates AS user_updates, t2.user_lookups - t1.user_lookups AS user_lookups, t2.system_seeks - t1.system_seeks AS system_seeks, t2.system_scans - t1.system_scans AS system_scans, t2.system_updates - t1.system_updates AS system_updates, t2.system_lookups - t1.system_lookups AS system_lookups FROM #temp1 t1 JOIN #temp2 t2 ON t1.index_name = t2.index_name; GO --Drop migawek DROP TABLE #temp1 DROP TABLE #temp2 |
W tym miejscu warto dodać, że restart instancji SQL Server czyści widok
sys.dm_db_index_usage_stats.
Operacje użytkownika na indeksach
Mało selektywne pobranie danych po kluczu z indeksu klastrowego kończy się skanowaniem indeksu co widac na poniżej.
|
1 2 3 |
SELECT * FROM [AdventureWorks2012].[Sales].[SalesOrderDetail] WHERE SalesOrderDetailID >= 13690 and SalesOrderDetailID <= 43760; GO |
Kolejny przykład, gdzie oczekujemy przeszukania indeksu nieklastrowego. Faktycznie ma to miejsce, jednak z również z mało pożądaną operacją Nested Loop. Jest to niezbędne w celu pobrania brakujących wartości z indeksu klastrowego które nie byłby dostępne w indeksie nieklastrowym.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], [ModifiedDate] FROM [AdventureWorks2012].[Sales].[SalesOrderDetail] WHERE ProductID = 841; |
Były polecenia pobrania danych, to kolej na INSERT. Dodanie jednego rekordu spowoduję operację user_updates na wszystkich indeksach w tej konkretnej tabeli.
|
1 2 3 4 |
INSERT INTO [AdventureWorks2012].[Sales].[SalesOrderDetail] (SalesOrderID, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate) VALUES (52229, 1, 934, 1, '29,99', '0,00', (SELECT NEWID()), '2018-06-06 00:00:00.000'); GO |
Jeszcze ciekawiej wygląda to dla poleceń DELETE. Usunięcie danych powoduję jedną operację więcej. Mowa o opracji user_seeks na klastrowym indeksie niezbędna do znalezienia usuwanego rekordu oraz 3 operacje users_updates związane z naniesieniem zmian na indeksach tak jak przy dodawaniu danych.
|
1 2 |
DELETE [AdventureWorks2012].[Sales].[SalesOrderDetail] WHERE SalesOrderID = 46630 GO |
Było wstawianie, usuwanie danych, zostało tylko modyfikowanie. Podobnie jak przy operacji usunięcia, w pierwszym kroku niezbędne jest pobierane rekordu stąd wyszukiwanie na indeksie klastrowym. Przykładowy UPDATE zmienia dwie wartości. Ta na indeksie klastrowym raczej nie wymaga tłumaczenia. Druga jednak wynika zmian na kolumnie ROWGUID, która jest kolumną indeksu nieklastrowego. W ten sposób SQL Server wykonał 3 operacje na indeksach.
|
1 2 3 4 |
UPDATE [AdventureWorks2012].[Sales].[SalesOrderDetail] SET UnitPrice = UnitPrice * 1.5, ROWGUID = (SELECT NEWID()) WHERE SalesOrderID = 52229 |
Operacje systemowe na indeksach
Tak jak wspomniałem wyżej, operacje związane z utrzymaniem indeksów gromadzone są do kolumn z prefiksem system. Operacje przebudowy indeksu wiążą się z pełnym skanowaniem indeksów.
|
1 2 |
ALTER INDEX uidx_GadgetType ON Examples.GadgetType REBUILD GO |
Update statystyk również wykonuję skanowania indeksów, co widać po zwiększonych licznikach w kolumnie system_scans.
|
1 2 3 4 5 |
UPDATE STATISTICS [Examples].[GadgetType] WITH SAMPLE 10 PERCENT; GO --lub UPDATE STATISTICS [Examples].[GadgetType] WITH FULLSCAN; GO |
Jako ciekawostkę dodam, że nie udało mi się zmusić SQL Server do zwiększenia wartości dla systemowych operacji w kolumnach innych niż wyżej wspomniany system_scans.
Podsumowanie
Wyżej pokazałem gdzie i kiedy SQL Server gromadzi dane na temat użycia indeksów i jak to wygląda dla różnych operacji. Mimo małej ilości indeksów w przykładowych tabelach, już można przypuszczać, że z wzrostem ilości indeksów nasze operacje DML staną się bardziej kosztowne. Jedna operacja wstawienia pociąga w tle operacje naniesienia zmian niekiedy na wszystkich indeksach dla tabeli.
Stąd tak ważne jest trzymanie w ryzach ilość indeksów w bazach danych nie tylko z względu na ilość zajmowanego miejsca, ale również z względu na wpływ na wydajność. Cykliczne przyglądanie widoku sys.dm_db_index_usage_stats jest jak najbardziej wskazane. Jeśli widzimy, indeksy, na których nie mamy żadnych operacji user_seeks, user_scans a dużą liczbę user_updates to jest to najprawdopodobniej idealny kandydat do usunięcia. Piszę najprawdopodobniej, ponieważ może się okazać, że jest to indeks używany rzadko a nasz serwer był restartowany.
Jak sobie z tym poradzić i jak usuwać indeksy, aby było po tym więcej korzyści niż problemów napiszę wkrótce. Jeśli masz pytania, spostrzeżenia lub swoje doświadczenia w tym temacie, zapraszam do komentowania bądź pisania na maila. Tymczasem idę pobiegać 🙂
Z pasją poświęcam czas na zdobywanie wiedzy w zakresie szeroko rozumianej Data Platform. Zachwycony językiem skryptowym Windows PowerShell. Swoją wiedzę, doświadczenia i spostrzeżenia opisuję na blogu.