Osnove praćenja performansi i rješavanja problema u SQL Serveru

U ovom ćemo članku pogledati popularne alate, T-SQL upite i skripte za otkrivanje i rješavanje različitih mogućih problema s performansama SQL Servera. Ovaj će vam članak pomoći da shvatite kada vaš SQL Server nema dovoljno resursa (memorija, CPU, IOP diskovi), pronađite zaključavanja i otkrivate sporo upite. Pogledajmo koji su ugrađeni alati i besplatne skripte i uslužni programi treće strane za analizu stanja Microsoft SQL Server.

sadržaj:

  • Dijagnostički alati SQL Server
  • Otkrivanje i rješavanje problema sa performansama SQL Servera

Dijagnostički alati SQL Server

Ako ste ispravno dijagnosticirali problem, tada je polovina posla već obavljena. Razmotrite koje alate najčešće koristi administrator sustava za dijagnosticiranje različitih problema u SQL Serveru:

  • T-SQL - Najmoćniji, jednostavan i nezamjenjiv alat za rješavanje problema i analizu performansi SQL Servera. Gotovo svi drugi alati za rad sa SQL Serverom koriste T-SQL. Ne možete učiniti ništa s T-SQL-om.
  • SQL Server Management Studio - Bez SSMS-a gotovo je nemoguće raditi sa SQL Serverom. Pomoću SSMS-a možete pogledati monitor aktivnosti, analizirati plan upita, pogledati parametre poslužitelja ili baze podataka i mnoge druge stvari.
  • Dnevnici pogrešaka SQL Server i Windows - ako nešto pođe po zlu, dnevnik grešaka je prvo mjesto koje administrator sustava izgleda. Dnevnik grešaka SQL Server može se promatrati kroz SSMS. Dnevnici sustava Windows mogu se pregledati kroz pomoćni program eventvwr.msc.
  • Windows Resource Monitor - resmon.exe je neophodan Windows alat za brzu procjenu stanja resursa poslužitelja. Upotrebu RAM-a i procesora može se promatrati kroz Upravitelj zadataka, ali detaljna upotreba mreže i tvrdog diska može se promatrati samo kroz resmon i perfmon.
  • Monitor sustava Windows (Monitor performansi) - Perfmon.exe je glavni alat za nadgledanje sustava Windows, sadrži mnoštvo „brojača“, kako mjernih podataka sustava, tako i aplikacija, uključujući SQL Server. Perfon-ovi šalteri se obično obrađuju pomoću drugih sustava praćenja, na primjer, Zabbix, jer je u perfmonu neugodno pohranjivati ​​i pregledavati podatke tijekom proteklog vremena..
  • Aplikacije trećih strana - Postoje mnoge plaćene i besplatne aplikacije za nadzor SQL Server. Na primjer, jedna od besplatnih aplikacija je dbForge Monitor iz tvrtke Devart. Aplikacija je instalirana kao dodatak SSMS-u i omogućava vam prikazivanje vrlo zgodne nadzorne ploče za prikaz trenutnog stanja vašeg SQL Servera (informacije o korištenju memorije, CPU-u, učitavanju, zaključavanju, procesima, informacijama o sigurnosnim kopijama, "teškim SQL upitima", performansama diskovnog podsustava itd. )..
  • Skripti Brentozar - Ovo je popularno rješenje za dijagnosticiranje postavki i zdravlja SQL Servera. Brentozar ima mnogo skripti za razne zadatke, ali za dijagnostiku nas zanima "sp_blitz". Možete ga besplatno preuzeti sa službene web stranice https://www.brentozar.com/blitz/. Pokrenite sp_Blitz.sql da biste instalirali potrebne postupke i izvršili ih. exec sp_blitz za dijagnozu. Zajednica SQL Server besplatan je i održava ovaj alat. Sp_blitz će prepoznati sve popularne probleme s vašim poslužiteljem i savjetovati ih kako ih riješiti..
  • T-SQL skripti setovi - prikladno je imati pri ruci zbirke različitih T-SQL upita za dijagnostiku SQL Servera, jer nema uvijek vremena za pisanje vlastitih upita, bolje je naoružati se unaprijed. Slijede linkovi na korisne T-SQL / PowerShell upite koje često koristim prilikom dijagnosticiranja i ugađanja MS SQL-a:
    • https://github.com/SQLadmin/AwesomeSQLServer - skup upita za nadgledanje IO CPU / RAM / diska i ostalih parametara.
    • https://github.com/dgavrikov/databases_scripts/tree/master/SQL%20Server - mnogo korisnih skripti i životnih hakova
    • https://github.com/ktaranov/sqlserver-kit - skripte i korisne informacije. Mnogo je primjera rada sa SQL Serverom putem PowerShell-a

