Versiunea DBA a unui clasic XKCD.

când identificați că sniffing parametru este o problemă, aveți nevoie pentru a testa dacă punerea în aplicare ‘recompile’ sugestii va ajuta planul de calitate. (Nu sunteți sigur ce parametru este sniffing? Aflați din această postare pe blog sau din acest videoclip gratuit de 30 de minute.) Trebuie să decideți: ce sugestie sau comandă folosiți și unde o puneți? Ce compromisuri faci atunci când vine vorba de a fi capabil să performanță tune SQL Server în viitor?

acest post se execută prin opțiuni comune trebuie să ghiont (sau nașpa) SQL Server în generarea unui plan de execuție proaspăt. Voi da câteva argumente pro și contra pentru fiecare metodă și voi explica ce este util și ce merită evitat.

pentru a păstra lucrurile relativ simplu, eu sunt doar discuta modul în care acest lucru se aplică procedurilor stocate astăzi–acest post nu acoperă alte forme de parametrizate (sau non parametrizate) interogări. Dacă aveți întrebări mari în aceste domenii, nu ezitați să o sugerați pentru o postare viitoare în comentarii.

Disclaimer: sugestii Recompile poate ucide performanța de iluminat procesoarele pe foc atunci când sunt utilizate incorect. Manipulați cu grijă!

Vizualizări utile de gestionare dinamică

când vorbesc despre impactul asupra cache – ului planului de execuție, mă voi referi la două DMV-uri:

• SYS.dm_exec_query_stats-acest DMV este util pentru a vedea instrucțiunile de top de pe serverul SQL, indiferent dacă fac parte dintr-o procedură sau nu. Consultați o interogare de probă aici.
• sys.dm_exec_procedure_stats – acest DMV este disponibil numai în SQL Server 2008 și mai mare. Acesta urmărește metrici de execuție pentru procedurile stocate. Consultați o interogare de probă în cărți Online.

pentru ambele DMV-uri, a avea un plan de execuție în cache este legat de posibilitatea de a vedea metrici de execuție: numărul de execuții totale, CPU total și mediu, citiri logice etc. Când un plan de execuție este eliminat din memoria cache din cauza recompilării, presiunii memoriei, repornirii sau a altor acțiuni, valorile de execuție aferente sunt eliminate, de asemenea.

executarea unei proceduri cu un indiciu de recompilare în timpul rulării

o opțiune care îmi place pentru testarea rapidă și ușoară este capacitatea de a apela o procedură stocată cu un indiciu de recompilare la timpul de execuție. Acest lucru este minunat, deoarece nu trebuie să schimbați niciun cod compilat. Sugestia dvs. se aplică și la ceea ce faceți în timpul rulării.

pentru a face acest lucru, utilizați doar sintaxa ca aceasta:

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. Puteți obține în continuare parametru sniffing pe orice apeluri la sub-proceduri în cadrul procedurii stocate în sine.

deci, în timp ce îmi place acest lucru uneori pentru testare, dacă aveți cuiburi în procedurile stocate, este posibil să nu vă ajute să obțineți un plan de execuție cu adevărat proaspăt.

utilizarea procedurii stocate a sistemului Sp_recompile

Acest lucru este uneori util dacă trebuie să interveniți în timpul unui incident de producție dacă aveți un plan rău utilizat în mod repetat de sesiunile primite. 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):

când se execută, planurile conexe nu sunt imediat eliminate din cache-ul planului de execuție SQL Server. În schimb, magia se întâmplă data viitoare interogări care fac referire la obiectul recompilat rula. În acel moment, statisticile de execuție existente în sys.dm_exec_query_stats vor fi resetate pentru declarațiile din plan.

Numărul de execuție în sys.dm_exec_procedure_stats nu va fi neapărat resetat la următoarea utilizare dacă marcați un tabel utilizat de o procedură stocată pentru recompilare. Dar dacă marcați procedura stocată în sine pentru recompilare, aceasta va fi resetată la următoarea utilizare. (Nu vă deranjați să încercați să memorați acest lucru, citiți doar paragraful următor.)

sunt mari dezavantaje cu acesta. Această comandă necesită permisiune ridicată — utilizatorul care o execută necesită permisiuni de modificare pe masă. De asemenea, necesită un nivel ridicat de blocare pentru a finaliza. Pe sistemele de ocupat am văzut această comandă să ia parte la unele lanțuri de blocare urât. Fii atent cu ea!

deși acest lucru poate fi ocazional util pentru depanare și testare, nu-l face parte din Codul de producție.

