Kiedy sp_executesql odświeża plan zapytań?

Musisz wybaczyć mojej naiwności, ponieważ nie jestem DBA, ale rozumiem, że z czasem statystyki zmiany bazy danych i procedura składowana muszą zostać ponownie skompilowane, aby plan kwerendy był aktualny z najnowszymi statystykami.

Zakładając, że mam procedurę przechowywaną w mojej bazy danych, która jest zrekompilowane przed najnowszymi statystykami w pewnym regularnych odstępach czasu, jakie są implikacje w-podszewka procedurę przechowywaną w kodzie i owijając go w sp_executesqlrachunku? Czy tracę odświeżenie planu zapytań, które miało miejsce w ramach ponownej kompilacji procedury?

Jeśli jest coś jeszcze (oprócz uprawnień), które muszę wziąć pod uwagę przed wprowadzeniem tej zmiany, byłbym wdzięczny za twoje spostrzeżenia.

Przeczytałem to na MSDN:

Zdolność optymalizatora zapytań SQL Server do dopasowania nowego ciągu Transact-SQL z istniejącym planem wykonania jest utrudniona przez ciągle zmieniające się wartości parametrów w tekście ciągu, szczególnie w złożonych instrukcjach Transact-SQL.

Więc zakładając, że procedura składowana, którą próbuję wbudować i zawinąć sp_executesql, rzeczywiście zawiera pewne parametry, czy to mówi, że chociaż mój plan wykonania jest buforowany, utrudniam SQL Serverowi jego znalezienie i ponowne użycie?

Linia z MSDN mówi o użyciu EXEC(), w następujący sposób:

SET @sql = 'SELECT foo FROM dbo.bar WHERE x = ''' + @x + ''';';
EXEC(@sql);

W moich testach współczesne wersje SQL Server nadal mogą ponownie wykorzystywać taki plan, ale mogą istnieć inne zmienne (takie jak wersja lub na przykład, jeśli dodasz WHEREklauzule warunkowe na podstawie obecności niektórych parametrów - w takim przypadku wygeneruje inny plan).

Jeśli używasz, sp_executesqlwartości parametrów mogą nadal powodować problemy z węszeniem parametrów (tak jak w przypadku zwykłego SQL), ale nie ma to nic wspólnego z tym, czy SQL Server może ponownie użyć planu. Ten plan będzie używany w kółko, zupełnie jakbyś go nie używał sp_executesql, chyba że zmienne, które spowodowałyby rekompilację bezpośredniego zapytania, w którym to przypadku również zostanie ponownie skompilowany (zasadniczo SQL Server nie przechowuj wszystko z planem, który mówi „to zostało wykonane z sp_executesql, ale to nie było):

SET @sql = N'SELECT foo FROM dbo.bar WHERE x = @x;';
EXEC sp_executesql @sql, N'@x VARCHAR(32)', @x;

Jako bonus, ma wbudowaną ochronę przed dynamicznym SQL i pozwala uniknąć obawy o podwojenie pojedynczych cudzysłowów z powodu ograniczników ciągów. Napisałem o tym trochę na blogu .

Jeśli masz problemy z planem ponownego użycia i / lub parametr wąchania, niektóre rzeczy należy spojrzeć na to OPTION (RECOMPILE), OPTIMIZE FOR, optimize for ad hoc workloadsi simple/forced parameterization. Odpowiedziałem na kilka podobnych pytań w odpowiedzi na niedawny webcast tutaj, być może warto go przejrzeć:

http://sqlperformance.com/performance-palooza

Istotą jest: nie bój się używać sp_executesql, ale używaj go tylko wtedy, gdy jest to potrzebne, i zużywaj energię na nadmierną optymalizację, gdy masz rzeczywisty problem z wydajnością. Powyższy przykład jest okropny, ponieważ nie ma powodu, aby używać dynamicznego SQL tutaj - napisałem tę odpowiedź, zakładając, że masz uzasadniony przypadek użycia.

Zapytania uruchamiane przez sp_executesql są zgodne z tymi samymi regułami planów wykonania, co normalne zapytania, które nie są uruchamiane przez sp_executesql. Jeśli tekst zapytania zmieni się, tworzony jest nowy plan. Jeśli tekst nie zmienia się z powodu użytkownika parametrów, plan jest ponownie wykorzystywany. Gdy statystyki są aktualizowane, plany wygasają i nowe plany są generowane przy następnym uruchomieniu zapytania.