de DBA-versie van een xkcd-klassieker.

wanneer u identificeert dat snuiven een probleem is, moet u testen of het implementeren van ‘recompile’ hints zal helpen bij het plannen van kwaliteit. (Niet zeker welke parameter snuiven is? Leer van deze blogpost of deze 30 minuten gratis video.) Je moet beslissen: welke hint of commando gebruik je, en waar zet je het? Welke trade-offs maak je als het gaat om de mogelijkheid om de prestaties af te stemmen van uw SQL Server in de toekomst?

Dit bericht draait door veelvoorkomende opties die je moet gebruiken om SQL Server te pushen (of whack) in het genereren van een nieuw uitvoeringsplan. Ik geef een aantal voors en tegens voor elke methode en uitleggen wat nuttig is en wat de moeite waard het vermijden.

om het relatief eenvoudig te houden, bespreek ik gewoon hoe dit van toepassing is op opgeslagen procedures vandaag–dit bericht behandelt geen andere vormen van geparametreerde (of niet-geparametreerde) queries. Als je grote vragen hebt in die gebieden, voel je vrij om het voor te stellen voor een toekomstige post in de comments.

Disclaimer: Hercompile hints kunnen uw prestaties doden door uw CPU ‘ s in brand te steken wanneer ze verkeerd worden gebruikt. Handvat met zorg!

Useful Dynamic Management Views

als ik het heb over impact op de cache van het uitvoeringsplan, verwijs ik naar twee DMV ‘ s:

• sys.dm_exec_query_stats – deze DMV is handig om de top statements op uw SQL Server te zien, ongeacht of ze deel uitmaken van een procedure of niet. Bekijk hier een voorbeeldquery.
• sys.dm_exec_procedure_stats-deze DMV is alleen beschikbaar in SQL Server 2008 en hoger. Het tracks uitvoering metrics voor opgeslagen procedures. Bekijk een voorbeeldquery in Books Online.

voor beide DMV ‘ s is het hebben van een uitvoeringsplan in de cache gekoppeld aan het kunnen zien van uitvoerstatistieken: aantal totale uitvoeringen, totale en gemiddelde CPU, logische reads, enz. Wanneer een uitvoeringsplan uit de cache wordt verwijderd vanwege hercompilatie, geheugendruk, herstart of andere acties, worden ook de bijbehorende uitvoerings-metrics verwijderd.

een procedure uitvoeren met een runtime recompile hint

een optie die ik leuk vind voor snel en eenvoudig testen is de mogelijkheid om een opgeslagen procedure met een recompile hint aan te roepen tijdens het uitvoeren. Dit is geweldig omdat je geen gecompileerde code hoeft te wijzigen. Uw hint is ook gewoon van toepassing op wat je doet op runtime.

om dit te doen, gebruik je gewoon de syntaxis zoals deze:

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. U kunt nog steeds parameter snuiven op alle oproepen naar sub-procedures binnen de opgeslagen procedure zelf.

dus hoewel ik dit soms leuk vind voor het testen, kan het zijn dat als je een nesting hebt in je opgeslagen procedures het je niet helpt om een echt nieuw uitvoeringsplan te krijgen.

gebruikmakend van de Sp_recompile systeem opgeslagen Procedure

Dit is soms handig als u tijdens een productie incident moet ingrijpen als u een slecht plan hebt dat herhaaldelijk wordt gebruikt door binnenkomende sessies. 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):

wanneer dit wordt uitgevoerd, worden gerelateerde plannen niet onmiddellijk verwijderd uit de uitvoerplan-cache van SQL Server. In plaats daarvan gebeurt de magie de volgende keer dat query ‘ s verwijzen naar het opnieuw gecompileerde object uitvoeren. Op dat moment zullen bestaande uitvoerstatistieken in sys.dm_exec_query_stats worden gereset voor statements in het plan.

Het aantal uitvoeringen in sys. dm_exec_procedure_stats zal niet noodzakelijk worden gereset bij het volgende gebruik als u een tabel markeert die wordt gebruikt door een opgeslagen procedure voor hercompilatie. Maar als u de opgeslagen procedure zelf markeert voor hercompilatie, wordt deze gereset bij het volgende gebruik. (Doe geen moeite om te proberen om dit te onthouden, lees gewoon de volgende paragraaf.)

Er zijn grote nadelen met deze. Dit commando vereist hoge rechten — de gebruiker die het uitvoert vereist’ alter ‘ rechten op de tabel. Het vereist ook een hoog niveau van slot te voltooien. Op drukke systemen heb ik gezien dat dit commando deel te nemen aan een aantal vervelende blokkerende ketens. Wees er voorzichtig mee!

