Care este parametrul sniffing și cum să-l lupta

Atunci când compilarea interogări parametrizate și proceduri stocate, SQL Server «arată“ valorile parametrilor si genereaza planuri optime de execuție pentru aceste valori. Ca urmare, în cazul în care datele din tabel sunt distribuite în mod inegal și compilarea pro.

Atunci când compilarea interogări parametrizate și proceduri stocate, SQL Server «arată“ valorile parametrilor si genereaza planuri optime de execuție pentru aceste valori. Ca urmare, în cazul în care datele din tabel sunt distribuite inegal și o compilație făcută pentru planuri valorile parametrilor atipice stocate în memoria cache nu poate fi optim pentru interogări tipice.

Ca un exemplu, imaginați-vă o companie cu sediul central situat la Moscova, cu un mic birou sucursală în București. Imaginați-vă că avem un tabel care conține informații despre personal:

crea dbo.Employee tabel

IDAngajat nu int nul,

Locație nvarchar (64) nu nul, - București sau București

Salariu bani nu nul,

cheie primară în cluster (IDAngajat)

crea IDX_Employee_Location indicele nonclustered

Marea majoritate a angajaților companiei sunt situate la Moscova. Imaginați-vă că avem o procedură stocată:

crea proc dbo.GetAvgSalaryByLocation (@Location nvarchar (64))

în cazul în care Locul de amplasare = @Location

În cazul în care o procedură stocată este compilat cu parametrul @ Locul de amplasare = plan de execuție în cache N'Buharest“va fi o scanare a indicelui cluster care este optimă pentru distribuția noastră de date. În cazul în cazul în care a existat o compilație a valorii parametrului @ Locul de amplasare = N'Buharest“, după cum va fi salvată în memoria cache căutare plan de execuție într-un index non-cluster cu operare cheie de căutare. Ca urmare, în cazul în care marea majoritate a apelurilor procedura este realizată cu parametrul @ Locul de amplasare = plan de execuție N'Buharest“nu este optimă.

Există mai multe soluții la această problemă:

1. Utilizarea variabilelor locale. În acest caz, SQL Server nu utilizează valorile parametrilor în timpul compilării

crea proc dbo.GetAvgSalaryByLocation (@Location nvarchar (64))

set @Loc = @Location

în cazul în care Locul de amplasare = @Loc

2. Utilizarea opțiunii OPTION (recompilați). Cu această opțiune, SQL Server va recompilați interogarea la fiecare apel, ceea ce presupune o povară suplimentară asupra procesorului. Pe de altă parte, planul de execuție interogare este optimă pentru orice valori ale parametrilor în fiecare apel

crea proc dbo.GetAvgSalaryByLocation (@Location nvarchar (64))

în cazul în care Locul de amplasare = @Location

3. Utilizarea Optimizați pentru opțiune. Atunci când această opțiune optimizează interogare SQL Server pentru valorile specifice ale parametrilor. Cu această opțiune, trebuie să fim siguri că distribuția statistică a datelor în sistem nu se va schimba

crea proc dbo.GetAvgSalaryByLocation (@Location nvarchar (64))

în cazul în care Locul de amplasare = @Location

opțiune (optimizare pentru (@ Location = N'Buharest „))

4. Utilizarea Optimizați pentru opțiunea NECUNOSCUT. Atunci când această opțiune optimizează interogare SQL Server pentru valorile-cheie cele mai comune bazate pe statistica

crea proc dbo.GetAvgSalaryByLocation (@Location nvarchar (64))

în cazul în care Locul de amplasare = @Location

opțiune (optimizare pentru NECUNOSCUTE)