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.

--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

--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;

--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.

SELECT * FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]
WHERE SalesOrderDetailID >= 13690 and SalesOrderDetailID <= 43760;
GO

SELECT * FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]

WHERE SalesOrderDetailID >= 13690 and SalesOrderDetailID <= 43760;

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.

SELECT [SalesOrderID],
       [SalesOrderDetailID],
       [CarrierTrackingNumber],
       [OrderQty],
       [ProductID],
       [SpecialOfferID],
       [UnitPrice],
       [UnitPriceDiscount],
       [ModifiedDate]
FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]
WHERE ProductID = 841;

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.

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

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');

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.

DELETE [AdventureWorks2012].[Sales].[SalesOrderDetail] WHERE SalesOrderID = 46630
GO

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.

UPDATE [AdventureWorks2012].[Sales].[SalesOrderDetail] SET 
UnitPrice = UnitPrice * 1.5, 
ROWGUID = (SELECT NEWID()) 
WHERE  SalesOrderID = 52229

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.

ALTER INDEX uidx_GadgetType ON Examples.GadgetType REBUILD
GO

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.

UPDATE STATISTICS [Examples].[GadgetType] WITH SAMPLE 10 PERCENT;
GO
--lub
UPDATE STATISTICS [Examples].[GadgetType] WITH FULLSCAN;
GO

UPDATE STATISTICS [Examples].[GadgetType] WITH SAMPLE 10 PERCENT;

--lub

UPDATE STATISTICS [Examples].[GadgetType] WITH FULLSCAN;

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ć 🙂

Tags: indeksy Index scans seeks SQL Server sys.dm_db_index_usage_stats

Mateusz Nadobnik

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.

Sprawdź, co robią Twoje indeksy – sys.dm_db_index_usage_stats

Pomiary i ich gromadzenie

Operacje użytkownika na indeksach

Operacje systemowe na indeksach

Podsumowanie

Mateusz Nadobnik

LEAVE A REPLY Anuluj pisanie odpowiedzi

Sprawdź, co robią Twoje indeksy – sys.dm_db_index_usage_stats

Darmowy ebook “Zacznij z PowerShell”

Pomiary i ich gromadzenie

Operacje użytkownika na indeksach

Operacje systemowe na indeksach

Podsumowanie

Mateusz Nadobnik

You may also like

O sprawności i testach kopii zapasowej

Wszystko co warto wiedzieć o Transparent Data Encryption

[cz. II] Always Encrypted ? implementacja ? SQL Server Security

Darmowy ebook “Zacznij z PowerShell”

LEAVE A REPLY Anuluj pisanie odpowiedzi