DBA-versio XKCD-klassikosta.

kun tunnistat, että parametrin haistelu on ongelma, sinun täytyy testata, auttaako ”recompile” – vihjeiden toteuttaminen laadun suunnittelussa. (Etkö ole varma, mitä parametria nuuhkiminen on? Opi tästä blogikirjoitus tai tämän 30 minuutin ilmainen video.) Sinun täytyy päättää: mitä vihjettä tai käskyä käytät ja mihin laitat sen? Mitä kompromisseja teet, kun se tulee pystyä suorituskyvyn virittää SQL Server tulevaisuudessa?

tämä viesti kulkee yhteisten vaihtoehtojen läpi, sinun täytyy tönäistä (tai läimäyttää) SQL Serveriä luomaan uusi suoritussuunnitelma. Annan joitakin etuja ja haittoja kunkin menetelmän ja selittää, mikä on hyödyllistä ja mitä kannattaa välttää.

pitää asiat suhteellisen yksinkertainen, Olen vain keskustella siitä, miten tämä koskee tallennettuja menettelyjä tänään–tämä viesti ei kata muita muotoja parametrisoitu (tai ei parametrisoitu) kyselyt. Jos sinulla on suuria kysymyksiä näillä aloilla, voit vapaasti ehdottaa sitä tulevaa viestiä kommenteissa.

Disclaimer: Recompile vihjeet voivat tappaa suorituksesi sytyttämällä suorittimesi tuleen väärin käytettynä. Käsittele varoen!

hyödyllisiä dynaamisia Hallintanäkymiä

Kun puhun vaikutuksesta suoritussuunnitelman välimuistiin, viittaan kahteen DMV: hen:

• sys.dm_exec_query_stats – tämä DMV on hyödyllinen näkemään SQL Serverin ylimmät väittämät riippumatta siitä, ovatko ne osa toimenpidettä vai eivät. Katso esimerkkikysely täältä.
• sys.dm_exec_procedure_stats – tämä DMV on saatavilla vain SQL Server 2008 ja uudempi. Se seuraa tallennettujen menettelyjen suoritusmittareita. Tutustu näyte kyselyn kirjoja verkossa.

molempien DMV: iden kohdalla suoritussuunnitelma välimuistissa on yhteydessä siihen, että voidaan nähdä suoritusmittarit: kokonaisteloitusten lukumäärä, kokonais-ja keskitasoinen suoritin, loogiset lukemat jne. Kun suoritussuunnitelma poistetaan välimuistista palautuksen, muistipaineen, uudelleenkäynnistyksen tai muiden toimintojen vuoksi, myös siihen liittyvät suoritusmittarit poistetaan.

toimenpiteen suorittaminen runtime recompile-vihjeellä

yksi vaihtoehto, jota rakastan nopeaan ja helppoon testaukseen, on kyky kutsua tallennettua menettelyä recompile-vihjeellä suoritusaikana. Tämä on hienoa, koska sinun ei tarvitse muuttaa mitään käännettyä koodia. Vinkkisi koskee myös vain sitä, mitä teet ajonaikana.

tehdäksesi tämän, käytä syntaksia näin:

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. Voit silti saada parametrin haistelua kaikista puheluista aliprosesseihin tallennetun menettelyn sisällä.

so while I do this sometimes for testing, if you have any pesä in your stored procedures it may not help you get a really fresh execution plan.

käyttämällä sp_recompile-järjestelmään tallennettua menettelyä

Tämä on joskus kätevä, jos sinun täytyy puuttua tuotannon aikana, jos sinulla on huono suunnitelma, jota käytetään toistuvasti tulevissa istunnoissa. 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):

kun tämä suoritetaan, siihen liittyviä suunnitelmia ei heti poisteta SQL Serverin suoritussuunnitelman välimuistista. Sen sijaan taika tapahtuu seuraavan kerran kyselyissä, jotka viittaavat uudelleen käännettyyn objektiajoon. Tässä vaiheessa olemassa olevat toteutustilastot SYS.dm_exec_query_statsissa nollataan suunnitelman lausumien osalta.

Suoritusmäärät SYS: ssä.dm_exec_procedure_stats-suoritusmääriä ei välttämättä nollata seuraavassa käytössä, Jos merkitset tallennetun menettelyn käyttämän taulukon uudelleenkompilointia varten. Mutta jos merkitset tallennetun menettelyn uudelleenkompilointia varten, se nollataan seuraavassa käytössä. (Älä vaivaudu yrittää muistaa tätä, vain lukea seuraava kappale.)

tässä on isoja miinuksia. Tämä komento vaatii suuren luvan — käyttäjä, joka käyttää sitä, vaatii ’alter’ – oikeudet pöydällä. Se vaatii myös korkean tason Lukko loppuun. Kiireisissä järjestelmissä olen nähnyt tämän komennon osallistuvan ikäviin estoketjuihin. Ole varovainen sen kanssa!

vaikka tästä voi joskus olla hyötyä vianmäärityksessä ja testauksessa, älä ota sitä osaksi tuotantokoodia.

palauta vihjeet tallennettuihin menettelyotsikoihin

kun sinulla on menettelyjä, joista haluat luoda uusia suunnitelmia, alat tarkastella, miten vihjeitä voidaan käyttää toimenpiteessä. Vaihtoehto useimmat ihmiset löytää ensin on käyttää ’kanssa käänteentekevä’ otsikossa tallennetun menettelyn, kuten tämä:

RECOMPILEN käyttäminen tallennetun prosessin otsikossa on melko rajua — menettely ei välimuistita suoritussuunnitelmaa, kun se suoritetaan. Tämä tarkoittaa:

• ei suoritussuunnitelmia välimuistissa tarkistettavaksi
• ei suoritustilastoja kirjattu sys: iin.dm_exec_query_stats
• ei suoritustilastoja kirjattu sys: iin.dm_exec_procedure_stats

Wow, se on harmi. Jos haluat tunnistaa vaikutus tämä menettely on ottaa palvelimeen, sinun täytyy ajaa jonkinlainen jäljittää tai laajennettu tapahtumia istunto ja sato ja tulkita tuloksia. Se ei ole nopeaa, ja juoksujäljet voivat vaikuttaa suorituskykyyn.

tavallaan iso negatiivinen. Siksi en pidä tästä valinnasta lainkaan.

UUSINTAVIHJEITÄ yksittäisistä lausunnoista

Tämä vaihtoehto on hieman työläämpi, mutta sillä on ajan myötä paljon parempi palkka. 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:

vaikka kaikissa väittämissä pitäisi käyttää vinkkiä eikä otsikkoa, tästä on silti hyötyä! Laittamalla recompile vihje lausuman tasolla proc, voit maagisesti saada:

• rajoitettu suoritus suunnitelmia välimuistissa tarkistaa (viimeinen suoritus)
• rajoitettu suoritus tilastot kirjataan sys.dm_exec_query_stats. saat vain tilastot viimeisestä suorituksesta, mutta plan_generation_num sarake kasvaa, ainakin antaa sinulle käsityksen, että jotain on tekeillä.
• Suoritustilastot kirjataan sys.dm_exec_procedure_stats

se, että saat joitakin tietoja näistä DMVs voi olla erittäin hyödyllinen ajan mittaan. Recompile-vihjeiden soveltaminen vain niitä tarvitseviin lausuntoihin on myös vain vastuullisempaa– se alentaa riskiä polttaa itsesi ajan myötä CPU Burnin avulla.

DBCC FREEPROCCACHE – ydinvaihtoehto

Tämä ei ole varsinaisesti takaisinveto– mutta kyllähän se aiheuttaa takaisinvedon. SQL Server on komento, jonka avulla voit kertoa sen, ”aloita uudelleen tuoreita suoritussuunnitelmia”. Sitä käytetään yleensä näin.:

This command makes everything start fresh– for every query:

• suorittimen käyttö saattaa nousta, kun uusia suunnitelmia laaditaan
• Suoritustilastot tyhjennetään sys: ssä.dm_exec_query_stats (välittömästi)
• Suoritustilastot tyhjennetään sys: ssä.dm_exec_procedure_stats (välittömästi)

Tämä komento ei ole paha– vianmäärityksessä on aika ja paikka, kun sitä käytetään, voi auttaa sinua pääsemään nopeasti suorituskykyongelman perimmäiseen syyhyn. Kuitenkin, koska se vaikuttaa koko välimuistin ja voi myös vaikuttaa suorituskykyyn, sinun täytyy olla hyvin varovainen käyttää sitä. En suosittele tämän komennon käyttämistä tavallisissa töissä, automatisoiduissa prosesseissa tai tuotantokoodissa.

kuten SQL Server 2008, voit käyttää tätä komentoa hieman varovaisemmin– voit poistaa tietyn suunnitelman välimuistista käyttämällä ”plan handle” tai ”sql handle”– mutta tietenkin sinun täytyy selvittää, mikä tuo kahva on ja miksi haluat poistaa sen. Tästä voisi olla hyötyä joissain kapeakatseisissa tilanteissa, mutta käytännössä sitä ei juuri tule. Voit myös tyhjentää Resurssienhallinnan, mutta sinun pitäisi käyttää Resurssienhallintaa.

What ’ s it all mean?

kun testaat, työkalupakissasi on paikka sekä ”EXEC procedure WITH RECOMPILE” – että sp_recompile-menettelylle. Käytä niitä huolellisesti ja varo mahdollisia seurauksia, erityisesti sp_recompile.

kun toteutat vihjeet koodina, älä käytä käänteisiä blindereitä– voit käyttää ”optimoida vihjeet” joskus myös onnistuneesti (vaikka joskus optimoitkin keskinkertaisia). Ja toisinaan, dynaaminen sql voi myös auttaa sinua ulos.

mutta jos käytät KÄÄNTÖVIHJEITÄ, pidä kääntövihjeet lausetasolla– eikä menettelyjesi otsikoissa.

saadaksesi lisätietoa tutustu parametrin Haistelutunnin perusteisiin.