Osnove spremljanja in odpravljanja težav v SQL Serverju

V tem članku si bomo ogledali priljubljena orodja, T-SQL poizvedbe in skripte za zaznavanje in reševanje različnih možnih težav z uspešnostjo SQL Serverja. Ta članek vam bo pomagal ugotoviti, če vaš SQL Server nima dovolj virov (pomnilnik, CPU, IOP diskov), najti zaklepe in odkriti počasne poizvedbe. Poglejmo, kaj so vgrajena orodja in brezplačni skripti in pripomočki drugih proizvajalcev za analizo stanja Microsoft SQL Server.

Vsebina:

  • Diagnostična orodja SQL Server
  • Zaznavanje in reševanje težav z zmogljivostjo SQL Serverja

Diagnostična orodja SQL Server

Če ste pravilno diagnosticirali težavo, je polovica dela že opravljena. Razmislite o tem, katera orodja sistemski administrator običajno uporablja za diagnosticiranje različnih težav v SQL Serverju:

  • T-sql - Najzmogljivejše, preprosto in nepogrešljivo orodje za odpravljanje težav in analizo uspešnosti SQL Serverja. Skoraj vsa druga orodja za delo s strežnikom SQL uporabljajo T-SQL. S T-SQL-om ne morete storiti ničesar.
  • SQL Server Management Studio - Brez SSMS je skoraj nemogoče delati s SQL strežnikom. S pomočjo SSMS si lahko ogledate nadzornik dejavnosti, analizirate načrt poizvedb, pogledate parametre strežnika ali baze podatkov in številne druge stvari.
  • Dnevniki napak SQL Server in Windows - če gre kaj narobe, je dnevnik napak na prvem mestu sistemskega skrbnika. Dnevnik napak SQL Server si lahko ogledate prek SSMS. Dnevnike sistema Windows si lahko ogledate prek vtičnika eventvwr.msc.
  • Windows Resource Monitor - resmon.exe je nepogrešljivo orodje sistema Windows za hitro oceno stanja strežniških virov. Uporaba RAM-a in procesorja si je mogoče ogledati prek upravitelja opravil, podrobno uporabo omrežja in trdega diska pa si lahko ogledate le skozi resmon in perfmon.
  • Nadzor sistema Windows (Monitor Performance) - Perfmon.exe je glavno orodje za spremljanje sistema Windows, vsebuje različne „števce“, tako sistemske metrike kot aplikacije, vključno s SQL Serverjem. Običajno števci perfamona obdelajo z uporabo drugih sistemov za spremljanje, na primer Zabbix, saj je pri perfmonu neprijetno shranjevanje in ogled podatkov za pretečeni čas..
  • Vloge tretjih oseb - Obstaja veliko plačljivih in brezplačnih aplikacij za spremljanje SQL Serverja. Na primer, ena od brezplačnih aplikacij je dbForge Monitor iz podjetja Devart. Aplikacija je nameščena kot dodatek k SSMS in omogoča prikaz zelo priročne nadzorne plošče za prikaz trenutnega stanja vašega SQL strežnika (informacije o porabi pomnilnika, CPU-ju, obremenitvah, zaklepih, procesih, informacije o varnostnih kopijah, "težka poizvedba SQL", zmogljivosti diskovnega podsistema itd. .).
  • Scenariji Brentozar - To je priljubljena rešitev za diagnosticiranje nastavitev in zdravja SQL Serverja. Brentozar ima veliko scenarijev za različne naloge, a za diagnostiko nas zanima "sp_blitz". Brezplačno ga lahko prenesete s uradne spletne strani https://www.brentozar.com/blitz/. Zaženite sp_Blitz.sql, da namestite potrebne postopke in jih izvedete. exec sp_blitz za diagnozo. To orodje je brezplačno in vzdržuje skupnost SQL Server. Sp_blitz bo identificiral vse priljubljene težave z vašim strežnikom in svetoval, kako jih rešiti..
  • Nabori skript T-SQL - priročno je imeti pri roki zbirke različnih poizvedb T-SQL za diagnostiko SQL Server, saj ni vedno časa za pisanje lastnih poizvedb, bolje je, da se oborožite vnaprej. Sledijo povezave do uporabnih poizvedb T-SQL / PowerShell, ki jih pogosto uporabljam pri diagnosticiranju in nastavitvi MS SQL:
    • https://github.com/SQLadmin/AwesomeSQLServer - niz poizvedb za spremljanje CPU / RAM / diska IO in drugih parametrov.
    • https://github.com/dgavrikov/databases_scripts/tree/master/SQL%20Server - veliko uporabnih skriptov in življenjskih krajev
    • https://github.com/ktaranov/sqlserver-kit - Skripti in koristne informacije. Veliko primerov dela s SQL strežnikom prek PowerShell-a

