DBA-versionen av en xkcd-klassiker.

När du identifierar den parametern sniffing är ett problem måste du testa om implementering av tips om kompilering hjälper till att planera kvalitet. (Är du inte säker på vilken parameter sniffing är? Lär dig av det här blogginlägget eller den här 30 minuters gratis videon.) Du måste bestämma: vilket tips eller kommando använder du, och var lägger du det? Vilka avvägningar gör du när det gäller att kunna ställa in din SQL Server i framtiden?

det här inlägget går igenom vanliga alternativ du måste knuffa (eller döda) SQL Server för att generera en ny exekveringsplan. Jag ska ge några fördelar och nackdelar för varje metod och förklara vad som är användbart och vad som är värt att undvika.

för att hålla saker relativt enkla diskuterar jag bara hur detta gäller lagrade procedurer idag–det här inlägget täcker inte andra former av parametriserade (eller icke parametriserade) frågor. Om du har stora frågor inom dessa områden kan du föreslå det för ett framtida inlägg i kommentarerna.

Disclaimer: kompilera tips kan döda din prestanda genom att tända dina processorer i brand när de används felaktigt. Hantera med försiktighet!

Användbara dynamiska Hanteringsvyer

När jag pratar om påverkan på exekveringsplanens cache hänvisar jag till två DMV:

• sys.dm_exec_query_stats – denna DMV är till hjälp för att se de bästa uttalandena på din SQL Server, oavsett om de är en del av en procedur eller inte. Kolla in en provfråga här.
• sys.dm_exec_procedure_stats – denna DMV är endast tillgänglig i SQL Server 2008 och högre. Den spårar exekveringsstatistik för lagrade procedurer. Kolla in en provfråga i böcker Online.

för båda dessa DMV: er är det kopplat att ha en exekveringsplan i cachen för att kunna se exekveringsmått: antal totala körningar, total och genomsnittlig CPU, logiska läsningar etc. När en exekveringsplan tas bort från cacheminnet på grund av omkompilering, minnestryck, omstart eller andra åtgärder tas de relaterade exekveringsmåtten också bort.

utför en procedur med en runtime recompile tips

ett alternativ som jag älskar för snabb och enkel testning är möjligheten att ringa en lagrad procedur med en recompile tips vid exekveringstid. Det här är bra eftersom du inte behöver ändra någon sammanställd kod. Din ledtråd gäller också bara vad du gör vid körning.

För att göra detta använder du bara syntax så här:

However, there’s a little bit of a gotcha. If you have nested stored procedures, the recompile hint only applies to code in the outermost procedure. Du kan fortfarande få parameter sniffa på alla samtal till delprocedurer inom den lagrade proceduren själv.

Så medan jag gillar det här ibland för testning, Om du har någon häckning i dina lagrade procedurer kan det inte hjälpa dig att få en riktigt ny exekveringsplan.

använda Sp_recompile-systemet lagrad procedur

detta är ibland praktiskt om du behöver ingripa under en produktionsincident om du har en dålig plan som används upprepade gånger av inkommande sessioner. You can force recompilation of plans by running sp_recompile against an object– most common choices are a table or a stored procedure. The command looks like this (a table in this example):

När detta körs tas inte relaterade planer omedelbart bort från SQL Servers exekveringsplancache. Istället händer magin nästa gång frågor som refererar till den omkompilerade objektkörningen. Vid den tidpunkten återställs befintlig exekveringsstatistik i sys.dm_exec_query_stats för uttalanden i planen.Exekveringsräkningar i sys.dm_exec_procedure_stats återställs inte nödvändigtvis vid nästa användning om du markerar en tabell som används av en lagrad procedur för omkompilering. Men om du markerar den lagrade proceduren själv för omkompilering, återställs den vid nästa användning. (Bry dig inte om att försöka memorera detta, läs bara nästa stycke.)

det finns stora nackdelar med den här. Detta kommando kräver hög behörighet-användaren som kör det kräver ’ändra’ behörigheter på bordet. Det kräver också en hög nivå av lås för att slutföra. På upptagna system har jag sett det här kommandot delta i några otäcka blockeringskedjor. Var försiktig med det!

även om detta ibland kan vara användbart för felsökning och testning, gör det inte till en del av produktionskoden.