Otkrivanje i rješavanje problema sa performansama SQL Servera

Najčešći problem s kojim se suočava administrator sustava koji radi sa SQL Serverom su pritužbe korisnika na rad upita i sam poslužitelj: "usporava", "upit traje dugo", itd..

Prije svega, morate osigurati da poslužitelj ima dovoljno resursa. Pogledajmo kako brzo analizirati upotrebu memorije, CPU-a, diskova i brava u SQL Serveru..

Analiza upotrebe RAM-a za SQL Server

Prvo morate utvrditi koliko je memorije dostupno na SQL Serveru. Da biste to učinili, pokrenite SSMS (SQL Server Management Studio), idite na server i idite na svojstva poslužitelja (RMB prema nazivu poslužitelja u Object Browser).

Sam raspoloživi RAM kapacitet neće vam ništa reći. Morate usporediti ovaj broj s memorijom koja se koristi u upravitelju zadataka i samom mehanizmu SQL Server pomoću DMV-a.

U upravitelju zadataka na kartici Detalji pronađite sqlservr.exe i pogledajte koliko RAM-a koristi ovaj postupak.

  • Ako poslužitelj, na primjer, ima 128 GB RAM-a, a postupak sqlservr.exe koristi 60 GB, a SQL Server nema ograničenja RAM-a, tada imate dovoljno RAM-a.
  • Ako SQL Server koristi 80-90% postavljenog ili maksimalnog RAM-a, tada morate gledati DMV. Imajte na umu da sqlservr.exe neće moći koristiti svu RAM memoriju. Ako poslužitelj ima 128 GB, tada sqlservr.exe može koristiti samo 80-90% (100-110 GB), jer je ostatak memorije rezerviran za operativni sustav.

Imajte na umu da postupak SQL Server ne vraća RAM natrag u sustav. Na primjer, vaš SQL Server obično koristi 20 GB memorije, ali s mjesečnim izvješćem povećava potrošnju na 100 GB, pa čak i kad je izračun izvješća završen i poslužitelj radi u prethodnom načinu, postupak SQL poslužitelja i dalje će koristiti 100 GB dok se usluga ne pokrene..

Čak i ako ste sigurni da poslužitelj ima dovoljno RAM-a, neće biti suvišno znati točno potrošenu RAM memoriju..

Možete saznati stvarnu uporabu RAM-a pomoću Dinamički prikazi upravljanja. DMV-ovi su administrativni gledatelji. Pomoću DMV-a možete dijagnosticirati gotovo bilo koji problem na SQL Serveru.

Pogledajmo sys.dm_os_sys_memory, za praktičnost koristimo zahtjev:

SELECT total_physical_memory_kb / 1024 AS [Ukupna fizička memorija], dostupno_physical_memory_kb / 1024 AS [Dostupna fizička memorija], total_page_file_kb / 1024 AS [Total Page File (MB)], available_page_file_kb / 1024 AS [Dostupna stranica datoteke (MB)], 100 - ( 100 * Cast (dostupno_physical_memory_kb AS DECIMAL (18, 3)) / Cast (total_physical_memory_kb AS DECIMAL (18, 3))) AS 'Postotak korišten', system_memory_state_desc AS [Stanje pamćenja] IZ sys.dm_os_sys_memory;