Zaznavanje in reševanje težav z zmogljivostjo SQL Serverja

Najpogostejša težava, s katero se srečuje sistemski administrator, ki deluje s SQL strežnikom, so pritožbe uporabnikov glede uspešnosti poizvedb in samega strežnika: "upočasni se", "poizvedba traja dlje časa" itd..

Najprej morate zagotoviti, da ima strežnik dovolj virov. Poglejmo, kako hitro analizirati uporabo pomnilnika, CPU-ja, diskov in ključavnic v SQL Serverju..

Analiza uporabe RAM-a SQL Server

Najprej morate določiti, koliko pomnilnika je na voljo v SQL Serverju. Če želite to narediti, zaženite SSMS (SQL Server Management Studio), pojdite na strežnik in pojdite na lastnosti strežnika (RMB po imenu strežnika v brskalniku objektov).

Sama razpoložljiva zmogljivost RAM-a vam ne bo nič povedala. To številko morate primerjati s pomnilnikom, ki se uporablja v upravitelju opravil in samem SQL Server motorju z uporabo DMV.

V upravitelju opravil na zavihku Podrobnosti poiščite sqlservr.exe in si oglejte, koliko RAM-a uporablja ta postopek.

  • Če ima strežnik na primer 128 GB RAM-a, proces sqlservr.exe pa uporablja 60 GB, SQL Server pa nima omejitev RAM-a, potem imate dovolj RAM-a.
  • Če SQL Server uporablja 80-90% nastavljenega ali največjega RAM-a, morate gledati DMV. Upoštevajte, da sqlservr.exe ne bo mogel uporabljati celotnega RAM-a. Če ima strežnik 128 GB, lahko sqlservr.exe porabi le 80-90% (100-110 GB), saj je preostali del pomnilnika rezerviran za operacijski sistem.

Upoštevajte, da postopek SQL Server ne pošlje RAM-a nazaj v sistem. Na primer, vaš SQL Server običajno porabi 20 GB pomnilnika, vendar z mesečnim poročilom poveča porabo na 100 GB, in tudi ko je izračun poročila končan in strežnik deluje v prejšnjem načinu, bo postopek SQL Server še vedno porabil 100 GB, dokler se storitev ne znova zažene..

Tudi če ste prepričani, da ima strežnik dovolj RAM-a, ne bo odveč natančno vedeti, koliko porabljene RAM-ove..

Resnično lahko uporabite RAM z uporabo Dinamični pogledi upravljanja. DMV-ji so upravni gledalci. Z DMV lahko v SQL Server diagnosticirate skoraj vsako težavo.

Poglejmo si sys.dm_os_sys_memory, za udobje uporabimo zahtevo:

SELECT total_physical_memory_kb / 1024 AS [Skupen fizični pomnilnik], na voljo_physical_memory_kb / 1024 AS [Na voljo fizični pomnilnik], total_page_file_kb / 1024 AS [Skupaj datoteko strani (MB)], na voljo_page_file_kb / 1024 AS [Razpoložljiva datoteka strani (MB)], 100 - ( 100 * Cast (na voljo_physical_memory_kb AS DECIMAL (18, 3)) / Cast (total_physical_memory_kb AS DECIMAL (18, 3))) AS 'Odstotek uporabljen', system_memory_state_desc AS [stanje spomina] IZ sys.dm_os_sys_memory;

