DBA-versionen af en klassiker.

når du identificerer, at parameter sniffing er et problem, skal du teste, om implementering af ‘recompile’ hints vil hjælpe med at planlægge kvalitet. (Ikke sikker på, hvilken parameter sniffing er? Lær af dette blogindlæg eller denne 30 minutters gratis video.) Du skal beslutte: hvilket tip eller kommando bruger du, og hvor sætter du det? Hvilke afvejninger laver du, når det kommer til at kunne præstere tune din server i fremtiden?

dette indlæg løber gennem almindelige indstillinger, du er nødt til at skubbe (eller slå) til at generere en ny eksekveringsplan. Jeg vil give nogle fordele og ulemper for hver metode og forklare, hvad der er nyttigt og hvad der er værd at undgå.

for at holde tingene relativt enkle diskuterer jeg bare, hvordan dette gælder for lagrede procedurer i dag–dette indlæg dækker ikke andre former for parameteriserede (eller ikke parameteriserede) forespørgsler. Hvis du har store spørgsmål på disse områder, er du velkommen til at foreslå det til et fremtidigt indlæg i kommentarerne.

ansvarsfraskrivelse: rekompilere tip kan dræbe din ydeevne ved at tænde dine CPU ‘ er i brand, når de bruges forkert. Håndter med omhu!

nyttige dynamiske Ledelsesvisninger

når jeg taler om indvirkning på eksekveringsplanens cache, henviser jeg til to DMV ‘ er:

• sys. Tjek en prøve forespørgsel her.
• sys.denne DMV er kun tilgængelig på Server 2008 og nyere. Det sporer udførelse metrics for lagrede procedurer. Tjek en prøve forespørgsel i bøger Online.

for begge disse DMV ‘ er er det at have en eksekveringsplan i cachen knyttet til at kunne se eksekveringsmålinger: antal samlede henrettelser, total og gennemsnitlig CPU, logiske læsninger osv. Når en eksekveringsplan fjernes fra cachen på grund af genkompilering, hukommelsestryk, genstart eller andre handlinger, fjernes de relaterede eksekveringsmålinger også.

udførelse af en procedure med et runtime recompile hint

en mulighed, som jeg elsker til hurtig og nem test, er muligheden for at kalde en gemt procedure med et recompile hint på udførelsestidspunktet. Dette er fantastisk, fordi du ikke behøver at ændre nogen kompileret kode. Dit tip gælder også kun for, hvad du laver på runtime.

for at gøre dette bruger du bare syntaks som denne:

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 stadig få parameter sniffing på alle opkald til underprocedurer inden for selve den lagrede procedure.

så mens jeg kan lide dette nogle gange til test, hvis du har nogen nesting i dine lagrede procedurer, kan det ikke hjælpe dig med at få en virkelig frisk eksekveringsplan.

brug af Sp_recompile-Systemlagret Procedure

Dette er undertiden nyttigt, hvis du har brug for at gribe ind under en produktionshændelse, hvis du har en dårlig plan, der bruges gentagne gange af indgående 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 dette køres, fjernes relaterede planer ikke straks fra cache Til eksekveringsplan. I stedet sker magien næste gang forespørgsler, der henviser til den genkompilerede objektkørsel. På det tidspunkt nulstilles eksisterende eksekveringsstatistikker i sys.

Eksekveringstællinger i sys.dm_procedure_stats nulstilles ikke nødvendigvis ved næste brug, hvis du markerer en tabel, der bruges af en gemt procedure til genkompilering. Men hvis du markerer selve den lagrede procedure til genkompilering, nulstilles den ved næste brug. (Gider ikke at prøve at huske dette, bare læs det næste afsnit.)

Der er store ulemper med denne. Denne kommando kræver høj tilladelse-brugeren, der kører den, kræver ‘alter’ tilladelser på bordet. Det kræver også et højt niveau af lås for at fuldføre. På travle systemer har jeg set denne kommando deltage i nogle grimme blokeringskæder. Vær forsigtig med det!

selvom dette lejlighedsvis kan være nyttigt til fejlfinding og test, skal du ikke gøre det til en del af produktionskoden.

rekompilere tip i Gemte procedureoverskrifter

