• Articles
• admin

Ostatnio pracowaliśmy nad rozwiązywaniem problemów z wydajnością serwera SQL dla niektórych naszych dużych klientów, którzy używają serwera SQL jako bazy danych. W tym artykule podsumuję, czego nauczyłem się z tego procesu, opisując kilka kroków, które możemy podjąć, aby rozwiązać problem z wydajnością.

sprawdź konfigurację serwera SQL

upewnij się, że serwer bazy danych jest skonfigurowany z wystarczającą ilością zasobów, takich jak liczba rdzeni procesora i ilość pamięci.

aby sprawdzić konfigurację serwera, możesz otworzyć widok „informacje o systemie”:

w celu sprawdzenia ustawień pamięci serwera SQL,

1. Uruchom SQL Server Management Studio.
2. kliknij prawym przyciskiem myszy instancję bazy danych i wybierz „Właściwości”.
3. kliknij tabelę „pamięć” w wyskakującym oknie „Właściwości serwera”.
4. Sprawdź ustawienia pamięci.

upewnij się, że tryb migawki jest włączony

upewnij się, że tryb migawki jest włączony dla bazy danych. Aby zapobiec blokowaniu się serwera SQL, należy włączyć flagę trybu migawki. Uruchom następujące zapytanie, aby sprawdzić, czy flaga jest włączona:

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= '<database_name>'

jeśli zapytanie zwraca „1”, tryb migawki jest już włączony. W przeciwnym razie uruchom następujące zapytanie, aby je włączyć.

ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

podobne: Definiowanie i implementowanie wymagań linie bazowe

Sprawdź indeksy bazy danych

Sprawdź indeksy bazy danych, aby upewnić się, że nie ma brakujących indeksów. Uruchom następujące zapytanie, aby wyświetlić wszystkie indeksy w bazie danych (kredyt: tsql-Lista wszystkich indeksów & kolumny indeksów w SQL Server DB – przepełnienie stosu):

SELECT t.name TableName, col.name ColumnName, ind.name IndexNameFROM sys.indexes indINNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_idINNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_idINNER JOIN sys.tables t ON ind.object_id = t.object_idWHERE (ind.is_primary_key = 0 AND t.is_ms_shipped = 0)ORDER BY t.name, col.name, ind.name

Zapisz wyniki zapytania do pliku tekstowego z kolumnami rozdzielonymi tabulatorami, aby można je było później zaimportować do arkusza kalkulacyjnego.

unikaj fragmentacji

upewnij się, że indeksy bazy danych nie są fragmentowane. Indeksy w bazie danych pomagają przyspieszyć zapytania do bazy danych. Ale kiedy są one fragmentowane, zapytania mogą działać bardzo wolno. W ramach konserwacji bazy danych indeksy powinny być regularnie reorganizowane lub ponownie budowane.

uruchom następujące zapytanie, aby sprawdzić procent fragmentacji dla wszystkich indeksów w bazie danych (kredyt: Jak sprawdzić fragmentację indeksu na indeksach w bazie danych):

SELECT dbschemas.Jama Software AS 'Schema', dbtables.Jama Software AS 'Table', dbindexes.Jama Software AS 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, dbindexes.fill_factorFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstatsINNER JOIN sys.tables dbtables ON dbtables. = indexstats.INNER JOIN sys.schemas dbschemas ON dbtables. = dbschemas.INNER JOIN sys.indexes AS dbindexes ON dbindexes. = indexstats. AND indexstats.index_id = dbindexes.index_idWHERE indexstats.database_id = DB_ID()ORDER BY indexstats.avg_fragmentation_in_percent DESC

oto przykładowy wynik zapytania dla bazy danych o bardzo fragmentarycznych indeksach:

dla indeksów z liczbą stron większą niż 1000, „avg_framentation_in_percent” powinien być utrzymywany poniżej 10%. Nie ma to większego znaczenia dla małych indeksów. Z reguły można zreorganizować indeksy o odsetkach fragmentacji od 5% do 30%, a indeksy o odsetkach fragmentacji większych niż 30% należy przebudować.

aby zmienić organizację pojedynczego indeksu:

ALTER INDEX REORGANIZE

odbudować pojedynczy indeks:

ALTER INDEX REBUILD WITH (ONLINE = ON)

możesz również zreorganizować lub odbudować indeks za pomocą SQL Server Management Studio, klikając prawym przyciskiem myszy indeks, który chcesz odbudować lub zreorganizować.

jeśli istnieje wiele defragmentowanych indeksów w bazie danych, możesz uruchomić następujące zapytanie, aby odbudować wszystkie z nich (kredyt: SQL SERVER-2008 -2005-odbudować każdy indeks ze wszystkich tabel bazy danych-odbudować indeks za pomocą FillFactor). Należy pamiętać, że zapytanie może działać przez jakiś czas dla dużej bazy danych. Zaleca się uruchamianie zapytania, gdy baza danych jest bezczynna lub offline.

DECLARE @TableName VARCHAR(255)DECLARE @sql NVARCHAR(500)DECLARE TableCursor CURSOR FORSELECT OBJECT_SCHEMA_NAME()+'.'+name AS TableNameFROM sys.tablesOPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableNameWHILE @@FETCH_STATUS = 0BEGINSET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'PRINT @sqlEXEC (@sql)FETCH NEXT FROM TableCursor INTO @TableNameENDCLOSE TableCursorDEALLOCATE TableCursorGO

rozważ skonfigurowanie zadania konserwacji w SQL Server studio, aby regularnie przeprowadzać reorganizację indeksu bazy danych. Chociaż przebudowa indeksów w SQL Server powinna odbywać się w trybie offline lub gdy system jest bezczynny, reorganizacja indeksów może odbywać się w trybie online. Oto dobry artykuł na ten temat: Rebuild or Reorganize: SQL Server Index Maintenance.

aby skonfigurować plan reorganizacji indeksu w SQL Server, kliknij prawym przyciskiem myszy „Zarządzanie”, a następnie „plany konserwacji” i wybierz „nowy Plan konserwacji” lub „Kreator planu konserwacji”. Postępuj zgodnie z instrukcjami, aby utworzyć plan.

RELATED: opcje zarządzania wersjami w Jama Connect

Uruchom raport o braku indeksu

SQL Server zapewnia narzędzie, które analizuje działania bazy danych i zaleca dodatkowe indeksy, które mogą pomóc w wydajności zapytań. Raport może dać nam kilka pomysłów na temat tego, dlaczego niektóre zapytania są powolne.

aby wygenerować raport, uruchom następujące zapytanie po tym, jak instancja bazy danych była używana przez jakiś czas (kredyt: Nie tylko ślepo tworzyć te” brakujące ” indeksy!):

SELECT d., s = OBJECT_SCHEMA_NAME(d.), o = OBJECT_NAME(d.), d.equality_columns, d.inequality_columns, d.included_columns, s.unique_compiles, s.user_seeks, s.last_user_seek, s.user_scans, s.last_user_scan, s.avg_total_user_cost, s.avg_user_impactFROM sys.dm_db_missing_index_details AS dINNER JOIN sys.dm_db_missing_index_groups AS gON d.index_handle = g.index_handleINNER JOIN sys.dm_db_missing_index_group_stats AS sON g.index_group_handle = s.group_handleWHERE d.database_id = DB_ID() AND OBJECTPROPERTY(d., 'IsMsShipped') = 0

przeczytaj artykuł Znajdowanie brakujących indeksów, aby zrozumieć wynik tego zapytania.

Monitoruj Sesje bazy danych

użyj Monitora aktywności do monitorowania sesji bazy danych. Microsoft SQL Server Management Studio jest wyposażony w monitor aktywności, który może być używany do monitorowania sesji bazy danych.

Szukaj sesji, które są blokowane przez inne sesje przez długi czas. Kliknij prawym przyciskiem myszy sesję i wybierz „Szczegóły”, aby wyświetlić szczegóły zapytania związane z procesem.

Użyj Windows Resource Monitor

Windows Resource Monitor może być używany do monitorowania wykorzystania pamięci i procesora procesora SQL Server, aby upewnić się, że w systemie jest wystarczająco dużo pamięci, a procesor nie jest nasycony. Należy pamiętać, że istnieje znany problem dla SQL Server 2008, w którym figura pamięci pokazana dla procesu SQL Server w monitorze zasobów nie jest poprawna.

Identyfikacja wolnych zapytań

jeśli ustaliliśmy, że wydajność nie jest związana z zasobami, następnym krokiem jest zidentyfikowanie wolnych zapytań, które prowadzą do złej wydajności.

poniższe zapytanie zwraca 100 pierwszych powolnych zapytań, które działają przez ponad 300 ms (kredyt: Long Running Queries):

SELECT st.text, qp.query_plan, qs.*FROM ( SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC) AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qpWHERE qs.max_worker_time > 300 OR qs.max_elapsed_time > 300

oto zrzut ekranu z przykładowymi wynikami:

po zebraniu listy powolnych zapytań możemy je uruchomić bezpośrednio w bazie danych i przyjrzeć się planom wykonania, aby zrozumieć te zapytania.

Inne narzędzia

Oto lista innych narzędzi, których można użyć do rozwiązywania problemów z serwerem SQL i aplikacjami Java:

• SQL Server Profiler: może być używany do identyfikacji wolnych zapytań. Możesz go uruchomić z SQL Server Management Studio
• VisualVM: może być używany do monitorowania wykorzystania zasobów Java i tworzenia zrzutów wątków
• Java Mission Control/Flight recording: monitorowanie i rejestrowanie zdarzeń systemowych
• JProfiler: identyfikowanie powolnych wywołań usług lub baz danych (rozwój, nie obsługiwany przez 8.x deployment)
• New Relic: może monitorować wydajność bazy danych i rejestrować powolne zapytania

listy kontrolne

Oto lista informacji, które można zebrać, aby rozwiązać problemy z serwerem SQL:

1. konfiguracja serwera bazy danych (liczba rdzeni procesora, pamięć fizyczna, miejsce na dysku, wersja Windows Server, wersja SQL Server, ustawienia pamięci SQL Server)
2. konfiguracja serwera aplikacji (Liczba rdzeni procesora, pamięć fizyczna, miejsce na dysku, ustawienia pamięci rdzenia Java)
3. statystyki indeksu bazy danych
4. statystyki fragmentacji bazy danych
6. statystyki pamięci serwera bazy danych i procesora gdy serwer jest wolny
7. operacje powodujące spowolnienie systemu
8. raport powolnego zapytania serwera SQL
9. statystyki tabeli bazy danych
10. kilka zrzutów wątków z aplikacji Java wykonanych podczas wolnych operacji, jeśli dotyczy

wnioski

debugowanie problemów z wydajnością serwera SQL nie zawsze jest proste. Ale z odpowiednimi narzędziami i cierpliwością, powinniśmy być w stanie zrozumieć przyczynę tych problemów. Mam nadzieję, że informacje w tym artykule pomogą w tym.