Upoštevajte vsak izhodni parameter:

  1. [Skupen fizični pomnilnik] - količina RAM-a, ki je na voljo v operacijskem sistemu. Na nekaterih strežnikih se lahko pokaže nekoliko več, kot je dejansko nameščeno.
  2. [Na voljo fizični pomnilnik] - količina RAM-a, ki je na voljo za SQL Server, razen zajetega SQL Serverja.
  3. [Skupna datoteka strani (MB)] - Obseg „omejitev omejitve“. Omeji omejitev = RAM + vse datoteke izmenjave. To pomeni, da če imate na strežniku 32 GB RAM-a in 16 GB datoteke za prestrezanje strani, bo omejitev odobritve 48 GB.
  4. [Razpoložljiva datoteka strani (MB)] - Velikost datoteke stranske datoteke.
  5. Uporabljeni odstotek - odstotek uporabljene RAM. Takega parametra v samem sys.dm_os_sys_memory ni, ampak se izračuna po formuli na voljo_physical_memory_kb / total_physical_memory_kb
  6. [Stanje pomnilnika] - stanje RAM-a Polje system_memory_state_desc vsebuje stanje porabe RAM-a v obliki besedila. Vrednost tega polja se upošteva na podlagi drugih dveh: system_low_memory_signal_state in system_high_memory_signal_state. Izberete jih lahko neposredno, če potrebujete logični / bitni format podatkov. Za vsa polja sys.dm_os_sys_memory glejte dokumentacijo https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact -sql? view = sql-server-ver15

Vsi ti podatki so koristni, če želite natančno določiti, koliko vaš SQL Server porabi RAM. Najpogosteje se uporablja, če obstaja sum, da je za primer dodeljeno preveč RAM-a..

Če morate zagotoviti, da ima strežnik dovolj RAM-a, si lahko ogledate samo polja system_low_memory_signal_state, system_high_memory_signal_state in system_memory_state_desc. Če sistem_low_memory_signal_state = 1, potem strežnik očitno nima dovolj RAM-a.

Uporaba procesorja v SQL strežniku

Obremenitev procesorja je lažje določiti, saj je to mogoče storiti v upravitelju opravil. Če želite izvedeti trenutno obremenitev procesorja, poiščite sqlservr.exe postopek v upravitelju opravil

Če želite vedeti obremenitev za pretekli čas, lahko uporabite poizvedbo:

Ne pozabite v nekaj minutah spremeniti @lastNMin na številko, ki jo potrebujete.
DECLARE @ts BIGINT; IZJAVI @lastNmin TINYINT; SET @lastNmin = 30; SELECT @ts = (IZBERI cpu_ticks / (cpu_ticks / ms_ticks) IZ sys.dm_os_sys_info); IZBIRAJTE TOP (@lastNmin) Dateadd (ms, -1 * (@ts - [timetamp]), Getdate ()) AS [EventTime], sqlprocessutilization AS [SQL Server Utilization], 100 - systemidle - sqlprocessusionis 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], [timetamp] FROM ( IZBERI [časovni žig], CONVERT (XML, posnetek) AS [zapis] IZ sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR 'IN posneti VELIČE' %% ') AS x) KAKO NAROČITE PO zapis_id DESC;

Kot rezultat dobimo minutno statistiko o porabi procesorja.

Analiza nalaganja diska SQL Server

Poglejmo si nalaganje diskov v operacijskem sistemu. Če želite to narediti, zaženite resmon.exe.

Potrebujemo zavihek Disk. V razdelku Diskovna dejavnost Prikažejo se datoteke, do katerih dostopate, in njihova hitrost branja / pisanja v trenutnem trenutku. Ta odsek filtrirajte po Skupaj (kliknite Skupaj). Na samem vrhu bodo datoteke, ki trenutno najbolje izkoristijo pogon. V primeru SQL Server je to lahko koristno za določitev, katera baza trenutno najbolj nalaga disk..

Oddelek Shranjevanje prikazuje vse pogone v sistemu. V tem razdelku potrebujemo 2 parametra - Active Time in Disk Queue. Aktivni čas kot odstotek prikazuje obremenitev diska, to je, če vidite na disku C: \ Active Time enako 90, to pomeni, da je vir branja / pisanja diska trenutno uporabljen na 90%. Stolpec čakalne vrste diska prikazuje čakalno vrsto dostopa do diska in če vrednost čakalne vrste ni nič, je disk 100% naložen in ne more obremeniti. Tudi če je aktivni čas blizu 100, se disk hitro uporablja skoraj na meji svojih zmogljivosti.

Oglejte si ključavnice v SQL Serverju

Ko smo se prepričali, da ima strežnik dovolj virov, lahko nadaljujemo z ogledom ključavnic.

Ključavnice si lahko ogledate s pomočjo Monitor Monitorja v SSMS, vendar bomo uporabili T-SQL, saj je ta možnost bolj priročna in vizualna. Zahtevo izpolnimo:

NASTAVITE NOCOUNT NA GO SELECT SPID, BLOCKED, ZAMENJAVI (ZAMENI (T.TEXT, CHAR (10), "), CHAR (13),") KAKAJ SERIJA V #T IZ sys.sysproces R CROSS APPLY sys.dm_exec_sql_text (R. SQL_HANDLE) T GOJITE Z BLOKERJI (SPID, BLOCKED, NIVO, SERIJA) KOT (IZBERITE SPID, BLOCKED, CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) KOT RAVNI, SERIJA OD #TR KJE (BLOKIRAN = 0 ALI BLOKIRAN = SPID) IN OBSTOJI (IZBERI * OD #T R2 KJE R2.BLOCKED = R.SPID IN R2.BLOCKED R2.SPID) UNION VSE IZBERITE R.SPID, R.BLOCKED, CAST (BLOCKERS.LEVEL + DESNO (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL, R.BATCH OF #T AS R INNER PRIDRUŽITE BLOKERA NA R. BLOCKED = BLOCKERS.SPID WHERE R. BLOCKED> 0 AND R. BLOCKED R.SPID) IZBERITE N "+ REPLICATE (N '|', LEN (LEVEL) / 4 - 1) + CASE WHEN ( LEN (STOPNJA) / 4 - 1) = 0 TAKO 'GLAVA -' ELSE '| ------' KRAJ + LEV (SPID KOT NVARCHAR (10)) + N "+ SERIJA KOT BLOKING_TREE IZ BLOKER NAROČI Z NIVO ASC GO DROP TABELA #T GO

Ta poizvedba vrne seznam ključavnic v obliki drevesa. To je priročno pri delovanju, saj običajno, če pride do ene ključavnice, izzove druge. Podobno lahko v Monitorju aktivnosti ali v izhodu sp_who2 vidite polje "Blokirano z".

Če zahteva ni vrnila ničesar, potem ni nobenih ključavnic.

Če je zahteva vrnila nekaj podatkov, morate analizirati verigo.

HEAD pomeni, da je ta zahteva razlog za vse druge ključavnice pod drevesom. 64 je identifikator procesa (SPID). Po tem se napiše telo zahteve, kar je povzročilo zaklepanje. Če imate dovolj strežniških virov, potem je najverjetneje zadeva v sami zahtevi in ​​v obojestranski pritožbi na nekatere predmete. Če želite biti natančnejši, morate analizirati posebno zahtevo, ki je povzročila zaklepanje.

Pravilniki SQL strežnika

Tudi ko vam vse dobro uspe in ni pritožb, se lahko v resnici pojavijo številne težave, ki se bodo pojavile kasneje. SQL Server ima za to pravilnike..

Politika v SQL Serveru v grobem preverja pravilo glede skladnosti z določeno vrednostjo. Na primer s pomočjo pravilnikov lahko zagotovite, da je samodejno skrčenje izklopljeno v vseh bazah podatkov na strežniku. Razmislite o primeru uvoza in izvajanja politike

V SSMS se povežite s strežnikom, na katerem želite izvajati politike (razdelek Upravljanje -> Upravljanje politik).

Uvozite datoteko samodejnega skrčenja.xml baze podatkov. Kliknite Oceni

Obstajajo dve zbirki podatkov na primer vozlišč1, test1 in test2. Samodejno povezovanje je omogočeno na testu2. Poglejmo podrobnosti.

Pravilnik je določil vključeni parameter AutoShrink, v opisu so ponavadi zapisane razlage pravil. V tem primeru je pojasnjeno, zakaj je samodejno krčenje bolje..

Politike se lahko izvajajo po urniku ali na zahtevo (enkrat). Rezultate izvajanja politike najdete v dnevniku pravilnikov..

Ko nameščate SQL Server, morate izbrati samo uporabljene komponente DBMS in določiti nastavitve v skladu s strojno konfiguracijo vašega strežnika. Vedno se prepričajte, da ima strežnik dovolj virov in da na strežniku ni zapornic

Najmočnejše diagnostično orodje SQL Server sta T-SQL in DMV. Priporočamo tudi, da zgradite neprekinjeno spremljanje SQL strežnika in njegove infrastrukture za odkrivanje vseh možnih težav..

Kategorija