kompilera tips i lagrade procedurrubriker

När du har procedurer som du vill generera nya planer börjar du titta på hur du använder tips i proceduren. Alternativet som de flesta upptäcker först är att använda ’med RECOMPILE’ i rubriken för den lagrade proceduren, så här:

använda kompilering i den lagrade procedurhuvudet är ganska drastisk — proceduren cachar inte en exekveringsplan när den körs. Detta betyder:

• inga exekveringsplaner i cachen för att granska
• ingen exekveringsstatistik inspelad i sys.dm_exec_query_stats
• ingen exekveringsstatistik inspelad i sys.dm_exec_procedure_stats

Wow, det är en bummer. Om du behöver identifiera vilken inverkan den här proceduren har på din server måste du köra någon form av spår eller utökad händelsessession och skörda och tolka resultaten. Det är inte snabbt, och körspår kan påverka prestanda.

typ av en stor negativ. Av den anledningen gillar jag inte det här valet alls.

kompilera tips om enskilda uttalanden

det här alternativet är lite mer arbete, men det har en mycket bättre utbetalning över tiden. With this option you take the RECOMPILE hint and apply it only to statements in the stored procedure where you’d like to implement the recompile, like this:

Även om du behöver använda tipset på alla uttalanden i proceduren snarare än i rubriken, har detta fortfarande fördelar! Genom att sätta omkompileringstipset på uttalandenivån i proc får du magiskt:

• begränsade exekveringsplaner i cache För att granska (senaste utförandet)
• begränsad exekveringsstatistik inspelad i sys.dm_exec_query_stats. du får bara statistik för den senaste körningen, men kolumnen plan_generation_num ökar, åtminstone ger dig insikt om att något är uppe.
• Exekveringsstatistik inspelad i sys.dm_exec_procedure_stats

det faktum att du får lite information i dessa DMVs kan vara super användbart över tiden. Att bara använda kompileringstips till de uttalanden som behöver dem är också bara mer ansvarsfullt– det sänker risken för att bränna dig själv över tiden med CPU burn.

DBCC FREEPROCCACHE – kärnkraftsalternativet

detta är inte strikt en rekompilering– men det orsakar verkligen omkompilering. SQL Server har ett kommando som du kan använda för att berätta det, ”Börja på nytt med nya exekveringsplaner”. Det brukar användas så här:

This command makes everything start fresh– for every query:

• CPU– användning kan gå upp som nya planer sammanställs
• Exekveringsstatistik rensas i sys.dm_exec_query_stats (omedelbart)
• Exekveringsstatistik rensas i sys.dm_exec_procedure_stats (omedelbart)

det här kommandot är inte ont-det finns en tid och en plats i felsökning när du använder det kan hjälpa dig att snabbt komma till grundorsaken till ett prestandaproblem. Men eftersom det påverkar hela cacheminnet och också kan påverka prestanda måste du vara mycket försiktig med att använda den. Jag rekommenderar inte att du använder det här kommandot i vanliga jobb, automatiserade processer eller produktionskod.

från och med SQL Server 2008 kan du använda det här kommandot lite mer försiktigt– du kan ta bort en specifik plan från cachen med ”planhandle” eller ”sql handle”– men naturligtvis måste du ta reda på vad det handtaget är och varför du vill ta bort det. Detta kan komma till nytta i vissa nischsituationer, men i praktiken kommer det inte upp mycket. Du kan också rensa en resursguvernörspool, men du måste använda resursguvernör.

Vad betyder allt?

när du testar finns det en plats i din verktygslåda för både ”EXEC-procedur med RECOMPILE” och sp_recompile-proceduren. Använd dem med försiktighet och akta dig för eventuella återverkningar, särskilt med sp_recompile.

När du implementerar tips i kod, har du inte kompilerade blinders på– du kan använda ’Optimera för’ tips ibland också framgångsrikt (även om du ibland optimerar för medioker). Och ibland kan dynamisk sql också hjälpa dig.

men om du använder KOMPILERINGSTIPS, behåll dina kompileringar på uttalandenivå-och inte i rubrikerna för dina procedurer.

för att lära dig mer, kolla in våra grunder för Parametersniffningsklass.