• Articles
• admin

onlangs hebben we gewerkt aan problemen met de prestaties van SQL server voor sommige van onze grote klanten die SQL server als hun database draaien. In dit artikel, Ik zal samenvatten wat ik heb geleerd van het proces door het beschrijven van een aantal stappen die we kunnen nemen om trouble-shoot prestaties probleem.

Controleer SQL Server Configuration

Controleer of de databaseserver is geconfigureerd met voldoende bronnen, zoals het aantal CPU-cores en de hoeveelheid geheugen.

om de serverconfiguratie te controleren, kunt u de weergave “Systeeminformatie” openen:

om SQL Server geheugen instelling te controleren,

1. Start SQL Server Management Studio.
2. Klik met de rechtermuisknop op uw database instance en selecteer “Properties”.
3. klik op de “Memory” tabel in het” Server Properties ” pop-up venster.
4. controleer de geheugeninstellingen.

Controleer of de Snapshot-modus op

Controleer of de snapshot-modus is ingeschakeld voor de database. Om te voorkomen dat SQL Server vergrendelt, moet de vlag voor snapshotmodus worden ingeschakeld. Voer de volgende zoekopdracht uit om te controleren of de vlag is ingeschakeld:

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

als de query ‘1’ retourneert, dan is de snapshot-modus al ingeschakeld. Voer anders de volgende query uit om deze in te schakelen.

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

gerelateerd: Definitie en implementatie van vereisten basislijnen

Controleer Databaseindexen

