4 sätt att lösa prestandaproblem i sql-server – del 1

När sajten går långsamt och orsaken är databasen kan man givetvis lösa problemet genom att köpa mer och/eller snabbare hårdvara. Ett billigare och enklare alternativ som man skall prova först är att undersöka om det går att optimera det befintliga på något sätt. Exempelvis genom att skapa eller modifiera index, skriva om lagrade procedurer eller denormalisera tabeller för att få bättre prestanda. I dessa inlägg beskriver jag fyra användbara sätt att identifiera var flaskhalsen finns och därigenom också veta hur problemet bäst skall lösas.

Jag har valt att dela upp inlägget i tre delar, de andra delarna publiceras de närmsta dagarna.
Del 1 går igenom hur man letar upp prestandaproblem med profiler, del 2 går igenom DMV och del 3 går igenom performance monitor.

Profiler

Profiler är ett verktyg som kan logga allt som händer i databasen. Man kan välja vilka händelser och vilken data (kolumner) som skall loggas.

Identifiera frågor som tar lång tid att exekvera

Det är mycket enkelt att ta reda på vilka frågor som tar lång tid att köra. I mitt exempel kommer jag att logga alla frågor som tar längre tid än 1 sekund att köra. Det brukar generellt vara en bra start. Om frågan tar lång tid att köra så genererar den förmodligen mycket IO och/eller CPU vilket gör frågan till en bra optimeringskandidat.
Börja med att starta sql-server profiler.

  1. Välj ”new trace”.
  2. Välj ”standard” template.
  3. Ta bort alla events förutom RPC:Completed och SQL:BatchCompleted.
  4. Kontrollera att kolumnerna textdata, cpu, reads, writes, duration är ikryssade.
  5. Klicka på kolumnen duration och mata in 1000 för ”greater than or equal”.
  6. Klicka på ”run” och låt den köra en stund

Relativt snart, beroende på hur ofta dina tunga frågor körs, bör du se data i fönstret. Om du inte får någon data kan det hända att alla dina frågor redan exekveras snabbare än 1 sekund, då kan du prova med 500ms eller 250ms.

Identifiera frågor som tar mycket IO

Det tidigare sättet identifierar enskilda frågor som tar lång tid att köra. Det är dock ännu vanligare att en fråga är relativt snabb men körs väldigt ofta och därmed tar väldigt mycket resurser från servern. För att hitta dessa frågor så loggar man alla frågor med profiler, sparar ner all data i en tabell och ställer frågor mot den. I vårt fall grupperar man på TextData-kolumnen som då innehåller hela sql-uttrycket.
Starta sql-server profiler.

  1. Välj ”new trace”.
  2. Välj ”standard” template.
  3. Ta bort alla events förutom RPC:Completed och SQL:BatchCompleted.
  4. Kontrollera att kolumnerna textdata, cpu, reads, writes, duration och starttime är ikryssade.
  5. Klicka på textdata, mata in exec sp_reset_connection i not like.
  6. Klicka på ”run” och låt den köra en stund. Gärna 24h, men beroende på trafikmängd kan det räcka med 1h. Har du färre än 100 000 poster efter 1h kan du köra ett tag till.

När du har samlat tillräckligt mycket data så skall du spara ner det i en tabell ”file -> save as -> trace table”.
I det nedanstående kodexemplet jag har har jag döpt min tabell till trace.

Mitt kodexempel bygger på att det endast är lagrade procedurer som körs. Men om du har ”vanliga” sql-frågor, kanske genererade av en ORM såsom nHibernate så måste du tvätta din trace på motsvarande sätt. Det jag gör nedan är att rensa bort allt förutom namnet på de lagrade procedurerna. Så om ett anrop såg ut såhär

exec myaccount.mystoredprocedure @Username = ‘patrikc’
så kommer det efter tvätten att endast vara myaccount.mystoredprocedure
Fört efter tvätten kan jag gruppera mina lagrade procedurer och få fram vilka procedurer som är mest kostsamma.

--rensa bort exec, hämta endast de poster som börjar på exec.
SELECT 
	SUBSTRING(CAST(textdata AS VARCHAR(MAX)),CHARINDEX('exec ',CAST(textdata AS VARCHAR(MAX))) + 5, LEN(CAST(textdata AS VARCHAR(MAX))) - CHARINDEX('exec ',CAST(textdata AS VARCHAR(MAX))) - 4) AS textdata, 
	cpu, 
	reads, 
	writes, 
	duration 