Uzmite u obzir svaki izlazni parametar:

  1. [Ukupna fizička memorija] - količina RAM-a dostupna u operativnom sustavu. Na nekim poslužiteljima može pokazati malo više nego što je stvarno instalirano.
  2. [Dostupna fizička memorija] - količina RAM-a dostupna za SQL Server, isključujući već zauzet SQL Server.
  3. [Ukupna datoteka stranice (MB)] - Volumen "ograničenje ograničenja". Uključi ograničenje = RAM + sve swap datoteke. To jest, ako na poslužitelju imate 32 GB RAM-a i 16 GB stranične datoteke, limit ograničenja bit će 48 GB.
  4. [Dostupna datoteka stranice (MB)] - Veličina datoteke stranične stranice.
  5. Korišteni postotak - postotak korištene RAM-a. Ne postoji takav parametar u samom sys.dm_os_sys_memory, ali on se izračunava pomoću formule available_physical_memory_kb / total_physical_memory_kb
  6. [Stanje memorije] - RAM status. Polje system_memory_state_desc sadrži stanje potrošnje RAM-a u obliku teksta. Vrijednost ovog polja smatra se na temelju druga dva: system_low_memory_signal_state i system_high_memory_signal_state. Možete ih odabrati izravno ako vam je potreban Boolean / bitni format podataka. Za sva polja sys.dm_os_sys_memory pogledajte dokumentaciju https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact -sql? view = sql-server-ver15

Svi su ti podaci korisni ako želite točno odrediti koliko vaš SQL Server troši RAM-a. Najčešće se koristi ako postoji sumnja da je za primjer dodijeljeno previše RAM-a..

Ako trebate osigurati da poslužitelj ima dovoljno RAM-a, možete pogledati samo polja system_low_memory_signal_state, system_high_memory_signal_state i system_memory_state_desc. Ako je system_low_memory_signal_state = 1, poslužitelj očito nema dovoljno RAM-a.

Upotreba CPU-a u SQL Serveru

Opterećenje procesora lakše je odrediti, što se može učiniti u upravitelju zadataka. Da biste saznali trenutno opterećenje procesora, pronađite postupak sqlservr.exe u upravitelju zadataka

Ako želite znati opterećenje za prošlo vrijeme, možete koristiti upit:

Ne zaboravite promijeniti minute @lastNMin na potreban broj.
DECLARE @ts BIGINT; IZJAVA @lastNmin TINYINT; SET @lastNmin = 30; SELECT @ts = (ODABIR cpu_ticks / (cpu_ticks / ms_ticks) OD sys.dm_os_sys_info); SELECT TOP (@lastNmin) Dateadd (ms, -1 * (@ts - [timetamp]), Getdate ()) AS [EventTime], sqlprocessutilization AS [SQL Server Utilization], 100 - systemidle - sqlprocessureslization [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], [timestamp] FROM ( SELECT [vremenska oznaka], CONVERT (XML, zapis) KAO [zapis] OD sys.dm_os_ring_buffers GDJE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR 'I snimite LIKE' %% ') AS x) KAO NARUČITE PO zapis_id DESC;

Kao rezultat, dobivamo po minutu statistike o korištenju procesora.

Analiza opterećenja diska SQL Server

Pogledajmo utovar diskova u operacijskom sustavu. Da biste to učinili, pokrenite resmon.exe.

Trebamo karticu disk. U odjeljku Diskovna aktivnost Prikazane su datoteke kojima pristupate i brzina njihovog čitanja / pisanja u trenutnom trenutku. Filtrirajte ovaj odjeljak prema Ukupno (kliknite na Ukupno). Na samom vrhu će se nalaziti datoteke koje trenutno najviše koriste. U slučaju SQL Servera, ovo može biti korisno za određivanje koja baza podataka trenutno najviše učitava disk..

Odjeljak Storage prikazuje sve pogone u sustavu. U ovom su odjeljku potrebna 2 parametra - Redak aktivnog vremena i diska. Aktivno vrijeme u postotku prikazuje opterećenje na disku, to jest, ako vidite da je C: \ Aktivno vrijeme na disku jednako 90, to znači da se resurs čitanja / pisanja diska trenutno koristi na 90%. Stupac Red čekanja diska prikazuje red pristupa disku, a ako vrijednost čekanja nije nula, disk je 100% učitan i ne može podnijeti opterećenje. Također, ako je aktivno vrijeme blizu 100, disk se upotrebljava gotovo na granici svojih mogućnosti u brzini.

Pogledajte brave u SQL Serveru

Nakon što smo osigurali da poslužitelj ima dovoljno resursa, možemo nastaviti s pregledom zaključavanja.

Brave se mogu gledati kroz Monitor Monitor u SSMS-u, ali koristit ćemo T-SQL jer je ova opcija prikladnija i vizualnija. Ispunjavamo zahtjev:

POSTAVITE NOCOUNT NA GO SELECT SPID, BLOCKED, ZAMJENA (ZAMJENA (T.TEXT, CHAR (10), "), CHAR (13),") AS BATCH INTO #T FROM sys.sysproces R CROSS APPLY sys.dm_exec_sql_text (R. SQL_HANDLE) ISPOD BLOKERA (SPID, BLOCKED, NIVO, SERIJA) KAO (ODABIR SPID, BLOCKED, CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) KAO RAZINA, SERIJA OD #TR GDJE (BLOKIRAN = 0 ILI BLOKIRAN = SPID) I POSTOJI (ODABIR * OD #T R2 GDJE R2.BLOCKED = R.SPID I R2.BLOCKED R2.SPID) UNION SVI ODABIR R.SPID, R.BLOCKED, CAST (BLOCKERS.LEVEL + DESNO (CAST ((1000 + R.SPID) KAO VARCHAR (100)), 4) AS VARCHAR (1000)) AS NEVEL, R.BATCH OF #T AS R INNER PRIDRUŽITE BLOKERE NA R. BLOCKED = BLOCKERS.SPID GDJE R. BLOCKED> 0 AND R. BLOCKED R.SPID) ODABIR N "+ REPLICATE (N '|', LEN (NEVEL) / 4 - 1) + CASE WHEN ( LEN (NIVO) / 4 - 1) = 0 THEN 'GLAVA -' ELSE '| ------' END + CAST (SPID KAO NVARCHAR (10)) + N "+ SERIJA KAO BLOCKING_TREE OD BLOKATORA NARUČI NIVO ASC GO DROP TABELA #T GO

Ovaj upit vraća popis brava u obliku stabla. Ovo je praktično u radu, jer obično, ako se dogodi jedna brava, izaziva druge. Slično tome, u Monitoru aktivnosti ili na izlazu sp_who2 možete vidjeti polje "Blokirano pomoću".

Ako zahtjev nije ništa vratio, tada nema zaključavanja.

Ako je zahtjev vratio neke podatke, tada morate analizirati lanac.

GLAVA znači da je ovaj zahtjev razlog za sve druge brave ispod stabla. 64 je identifikator procesa (SPID). Nakon toga se piše tijelo zahtjeva što je uzrokovalo blokadu. Ako imate dovoljno resursa za poslužitelj, stvar je najvjerojatnije u samom zahtjevu i u međusobnoj privlačnosti nekih objekata. Da biste bili precizniji, morate analizirati konkretan zahtjev koji je uzrokovao zaključavanje.

Pravila SQL poslužitelja

Čak i kada vam sve dobro uspije i nema pritužbi, u stvari se mogu pojaviti kasnije. SQL Server za to ima pravila..

Politika u SQL Serveru je, grubo rečeno, provjera pravila u skladu s danom vrijednošću. Na primjer, pomoću pravila možete osigurati da je Automatsko skupljanje isključeno u svim bazama podataka na poslužitelju. Razmotrite primjer uvoza i izvršavanja pravila

U SSMS-u povežite se s poslužiteljem na kojem želite izvršavati politike (odjeljak Upravljanje -> Upravljanje politikama).

Uvezite datoteku Automatska smanjivanja.xml baze podataka. hit procijeniti

Postoje dvije baze podataka na primjeru čvora1, test1 i test2. Automatsko povezivanje omogućeno je na testu2. Pogledajmo detalje.

Pravilo određuje uključeni parametar Automatska povezivanja, u opisu pravila obično se pišu objašnjenja. U ovom slučaju daje se objašnjenje zašto je automatsko smanjivanje bolje..

Politike se mogu izvesti bilo prema rasporedu ili na zahtjev (jednokratno). Rezultati provođenja pravila mogu se naći u zapisniku pravila..

Kad instalirate SQL Server, trebate odabrati samo korištene DBMS komponente i odrediti postavke u skladu s hardverskom konfiguracijom vašeg poslužitelja. Uvijek provjerite da poslužitelj ima dovoljno resursa i da nema zaključavanja na poslužitelju

Najsnažniji dijagnostički alat SQL Server su T-SQL i DMV. Također se preporučuje izgradnja 24-satnog praćenja SQL Servera i njegove infrastrukture kako bi otkrili sve moguće probleme..

Kategorija