controleer de databaseindexen om er zeker van te zijn dat er geen ontbrekende indexen zijn. Voer de volgende query uit om alle indexen in de database weer te geven (credit: tsql-lijst van alle index & indexkolommen in 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

sla de query resultaten op in een tekstbestand met tab gescheiden kolommen, zodat ze later kunnen worden geïmporteerd in een spreadsheet toepassing.

vermijd fragmentatie

zorg ervoor dat de databaseindexen niet gefragmenteerd zijn. De indexen in de database helpen om de database query te versnellen. Maar als ze gefragmenteerd raken, kunnen de zoekopdrachten heel traag verlopen. Als onderdeel van het onderhoud van de database moeten de indexen regelmatig opnieuw worden georganiseerd of opnieuw worden opgebouwd.

voer de volgende query uit om het fragmentatiepercentage te controleren voor alle indexen in de database (credit: Hoe controleert u de fragmentatie van indexen in een Database):

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

hier is een voorbeeld query resultaat voor een database met zeer gefragmenteerde indexen:

voor indexen met paginatellingen groter dan 1000 moet het “avg_framentation_in_percentage” Onder 10% worden gehouden. Het maakt niet veel uit voor kleine indexen. Als vuistregel kunnen indexen met fragmentatiepercentages tussen 5% en 30% worden gereorganiseerd en moeten indexen met fragmentatiepercentages groter dan 30% opnieuw worden opgebouwd.

om een enkele index te reorganiseren:

ALTER INDEX REORGANIZE

om een enkele index opnieuw op te bouwen:

ALTER INDEX REBUILD WITH (ONLINE = ON)

U kunt ook een index reorganiseren of opnieuw opbouwen met behulp van SQL Server Management Studio door met de rechtermuisknop te klikken op de index die u wilt herbouwen of reorganiseren.

als er veel defragmenteerde indexen in de database zijn, kunt u de volgende query uitvoeren om ze allemaal opnieuw op te bouwen (credit: SQL SERVER – 2008 -2005-Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor). Let op, de query kan een tijdje draaien voor een grote database. Het wordt aanbevolen om de query uit te voeren terwijl de database inactief of offline is.

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

overweeg onderhoudstaak op te zetten in SQL Server studio om de database-index regelmatig opnieuw te organiseren. Hoewel het herbouwen van indexen in SQL Server waarschijnlijk offline moet worden gedaan of wanneer het systeem inactief is, index reorganisatie kan online worden gedaan. Hier is een goed artikel over dit onderwerp: Rebuild of reorganiseren: SQL Server Index Maintenance.

om een index reorganisatieplan in SQL Server op te zetten, klikt u met de rechtermuisknop op “Management”, vervolgens op “Maintenance Plans”, en selecteert u “New Maintenance Plan” of “Maintenance Plan Wizard”. Volg de instructies om een plan te maken.

gerelateerd: Release Management opties in Jama Connect

Run Missing Index Report

SQL Server biedt een tool die databaseactiviteiten analyseert en extra indexen aanbeveelt die kunnen helpen bij de query performance. Het verslag zou ons enkele ideeën kunnen geven over de reden waarom bepaalde vragen traag zijn.

om het rapport te genereren, voert u de volgende query uit nadat de database-instantie een tijdje is gebruikt (credit: Maak niet alleen blindelings die “ontbrekende” 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

lees het artikel Finding Missing Indexes om de output van deze query te begrijpen.

Databasesessies monitoren

Activity Monitor gebruiken om databasesessies te monitoren. Microsoft SQL Server Management Studio wordt geleverd met Activity Monitor, die kan worden gebruikt om de database sessies te controleren.

zoek naar sessies die lange tijd door andere sessies worden geblokkeerd. Klik met de rechtermuisknop op de sessie en selecteer “Details” om de details van de query die is gekoppeld aan het proces weer te geven.

gebruik Windows Resource Monitor

Windows Resource Monitor kan worden gebruikt om het geheugen en CPU-gebruik van het SQL Server-proces te controleren om er zeker van te zijn dat er voldoende geheugen in het systeem aanwezig is en dat de CPU niet verzadigd is. Houd er rekening mee dat er een bekend probleem is voor SQL Server 2008 waarbij het getoonde geheugenfiguur voor het SQL Server-proces in bronmonitor niet correct is.

Identificeer trage query ‘s

als we hebben vastgesteld dat de performance geen brongerelateerd is, is de volgende stap het identificeren van trage query’ s die leiden tot slechte prestaties.

de volgende query geeft de top 100 trage query ’s terug die langer dan 300 ms draaien (credit: lang lopende query’ s):

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

hier is een screenshot van enkele voorbeeldresultaten:

zodra we een lijst met trage queries hebben verzameld, kunnen we ze direct in de database uitvoeren en de uitvoeringsplannen bekijken om deze queries te begrijpen.

Andere tools

hier is een lijst met andere tools die kunnen worden gebruikt om problemen met SQL Server en Java-toepassingen op te lossen:

• SQL Server Profiler: kan worden gebruikt om langzame query ‘ s te identificeren. U kunt het starten vanuit SQL Server Management Studio
• VisualVM: kan worden gebruikt om het gebruik van Java-bronnen te monitoren en threaddumps
• te maken Java Mission Control/Flight recording: monitor en record systeemgebeurtenissen
• JProfiler: identificeer trage service-of databaseaanroepen (ontwikkeling, niet ondersteund door 8.x deployment)
• New Relic: kan de prestaties van de database controleren en langzame queries opnemen

checklijsten

hier is een lijst met informatie die kan worden verzameld om problemen met SQL Server op te lossen:

1. Databaseserverconfiguratie (aantal CPU-cores, fysiek geheugen, schijfruimte, Windows Server-versie, SQL Server-versie, SQL Server-geheugeninstellingen)
2. Applicatieserverconfiguratie (aantal CPU-cores, fysiek geheugen, schijfruimte, Java-Core-geheugeninstellingen)
3. Databasefragmentatiestatistieken
4. SQL server missing index report
5. databaseservergeheugen-en CPU-statistieken wanneer de server traag is
6. bewerkingen die ervoor zorgen dat het systeem traag is
7. SQL Server slow query report
8. databasetabelstatistieken
9. een paar threaddumps van Java-toepassingen genomen tijdens de langzame bewerkingen, indien van toepassing

conclusie

Debugging van SQL server performance issues is niet altijd eenvoudig. Maar met de juiste instrumenten en geduld, moeten we in staat zijn om de oorzaak van deze kwesties te begrijpen. Hoop dat de informatie in dit artikel helpt met dat.