• Articles
• admin

nyligen har vi arbetat med felsökning av SQL server-prestandaproblem för några av våra stora kunder som kör SQL server som sin databas. I den här artikeln kommer jag att sammanfatta vad jag har lärt mig av processen genom att beskriva några steg vi kan vidta för att felsöka prestandaproblem.

kontrollera SQL Server-konfiguration

kontrollera att databasservern är konfigurerad med tillräckligt med resurser, till exempel antal CPU-kärnor och mängden minne.

för att kontrollera serverkonfigurationen kan du öppna” Systeminformation ” – vyn:

för att kontrollera SQL Server minnesinställning,

1. starta SQL Server Management Studio.
2. högerklicka på din databasinstans och välj ”Egenskaper”.
3. klicka på” Memory ”-tabellen i popup-fönstret” Server Properties”.
4. kontrollera minnesinställningarna.

se till att Snapshot-läget är på

se till att snapshot-läget är aktiverat för databasen. För att förhindra att SQL Server låses, bör snapshot mode-flaggan vara påslagen. Kör följande Fråga för att kontrollera om flaggan är på:

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

om frågan returnerar ’1’ är stillbildsläget redan aktiverat. Annars kör följande Fråga för att slå på den.

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

relaterade: Definiera och implementera Kravbaslinjer

kontrollera Databasindex

kontrollera databasindex för att se till att det inte saknas index. Kör följande Fråga för att lista alla alla index i databasen (kredit: tsql-lista över alla index & indexkolumner i 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

spara frågeresultaten i en textfil med tabbavgränsade kolumner så att de kan importeras till ett kalkylprogram senare.

Undvik fragmentering

se till att databasindexen inte är fragmenterade. Indexerna i databasen hjälper till att påskynda databasfrågan. Men när de blir fragmenterade kan frågorna gå riktigt långsamt. Som en del av databasunderhållet bör indexen omorganiseras eller byggas om regelbundet.

kör följande Fråga för att kontrollera fragmenteringsprocenten för alla index i databasen (kredit: Hur man kontrollerar Indexfragmentering på Index i en databas):

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

här är ett exempel på frågeresultat för en databas med mycket fragmenterade index:

för index med sidantal större än 1000 bör ”avg_framentation_in_percent” hållas under 10%. Det spelar ingen roll mycket för små index. Som tumregel kan Index med fragmenteringsprocent mellan 5% och 30% omorganiseras och index med fragmenteringsprocent större än 30% bör byggas om.

för att omorganisera ett enda index:

ALTER INDEX REORGANIZE

att bygga om ett enda index:

ALTER INDEX REBUILD WITH (ONLINE = ON)

du kan också omorganisera eller bygga om ett index med SQL Server Management Studio genom att högerklicka på det index du vill bygga om eller omorganisera.

om det finns många defragmenterade index i databasen kan du köra följande Fråga för att bygga om dem alla (kredit: SQL SERVER – 2008 -2005-bygga om varje Index för alla tabeller i databasen – bygga om Index med FillFactor). Observera att frågan Kan köras ett tag för en stor databas. Det rekommenderas att köra frågan medan databasen är inaktiv eller 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

överväg att ställa in underhållsjobb i SQL Server studio för att köra omorganisation av databasindex regelbundet. Även om ombyggnad av index i SQL Server förmodligen bör göras offline eller när systemet är inaktivt, kan omorganisation av index göras online. Här är en bra artikel om detta ämne: bygga om eller omorganisera: SQL Server Index underhåll.

för att skapa en indexreorganiseringsplan i SQL Server, högerklicka på” Management”, sedan” Maintenance Plans ”och välj” New Maintenance Plan ”eller”Maintenance Plan Wizard”. Följ instruktionerna för att skapa en plan.

relaterat: Släpphanteringsalternativ i Jama Connect

kör saknad Indexrapport

SQL Server tillhandahåller ett verktyg som analyserar databasaktiviteter och rekommenderar ytterligare index som kan hjälpa till med frågeprestanda. Rapporten kan ge oss några tankar om varför vissa frågor är långsamma.

för att generera rapporten, kör följande fråga efter att databasinstansen har använts ett tag (kredit: Skapa inte bara blint de ”saknade” indexen!):

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

Läs artikeln hitta saknade index för att förstå utdata från den här frågan.

övervaka Databassessioner

använd Activity Monitor för att övervaka databassessioner. Microsoft SQL Server Management Studio levereras med Activity Monitor, som kan användas för att övervaka databassessioner.

leta efter sessioner som blockeras av andra sessioner under lång tid. Högerklicka på sessionen och välj ”Detaljer” för att visa detaljerna i frågan som är associerad med processen.

Använd Windows Resource Monitor

Windows Resource Monitor kan användas för att övervaka minne och CPU-användning av SQL Server-processen för att se till att det finns tillräckligt med minne i systemet och processorn inte är mättad. Observera att det finns ett känt problem för SQL Server 2008 där minnesfiguren som visas för SQL Server-processen i Resource Monitor inte är korrekt.

identifiera långsamma frågor

om vi har fastställt att prestandan inte är resursrelaterad är nästa steg att identifiera långsamma frågor som leder till dålig prestanda.

följande fråga returnerar de 100 bästa långsamma frågorna som körs i mer än 300 ms( kredit: långa frågor):

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

här är en skärmdump av några provresultat:

när vi har samlat en lista med långsamma frågor kan vi köra dem direkt i databasen och titta på exekveringsplanerna för att förstå dessa frågor.

andra verktyg

här är en lista över andra verktyg som kan användas för att felsöka SQL Server-och Java-applikationer:

• SQL Server Profiler: kan användas för att identifiera långsamma frågor. Du kan starta det från SQL Server Management Studio
• VisualVM: kan användas för att övervaka användningen av Java-resurser och göra tråddumpar
• Java Mission Control/Flight recording: övervaka och spela in systemhändelser
• JProfiler: identifiera långsamma service-eller databasanrop (utveckling, stöds inte av 8.X deployment)
• New Relic: kan övervaka databasprestanda och spela in långsamma frågor

checklistor

här är en lista med information som kan samlas in för att felsöka SQL Server-problem:

1. Databasserverkonfiguration (antal CPU-kärnor, fysiskt minne, diskutrymme, Windows Server-version, SQL Server-version, SQL Server-minnesinställningar)
2. Applikationsserverkonfiguration (antal CPU-kärnor, fysiskt minne, diskutrymme, Java core-minnesinställningar)
3. Databasindex statistik
4. databasfragmenteringsstatistik
5. SQL server saknad indexrapport
6. databasserverminne och CPU-statistik när servern är långsam
7. operationer som gör att systemet är långsamt
8. SQL Server slow query report
9. Databastabellstatistik
10. ett par tråddumpar från Java-applikationer som tagits under de långsamma operationerna om tillämpligt

slutsats

felsökning av SQL server-prestandaproblem är inte alltid rakt framåt. Men med rätt verktyg och tålamod borde vi kunna förstå orsaken till dessa problem. Hoppas informationen i den här artikeln hjälper till med det.