DBA-versjonen AV EN xkcd-klassiker.

når du identifiserer at parameteren sniffing er et problem, må du teste om implementering av rekompilering tips vil hjelpe plan kvalitet. (Ikke sikker på hvilken parameter sniffing er? Lær av dette blogginnlegget eller denne 30 minutters gratis videoen.) Du ma bestemme: hva hint eller kommando bruker du, og hvor legger du det? Hvilke avveininger gjør du når det gjelder å kunne ytelsesjustere SQL Server i fremtiden?

dette innlegget går gjennom vanlige alternativer du må knuse (eller knerte) SQL Server til å generere en ny utførelsesplan. Jeg skal gi noen fordeler og ulemper for hver metode og forklare hva som er nyttig og hva som er verdt å unngå.For å holde ting relativt enkelt, diskuterer jeg bare hvordan dette gjelder lagrede prosedyrer i dag – dette innlegget dekker ikke andre former for parameteriserte (eller ikke-parameteriserte) spørringer. Hvis du har store spørsmål på disse områdene, kan du gjerne foreslå det for et fremtidig innlegg i kommentarene.Disclaimer: Rekompilere tips kan drepe ytelsen ved å tenne Cpuer i brann når de brukes feil. Håndter med forsiktighet!

Nyttige Dynamiske Ledelsesvisninger

Når jeg snakker om innvirkning på utførelsesplanbufferen, refererer jeg til to Dmver:

• sys. dm_exec_query_stats – DENNE DMV er nyttig for å se de beste setningene på SQL-Serveren din, uansett om de er en del av en prosedyre eller ikke. Sjekk ut et eksempel spørring her.
• sys.dm_exec_procedure_stats – DENNE DMV er bare tilgjengelig I SQL Server 2008 og høyere. Den sporer utførelsesmålinger for lagrede prosedyrer. Sjekk ut et eksempelspørring I Books Online.

for begge Disse Dmvene er det å ha en utførelsesplan i hurtigbufferen knyttet til å kunne se utførelsesmålinger: antall totale henrettelser, total OG gjennomsnittlig CPU, logisk lesing, etc. Når en utførelsesplan fjernes fra hurtigbufferen på grunn av rekompilering, minnetrykk, omstart eller andre handlinger, fjernes også de relaterte utførelsesberegningene.

Utføre en prosedyre med en runtime rekompilering hint

Et alternativ som jeg elsker for rask og enkel testing er evnen til å ringe en lagret prosedyre med en rekompilering hint på kjøretid. Dette er bra fordi du ikke trenger å endre noen kompilert kode. Din hint gjelder også bare for hva du gjør under kjøring.

for å gjøre dette bruker du bare syntaks som dette:

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 fortsatt få parameter sniffing på noen samtaler til sub-prosedyrer innenfor den lagrede prosedyren selv.

Så mens jeg liker dette noen ganger for testing, hvis du har noen nesting i dine lagrede prosedyrer, kan det ikke hjelpe deg med å få en virkelig ny utførelsesplan.

Bruke Sp_recompile System Lagret Prosedyre

dette kommer noen ganger til nytte hvis du trenger å gripe inn under en produksjonshendelse hvis du har en dårlig plan som brukes gjentatte ganger av innkommende økter. 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 dette kjøres, fjernes ikke relaterte planer umiddelbart fra HURTIGBUFFEREN FOR utførelsesplan FOR SQL Server. I stedet skjer magien neste gang spørringer som refererer til det rekompilerte objektkjøringen. På det tidspunktet vil eksisterende utførelsesstatistikk i sys.dm_exec_query_stats bli tilbakestilt for uttalelser i planen.dm_exec_procedure_stats vil ikke nødvendigvis bli tilbakestilt ved neste bruk hvis du markerer en tabell som brukes av en lagret prosedyre for rekompilering. Men hvis du merker den lagrede prosedyren selv for rekompilering, blir den tilbakestilt ved neste bruk. (Ikke bry deg om å prøve å huske dette, bare les neste avsnitt.)

Det er store ulemper med denne. Denne kommandoen krever høy tillatelse-brukeren kjører det krever ‘endre’ tillatelser på bordet. Det krever også et høyt nivå av lås for å fullføre. På travle systemer har jeg sett denne kommandoen ta del i noen ekle blokkerende kjeder. Vær forsiktig med det!

