• Articles
• admin

recent, am lucrat la probleme de performanță SQL server de fotografiere pentru unii dintre clienții noștri mari care rulează SQL server ca baza lor de date. În acest articol, voi rezuma ceea ce am învățat din proces prin descrierea unor pași pe care îi putem face pentru a rezolva problema performanței.

verificați configurația SQL Server

asigurați-vă că serverul bazei de date este configurat cu suficiente resurse, cum ar fi numărul de nuclee CPU și cantitatea de memorie.

pentru a verifica configurația serverului, puteți deschide vizualizarea ” informații despre sistem:

pentru a verifica setarea memoriei SQL Server,

1. porniți SQL Server Management Studio.
2. faceți clic dreapta pe instanța bazei de date și selectați „Proprietăți”.
3. Faceți clic pe tabelul „memorie” din fereastra pop-up „Proprietăți Server”.
4. verificați setările de memorie.

asigurați-vă că modul instantaneu este activat

asigurați-vă că modul instantaneu este activat pentru baza de date. Pentru a împiedica blocarea SQL Server, steagul modului instantaneu ar trebui să fie activat. Rulați următoarea interogare pentru a verifica dacă steagul este activat:

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

dacă interogarea returnează ‘1’, atunci modul instantaneu este deja activat. În caz contrar, executați următoarea interogare pentru ao activa.

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

înrudit: Definirea și implementarea cerințelor linii de bază

verificați indexurile bazei de date

verificați indexurile bazei de date pentru a vă asigura că nu lipsesc indexurile. Rulați următoarea interogare pentru a lista toate toate indexurile din Baza de date (credit: tsql-lista tuturor coloanelor index & în Sql server DB – Stack Overflow):

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

salvați rezultatele interogării într-un fișier text cu coloane delimitate de file, astfel încât acestea să poată fi importate ulterior într-o aplicație de calcul tabelar.

evitați fragmentarea

asigurați-vă că indexurile bazei de date nu sunt fragmentate. Indexurile din Baza de date ajută la accelerarea interogării bazei de date. Dar când sunt fragmentate, interogările ar putea rula foarte lent. Ca parte a întreținerii bazei de date, indexurile ar trebui reorganizate sau reconstruite în mod regulat.

rulați următoarea interogare pentru a verifica procentul de fragmentare pentru toți indexurile din Baza de date (credit: Cum se verifică fragmentarea indexului pe indexurile dintr-o bază de date):

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

Iată un exemplu de rezultat al interogării pentru o bază de date cu indici foarte fragmentați:

pentru indexurile cu număr de pagini mai mare de 1000, „avg_framentation_in_percent” ar trebui să fie păstrate sub 10%. Nu contează prea mult pentru indicii mici. De regulă, indicii cu procente de fragmentare cuprinse între 5% și 30% ar putea fi reorganizați, iar indicii cu procente de fragmentare mai mari de 30% ar trebui reconstruiți.

pentru a reorganiza un singur index:

ALTER INDEX REORGANIZE

pentru a reconstrui un singur index:

ALTER INDEX REBUILD WITH (ONLINE = ON)

de asemenea, puteți reorganiza sau reconstrui un index folosind SQL Server Management Studio făcând clic dreapta pe indexul pe care doriți să îl reconstruiți sau să îl reorganizați.

dacă există multe indexuri defragmentate în baza de date, puteți rula următoarea interogare pentru a le reconstrui pe toate (credit: SQL SERVER – 2008 -2005-reconstruiți fiecare Index al tuturor tabelelor bazei de date – reconstruiți indexul cu FillFactor). Rețineți că interogarea poate rula o perioadă pentru o bază de date mare. Se recomandă rularea interogării în timp ce baza de date este inactivă sau 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

luați în considerare configurarea lucrării de întreținere în SQL Server studio pentru a rula periodic reorganizarea indexului bazei de date. Deși reconstruirea indexurilor în SQL Server ar trebui să se facă probabil offline sau când sistemul este inactiv, reorganizarea indexului ar putea fi făcută online. Iată un articol bun pe această temă: reconstruiți sau reorganizați: SQL Server Index Maintenance.