recompilați indicii în anteturile de procedură stocate

când aveți proceduri pe care doriți să le generați planuri noi, începeți să vă uitați la modul de utilizare a indicațiilor în cadrul procedurii. Opțiunea cei mai mulți oameni descoperă mai întâi este de a utiliza ‘cu RECOMPILE’ în antetul procedurii stocate, ca aceasta:

utilizarea RECOMPILĂRII în antetul procedurii stocate este destul de drastică — procedura nu va cache un plan de execuție atunci când rulează. Acest lucru înseamnă:

• nu există planuri de execuție în cache pentru a revizui
• nu există statistici de execuție înregistrate în sys.dm_exec_query_stats
• nu există statistici de execuție înregistrate în sys.dm_exec_procedure_stats

Wow, asta e un bummer. Dacă trebuie să identificați impactul pe care această procedură îl are asupra serverului dvs., trebuie să rulați un fel de sesiune de urmărire sau evenimente extinse și să recoltați și să interpretați rezultatele. Acest lucru nu este rapid, iar rularea urmelor poate afecta performanța.

un fel de mare negativ. Din acest motiv, nu-mi place deloc această alegere.

recompilați sugestii pe declarații individuale

această opțiune este un pic mai mult de lucru, dar are un câștig mult mai bine în timp. 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:

chiar dacă aveți nevoie pentru a utiliza indiciu cu privire la toate declarațiile în procedura, mai degrabă decât în antet, acest lucru are încă beneficii! Prin punerea indiciu recompile la nivelul declarației în proc, veți obține Magic:

• planuri de execuție limitate în cache pentru a revizui (Ultima execuție)
• statistici de execuție limitate înregistrate în sys.dm_exec_query_stats. veți obține statistici doar pentru ultima execuție, dar coloana plan_generation_num va crește, cel puțin oferindu-vă o perspectivă că ceva este în sus.
• statistici de execuție înregistrate în sys.dm_exec_procedure_stats

faptul că obțineți unele informații în aceste DMV-uri poate fi foarte util în timp. Aplicarea sugestiilor de recompilare numai la declarațiile care au nevoie de ele este, de asemenea, mai responsabilă– vă reduce riscul de a vă arde în timp cu arderea procesorului.

DBCC FREEPROCCACHE-opțiunea nucleară

acesta nu este strict un indiciu de recompilare– dar cu siguranță provoacă recompilare. SQL Server are o comandă pe care o puteți utiliza pentru a spune, „începe din nou cu planuri de execuție proaspete”. Este de obicei folosit astfel:

This command makes everything start fresh– for every query:

• utilizarea procesorului poate crește pe măsură ce sunt compilate noi planuri
• statisticile de execuție sunt șterse în sys.dm_exec_query_stats (imediat)
• statisticile de execuție sunt șterse în sys.dm_exec_procedure_stats (imediat)

această comandă nu este rea– există un timp și un loc în depanare atunci când o utilizați vă poate ajuta să ajungeți rapid la cauza principală a unei probleme de performanță. Cu toate acestea, deoarece afectează întreaga memorie cache și poate afecta și performanța, trebuie să fiți foarte atenți să o utilizați. Nu recomand utilizarea acestei comenzi în niciun fel de lucrări obișnuite, procese automate sau cod de producție.

începând cu SQL Server 2008, puteți utiliza această comandă un pic mai ușor– puteți elimina un plan specific din memoria cache folosind „mâner plan” sau „mâner sql”– dar, desigur, trebuie să vă dați seama ce este acel mâner și de ce doriți să îl eliminați. Acest lucru ar putea fi util în unele situații de nișă, dar în practică nu apare prea mult. De asemenea, puteți șterge un fond de guvernator al resurselor, dar, bine, ar trebui să utilizați resource governor.

ce înseamnă totul?

când testați, există un loc în setul de instrumente atât pentru procedura EXEC cu recompilare, cât și pentru procedura sp_recompile. Folosiți-le cu grijă și feriți-vă de posibilele repercusiuni, în special cu sp_recompile.

când implementați indicii în cod, nu aveți ochelari de recompilare– puteți utiliza indicii ‘optimizați pentru’ uneori și cu succes (deși uneori optimizați pentru mediocru). Și uneori, sql dinamic vă poate ajuta, de asemenea.

dar dacă utilizați sugestii RECOMPILE, vă rugăm să păstrați recompile la nivel de declarație– și nu în anteturile procedurilor.

pentru a afla mai multe, a verifica afară fundamentele noastre de clasa parametru Sniffing.