selv om dette noen ganger kan være nyttig for feilsøking og testing, må du ikke gjøre det til en del av produksjonskoden.

REKOMPILERE hint i lagrede prosedyreoverskrifter

når du har prosedyrer som du vil generere nye planer, begynner du å se på hvordan du bruker hint i prosedyren. Alternativet de fleste oppdager først, er å bruke ‘MED REKOMPILERING’ i overskriften til den lagrede prosedyren, slik:

Bruk AV REKOMPILERING i den lagrede prosedyreoverskriften er ganske drastisk — prosedyren vil ikke cache en utførelsesplan når den kjører. Dette betyr:

• ingen utførelsesplaner i hurtigbufferen for å gjennomgå
• Ingen utførelsesstatistikk registrert i sys.dm_exec_query_stats
• Ingen utførelsesstatistikk registrert i sys.dm_exec_procedure_stats

Wow, det er en bummer. Hvis du trenger å identifisere virkningen denne prosedyren har på serveren din, må du kjøre en slags spor eller utvidet hendelsesøkt og høste og tolke resultatene. Det er ikke raskt, og løpende spor kan påvirke ytelsen.

Slags en stor negativ. Av den grunn liker jeg ikke dette valget mye i det hele tatt.

REKOMPILERE hint på individuelle uttalelser

dette alternativet er litt mer arbeid, men det har en mye bedre utbetaling over tid. 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:

selv om du trenger å bruke hintet på alle setningene i prosedyren i stedet for i overskriften, har dette fortsatt fordeler! Ved å sette rekompilere hint på setningen nivå i proc, du magisk får:

• Begrensede utførelsesplaner i cache for å gjennomgå (siste utførelse)
• Begrenset utførelsesstatistikk registrert i sys.dm_exec_query_stats. Du får bare statistikk for den siste utførelsen, men kolonnen plan_generation_num vil øke, i det minste gi deg innsikt om at noe er oppe.dm_exec_procedure_stats

det faktum at du får litt informasjon i Disse DMVs kan være super nyttig over tid. Bruk rekompilere hint bare til uttalelser som trenger dem er også bare mer ansvarlig-det reduserer risikoen for å brenne deg opp over tid MED CPU burn.

DBCC FREEPROCCACHE – nuclear alternativet

Dette er ikke strengt et rekompileringshint-men det forårsaker absolutt rekompilering. SQL Server har en kommando du kan bruke til å fortelle det, «Start på nytt med ferske utførelsesplaner». Det brukes vanligvis slik:

This command makes everything start fresh– for every query:DENNE kommandoen er ikke ond-Det er en tid og et sted i feilsøking når du bruker det, kan hjelpe deg med å komme til grunnårsaken til et ytelsesproblem raskt– Men siden det påvirker hele hurtigbufferen og også kan påvirke ytelsen, må du være veldig forsiktig med å bruke den. Jeg anbefaler IKKE å bruke denne kommandoen i vanlige jobber, automatiserte prosesser eller produksjonskode.

FRA SQL Server 2008 kan du bruke denne kommandoen litt mer forsiktig-du kan fjerne en bestemt plan fra hurtigbufferen ved hjelp av «planhåndtaket» eller » sql-håndtaket– – men selvfølgelig må du finne ut hva det håndtaket er og hvorfor du vil fjerne det. Dette kan komme til nytte i noen nisjesituasjoner, men i praksis kommer det ikke opp mye. Du kan også fjerne et ressursguvernørbasseng, men vel, du må bruke ressursguvernør.

Hva betyr det?

når du tester, er det et sted i verktøykassen for både «EXEC-prosedyre MED REKOMPILERING» og sp_recompile-prosedyren. Bruk dem med forsiktighet og pass opp for mulige konsekvenser, spesielt med sp_recompile.

når du implementerer hint i kode, må DU ikke KOMPILERE skylapper på– du kan også BRUKE ‘OPTIMALISER for’ hint noen ganger også (selv om du noen ganger optimaliserer for middelmådig). Og til tider kan dynamic sql også hjelpe deg.

men hvis du bruker REKOMPILERINGSTIPS, må du holde rekompilene dine på setningsnivå-og ikke i overskriftene til prosedyrene dine.

for å lære mer, sjekk Ut Våre Grunnleggende Parameter Sniffing klasse.