pentru a configura un plan de reorganizare index în SQL Server, faceți clic dreapta pe „Management”, apoi pe „planuri de întreținere” și selectați „Plan de întreținere nou” sau „expert Plan de întreținere”. Urmați instrucțiunile pentru a crea un plan.

RELATED: Release Management Options în JAMA Connect

Run Missing Index Report

SQL Server oferă un instrument care analizează activitățile bazei de date și recomandă indici suplimentari care pot ajuta cu performanța interogării. Raportul ne-ar putea oferi câteva idei despre motivul pentru care anumite întrebări sunt lente.

pentru a genera raportul, executați următoarea interogare după ce instanța bazei de date a fost utilizată o perioadă (credit: Nu creați doar orbește acele indexuri” lipsă”!):

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

citiți articolul găsirea indexurilor lipsă pentru a înțelege ieșirea din această interogare.

Monitor sesiuni de baze de date

utilizați Activity Monitor pentru a monitoriza sesiunile de baze de date. Microsoft SQL Server Management Studio vine cu Activity Monitor, care poate fi folosit pentru a monitoriza sesiunile bazei de date.

căutați sesiuni care sunt blocate de alte sesiuni pentru mult timp. Faceți clic dreapta pe sesiune și selectați „Detalii” pentru a afișa detaliile interogării asociate procesului.

Utilizare Windows Resource Monitor

Windows Resource Monitor poate fi utilizat pentru a monitoriza memoria și utilizarea procesorului procesului SQL Server pentru a vă asigura că există suficientă memorie în sistem și CPU nu este saturat. Vă rugăm să rețineți există o problemă cunoscută pentru SQL Server 2008 în cazul în care figura de memorie afișate pentru procesul SQL Server în Resource Monitor nu este corectă.

identificați interogările lente

dacă am stabilit că performanța nu este legată de resurse, următorul pas este identificarea interogărilor lente care duc la performanțe proaste.

următoarea interogare returnează primele 100 de interogări lente care rulează mai mult de 300 ms (credit: interogări de lungă durată):

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

aici este o captură de ecran a unor rezultate eșantion:

odată ce am adunat o listă de interogări lente, le-am putea rula direct pe baza de date și uita-te la planurile de execuție pentru a înțelege aceste interogări.

alte instrumente

Iată o listă de alte instrumente care pot fi utilizate pentru depanarea aplicațiilor SQL Server și Java:

• SQL Server Profiler: poate fi folosit pentru a identifica interogări lente. Puteți începe de la SQL Server Management Studio
• VisualVM: poate fi folosit pentru a monitoriza utilizarea resurselor Java și de a face haldele fir
• Java Mission Control/zbor de înregistrare: monitor și evenimente de sistem de înregistrare
• JProfiler: identificarea serviciu lent sau apeluri de baze de date (dezvoltare, nu sunt acceptate de 8.x deployment)
• New Relic: poate monitoriza performanța bazei de date și înregistra interogări lente

liste de verificare

Iată o listă de informații care pot fi colectate pentru a depana problemele SQL Server:

1. configurarea serverului bazei de date (număr de nuclee CPU, memorie fizică, spațiu pe disc, versiune Windows Server, versiune SQL Server, setări de memorie SQL Server)
2. configurarea serverului de aplicații (număr de nuclee CPU, memorie fizică, spațiu pe disc, Setări de memorie Java core)
3. statistici privind indexul bazei de date
4. statistici privind fragmentarea bazei de date
5. SQL server
6. memorie server de baze de date și statistici CPU atunci când serverul este lent
7. operațiuni care determină sistemul să fie lent
8. sql server raport de interogare lent
9. Statistica tabelului bazei de date
10. câteva depozite de fire din aplicațiile Java luate în timpul operațiilor lente, dacă este cazul

concluzie

Depanarea problemelor de performanță SQL server nu este întotdeauna directă. Dar, cu instrumentele și răbdarea potrivite, ar trebui să putem înțelege cauza acestor probleme. Sper că informațiile din acest articol vă ajută cu asta.