Kurs online „Podstawy APM”: optymalizacja wydajności serwera SQL

Optymalizacja bufora bufora

Dlaczego optymalizacja pamięci podręcznej jest tak ważna? Tak, ponieważ podczas wykonywania jakichkolwiek zapytań serwer SQL odczytuje dane nie z podsystemu dysku, ale z pamięci podręcznej bufora. Nawet jeśli nie ma tam niezbędnych danych, najpierw zostaną załadowane do pamięci podręcznej, a następnie SQL zabierze je z tego miejsca, a zapytanie będzie czekać cały czas.

Aktualizacje i usuwanie są również wykonywane w pamięci podręcznej, a jakiś czas później zostaną pobrane na dysk. Ten mechanizm umożliwia serwerowi SQL optymalizację operacji we / wy:

• Wiele stron może być odczytywanych lub zapisywanych dla jednego dostępu do dysku.
• Serwer może zauważyć, że do pewnych operacji wymagane są kolejne strony, i zakładamy, że po żądaniu jednego z nich następny adres zostanie wysłany na sąsiednie strony i załaduje je wstępnie do bufora.

Istnieją dwa wskaźniki pamięci podręcznej bufora:

MSSQL $ Instance: Buffer Manager Współczynnik trafień bufora buforowego - stosunek żądanych stron znalezionych w pamięci podręcznej do stron, których tam nie było (i które musiały zostać pobrane z dysku). Im większy jest ten stosunek, tym bardziej wydajna jest pamięć podręczna. Pamiętaj jednak, że nawet przy dużym współczynniku może wystąpić efekt rzucania pamięci podręcznej.

Instancja MSSQL $: Menedżer buforów Długość życia strony - czas życia stron w pamięci podręcznej. Uważa się, że jeśli ten wskaźnik jest dłuższy niż pięć minut, jest to normalne. Jeśli wartość jest niższa, oznacza to, że jest za mało pamięci lub istnieje przeciążenie pamięci podręcznej.

Przeciążenie pamięci podręcznej często występuje podczas skanowania dużego stołu lub indeksu. Powód jest prosty: każda strona skanu musi przechodzić przez pamięć podręczną, a wiele przydatnych stron można wyładować, aby pasowały do ​​stron, do których nikt nie będzie się zwracał. Powoduje to poważne obciążenie operacji odczytu i zapisu, ponieważ użyteczne strony zostaną ponownie załadowane z dysku do pamięci podręcznej przy następnym żądaniu.

Jeśli po przeanalizowaniu liczników istnieje podejrzenie przeciążenia pamięci podręcznej, możesz spróbować zoptymalizować pracę z pamięcią podręczną. Aby zrozumieć, które tabele i indeksy zajmują dużo miejsca w pamięci podręcznej, możesz odwołać się do sys.dm_os_buffer_descriptors .

SELECT o.name, i.name, bd. *
FROM sys.dm_os_buffer_descriptors bd
INNER JOIN sys.allocation_units a
ON bd.allocation_unit_id = a.allocation_unit_id
INNER JOIN
sys.partitions p
ON (a.container_id = p.hobt_id I a.type IN (1, 3))
OR (a.container_id = p.partition_id AND a.type = 2)
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.indexes i
ON p.object_id = i.object_id ORAZ p.index_id = i.index_id

Następnie przeanalizuj, które z nich powodują maksymalne obciążenie wymiany stron z podsystemem dyskowym oraz co można zmienić w bazie danych i zapytania, aby poprawić sytuację.

Wyszukaj „złe” indeksy

Aby określić przydatność indeksów, musisz najpierw zrozumieć, w jaki sposób serwer korzysta z dostępnych indeksów. sys.dm_db_index_operational_stats zawiera informacje o operacjach we / wy niskiego poziomu, blokadach, dostępach dla każdego indeksu. Te informacje można wykorzystać do udzielenia odpowiedzi na pytania:

• Czy są popularne indeksy? Czy jest dla nich walka? Czy oczekuje się zamków?
• Czy używany jest indeks nieefektywnie? Jakie indeksy prowadzą do wąskiego gardła w pracy z wejściem / wyjściem? page_io_latch_wait_ms pokaże, czy oczekiwanie na załadowanie stron indeksu do pamięci podręcznej bufora - dobry wskaźnik skanowania indeksu.
• Jak używane są indeksy, jakie wzorce dostępu są używane?

Za pomocą sys.dm_db_index_usage_stats można dowiedzieć się, ile i jakie operacje zostały zastosowane do indeksu i kiedy ostatnio zastosowano określony typ operacji. Pomoże to odpowiedzieć na pytania:

• Jak użytkownicy pracują z indeksem? Jakie operacje są najczęściej używane?
• Jak jest utrzymywany indeks i jak często jest aktualizowany?
• Kiedy ostatnio używano indeksu i po co?

Za pomocą różnych zapytań możliwe jest uzyskanie i udoskonalenie informacji o indeksach i ostatecznie uzyskanie kandydatów do optymalizacji. Co więcej, możesz zoptymalizować zapytania korzystające z indeksu - począwszy od SQL Server 2005 te informacje są dostępne dla każdego indeksu.

Istnieją różne strategie optymalizacji - właściwa architektura, kombinacja indeksów, zmiana w utrzymaniu. Jest to jeden z najtrudniejszych, ale także najbardziej produktywnych aspektów optymalizacji i jest omawiany bardziej szczegółowo w odpowiednich witrynach i kastach internetowych.

Nie możesz powiedzieć wszystkiego od razu, ale dostarczone informacje powinny dać dobry początek wszelkim inicjatywom w zakresie optymalizacji wydajności serwera SQL.

Zespół ALG DevOps będzie wdzięczny za odpowiedzi na następujące pytania: