W tym artykule przyjrzymy się popularnym narzędziom, zapytaniom T-SQL i skryptom do wykrywania i rozwiązywania różnych możliwych problemów z wydajnością SQL Server. Ten artykuł pomoże Ci dowiedzieć się, kiedy Twój SQL Server ma niewystarczające zasoby (pamięć, procesor, procesory IOP dysków), znaleźć blokady i wykryć wolne zapytania. Zobaczmy, jakie są wbudowane narzędzia oraz bezpłatne skrypty i narzędzia innych firm do analizy stanu Microsoft SQL Server.
Treść
- Narzędzia diagnostyczne programu SQL Server
- Wykrywanie i rozwiązywanie problemów z wydajnością programu SQL Server
Narzędzia diagnostyczne programu SQL Server
Jeśli poprawnie zdiagnozowałeś problem, połowa pracy została już wykonana. Zastanów się, jakie narzędzia są często używane przez administratora systemu do diagnozowania różnych problemów w SQL Server:
- T-sql - Najbardziej wydajne, proste i niezbędne narzędzie do rozwiązywania problemów i analizowania wydajności programu SQL Server. Prawie wszystkie inne narzędzia do pracy z SQL Server używają T-SQL. Nic nie można zrobić z T-SQL.
- SQL Server Management Studio - Bez SSMS praca z programem SQL Server jest prawie niemożliwa. Za pomocą SSMS możesz spojrzeć na Monitor aktywności, przeanalizować plan zapytań, spojrzeć na parametry serwera lub bazy danych i wiele innych rzeczy.
- Dzienniki błędów SQL Server i Windows - jeśli coś pójdzie nie tak, dziennik błędów jest pierwszym miejscem, w którym wygląda administrator systemu. Dziennik błędów SQL Server można przeglądać przez SSMS. Dzienniki systemu Windows można przeglądać za pomocą przystawki eventvwr.msc.
- Monitor zasobów Windows - resmon.exe to niezbędne narzędzie systemu Windows do szybkiej oceny stanu zasobów serwera. Wykorzystanie pamięci RAM i procesora można wyświetlić za pomocą Menedżera zadań, ale szczegółowe wykorzystanie sieci i dysku twardego można wyświetlić tylko za pomocą resmon i perfmon.
- Monitor systemu Windows (Monitor wydajności) - Perfmon.exe jest głównym narzędziem do monitorowania systemu Windows, zawiera wiele „liczników”, zarówno wskaźników systemowych, jak i aplikacji, w tym SQL Server. Zazwyczaj liczniki perfmon są przetwarzane przy użyciu innych systemów monitorowania, na przykład Zabbix, ponieważ w perfmon przechowywanie i przeglądanie danych przez upływ czasu jest niewygodne.
- Aplikacje innych firm - Istnieje wiele płatnych i bezpłatnych aplikacji monitorujących SQL Server. Na przykład jedna z bezpłatnych aplikacji to dbForge Monitor od firmy Devart. Aplikacja jest instalowana jako dodatek do SSMS i umożliwia wyświetlanie bardzo wygodnego pulpitu nawigacyjnego do wyświetlania aktualnego stanu serwera SQL Server (informacje o zużyciu pamięci, procesorze, ładowaniach, blokadach, procesach, informacje o kopiach zapasowych, „ciężkich zapytaniach SQL”, wydajności podsystemu dyskowego itp. .).
- Skrypty Brentozar - Jest to popularne rozwiązanie do diagnozowania ustawień i kondycji SQL Server. Brentozar ma wiele skryptów do różnych zadań, ale do diagnostyki interesuje nas „sp_blitz”. Możesz pobrać go bezpłatnie z oficjalnej strony internetowej https://www.brentozar.com/blitz/. Uruchom sp_Blitz.sql, aby zainstalować niezbędne procedury i je wykonać. exec sp_blitz do diagnozy. To narzędzie jest bezpłatne i obsługiwane przez społeczność SQL Server. Sp_blitz zidentyfikuje wszystkie popularne problemy z Twoim serwerem i doradzi, jak je rozwiązać..
- Zestawy skryptów T-SQL - wygodnie jest mieć pod ręką kolekcje różnych zapytań T-SQL do diagnostyki SQL Server, ponieważ nie zawsze jest czas na pisanie własnych zapytań, lepiej uzbroić się wcześniej. Poniżej znajdują się łącza do przydatnych zapytań T-SQL / PowerShell, których często używam podczas diagnozowania i dostrajania MS SQL:
- https://github.com/SQLadmin/AwesomeSQLServer - zestaw zapytań do monitorowania IO procesora / pamięci RAM / dysku i innych parametrów.
- https://github.com/dgavrikov/databases_scripts/tree/master/SQL%20Server - wiele przydatnych skryptów i life hacków
- https://github.com/ktaranov/sqlserver-kit - Skrypty i przydatne informacje. Wiele przykładów pracy z programem SQL Server za pomocą programu PowerShell
Wykrywanie i rozwiązywanie problemów z wydajnością programu SQL Server
Najczęstszym problemem, z którym boryka się administrator systemu współpracujący z SQL Server, są skargi użytkowników dotyczące wydajności zapytań i samego serwera: „spowalnia”, „zapytanie zajmuje dużo czasu” itd..
Przede wszystkim musisz upewnić się, że serwer ma wystarczającą ilość zasobów. Zobaczmy, jak szybko przeanalizować wykorzystanie pamięci, procesora, dysków i blokad w programie SQL Server.
Analiza użycia pamięci RAM serwera SQL
Najpierw musisz ustalić, ile pamięci jest dostępne w SQL Server. Aby to zrobić, uruchom SSMS (SQL Server Management Studio), przejdź do serwera i przejdź do właściwości serwera (RMB według nazwy serwera w przeglądarce obiektów).
Dostępna pojemność pamięci RAM sama niczego nie powie. Musisz porównać tę liczbę z pamięcią używaną w Menedżerze zadań i samym silniku SQL Server za pomocą DMV.
W Menedżerze zadań na karcie Szczegóły znajdź plik sqlservr.exe i sprawdź, ile pamięci RAM zużywa ten proces.
- Jeśli na przykład serwer ma 128 GB pamięci RAM, a proces sqlservr.exe używa 60 GB, a SQL Server nie ma limitów pamięci RAM, oznacza to, że masz wystarczającą ilość pamięci RAM.
- Jeśli SQL Server zużywa 80–90% ustawionej lub maksymalnej pamięci RAM, musisz obejrzeć DMV. Należy pamiętać, że sqlservr.exe nie będzie w stanie wykorzystać całej pamięci RAM. Jeśli serwer ma 128 GB, program sqlservr.exe może zużywać tylko 80–90% (100–110 GB), ponieważ pozostała część pamięci jest zarezerwowana dla systemu operacyjnego.
Należy pamiętać, że proces programu SQL Server nie wysyła pamięci RAM z powrotem do systemu. Na przykład SQL Server zwykle zużywa 20 GB pamięci, ale w przypadku raportu miesięcznego zwiększa zużycie do 100 GB, a nawet po zakończeniu obliczeń raportu i pracy serwera w poprzednim trybie proces SQL Server będzie nadal zużywał 100 GB do momentu ponownego uruchomienia usługi.
Nawet jeśli masz pewność, że serwer ma wystarczającą ilość pamięci RAM, nie będzie zbędne wiedzieć dokładnie, ile pamięci RAM zostało zużyte..
Możesz dowiedzieć się, jakie jest rzeczywiste użycie pamięci RAM Dynamiczne widoki zarządzania. DMV są przeglądarkami administracyjnymi. Dzięki DMV możesz zdiagnozować prawie każdy problem w SQL Server.
Zobaczmy sys.dm_os_sys_memory, dla wygody korzystamy z żądania:
SELECT total_physical_memory_kb / 1024 AS [Total Physical Memory], available_physical_memory_kb / 1024 AS [Dostępna pamięć fizyczna], total_page_file_kb / 1024 AS [Total Page File (MB)], available_page_file_kb / 1024 AS [Dostępny plik strony (MB)], 100 - ( 100 * Cast (available_physical_memory_kb AS DECIMAL (18, 3)) / Cast (total_physical_memory_kb AS DECIMAL (18, 3))) AS „Procent wykorzystany”, system_memory_state_desc AS [Stan pamięci] OD sys.dm_os_sys_memory;
Rozważ każdy parametr wyjściowy:
- [Całkowita pamięć fizyczna] - ilość pamięci RAM dostępnej w systemie operacyjnym. Na niektórych serwerach może pokazywać nieco więcej niż faktycznie zainstalowany.
- [Dostępna pamięć fizyczna] - ilość pamięci RAM dostępnej dla programu SQL Server, z wyjątkiem SQL Server już przechwyconego.
- [Plik strony ogółem (MB)] - „Limit zatwierdzania” woluminu. Commit Limit = RAM + wszystkie pliki wymiany. Oznacza to, że jeśli masz 32 GB pamięci RAM i 16 GB pliku stronicowania na serwerze, limit zatwierdzeń wyniesie 48 GB.
- [Dostępny plik strony (MB)] - Rozmiar pliku stronicowania.
- Wykorzystany procent - procent wykorzystanej pamięci RAM. Nie ma takiego parametru w samej sys.dm_os_sys_memory, ale oblicza się go za pomocą formuły available_physical_memory_kb / total_physical_memory_kb
- [Stan pamięci] - Status pamięci RAM. Pole system_memory_state_desc zawiera stan zużycia pamięci RAM w formie tekstu. Wartość tego pola jest rozpatrywana na podstawie dwóch pozostałych: system_low_memory_signal_state i system_high_memory_signal_state. Możesz wybrać je bezpośrednio, jeśli potrzebujesz logicznego / bitowego formatu danych. Wszystkie pola sys.dm_os_sys_memory znajdują się w dokumentacji https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact -sql? view = sql-server-ver15
Wszystkie te dane są przydatne, jeśli chcesz dokładnie określić, ile SQL Server zużywa pamięć RAM. Jest to najczęściej używane, jeśli istnieje podejrzenie, że dla instancji przydzielono zbyt dużo pamięci RAM..
Jeśli musisz się upewnić, że serwer ma wystarczającą ilość pamięci RAM, możesz spojrzeć tylko na pola system_low_memory_signal_state, system_high_memory_signal_state i system_memory_state_desc. Jeśli system_low_memory_signal_state = 1, serwer oczywiście nie ma wystarczającej ilości pamięci RAM.
Wykorzystanie procesora w programie SQL Server
Obciążenie procesora jest łatwiejsze do ustalenia, ponieważ można to zrobić w Menedżerze zadań. Aby sprawdzić bieżące obciążenie procesora, znajdź proces sqlservr.exe w Menedżerze zadań
Jeśli chcesz poznać obciążenie z przeszłości, możesz użyć zapytania:
Nie zapomnij zmienić @lastNMin na potrzebny numer w ciągu kilku minut.ZADEKLARUJ @ts BIGINT; ZADEKLARUJ @lastNmin TINYINT; SET @lastNmin = 30; SELECT @ts = (SELECT cpu_ticks / (cpu_ticks / ms_ticks) FROM sys.dm_os_sys_info); WYBIERZ TOP (@lastNmin) Dateadd (ms, -1 * (@ts - [timestamp]), Getdate ()) AS [EventTime], sqlprocessutilization AS [SQL Server Utilization], 100 - systemidle - sqlprocessutilization AS [Other Process CPU_Utilization] , systemidle AS [System Idle] FROM (SELECT record.value ('(./ Record / @ id) [1]', 'int') AS record_id, record.value ('(./ Record / SchedulerMonitorEvent / SystemHealth / SystemIdle ) [1] ',' int ') AS [SystemIdle], record.value (' (./ Record / SchedulerMonitorEvent / SystemHealth / ProcessUtilization) [1] ',' int ') AS [SQLProcessUtilization], [sygnatura czasowa] FROM ( WYBIERZ [znacznik czasu], KONWERSJA (XML, rekord) JAKO [rekord] OD sys.dm_os_ring_buffers GDZIE typ_buforu = N'RING_BUFFER_SCHEDULER_MONITOR 'I NAPISZ JAK' %% ') AS x) JAKO Y ZAMÓWIENIE według record_id DESC;
W rezultacie otrzymujemy statystyki na minutę wykorzystania procesora.
Analiza obciążenia dysku programu SQL Server
Spójrzmy na ładowanie dysków w systemie operacyjnym. Aby to zrobić, uruchom resmon.exe. 
Potrzebujemy zakładki Dysk. W dziale Aktywność dysku wyświetlane są pliki, do których uzyskiwany jest dostęp, oraz ich prędkość odczytu / zapisu w danym momencie. Filtruj tę sekcję według Suma (kliknij Suma). Na samej górze będą pliki, które obecnie najlepiej wykorzystują dysk. W przypadku SQL Server może to być przydatne do ustalenia, która baza danych aktualnie najbardziej ładuje dysk..
Sekcja Pamięć wyświetla wszystkie dyski w systemie. W tej sekcji potrzebujemy 2 parametrów - Czas aktywności i Kolejka dysków. Czas aktywności jako procent wyświetla obciążenie dysku, to znaczy, jeśli widzisz na dysku C: \ Active Time wartość równą 90, oznacza to, że zasób odczytu / zapisu dysku jest obecnie używany w 90%. Kolumna Kolejka dysków wyświetla kolejkę dostępu do dysku, a jeśli wartość kolejki nie jest równa zero, dysk jest w 100% załadowany i nie może obsłużyć obciążenia. Ponadto, jeśli czas aktywności jest bliski 100, dysk jest używany prawie na granicy swoich możliwości prędkości.
Zobacz zamki w SQL Server
Po upewnieniu się, że serwer ma wystarczającą ilość zasobów, możemy przejść do przeglądania blokad.
Zamki można wyświetlać za pośrednictwem Monitora aktywności w SSMS, ale użyjemy T-SQL, ponieważ ta opcja jest wygodniejsza i bardziej wizualna. Realizujemy zapytanie:
USTAW NOCOUNT NA GO WYBIERZ SPID, ZABLOKOWANY, WYMIANA (WYMIANA (T.TEKST, ZNAK (10), "), CHAR (13),") JAKO SERIĘ DO #T OD sys.sysprocesses R ZASTOSUJ KRZYŻ sys.dm_exec_sql_text (R. SQL_HANDLE) T GO WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH) AS (SELECT SPID, BLOCKED, CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) JAKO POZIOM, SERIA OD #TR GDZIE (ZABLOKOWANE = 0 LUB ZABLOKOWANE = SPID) I ISTNIEJE (WYBIERZ * Z #T R2 GDZIE R2.BLOCKED = R.SPID I R2.BLOCKED R2.SPID) UNION ALL SELECT R.SPID, R.BLOCKED, CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL, R.BATCH FROM #T AS R WEWNĘTRZNE DOŁĄCZ BLOKERY NA R. ZABLOKOWANE = BLOKOWANE. PID GDZIE R. ZABLOKOWANE> 0 ORA R. LEN (POZIOM) / 4 - 1) = 0 NASTĘPNIE „GŁOWA -„ ELSE ”| ------” KONIEC + CAST (SPID JAKO NVARCHAR (10)) + N ”+ SERIA JAKO DRZEWO BLOKOWANE ZAMÓWIENIE POZIOMU ASC GO DROP TABLE #T GO To zapytanie zwraca listę blokad w postaci drzewa. Jest to wygodne w działaniu, ponieważ zwykle, jeśli nastąpi jedna blokada, prowokuje inne. Podobnie w Monitorze aktywności lub w wynikach sp_who2 widać pole „Zablokowane przez”.
Jeśli żądanie niczego nie zwróciło, nie ma żadnych blokad.
Jeśli żądanie zwróciło niektóre dane, musisz przeanalizować łańcuch.
HEAD oznacza, że to żądanie jest przyczyną wszystkich innych zamków pod drzewem. 64 to identyfikator procesu (SPID). Następnie zapisywana jest treść żądania, co spowodowało blokadę. Jeśli masz wystarczającą ilość zasobów serwera, najprawdopodobniej chodzi o samą prośbę i wzajemne odwołanie do niektórych obiektów. Aby być bardziej precyzyjnym, musisz przeanalizować konkretne żądanie, które spowodowało blokadę.
Zasady programu SQL Server
Nawet jeśli wszystko działa dobrze i nie ma żadnych skarg, w rzeczywistości może być wiele problemów, które pojawią się później. SQL Server ma do tego zasady..
Zasadą w SQL Server jest, z grubsza mówiąc, sprawdzanie reguły pod kątem zgodności z daną wartością. Na przykład za pomocą zasad można upewnić się, że funkcja automatycznego zmniejszania jest wyłączona we wszystkich bazach danych na serwerze. Rozważ przykład wykonania importu i polityki
W SSMS połącz się z serwerem, na którym chcesz wykonywać polityki (Zarządzanie -> sekcja Zarządzanie polityką).
Zaimportuj plik Auto Shrink.xml bazy danych. Kliknij Oceń
Istnieją dwie bazy danych na instancji node1, test1 i test2. Automatyczne testowanie jest włączone w teście2. Zobaczmy szczegóły. 
Polityka określiła dołączony parametr AutoShrink, w opisie zwykle zapisywane są objaśnienia do reguł. W takim przypadku podano wyjaśnienie, dlaczego auto skurcz jest lepszy..
Polisy można realizować zgodnie z harmonogramem lub na żądanie (jednorazowo). Wyniki wykonania zasad można znaleźć w dzienniku zasad..
Podczas instalowania programu SQL Server należy wybrać tylko używane komponenty DBMS i określić ustawienia zgodnie z konfiguracją sprzętową serwera. Zawsze upewnij się, że serwer ma wystarczającą ilość zasobów i że nie ma żadnych blokad na serwerze
Najpotężniejszym narzędziem diagnostycznym SQL Server jest T-SQL i DMV. Zaleca się również budowanie całodobowego monitorowania programu SQL Server i jego infrastruktury w celu wykrycia wszystkich możliwych problemów..
