Performance optimization and tuning

Cachen tyhjentäminen

Ei tarvitse käynnistää SQL Serveriä uudelleen, että saa cachen osalta (esim. benchmarkkeja varten) tyhjennettyä.

dbcc dropcleanbuffers

Wait-statistiikan tyhjentäminen

dbcc SQLPERF('sys.dm_os_wait_stats',clear)

Execution plan

Execution plan proceduurille luodaan ensimmäistä kertaa plan cachen tyhjentämisen jälkeen uudelleen. Ongelma tästä voi syntyä sellaisessa tilanteessa, että procedurelta kysytään tietoa, joka poikkeaa tavanomaisesta.

Esimerkiksi proceduurilta kysytään jotain, joka palauttaa vain muutaman rivin vaikka normaalisti palautetaan tuhansia/kymmeniätuhansia rivejä. Tällöin execution planiksi voi optimoijan takia tulla jotain, joka poikkeaa hyvin paljon normaalista. Esim. pyydetään jotain, joka löytyy indeksistä vain muutaman kerran; tällöin voidaan tehdä index seek, mutta pyydettässä jotain, jota on taulussa esim 90% arvoista kannattaisi tehdä samantien table scan.

Erilaisia hyödyllisiä funktioita tai system view

Katso books onlinesta esimerkit ja varsinainen käyttö.

indeksin statistiikkaa

sys.dm_db_index_operational_stats

Tiedoston statistiikkaa

Hyödyllinen esim. tempdb:n vahtimiseen. Voi tarkkailla milloin mennään tempdb:n puolelle ja milloin ei.

DECLARE @db_id int;
SET @db_id = DB_ID(N'tempdb');
SELECT * from sys.dm_io_virtual_file_stats(@db_id, NULL)

Kyselyjen muistin vahtiminen

HUOM: katso nimenomaan paljonko varattu ja paljonko oikeasti käytetty. Jos varattu ja käytetty on samat, mennään tempdb:n puolelle == hidasta

select granted_memory_kb, used_memory_kb, max_used_memory_kb from sys.dm_exec_query_memory_grants where session_id = XX

Kyselyjen vahtiminen

Katso erityisesti wait_type

SELECT s2.dbid, s2.text, s1.*  

FROM sys.dm_exec_requests AS s1 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
ORDER BY s1.sql_handle

Profiler

Profilerissa voi/kannattaa tarkkailla tiettyjä varoituksia liittyen esim. sorttauksen menemiseen tempdb:n puolelle.

Profilerista valitaan pelkästään "Events -> Warnings & errors -> Sort"

Kyselyt

Kyselyistä saa helposti IO tietoja laittamalla kyselyä ennen query ikkunassa tämän. Näkyy "messages" ikkunassa luvut ja kirjoitukset:

SET STATISTICS IO ON

SET STATISTICS TIME ON

Kyselyn statuksen vahtiminen (WAIT_TYPE)

Tee jostain kyselystä looppi ja toisessa query ikkunassa tarkkaile kyselyn statusta.

Esimerkki query ikkunaan 1:

WHILE 1=1
BEGIN
SELECT Id FROM JOKUTAULU WHERE xField>1000
END

Kysely query ikkunaan 2, jota suoritetaan useita kertoja ja katsotaan wait_type (katso toisen query ikkkunan session id ensin (näkyy query ikkunan "tabissä" ja korvaa se kohtaan XX):

SELECT status, wait_type FROM sys.dm_exec_requests WHERE session_id = XX 

Mikäli status pysyy "runnable" tyyppisenä, on IO:n puolesta kaikki ok. Mikäli status on "waiting" ja wait_type = "IO_COMPLETION" voi kyseessä olla tempdb:n tallennettavat tulokset (sorttaus sieltä).

Selvittämiseen käytetään artikkelissa aiemmin mainittua tempdb:n tiedoston statistiikkaa (kuinka paljon kyselyn aikana tallennettiin ja luettiin tempdb:stä)