Veckans fråga med svar – vecka 12

Veckans fråga kommer ovan molnen på väg till vår nästa utbildning i Göteborg. Steinar skrev förra veckan att det blir viktigt att kunna klara sig på så få CORE som möjligt av kostnadsskäl med SQL Server 2012 pga den nya licensmodellen.

Fråga vecka 12

Vad kan vi som utvecklare göra för att klara oss med färre CORE ?

Svar: Hittills har vi som utvecklare kommit ganska lindrigt undan även om vi skulle slarva med att kontrollera hur våra frågor verkligen exekveras. Saknas det t. ex. ett index och SQL Servers optimerare väljer  att göra en “table scan” så kan dessa ofta göras parallellt. Har vi då tillgång till många kärnor så går det ganska fort i alla fall. Nu är det slut med det. Självfallet skall vi även i fortsättningen utnyttja flera kärnor när vi behöver det – men det blir än viktigare att göra det i RÄTT frågor och inte slösa med det.  

Orsaker till parallellism och hur vi hanterar den:

– Komplexa och/eller långa frågor
Vissa typer av frågor kräver hantering av stora datamängder, t.ex. rapporter med aggregerad information. Dessa har ofta mycket att vinna på att utnyttja flera kärnor och ska få göra det i fortsättningen också.  

 – Saknade/felaktiga index
Ett saknat index kan leda till onödig parallellisering av frågor som med rätt index skulle kunna exekvera snabbt även på en kärna.

– Ta för vana att inte bara kontrollera att du får rätt svar – utan kasta också ett öga på exekveringsplanen.
Titta efter de typiska blåa symbolerna, du ser dem snabbt.  

 – Konfiguration
SQL Server har huvudsakligen två inställningar som avgör om en viss fråga kommer att köras parallellt eller ej (förutsatt att det går!). Dessa inställningar ligger ibland utanför utvecklarens kontroll, men det är viktigt att känna till hur de fungerar. (Och många utvecklare dubblerar ju som DBA också!) Du kan enkelt kontrollera vilka inställningar du har med denna fråga:  SELECT name, value_in_use FROM sys.configurations c WHERE c.name LIKE ‘%max degree%’ OR c.name LIKE ‘%cost threshold%’    

 – Max degree of parallelism (MAXDOP)
Anger hur många kärnor varje steg i exekveringsplanen maximalt kan utnyttja och kan ställas in mellan 0 och max antal kärnor på servern. (0=parallellisera maximalt, 1=”avstängd” parallellisering). Vilket värde som är optimalt beror bland annat på antalet tillgängliga kärnor, vilket typ av frågor som ställs (komplexa eller enkla) mm, men det får vi återkomma till.  

– Cost threshold for parallelism
För varje exekveringsplan som SQL Server tar fram beräknas en total “kostnad” för exekveringen  (“Estimated subtree cost” i bilden nedan). Om den kostnaden för den sekvensiella planen är högre än “cost threshold” så försöker SQL Server hitta en billigare parallell plan istället. Denna inställning har haft defaultvärdet 5 ända sedan SQL 2000 (och har det fortfarande i SQL 2012!), vilket är alldeles för lågt i de flesta sammanhang. En fråga med så låg kostnad vinner oftast inte mycket på att parallelliseras. Vad är då “rätt” inställning? Som vanligt; det beror på. Ett sätt att hitta en hyfsad nivå är att titta på de planer som SQL Server har sparat i cachen. Där plockar vi fram alla frågor som körs parallellt och går igenom de med lägst kostnad. Dessa testkör vi både parallellt och sekventiellt (använd query hint OPTION (MAXDOP 1)) och jämför exekveringstiderna. Är den inte avsevärt längre så kan vi höja cost threshold till strax över den (sekventiella) kostnaden.  

Var ska jag börja då?
Titta på de aktuella frågorna i cachen med hjälp av denna fråga. Sedan är det bara till att beta av dem en efter en. OBS! På större system kan den ta lång tid att köra och det är bäst att köra den när det inte är så hög belastning!  

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT  n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS Cost  
 , n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') AS IsParallel  
 , cp.objtype  
 , cp.usecounts  
 , cp.size_in_bytes  
 , st.text AS SQLStatement  
 , query_plan 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st 
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n) 
WHERE  n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]')  = 1 
ORDER BY IsParallel DESC, Cost

 /Martin Höglund