Jak dostroić Microsoft SQL Server pod kątem wydajności?

Aby zachować swoich użytkowników, każda aplikacja lub strona internetowa musi działać szybko. W środowiskach o znaczeniu krytycznym kilka milisekund opóźnienia w uzyskaniu informacji może spowodować duże problemy. Ponieważ rozmiary bazy danych rosną z dnia na dzień, musimy pobierać dane tak szybko, jak to możliwe, i jak najszybciej zapisywać je z powrotem do bazy danych. Aby upewnić się, że wszystkie operacje przebiegają płynnie, musimy dostroić nasz serwer bazy danych pod kątem wydajności.

W tym artykule opiszę krok po kroku procedurę podstawowego strojenia wydajności na jednym z najlepszych serwerów bazodanowych na rynku: Microsoft SQL Server (w skrócie SQL Server).

#1 Znalezienie winowajców

Podobnie jak w przypadku każdego innego oprogramowania, musimy zrozumieć, że SQL Server to złożony program komputerowy. Jeśli mamy z tym problem, musimy odkryć, dlaczego nie działa zgodnie z oczekiwaniami.

Z SQL Server musimy pobierać i przesyłać dane tak szybko i dokładnie, jak to możliwe. Jeśli wystąpią problemy, kilka podstawowych powodów i dwie pierwsze rzeczy do sprawdzenia to:

• Ustawienia sprzętu i instalacji, które mogą wymagać korekty, ponieważ potrzeby programu SQL Server są specyficzne
• Jeśli dostarczyliśmy poprawny kod T-SQL do wdrożenia SQL Server

Mimo że SQL Server jest oprogramowaniem własnościowym, firma Microsoft udostępniła wiele sposobów na zrozumienie go i efektywne wykorzystanie.

Jeśli sprzęt jest w porządku, instalacja przebiegła prawidłowo, ale serwer SQL nadal działa wolno, najpierw musimy sprawdzić, czy nie występują błędy związane z oprogramowaniem. Aby sprawdzić, co się dzieje, musimy obserwować, jak radzą sobie różne wątki. Osiąga się to poprzez obliczanie statystyk oczekiwania różnych wątków. SQL Server używa wątków dla każdego żądania użytkownika, a wątek jest niczym innym jak innym programem wewnątrz naszego złożonego programu o nazwie SQL Server. Należy zauważyć, że ten wątek nie jest wątkiem systemu operacyjnego, na którym zainstalowany jest serwer SQL; jest powiązany z wątkiem SQLOS, który jest pseudo systemem operacyjnym dla SQL Server.

Statystyki oczekiwania można obliczyć za pomocą sys.dm_os_wait_stats Widok dynamicznego zarządzania (DMV), który dostarcza dodatkowych informacji o jego aktualnym stanie. Istnieje wiele skryptów online do wysyłania zapytań do tego widoku, ale moim ulubionym jest skrypt Paula Randala, ponieważ jest łatwy do zrozumienia i ma wszystkie ważne parametry do obserwowania statystyk oczekiwania:

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

Kiedy wykonujemy ten skrypt, musimy skoncentrować się na górnych wierszach wyniku, ponieważ są one ustawiane jako pierwsze i reprezentują maksymalny typ oczekiwania.

Musimy zrozumieć typy oczekiwania, aby móc podejmować właściwe decyzje. Aby poznać różne typy oczekiwania, możemy przejść do doskonałej dokumentacji Microsoft.

Weźmy przykład, w którym mamy za dużo PAGEIOLATCH_XX . Oznacza to, że wątek czeka na odczytanie strony danych z dysku do bufora, który jest niczym innym jak blokiem pamięci. Musimy być pewni, że rozumiemy, co się dzieje. Niekoniecznie oznacza to słaby podsystem we/wy lub niewystarczającą ilość pamięci, a zwiększenie podsystemu we/wy i pamięci rozwiąże problem, ale tylko tymczasowo. Aby znaleźć trwałe rozwiązanie, musimy dowiedzieć się, dlaczego z dysku odczytuje się tak dużo danych: Jakie typy poleceń SQL powodują to? Czy czytamy za dużo danych zamiast czytać mniej danych za pomocą filtrów, takich jak klauzule where ? Czy z powodu skanowania tabeli lub indeksu dzieje się zbyt wiele odczytów danych? Czy możemy przekonwertować je na przeszukiwania indeksów, wdrażając lub modyfikując istniejące indeksy? Czy piszemy zapytania SQL, które są źle rozumiane przez Optymalizator SQL (inny program w naszym programie serwera SQL)?

Musimy myśleć z różnych perspektyw i używać różnych przypadków testowych, aby znaleźć rozwiązania. Każdy z powyższych typów oczekiwania wymaga innego rozwiązania. Administrator bazy danych musi je dokładnie zbadać przed podjęciem jakichkolwiek działań. Jednak w większości przypadków znalezienie problematycznych zapytań T-SQL i dostrojenie ich rozwiąże 60 do 70 procent problemów.

#2 Znajdowanie problematycznych zapytań

Jak wspomniano powyżej, pierwszą rzeczą, którą możemy zrobić, jest wyszukanie problematycznych zapytań. Poniższy kod T-SQL znajdzie 20 najgorzej działających zapytań:

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

Musimy być ostrożni z wynikami; chociaż zapytanie może mieć maksymalny średni czas wykonania, jeśli jest uruchamiane tylko raz, łączny wpływ na serwer jest niski w porównaniu z zapytaniem, które ma średni średni czas wykonania i jest uruchamiane wiele razy w ciągu dnia.

#3 Zapytania dotyczące dostrajania

Ważną koncepcją jest dostrojenie zapytania T-SQL. Podstawową rzeczą do zrozumienia jest to, jak dobrze możemy pisać zapytania T-SQL i implementować indeksy, tak aby optymalizator SQL mógł znaleźć zoptymalizowany plan wykonania tego, co oczekiwaliśmy. Z każdą nową wersją SQL Server otrzymujemy bardziej wyrafinowany optymalizator, który zakryje nasze błędy w pisaniu niezoptymalizowanych zapytań SQL, a także naprawi wszelkie błędy związane z poprzednim optymalizatorem. Ale bez względu na to, jak inteligentny może być optymalizator, jeśli nie możemy powiedzieć mu, czego chcemy (pisząc odpowiednie zapytania T-SQL), optymalizator SQL nie będzie w stanie wykonać swojej pracy.

SQL Server wykorzystuje zaawansowane algorytmy wyszukiwania i sortowania. Jeśli jesteśmy dobrzy w wyszukiwaniu i sortowaniu algorytmów, to w większości przypadków możemy odgadnąć, dlaczego SQL Server podejmuje określone działania. Najlepszą książką do nauczenia się więcej i zrozumienia takich algorytmów jest The Art of Computer Programming autorstwa Donalda Knutha.

Kiedy badamy zapytania, które wymagają dopracowania, musimy wykorzystać plan wykonania tych zapytań, abyśmy mogli dowiedzieć się, jak serwer SQL je interpretuje.

Nie mogę tutaj omówić wszystkich aspektów planu wykonania, ale na podstawowym poziomie mogę wyjaśnić, co musimy wziąć pod uwagę.

• Najpierw musimy dowiedzieć się, które operatory pokrywają większość kosztów zapytania.
• Jeśli operator ponosi duże koszty, musimy dowiedzieć się, dlaczego. W większości przypadków skanowanie będzie kosztować więcej niż wyszukiwanie. Musimy zbadać, dlaczego określone skanowanie (skanowanie tabeli lub skanowanie indeksu) ma miejsce zamiast wyszukiwania indeksu. Możemy rozwiązać ten problem, implementując odpowiednie indeksy na kolumnach tabeli, ale tak jak w przypadku każdego złożonego programu, nie ma stałego rozwiązania. Na przykład, jeśli tabela jest mała, skanowanie jest szybsze niż wyszukiwanie.
• Istnieje około 78 operatorów, które reprezentują różne działania i decyzje planu wykonania SQL Server. Musimy je dogłębnie przestudiować, zapoznając się z dokumentacją Microsoft, abyśmy mogli lepiej je zrozumieć i podjąć właściwe działania.

#4 Ponowne wykorzystanie planu wykonania

Nawet jeśli zaimplementujemy odpowiednie indeksy na tabelach i napiszemy dobry kod T-SQL, jeśli plan wykonania nie zostanie ponownie wykorzystany, będziemy mieli problemy z wydajnością. Po dopracowaniu zapytań musimy upewnić się, że plan wykonania może zostać ponownie wykorzystany w razie potrzeby. Większość czasu procesora zostanie poświęcona na obliczenie planu wykonania, który można wyeliminować, jeśli ponownie wykorzystamy plan.

Możemy użyć poniższego zapytania, aby dowiedzieć się, ile razy plan wykonania jest ponownie używany, gdzie usecounts określa, ile razy plan jest ponownie używany:

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

Najlepszym sposobem ponownego wykorzystania planu wykonania jest implementacja sparametryzowanych procedur składowanych. Gdy nie jesteśmy w stanie zaimplementować procedur składowanych, możemy użyć sp_executesql , którego można zamiast tego użyć do wykonania instrukcji T-SQL, gdy jedyną zmianą w instrukcjach SQL są wartości parametrów. SQL Server najprawdopodobniej ponownie wykorzysta plan wykonania wygenerowany podczas pierwszego wykonania.

Podobnie jak w przypadku każdego złożonego programu komputerowego, nie ma stałego rozwiązania. Czasami lepiej skompilować plan od nowa.

Przyjrzyjmy się następującym dwóm przykładowym zapytaniom:

• select name from table where name = 'sri';
• select name from table where name = 'pal';

Załóżmy, że mamy indeks nieklastrowy w kolumnie name i połowa tabeli ma wartość sri , a kilka wierszy ma pal w kolumnie name . W przypadku pierwszego zapytania SQL Server użyje skanowania tabeli, ponieważ połowa tabeli ma te same wartości. Ale w przypadku drugiego zapytania lepiej jest użyć skanowania indeksu, ponieważ tylko kilka wierszy ma wartość pal .

Mimo że zapytania są podobne, ten sam plan wykonania może nie być dobrym rozwiązaniem. W większości przypadków będzie to inna sprawa, więc zanim podejmiemy decyzję, musimy wszystko dokładnie przeanalizować. Jeśli nie chcemy ponownie wykorzystać planu wykonania, zawsze możemy skorzystać z opcji „rekompilacja” w procedurach składowanych.

Należy pamiętać, że nawet po użyciu procedur składowanych lub sp_executesql zdarzają się sytuacje, w których plan wykonania nie zostanie ponownie użyty. Oni są:

• Gdy indeksy używane przez zapytanie zmieniają się lub są usuwane
• Gdy zmieniają się statystyki, struktura lub schemat tabeli używanej przez zapytanie
• Kiedy używamy opcji „rekompiluj”
• W przypadku dużej liczby wstawek, aktualizacji lub usunięć
• Kiedy mieszamy DDL i DML w jednym zapytaniu

#5 Usuwanie niepotrzebnych indeksów

Po dopracowaniu zapytań musimy sprawdzić, jak wykorzystywane są indeksy. Konserwacja indeksu wymaga dużej ilości procesora i we/wy. Za każdym razem, gdy wstawiamy dane do bazy danych, SQL Server również musi aktualizować indeksy, więc lepiej je usunąć, jeśli nie są używane.

Serwer SQL udostępnia nam dm_db_index_usage_stats DMV do wyszukiwania statystyk indeksów. Kiedy uruchamiamy poniższy kod T-SQL, otrzymujemy statystyki użycia dla różnych indeksów. Jeśli znajdziemy indeksy, które w ogóle nie są używane lub używane rzadko, możemy je porzucić, aby zwiększyć wydajność.

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

#6 Instalacja i konfiguracja bazy danych SQL Server

Konfigurując bazę danych, musimy oddzielnie przechowywać dane i pliki dziennika. Głównym tego powodem jest to, że zapisywanie i uzyskiwanie dostępu do plików danych nie jest sekwencyjne, podczas gdy zapisywanie i uzyskiwanie dostępu do plików dziennika jest sekwencyjne. Jeśli umieścimy je na tym samym dysku, nie możemy ich używać w sposób zoptymalizowany.

Kiedy kupujemy Storage Area Network (SAN), sprzedawca może dać nam kilka zaleceń, jak ją skonfigurować, ale ta informacja nie zawsze jest pomocna. Musimy szczegółowo omówić z naszymi specjalistami od sprzętu i sieci, jak przechowywać dane i pliki dziennika oddzielnie iw zoptymalizowany sposób.

#7 Nie przeciążaj SQL Server

Podstawowym zadaniem każdego administratora bazy danych jest upewnienie się, że serwer produkcyjny działa płynnie i jak najlepiej obsługuje klientów. Aby tak się stało, musimy utrzymywać oddzielne bazy danych (jeśli to możliwe, na osobnych maszynach) dla następujących środowisk:

• Produkcja
• Rozwój
• Testowanie
• Analityczny

W przypadku produkcyjnej bazy danych potrzebujemy bazy danych z pełnym trybem odzyskiwania, a dla innych baz wystarczy prosty tryb odzyskiwania.

Testowanie na produkcyjnej bazie danych spowoduje duże obciążenie dziennika transakcji, indeksów, procesora i we/wy. Dlatego musimy używać oddzielnych baz danych do produkcji, rozwoju, testowania i analizowania. Jeśli to możliwe, używaj oddzielnych maszyn dla każdej bazy danych, ponieważ zmniejszy to obciążenie procesora i we/wy.

#8 Dziennik transakcji, tempdb i pamięć

Plik dziennika musi mieć wystarczającą ilość wolnego miejsca do normalnych operacji, ponieważ operacja automatycznego powiększania na pliku dziennika jest czasochłonna i może zmusić inne operacje do oczekiwania na zakończenie. Aby poznać rozmiar pliku dziennika dla każdej bazy danych i ile jest on używany, możemy użyć DBCC SQLPERF(logspace) .

Najlepszym sposobem na skonfigurowanie tempdb jest umieszczenie go na osobnym dysku. Musimy utrzymać rozmiar początkowy na tyle, na ile nas stać, ponieważ gdy dojdzie do sytuacji autowzrostu, wydajność spadnie.

Jak wspomniano wcześniej, musimy upewnić się, że serwer SQL działa na oddzielnej maszynie, najlepiej bez żadnej innej aplikacji. Musimy zachować trochę pamięci dla systemu operacyjnego, a także więcej, jeśli jest on częścią klastra, więc w większości przypadków powinno wystarczyć około 2 GB.

Wniosek:

Omówione tutaj procedury i sugestie dotyczą tylko podstawowego dostrajania wydajności. Jeśli wykonamy te kroki, możemy uzyskać średnio od 40 do 50 procent poprawy wydajności. Aby wykonać zaawansowane dostrajanie wydajności SQL Server, musielibyśmy zagłębić się w każdy z omówionych tutaj kroków.

Dalsza lektura na blogu Toptal Engineering:

• Rozwiązywanie wąskich gardeł za pomocą indeksów i partycji SQL
• Przewodnik po migracji z Oracle do SQL Server i SQL Server do Oracle