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.