De performance van een applicatie kan afhankelijk zijn van vele factoren. Zoals de gekozen applicatie architectuur, het applicatie ontwerp, het gekozen isolation level met betrekking tot transacties, de geschreven sql-code, gebruikte hardware en de configuratie van SQL Server zelf.
Resources
Om SQL server efficiënt query’s te laten uitvoeren vraagt deze om resources. Als een bepaalde resource niet optimaal benaderbaar is zal dit zeker tot performance problemen lijden. De belangrijkste resources die SQL Server gebruikt zijn de volgende:
• CPU
• Geheugen
• I/O
• Tempdb
• Blocking
Het is belangrijk om te weten hoe te achterhalen is dat 1 van deze resources de performance bottleneck is.
CPU probleem
SQL Server heeft een set van ‘workers’ tot zijn beschikking om queries en interne acties uit te voeren. Een worker krijgt een taak uitgedeeld door SQL Server die 1 op 1 wordt gekoppeld aan een thread of fiber van windows. Elke batch of query die SQL Server moet uitvoeren wordt gekoppeld aan 1 worker. De worker blijft aan zijn of haar taak gekoppeld totdat deze in z’n geheel is uitgevoerd, daarna zal SQL Server dezelfde worker gaan gebruiken voor het uitvoeren van een andere batch of query. Workers kunnen vele statussen hebben, de onderstaande zijn het interessants:
• Running - De worker gebruikt momenteel actief de CPU
• Runnable - De worker wacht tot het zijn beurt is om de CPU te mogen gebruiken
• Suspended - De worker is aan het wachten op een resource bv I/O of een lock.
Bovenstaande statussen laten zijn dat als er veel workers een runnable status hebben dat dit kan duiden op te weinig CPU kracht. Daarintegen als er veel workers de status suspended hebben kan dit weer aangeven dat er een I/O of een uitgebreid lock probleem aanwezig is.
NB: Als er meer dan 1 processor aanwezig is op een Server kan SQL Server besluiten op 1 query of batch te verdelen over meerdere workers en dus meerdere threads. Dit is het zogenaamde parallel uitvoeren van een query wat dan ook terug te zien is in bijvoorbeeld executieplannen.
Wanneer het duidelijk is dat er een CPU probleem is dan zal moeten worden uitgezocht worden waar dit vandaan komt. Twee veel voorkomende oorzaken zijn:
• Inefficiënt query plan
• Veel recompiles van executie plannen
Inefficiente query plannen kunnen een bottleneck zijn en leiden tot een cpu performance probleem. Veel oorzaken kunnen leiden tot een minder inefficiënt query plan, zo kan er per ongeluk een nuttige index zijn verwijderd of er zijn geen goede statistieken aanwezig voor SQL server om een goed plan uit te rekenen.
Ook kan het voorkomen dat er veel recompile plaats vinden van executieplannen. Het uitrekenen van een executieplan is een erg CPU intensief proces, het vaak opnieuw uitrekenen van een plan kan dus ook een zware druk leggen op de CPU. Een recompile kan veroorzaakt worden door nieuwe statistieken, verandering van indexen etc.
Met behulp van Dynamic Management Views (DMV’s) en Dynamic Management Functions (DMF’s) kunnen performance problemen worden gevonden.
NB: DMV’s en DMF’s geven alleen waarden terug van queries die gecached zijn. Er kunnen dus dure queries buiten schot blijven omdat er bijvoorbeeld geheugen te kort was er queries uit de cache zijn verstoten. Om een goed totaal beeld te krijgen moeten de DMV’s regelmatig geraadpleegd worden.
Hieronder handige code om CPU problemen te achterhalen
---==== CPU Performance ====---
--Reset wacht counters
--LET OP 1 malig uitvoeren alvorens meeting te beginnen
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO
--===================================================================----
--===================================================================----
--Toon workers en hun status mbt de CPU.
SELECT dmT.session_id AS sessie_wacht_voor_cpu_time
,dmW.last_wait_type
,dmER.command AS type_sql_command
,db_name(dmER.database_id) AS databasenaam
,CASE
WHEN dmW.state = 'RUNNABLE' THEN 'wacht op cpu'
WHEN dmW.state = 'RUNNING' THEN 'gebruikt cpu'
WHEN dmW.state = 'SUSPENDED' THEN 'wacht op resource (bv I/O of lock)'
END
FROM sys.dm_os_workers dmW
INNER JOIN sys.dm_os_schedulers dmS ON dmW.scheduler_address = dmS.scheduler_address
INNER JOIN sys.dm_os_tasks dmT ON dmW.task_address = dmT.task_address
INNER JOIN sys.dm_exec_requests dmER ON dmER.session_id = dmT.session_id
AND dmER.request_id = dmT.request_id
WHERE dmS.scheduler_id < 255 --kleiner dan 255 zijn queries
AND dmW.state IN ('RUNNABLE','RUNNING','SUSPENDED')
AND dmT.session_id > 50 -- kleiner dan 50 is SQL Server zelf
ORDER BY dmW.state
--Toon totaal aan gewachte tijd voor CPU
SELECT SUM(signal_wait_time_ms)
FROM sys.dm_os_wait_stats
--===================================================================----
--===================================================================----
--Queries met meeste CPU Time
SELECT TOP 10
dmQS.total_worker_time/dmQS.execution_count AS gemiddelde_cpu_tijd
,dmQS.execution_count AS aantal_keer_uitgevoerd
,(SELECT db_name(dbid)
FROM sys.dm_exec_sql_text(dmQS.sql_handle)) AS databasenaam
,(SELECT SUBSTRING( text
,statement_start_offset/2+1
,(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - statement_start_offset) / 2)
FROM sys.dm_exec_sql_text(dmQS.sql_handle)) AS QueryText
FROM sys.dm_exec_query_stats dmQS
ORDER BY gemiddelde_cpu_tijd DESC
--Queries meest uitgevoerd
SELECT TOP 10
dmQS.total_worker_time
,dmQS.execution_count AS aantal_keer_uitgevoerd
,(SELECT db_name(dbid)
FROM sys.dm_exec_sql_text(dmQS.sql_handle)) AS databasenaam
,(SELECT SUBSTRING( text
,statement_start_offset/2+1
,(CASE
WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - statement_start_offset) / 2)
FROM sys.dm_exec_sql_text(dmQS.sql_handle)) AS QueryText
FROM sys.dm_exec_query_stats dmQS
ORDER BY aantal_keer_uitgevoerd DESC
--===================================================================----
--===================================================================----
Geheugen probleem
Meeste productie databases zijn groter dan het geheugen wat aanwezig is op de server. De data van SQL Server staat opgeslagen op zo genoemde ‘data-pages’. Als SQL Server een data-page niet kan vinden in het geheugen dan zal hij deze laden van schijf en laden in het geheugen (buffer pool) om de volgende keer sneller te raadplegen. SQL Server bepaald via een algoritme (Least Recently Used – caching algorithme) welke data-pages in het geheugen blijven staan. Ook gebruikt SQL Server het geheugen voor interne doeleinden om bijvoorbeeld locks, connecties, workers en geoptimaliseerde plannen te managen. Te weinig geheugen kan leiden tot een belasting van andere resources en de gehele performance omlaag halen. Zo kan SQL Server bijvoorbeeld minder data-pages in het geheugen houden wat zal leiden tot meer I/O acties, of minder query plannen hergebruiken wat weer kan leiden tot meer CPU acties.
Er kan onderscheid worden gemaakt tussen 2 verschillende type geheugen problemen:
• Fysiek geheugen
• Virtueel geheugen
Fysiek geheugen
Het fysieke geheugen, ook wel RAM genoemd, kan onder druk staan doordat er kort weg te weinig RAM beschikbaar is, voor de applicaties die draaien op de Server. SQL Server monitort de aanwezige geheugen druk en zal zijn buffer-pool verkleinen als middel om deze te verlagen.
Ook kunnen interne processen van SQL Server besluiten om geheugen te ‘stelen’ van de buffer pool, dit levert zo geheten ‘stolen pages’ op.
Het makkelijkst om te controleren of een server een te kort heeft aan fysiek geheugen is door te controleren hoeveel RAM er nog beschikbaar is door op de performance tab van de task manager te kijken. Als er minder dan 50 a 100 mb beschikbaar is dan zal te kunnen leiden tot performance problemen. Ook kunnen performance counters worden ingesteld:
• Memory: Available Bytes
Dit geeft het totaal geheugen aan dat beschikbaar is voor programma’s om te gebruiken.
• SQL Server: Buffer Manager: Buffer Cache Hit Ratio
Dit geeft aan hoe vaak een page gevonden is in het geheugen inplaats van op schijf, hoort hoog in de waarde 90 te zitten.
• SQL Server: Buffer Manager: Page Life Expectancy
Dit geeft het aantal seconden weer dat een page in het geheugen (buffer pool) aanwezig blijft, een te lage waarde kan duiden op een geheugen probleem.
Er is ook een DBCC commando MEMORYSTATUS aanwezig waarmee geheugen problemen geanalyseerd kunnen worden.
Virtueel geheugen
Een te kort virtueel geheugen kan ook leiden tot een langzaam systeem doordat dit zal leiden tot meer I/O acties. SQL server detecteert ook onder water het te kort of te gefragmenteerd virtueel geheugen en zal dan zijn gereserveerde stukken vrij geven en zijn caches verkleinen.
Het makkelijkst om te controleren of een server een te kort heeft aan virtueel geheugen is ook hier om op te performance tab van de taskmanager te kijken naar ‘Commit Charge’ mbt het total en het limit aan beschikbaar geheugen. Ook kunnen performance counters worden ingesteld:
• Paging file: % Usage
Geeft het percentage weer dat de page file in gebruik is
• Memory: Commit Limit
Geeft het aantal bytes aan dat nog beschikbaar is voor gebruik alvorens de page file vergroot moet gaan worden.
Hieronder handige code om Memory problemen te achterhalen
---==== MEMORY Performance ====---
--===================================================================---
--===================================================================----
--Controleer kolom buffer counts committed versus target
--Controleer kolom buffer counts committed versus buffer distribution kolom stolen (niet hoger dan 75%)
DBCC MEMORYSTATUS
--===================================================================----
--===================================================================----
I/O probleem
SQL Server zijn performance is sterk afhankelijk van een snel I/O verkeer. Zolang de database niet totaal in het geheugen past zullen er pages van disk naar het geheugen geschreven moeten worden en omgekeerd. Ook worden aanpassingen aan de database gelogd en voordat een transactie gecommit kan worden moet de data naar schijf geschreven zijn. Ook wordt de tempdb gebruikt om tijdelijk data naar weg te schrijven om bijvoorbeeld data te orderen (order by clause) of indexen bij te werken.
Een van de mogelijkheden om een I/O bottleneck te detecteren is gebruik te maken van performance counters:
• PhysicalDisk Object: Avg. Disk Queue Length
Geeft het gemiddeld aantal fysieke lees en schrijf acties weer die staan te wachten in queue. Als de queue lengte meer dan 2 per schijf is dan kan dit duiden op een I/O probleem.
• PhysicalDisk Object: Avg. Disk Sec/Read en Avg. Disk Sec/Write
Geeft de gemiddelde waarde weer in seconden van de duur om data van en naar schijf te schrijven
• PhysicalDisk Object: Disk Reads/Sec en Disk Writes/Sec
Geeft het aantal lees en schrijf acties weer op de schijf, zorg ervoor dat het onder de 85 procent blijft anders wordt de schijft toegangs snelheid sterk vertraagd.
Bovenstaande counters geven weer of er een probleem op schijf niveau is, echter kan er niet mee achterhaald worden voor welke file het probleem bestaat, is het de tempdb of log file die problemen veroorzaakt en naar een aparte schijf geplaats moet worden. Dit soort informatie kan in SQL server met behulp van een DMV worden uitgevraagd.
Als er geconcludeerd is dat er lange wachttijden zijn op I/Q requests dan is het verleidelijk om snel te wijzen naar de schijven, echter kan een te kort aan geheugen, indexen of slechte queries leiden tot meer I/O verkeer. Met DMV’s kan dit worden achterhaald.
Hieronder handige code om I/O problemen te achterhalen
---==== I/O Performance ====---
--===================================================================----
--===================================================================----
---Wachten tijden per SQL file sinds start van SQL Server
SELECT db_name(dmVFS.database_id) AS databasenaam
,sMF.name AS bestandnaam
,sMF.physical_name AS bestandslocatie
,dmVFS.io_stall_write_ms AS schrijf_vertraging_ms
,dmVFS.io_stall_read_ms AS lees_vertraging_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) dmVFS
INNER JOIN sys.master_files sMF ON sMF.database_id = dmVFS.database_id
AND sMF.file_id = dmVFS.file_id
--===================================================================----
--===================================================================----
---Wachten van een worker totdat de data-page van schijf in pool-buffer is geschreven
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO
SELECT wait_type
,waiting_tasks_count AS aantal_keren_gewacht_op_io
,wait_time_ms AS ms_workers_in_suspended_en_runnable_mode
-- signal_wait_time_ms: mag io gaan gebruiken van systeem
--, tijd die het duurt alvorens de worker de io ook echt gebruikt.
,signal_wait_time_ms AS ms_workers_in_runnable_mode
,(wait_time_ms - signal_wait_time_ms) AS ms_workers_gewacht_op_io
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('PAGEIOLATCH_EX', 'PAGEIOLATCH_SH')
ORDER BY wait_type
--===================================================================----
--===================================================================----
-- Toon 10 meest dure queries
SELECT TOP 10
(total_logical_reads/execution_count) AS gemiddeld_logical_reads
,(total_logical_writes/execution_count) AS gemiddeld_logical_writes
,(total_physical_reads/execution_count) AS gemiddeld_phys_reads
,execution_count AS aantal_keren_uitgevoerd
,(SELECT SUBSTRING( text
,statement_start_offset/2+1
,(CASE
WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - statement_start_offset) / 2)
FROM sys.dm_exec_sql_text(sql_handle)) AS QueryText
,(SELECT query_plan
FROM sys.dm_exec_query_plan(plan_handle)) AS QueryPlanXML
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes) DESC
TempDB probleem
De tempdb is een resource voor de gehele SQL Server instance. SQL Server gebruikt de tempdb onder andere voor het wegschrijven van tijdelijke tabellen of tabel variabelen, sorteringen, rebuilds van indexen, joinen van hash tabellen, uitvoeren van cursoren etc etc.
Dit doet SQL Server naar de tempdb en niet naar de gebruikte database zelf, omdat:
• Er een rechten conflict kan ontstaan niet elke user heeft rechten om een CREATE TABLE commando uit te voeren.
• Er geen logging overhead aanwezig is in de tempdb, elke tabel gecreëerd in de database zelf zou log informatie onthouden om gegevens ongedaan te maken.
• Anders de zelfde I/O pad gedeeld moet worden, nu kunnen de tempdb en de gebruikers database op afzonderlijke schijven geïnstalleerd worden.
• Er kan vervuiling in de database ontstaan als er tijdelijke tabellen achter zouden blijven.
Omdat de tempdb niet onder deel is van het transactie log systeem en bij een crash van de server geen data herstelt hoeft te worden, is de tempdb uit termate geschikt om op andere type schijven te worden gezet voor snellere I/O toegang zoals een RAM schijf of een schijf met veel cache.
Een van de mogelijkheden om een tempdb bottleneck te detecteren is gebruik te maken van performance counters:
• SQLServer:Access Methods: Worktable Created/Sec
Het aantal werk tabellen die gecreerd worden door SQL Server per seconde in de tempdb. Normaal hoort dit onder de 200 tabellen per seconden te zijn. (werk tabellen zijn bedoeld voor cursoren, ordeningen etc)
• SQLServer: General Statistics: Temp Tables Creation Rate
Het aantal tijdelijke tabellen of tabel variabelen gecreerd per seconde.
• SQLServer: General Statistics: Temp Tables for Destruction
Het aantal tijdelijke tabellen of tabel variabelen die wachten om opgeruimd te worden.
Je kan tempdb problemen verkleinen door te achterhalen met DMV’s welke queries veel I/O verkeer veroorzaken in de tempdb.
Hieronder handige code om TempDB problemen te achterhalen
---==== Tempdb Performance ====---
--===================================================================----
--===================================================================----
-- Geef de queries terug die het meest I/O verkeer veroorzaken in tempdb
SELECT TOP 10
dmDTSU.session_id AS sessie
,db_name(dmDTSU.database_id) AS databasenaam
,dmDTSU.pages_allocation AS pages_gereserveerd_voor_sessie
,dmDTSU.pages_deallocation AS pages_vrij_gegeven_voor_sessie
,(SELECT SUBSTRING( text
,statement_start_offset/2+1
,(CASE
WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - statement_start_offset) / 2)
FROM sys.dm_exec_sql_text(dmER.sql_handle)) AS QueryText
,(SELECT query_plan
FROM sys.dm_exec_query_plan(dmER.plan_handle)) AS QueryPlanXML
FROM (SELECT session_id
,request_id
,database_id
,SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS pages_allocation
,SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS pages_deallocation
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id,database_id) dmDTSU
INNER JOIN sys.dm_exec_requests dmER ON dmER.session_id = dmDTSU.session_id
AND dmER.request_id = dmDTSU.request_id
WHERE dmDTSU.session_id > 50
ORDER BY pages_gereserveerd_voor_sessie DESC
Blocking probleem
Vaak is er een misverstand over het feit dat locking iets verkeerds zou zijn. Echter geen enkele database zou in een productie omgeving kunnen werken zonder lockingmechanisme. Een record kan immers niet door twee personen tegelijkertijd worden aangepast. Echter significant veel locking kan blocking problemen opleveren en veroorzaakt vertraagde response tijden. Vaak komt dit door langlopende transacties of het isolation level waar de transacties in uitgevoerd worden. Als een transactie een rij aanpast wordt een zogeheten ‘X-lock’ vastgehouden voor de duur van de transactie. Als het transactie level hoger is (repeatable read, serializable) worden zelfs de S-locks niet vrijgegeven voor de duur van de transacties.
Er zijn een paar standaard regels om blocking problemen te verkleinen:
• Verkort de duur van de transactie en laat transacties in een lager isolation level draaien. Zorg er voor dat gebruikers nooit invloed kunnen hebben op de duur van de transactie.
• Gebruik zo min mogelijk data binnen een transactie, soms ontbreekt er een index en wordt er veel te veel data gelezen die niet relevant zijn voor de te aan te passen waarden.
• Als er mutaties binnen 1 transactie plaatsvinden of veel verschillende rijen, breek dit op in meerdere transacties.
Een van de mogelijkheden om een blocking bottleneck te detecteren is gebruik te maken van performance counters:
• SQLServer: Locks: Average Wait Time (ms)
Gemiddelde wachttijd for elke lock request dat resulteerde in een block situatie
• SQLServer: Locks: Lock Requests/Sec
Geeft het aantal nieuwe locks weer die de lockmanager van SQL Server aanvraagt per seconde
• SQLServer: Locks: Lock Wait Time (ms)
Geeft de totale tijd weer van het aantal ms dat locks staan te wachten per seconde.
• SQLServer: Locks: Lock Waits/Sec
Geeft het aantal lock requests aan dat niet meteen door SQL Server kon worden toegekend en waar de gebruiker voor moest wachten.
• SQLServer: Locks: Number of Deadlocks/Sec
Geeft het aantal deadlocks weer per seconde
• SQLServer: General Statistics: Processes Blocked
Geeft het aantal processes weer dat momenteel geblocked worden
• SQLServer: Access Methods: Table Lock Escalations/Sec
Geeft het aantal keer weer dat een lock geescaleerd is tot een tabel lock.
Om blocking problemen te verhelpen, zal je moeten weten welke transacties locks vasthouden en welke transacties geblocked worden. Dit kan doormiddel van DMV’s worden geanalyseerd.
Hieronder handige code om Blocking problemen te achterhalen
---==== Blocking Performance ====---
--===================================================================----
--===================================================================----
---Laat zien welke en hoe transacties elkaar beinvloeden door locking
SELECT dmOWT.session_id AS request_sessieid
,dmTL.request_mode AS request_type_lock
,dmOWT.blocking_session_id AS blocking_sessieid
,dmOWT.wait_duration_ms AS wachttijd_ms
,db_name(dmTL.resource_database_id) AS databasenaam
,dmOWT.resource_description AS beschrijving_blocking_resource
,(SELECT SUBSTRING( text
,statement_start_offset/2+1
,(CASE
WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - statement_start_offset) / 2)
FROM sys.dm_exec_sql_text(sql_handle)) AS QueryText
FROM sys.dm_tran_locks dmTL
INNER JOIN sys.dm_os_waiting_tasks dmOWT ON dmTL.lock_owner_address = dmOWT.resource_address
INNER JOIN sys.dm_exec_requests dmER ON dmTL.request_request_id = dmER.request_id
AND dmOWT.session_id = dmER.session_id
--===================================================================----
--===================================================================----
--Dedecteer een table lock
GO
SELECT dmTL.request_session_id AS sessieid
,dmTL.resource_type
,db_name(dmTL.resource_database_id) AS databasenaam
,object_name(dmTL.resource_associated_entity_id) AS tabelnaam
,dmTL.request_mode
,dmTL.request_type
,dmTL.request_status
FROM sys.dm_tran_locks dmTL
INNER JOIN sys.all_objects sAO ON dmTL.resource_associated_entity_id = sAO.object_id
WHERE dmTL.request_type = 'LOCK'
AND dmTL.request_status = 'GRANT'
AND sAO.type = 'U'
AND dmTL.resource_type = 'OBJECT'
AND dmTL.resource_database_id = DB_ID()
AND dmTL.request_mode IN ('X','S')
--===================================================================----
--===================================================================----
Geen opmerkingen:
Een reactie posten