Når du har procedurer, som du vil generere nye planer, begynder du at se på, hvordan du bruger Tip inden for proceduren. Den mulighed, de fleste mennesker opdager først, er at bruge ‘med genkompilering’ i overskriften på den lagrede procedure, som denne:

brug af REKOMPILERING i den lagrede procedureoverskrift er ret drastisk — proceduren cache ikke en eksekveringsplan, når den kører. Dette betyder:

• ingen eksekveringsplaner i cache for at gennemgå
• ingen eksekveringsstatistik registreret i sys. Hvis du har brug for at identificere den indflydelse, denne procedure har på din server, skal du køre en slags spor eller udvidet begivenhedssession og høste og fortolke resultaterne. Det er ikke hurtigt, og løbende spor kan påvirke ydeevnen.

  slags en stor negativ. Af den grund kan jeg slet ikke lide dette valg.

  rekompilere tip om individuelle udsagn

  denne mulighed er lidt mere arbejde, men den har en meget bedre udbetaling 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:

  Transact-SQL

  1 2 3 4 5 6 7 8 9

  ALTER PROCEDURE dbo.Proc3 @parameter1 int AS vælg distinkt by som Cityframproc3 fra Person.Adresse hvor StateProvinceID=@parameter1 OPTION (rekompilere); eksekver dbo.Proc2 @parameter1; GO

  selvom du skal bruge tipet på alle udsagnene i proceduren snarere end i overskriften, har dette stadig fordele! Ved at sætte rekompilet hint på udsagnsniveauet i proc, får du magisk:

  • begrænsede eksekveringsplaner i cache til gennemgang (sidste udførelse)
  • begrænset eksekveringsstatistik optaget i sys.det faktum, at du får nogle oplysninger i disse DMV ‘ er, kan være super nyttigt over tid. Anvendelse recompile hints kun til de udsagn, der har brug for dem er også bare mere ansvarlig– det sænker din risiko for at brænde dig selv op over tid med CPU burn.
    

    DBCC FREEPROCCACHE – nuclear option

    Dette er ikke strengt en recompile hint– men det forårsager bestemt genkompilering. Server har en kommando, du kan bruge til at fortælle det, “Start på ny med nye eksekveringsplaner”. Det bruges normalt som dette:

    Transact-SQL

    1 2 3

    –This is the nuclear option DBCC FREEPROCCACHE GO

    This command makes everything start fresh– for every query:

    • CPU– brug kan gå op, når nye planer kompileres
    • Eksekveringsstatistikker ryddes i sys.DM>
    • Eksekveringsstatistikker ryddes i sys.Dm_li>
    • procedure_stats (straks)

    denne kommando er ikke ond-der er en tid og et sted i fejlfinding, når du bruger den, kan hjælpe dig med hurtigt at komme til årsagen til et ydelsesproblem. Men da det påvirker hele Cachen og også kan påvirke ydeevnen, skal du være meget forsigtig med at bruge den. Jeg anbefaler ikke at bruge denne kommando i almindelige job, automatiserede processer eller produktionskode.

    fra og med Server 2008 kan du bruge denne kommando lidt mere forsigtigt– du kan fjerne en bestemt plan fra cachen ved hjælp af “planhåndtag” eller “kvm– håndtag” – men selvfølgelig skal du finde ud af, hvad det håndtag er, og hvorfor du vil fjerne det. Dette kan være nyttigt i nogle nichesituationer, men i praksis kommer det ikke meget op. Du kan også rydde en ressource guvernør pulje, men, godt du skulle bruge ressource guvernør.

    hvad betyder det hele?

    når du tester, er der et sted i dit værktøjssæt til både ‘EKSEKVERINGSPROCEDURE med genkompilering’ og sp_recompileproceduren. Brug dem med omhu og pas på mulige konsekvenser, især med sp_recompile.

    Når du implementerer tip i kode, har du ikke genkompilere blindere på– du kan også bruge ‘Optimer til’ Tip Nogle gange med succes (selvom du nogle gange optimerer til middelmådig). Og til tider kan dynamic CCL også hjælpe dig.

    men hvis du bruger RECOMPILE hints, skal du holde dine recompiles på udsagnsniveauet– og ikke i overskrifterne i dine procedurer.

    for at lære mere, tjek vores Fundamentals of Parameter Sniffing class.