SQLShack
Wprowadzenie
W tym artykule omówimy wiele narzędzi, które możemy wykorzystać do monitorowania wydajności SQL Server. SQL Server jest pełen wielu dobrych raportów, które pozwalają DBA szybko zauważyć, czy istnieje jakieś wąskie gardło wydajności serwera SQL. Wiele z nich opiera się na DMV, ale dają nam wizualnie interaktywny sposób patrzenia i pracy z danymi. Zaczniemy od raportów SQL Server Performance Dashboard Reports.
Raporty Dashboard Reports
Skoczmy do SQL Server Management Studio (SSMS) i pierwszą rzeczą, jaką zamierzamy zrobić jest zapoznanie się z dostępnymi raportami dashboardowymi wszystkich poziomów. Znajdziemy je klikając prawym przyciskiem myszy na instancję SQL Servera w Object Explorerze, a z menu kontekstowego wybierzemy Raporty > Raporty standardowe:
Wszystkie raporty pulpitowe są przydatne, Nie będziemy ich wszystkich omawiać, gdyż wymagałoby to dużo czasu i słów, ale nie krępuj się sprawdzić ich wszystkich, kiedy tylko będziesz miał okazję. Aby pokazać przykład, wybierz Server Dashboard z menu kontekstowego. Raport ten dostarcza nam wielu informacji na temat aktualnego stanu instancji SQL Server, w tym jej konfiguracji, wersji, usług i aktywności na niej:
Tutaj mamy również nie domyślne opcje konfiguracyjne. Są to te, które zostały zmienione w stosunku do instalacji waniliowej:
Również, na poziomie bazy danych, jeśli klikniemy prawym przyciskiem myszy na bazę danych, i przejdziemy do raportów, mamy wszelkiego rodzaju raporty użycia dysku, zdarzenia backupu i przywracania, top transakcje, statystyki indeksów, itp.
Więc wszystkie te raporty dashboardowe są świetne, są łatwe do konsumpcji i pracy z nimi.
Monitor aktywności
Następnie przyjrzymy się Monitorowi aktywności, który jest monitorem czasu rzeczywistego w SQL Server, którego możemy użyć do monitorowania wszystkiego, od wydajności, przez koszty wejścia/wyjścia, po kosztowne zapytania, itp. Aby uruchomić Monitor aktywności, kliknij prawym przyciskiem myszy na instancję SQL Server w Object Explorer i z menu kontekstowego wybierz Monitor aktywności. Możesz również uruchomić go z paska narzędzi Standard, klikając na ikonę Monitor aktywności:
Processes – daje nam możliwość przyjrzenia się aktualnie uruchomionym procesom, dzięki czemu możemy nimi zarządzać. Kliknięcie prawym przyciskiem myszy powoduje wyświetlenie menu kontekstowego, z którego możemy je zakończyć, prześledzić w SQL Server Profiler (więcej o nim później), obejrzeć jako plan wykonania, a także wyświetlić szczegóły sesji, co spowoduje wyświetlenie okna pokazującego ostatnią partię poleceń T-SQL:
Resources Waits – pokazuje informacje o oczekiwaniach na zasoby:
Data File I/O – pokazuje bieżące informacje o I/O pliku danych, które dzieją się na poziomie pliku:
Recent/Active Expensive Queries – pokazuje ostatnie/aktywne drogie zapytania, które zużywają wiele zasobów (pamięć, aktywność dyskowa, sieć):
Jest to świetny punkt wyjścia do znalezienia, jakie zapytania powodują problemy, zabierają zbyt wiele zasobów itp. ponieważ raz znalezione, mogą być przeglądane jako plan wykonania, dzięki czemu można łatwo znaleźć gorące punkty:
Top DBA DMVs
Przechodząc dalej, sprawdźmy top DMVs, które każdy DBA powinien znać. Są to DMV, które zawsze powinieneś mieć w kieszeni. Aby uzyskać szybką listę wszystkich DMO (DMVs i DMFs) po prostu spytaj o nie w poniższej partii:
|
1
2
3
4
5
6
7
|
-.- Lista wszystkich DMO (DMVs & DMFs)
SELECT nazwa,
typ,
typ_desc
FROM sys.system_objects so
WHERE so.name LIKE 'dm_%'
ORDER BY so.name;
|
To zwróci wszystkie DMVs & DMFs w SQL Server. Spójrz na kolumnę type i zauważ, że „V” oznacza widok, a „IF” funkcję:
Powyższe zapytanie zwróciło 243 DMO w systemie. Oto lista najbardziej przydatnych z nich:
Wykonanie
sys.dm_exec_connections = Nawiązane połączenie
sys.dm_exec_sessions = Uwierzytelnione sesje
sys.dm_exec_requests = Bieżące żądania
Wykonanie (związane z zapytaniami)
sys.dm_exec_cached_plans = Plany wykonania w pamięci podręcznej
sys.dm_exec_query_plan = Pokaż plan dla danego zbuforowanego planu_handle
sys.dm_exec_query_stats = Statystyki wydajności zapytania
sys.dm_exec_sql_text = Tekst SQL dla danego sql_handle
Indeks
ys.dm_db_index_physical_stats = Rozmiar indeksu i fragmentacja
sys.dm_db_index_usage_stats = Wykorzystanie indeksu przez optymalizator zapytań
sys.dm_db_missing_index_details = Odkryj brakujące indeksy
OS
sys.dm_os_performance_counters = Lista wszystkich liczników wydajności SQL Server i ich wartości
sys.dm_os_schedulers = Wykryj presję CPU
sys.dm_os_waiting_tasks = Zadania oczekujące na zasoby
sys.dm_os_wait_stats = Wszystkie typy i statystyki oczekiwania
I/O
sys.dm_io_virtual_file_stats = Statystyki I/O dla danych i plików dziennika
sys.dm_io_pending_io_requests = Oczekujące żądania I/O
CLR
sys.dm_clr_loaded_assemblies = Załadowane asemblies
sys.dm_clr_tasks = Zadania związane z CLR
Książki online (docs.microsoft.com) mają świetne strony przeglądowe dla wszystkich DMO. Zachęcamy do skopiowania dowolnej nazwy z siatki wyników, wklejenia jej do przeglądarki i wyszukania. Najprawdopodobniej pierwszy artykuł na samej górze będzie pochodził z MS docs i będzie przedstawiał przegląd i zastosowanie T-SQL.
Na sam koniec zerknijmy na SQL Server Profiler, aby stworzyć i odtworzyć ślad. Jest to niezwykle przydatne podczas rozwiązywania problemów, niezależnie od tego, czy pochodzą one z aplikacji, czy z wnętrza bazy danych. Tworzenie śladu pozwala nam na uchwycenie obciążenia roboczego. A jeśli to obciążenie jest tym, które powoduje problemy, to mając możliwość odtworzenia tego problemu, znacznie łatwiej będzie zweryfikować, czy rzeczywiście go naprawiliśmy podczas rozwiązywania problemu.
Niestety Microsoft ogłosił deprecjację SQL Server Profiler, ale funkcja ta jest nadal dostępna w SQL Server 2016 i zostanie usunięta w przyszłych wersjach.
Przejdźmy więc do SQL Server Profiler i zobaczmy, jak działa. Aby uruchomić SQL Server Profiler, uderz w ikonę Windows Start lub naciśnij klawisz Windows i wpisz „SQL Server Profiler 17”:
Pamiętaj, SQL Server Profiler można uruchomić również z poziomu Monitora Aktywności, wystarczy kliknąć na panel Processes, prawym przyciskiem myszy na proces, który chcemy profilować, a następnie z menu kontekstowego prawego przycisku myszy wybrać opcję Trace Process in SQL Server Profiler.
Po uruchomieniu okna należy kliknąć pierwszy przycisk z napisem „New Trace”. Spowoduje to wyświetlenie okna dialogowego połączenia z instancją:
Po nawiązaniu połączenia z instancją, którą chcemy profilować i tworzyć na niej ślady, pojawi się okno Trace Properties. Nadaj ścieżce nazwę w polu Trace name. A ponieważ chcemy odtworzyć ten ślad, wybierz wbudowany szablon śladu, ponieważ istnieje kilka wymagań dotyczących odtworzeń, pewne zdarzenia i pola muszą być przechwytywane, a prostym sposobem na to jest wybranie szablonu TSQL_Replay z listy Użyj szablonu:
Na razie zapisz to jako plik zaznaczając opcję Zapisz do pliku, a następnie zmień zakładkę na Wybór zdarzeń. Ponieważ wybraliśmy szablon, będzie on wstępnie wybierał wiele zdarzeń. Z tego miejsca można bawić się opcjami, pokazywać wszystkie zdarzenia/kolumny, dodawać/usuwać określone zdarzenia, itd.:
W tym przypadku kliknijmy przycisk Filtry kolumn, aby ustawić filtr na nazwę bazy danych. Chcemy wyłapać tylko te zdarzenia, które trafiają do bazy AdventureWorks2014. Jest to świetny sposób na usunięcie wszystkich dodatkowych szumów, które pojawią się w prawdziwym scenariuszu:
Po ustawieniu wszystkiego, kliknij OK w oknie Edycja filtra, aby zapisać ustawienia i zamknąć je. Z powrotem w początkowych oknach, naciśnij przycisk Uruchom, aby rozpocząć śledzenie. Tak więc, ten ślad jest teraz uruchomiony, monitorując wszystko, co dzieje się w bazie danych AdventureWorks2014:
Przełącz SSMS i wykonaj kilka zapytań lub po prostu pozwól mu działać w tle przez chwilę. Różne zdarzenia zostaną przechwycone, nawet jeśli żadne zapytanie użytkownika nie trafi do bazy danych:
Po dłuższym czasie, zatrzymaj śledzenie klikając czerwony przycisk stop, jak pokazano poniżej:
Teraz, gdy mamy już wszystko przechwycone w tym wcześniej zapisanym pliku, możemy na przykład uruchomić tą powtórkę na innej instancji. Dlaczego? Ponieważ może to być niezwykle przydatne, gdy mamy problem na produkcji. Jest bardzo mało prawdopodobne, że jakiekolwiek duże problemy mogą być naprawione bezpośrednio na serwerze produkcyjnym. W tym przypadku, najlepszym podejściem byłoby skopiowanie produkcji do testu, pobranie śladu, który został przechwycony na produkcji, a następnie możemy zrobić wszystko, co chcemy na testowej bazie danych w zakresie rozwiązywania i usuwania problemu.
Aby to zrobić, należy otworzyć ślad w SQL Server Profiler:
Teraz przechodzimy do Replay i wybieramy Start:
To, po raz kolejny, spowoduje wyświetlenie okna dialogowego połączenia SQL Server z instancją:
Które następnie przeniesie nas do okna Replay Configuration, w którym możemy zmienić serwer replay, jeśli potrzebujemy w celu wspomnianym wcześniej. Od tego momentu wszystko jest już całkiem proste. Wystarczy nacisnąć OK, aby odtworzyć ślad:
Pierwszy wiersz zawsze będzie zawierał wszystkie zdarzenia Ustawień Odtwarzania. Następnie w każdej chwili, gdy pojawi się jakieś zdarzenie, będzie ono odpalać to zdarzenie. W zestawie wyników będzie tylko informacja, że zostało wykonane:
Jeśli przewiniemy w prawo, w każdym zestawie wyników będzie znajdował się zmierzony przez profilera czas trwania – ile trwało uruchomienie zdarzenia:
Na samym końcu, znajdują się statystyki odtwarzania, które pokazują całkowitą liczbę zdarzeń, błędy dostawcy/wewnętrzne, które wystąpiły, statystyki współczynnika trafień oraz całkowity czas odtwarzania:
Ponownie, jest to dobre dla scenariuszy, w których występuje problem z wydajnością SQL Server i chcesz odtworzyć problem. W pierwszej kolejności należy uchwycić problem, co da nam możliwość wykonania wszelkich czynności na serwerze SQL w celu rozwiązania problemu i naprawienia go, a następnie odtworzenia problemu i sprawdzenia, czy udało nam się go rozwiązać.
Podsumowanie
W tym artykule omówiliśmy cztery narzędzia do monitorowania wydajności serwera SQL. Mamy nadzieję, że teraz będziesz w stanie zbierać informacje o wydajności i systemie używając Database Reports do interaktywnego, wizualnego wglądu w to, co robi SQL Server. Następnie przyjrzeliśmy się narzędziu do monitorowania wydajności SQL Server w czasie rzeczywistym o nazwie Activity Monitor. Przyjrzeliśmy się również jak używać DMVs, a na sam koniec dowiedzieliśmy się jak używać SQL Server Profiler.
- Autor
- Recent Posts
Bojan pracuje w ApexSQL w Nis, w Serbii, jako integralna część zespołu zajmującego się projektowaniem, rozwojem i testowaniem następnej generacji narzędzi bazodanowych, w tym MySQL i SQL Server, zarówno samodzielnych narzędzi, jak i integracji z Visual Studio, SSMS i VSCode.
See more about Bojan at LinkedIn
View all posts by Bojan Petrovic
- Visual Studio Code for MySQL and MariaDB development – 13 sierpnia, 2020
- Wyjaśnienie składni SQL UPDATE – 10 lipca 2020
- CREATE VIEW SQL: Praca z widokami indeksowanymi w SQL Server – 24 marca 2020