hoewel dit soms nuttig kan zijn voor het oplossen en testen van problemen, Maak het geen deel uit van de productiecode.

hercompileer hints in opgeslagen procedure headers

wanneer u procedures hebt die u nieuwe plannen wilt genereren, begint u te kijken hoe u hints kunt gebruiken binnen de procedure. De optie die de meeste mensen als eerste ontdekken is om ‘met opnieuw compileren’ te gebruiken in de header van de opgeslagen procedure, zoals dit:

het opnieuw compileren in de opgeslagen procedure header is behoorlijk drastisch — de procedure zal een uitvoeringsplan niet in de cache zetten als het wordt uitgevoerd. Dit betekent:

• geen uitvoeringsplannen in de cache om
• te bekijken geen uitvoerstatistieken opgenomen in sys. dm_exec_query_stats
• geen uitvoerstatistieken opgenomen in sys. dm_exec_procedure_stats

Wow, dat is een bummer. Als u de impact van deze procedure op uw server moet identificeren, moet u een soort trace of extended events-sessie uitvoeren en de resultaten oogsten en interpreteren. Dat is niet snel, en het uitvoeren van sporen kan invloed hebben op de prestaties.

een soort groot negatief. Daarom hou ik niet van deze keuze.

hercompileer hints op individuele statements

Deze optie is iets meer werk, maar heeft een veel betere uitbetaling na verloop van tijd. 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:

zelfs als u de hint moet gebruiken op alle statements in de procedure in plaats van in de header, heeft dit nog steeds voordelen! Door de hercompile hint op het statement niveau in het proc te zetten, krijg je op magische wijze:

• beperkte uitvoeringsplannen in de cache om te bekijken (laatste uitvoering)
• beperkte uitvoerstatistieken opgenomen in sys.dm_exec_query_stats. u krijgt alleen statistieken voor de laatste uitvoering, maar de kolom plan_generation_num zal toenemen, zodat u tenminste inzicht krijgt dat er iets aan de hand is.
• uitvoerstatistieken opgenomen in sys.dm_exec_procedure_stats

het feit dat u enige informatie krijgt in deze DMV ‘ s kan na verloop van tijd super nuttig zijn. Het toepassen van hercompile hints alleen op de statements die ze nodig hebben is ook gewoon meer verantwoordelijk– het verlaagt uw risico om jezelf op te branden na verloop van tijd met CPU burn.

DBCC FREEPROCCACHE – de nucleaire optie

Dit is niet strikt een hercompilatie hint– maar het veroorzaakt zeker hercompilatie. SQL Server heeft een commando dat je kunt gebruiken om het te vertellen, “Start opnieuw met nieuwe uitvoeringsplannen”. Het wordt meestal zo gebruikt:

This command makes everything start fresh– for every query:

• CPU-gebruik kan tot nieuwe plannen zijn opgesteld
• Uitvoering statistieken zijn uitgeschakeld in sys.dm_exec_query_stats (onmiddellijk)
• Uitvoering statistieken zijn uitgeschakeld in sys.dm_exec_procedure_stats (onmiddellijk)

Dit commando is geen kwaad, er is een tijd en een plaats in het oplossen van problemen bij het gebruik van het kan u helpen om de oorzaak van een probleem met de prestaties snel. Echter, omdat het invloed heeft op de hele cache en kan ook invloed hebben op de prestaties, je moet heel voorzichtig zijn met behulp van het. Ik raad niet aan om dit commando te gebruiken in normale taken, geautomatiseerde processen of productiecode.

vanaf SQL Server 2008 kunt u dit commando wat voorzichtiger gebruiken– U kunt een specifiek plan uit de cache verwijderen met behulp van de “plan handle” of “sql handle”– maar natuurlijk moet u uitzoeken wat dat handle is en waarom u het wilt verwijderen. Dit kan nuttig zijn in sommige niche situaties komen, maar in de praktijk komt het niet veel. Je kunt ook een resource governor pool wissen, maar je moet wel resource governor gebruiken.

wat betekent het allemaal?

wanneer u test, is er een plaats in uw toolkit voor zowel ‘EXEC procedure WITH RECOMPILE’ als de sp_recompile procedure. Gebruik ze met zorg en pas op voor mogelijke gevolgen, met name met sp_recompile.

wanneer u hints implementeert in code, heeft u geen recompile blinders aan– u kunt ‘optimaliseren voor’ hints soms ook succesvol gebruiken (hoewel u soms optimaliseert voor middelmatig). En soms kan dynamic sql u ook helpen.

maar als u RECOMPILE hints gebruikt, houd dan uw recompiles op het statement niveau– en niet in de headers van uw procedures.

voor meer informatie, Bekijk onze Fundamentals of Parameter Sniffing class.