INTO   
	#trace 
FROM
	trace
WHERE
	CAST(textdata AS VARCHAR(MAX)) LIKE 'exec%' 

--vissa sps körs med sp_executesql så här raderar jag helt enkelt den informationen.
SELECT 
	REPLACE(textdata,'sp_executesql N''EXEC ','') AS textdata, 
	cpu, 
	reads, 
	writes, 
	duration 
INTO
	#trace2 
FROM
	#trace 

--rensa bort allt efter det första mellanslaget (om det finns något mellanslag). gör en union mot de poster som saknar mellanslag.
SELECT 
	SUBSTRING(textdata,1,CHARINDEX(' ',textdata) - 1) AS textdata, 
    cpu, 
    reads, 
    writes, 
    duration 
INTO   
	#trace3 
FROM
	#trace2 
WHERE  
	CHARINDEX(' ',textdata) > 0 
UNION ALL 
SELECT 
	textdata, 
    cpu, 
    reads, 
    writes, 
    duration 
FROM   
	#trace2 
WHERE  
	CHARINDEX(' ',textdata) = 0 

--hämtar tiden som tracen kördes (används för att beräkna antal exekveringar per sekund)
DECLARE  @runtime REAL 
SELECT 
	@runtime = DATEDIFF(SECOND,MIN(starttime),MAX(starttime)) 
FROM   
	trace

--grupperar alla poster på textdata och summerar cpu, reads, writes, duration. Sorterar på de poster med mest reads.
SELECT   
	textdata, 
	COUNT(0)                                             AS execcount, 
	(CAST(COUNT(0) AS REAL) / CAST(@runtime AS REAL))    AS execpersec, 
    SUM(duration)                                        AS sumduration, 
    SUM(CAST(duration AS REAL)) / CAST(COUNT(0) AS REAL) AS avgduration, 
    MAX(duration)                                        AS maxduration, 
    SUM(reads)                                           AS sumreads, 
    AVG(CAST(reads AS REAL))                             AS avgreads, 
    MAX(reads)                                           AS maxreads, 
    SUM(CAST(reads AS REAL)) / @runtime                  AS readspersec, 
    SUM(writes)                                          AS sumwrites, 
    AVG(CAST(writes AS REAL))                            AS avgwrites, 
    MAX(writes)                                          AS maxwrites, 
    SUM(CAST(writes AS REAL)) / @runtime                 AS writespersec, 
    SUM(cpu)                                             AS sumcpu, 
    AVG(CAST(cpu AS REAL))                               AS avgcpu, 
    MAX(cpu)                                             AS maxcpu, 
    SUM(CAST(cpu AS REAL)) / @runtime                    AS cpupersec 
FROM     
	#trace3 
GROUP BY 
	textdata 
ORDER BY 
	SUM(reads) DESC 

Anledningen att jag väljer att i första hand sortera på de poster med mest reads är att det i de flesta fall är de som tar mest resurser från servern. Om man får ner de största posterna på reads så kan exempelvis duration gå ner på andra frågor, just eftersom diskköerna har gått ner.

Läs del två om DMV.

4 kommentarer

  1. Christian said

    Bra blogg!

    SQL optimering är roligt och som du säger kan det spara mycket hårdvaruinvesteringar om man gör det rätt! En bra inbyggd rapport som jag hittat nyligen finns i SQL 2008 och heter Object Execution Statistics (Högerklicka på databasen under menyn Reports finns rapporten). Den här rapporten byggs upp automatiskt och ger en fingervisning för var du ska satsa dina optimerings-skills ;)

    (vill du jobba på iteam?)

    • patrik said

      Bra tips. Ja, de rapporterna kan helt klart också vara användbara för att ta reda på hur databasen används.

  2. […] 2 av 3 i en serie om att lösa prestandaproblem i sql-server. Del 1 går igenom hur man letar upp prestandaproblem med profiler, del 2 går igenom DMV och del 3 går igenom performance […]

  3. […] 3 av 3 i en serie om att lösa prestandaproblem i sql-server. Del 1 går igenom hur man letar upp prestandaproblem med profiler, del 2 går igenom DMV och del 3 går igenom performance […]

RSS feed for comments on this post

Kommentarer inaktiverade.

%d bloggare gillar detta: