De OPTION (RECOMPILE) optie gebruiken voor een statement

Ik ben deze serie begonnen met de post getiteld: Little Bobby Tables, SQL Injection en EXECUTE AS. Daarna besprak ik een aantal verschillen met de post getiteld: EXEC en sp_executesql – hoe zijn ze verschillend?

Vandaag wil ik ingaan op een paar van de opmerkingen en doorgaan met een paar tips en trucs bij het gebruik van deze commando’s.

Eerst – hadden we de prestaties van het sp_executesql statement kunnen verbeteren?

Ja…

Als we weten dat een statement een variërende hoeveelheid data retourneert (gebaseerd op de opgegeven parameters), dan kunnen we de SQL Server 2005 functie WITH RECOMPILE gebruiken om SQL Server te vertellen dat voor het statement dat wordt uitgevoerd een eigen plan moet worden gemaakt en dat eerdere plannen (als ze bestaan) het statement niet opnieuw moeten gebruiken. Het vertelt SQL Server ook dat dit specifieke plan een “single-use plan” is dat niet hergebruikt mag worden door volgende gebruikers. Om de combinatie van al deze dingen te zien – zal ik een aantal van de DMVs gebruiken die plan cache en plan gebruik bijhouden.

DBCC FREEPROCCACHE
GO

SELECT st.text, qs.EXECUTION_COUNT -, qs.*, cp.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS cp
WERE st.text like ‘%FROM dbo.member%’
GO

Op dit moment retourneert deze query 0 rijen.

Ik voer het volgende uit en controleer dan opnieuw de plan cache:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.member WHERE lastname LIKE @lastname’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Tripp’
GO

Nu hebben we een rij voor ons geparametriseerde query plan:

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 1

Dus, wat laat dit ons zien… het laat ons zien dat er een plan in cache is voor dit statement. En, als we geïnteresseerd zijn in het plan, kunnen we het becommentarieerde cp.* in de query hierboven verwijderen om de cp.query_plan kolom te krijgen. Klik op een XML-showplan en SSMS gaat DIRECT naar een grafisch queryplan-venster (vanaf 2008):

En, nogmaals, we zien het optimale plan (om de index te gebruiken en een bookmark lookup te doen) omdat deze query zeer selectief is (slechts 1 rij).

We voeren nu weer precies hetzelfde statement uit – met de waarde Anderson, alleen maar om weer op het punt te komen waar we vorige week waren:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.member WHERE lastname LIKE @lastname’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Anderson’
GO

En, we zien dat het EXACT hetzelfde plan gebruikt (als we kijken naar showplan). In feite kunnen we dat ook zien door onze plan cache te controleren:

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2

En, terwijl we weten dat dit plan (om de index te gebruiken) goed is voor de zeer selectieve waarde van ‘Tripp’ is het niet goed voor de waarde van Anderson omdat er veel rijen zijn die overeenkomen. Als we dit vermoedden en/of wisten toen we uitvoerden (vanaf de client), dan zouden we de OPTIE (RECOMPILE) kunnen gebruiken om SQL Server te dwingen een nieuw plan te krijgen:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.member WHERE lastname LIKE @lastname OPTION (RECOMPILE)’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Anderson’
go

Het queryplan als resultaat van deze uitvoering is:

En, dit is het meest optimale plan gegeven deze parameter. Op dit punt zijn de vragen (IMO):

1. Zou iemand echt in staat zijn om programmatisch te schatten dat een parameter die door de client wordt opgegeven “atypisch” is en/of dat deze hercompilatie rechtvaardigt? En ik denk dat ik ja kan antwoorden – voor sommige parameters – zoals die met een wildcard. Maar als we het hebben over twee verschillende waarden voor een enkele kolom, zou dit gissen zijn naar de statistieken van de gegevens.
2. Zou dit specifieke statement altijd moeten worden uitgevoerd om te hercompileren en nooit een plan moeten opslaan?
3. Wat heeft SQL Server met het plan zelf gedaan?

Ik ga eerst voor het antwoord op #3, omdat dat een gemakkelijk te beantwoorden antwoord is. Met hetzelfde statement zal ik opnieuw de plan cache bevragen:

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2

Ondanks dat dit de derde keer is dat we dit statement hebben uitgevoerd, is deze laatste uitvoering NIET in de cache geplaatst. Het is alleen gebruikt voor de uitvoering met OPTIE (RECOMPILE). En, het heeft GEEN invloed op toekomstige executies. Als ik terugga en het uitvoer zonder de OPTION (RECOMPILE) dan krijg ik het vorige plan (om de index te gebruiken).

Nou, de andere twee vragen – deze zijn een stuk interessanter en dit is waar ik denk dat stored procedures gebruikt zouden moeten worden. Persoonlijk denk ik dat ontwikkelaars die de gegevens kennen en de applicatie kennen – veel beter in staat zullen zijn om de juiste code te maken, vooral als ze alle opties begrijpen die tot hun beschikking staan.

Hier volgt hoe ik denk over stored procedures en hercompilatie:

• Als ik weet dat een bepaald statement altijd hetzelfde aantal rijen retourneert en hetzelfde plan gebruikt (en, ik zou dit weten door te testen), dan maak ik de stored procedure normaal en laat het plan in de cache staan.
• Als ik weet dat een bepaald statement wild varieert van uitvoering tot uitvoering en het optimale plan varieert (ook dit zou ik moeten weten uit het testen van meerdere voorbeeld uitvoeringen), dan maak ik de opgeslagen procedure normaal aan en gebruik ik OPTION (RECOMPILE) om ervoor te zorgen dat het plan van het statement niet wordt gecached of opgeslagen met de opgeslagen procedure. Bij elke uitvoering krijgt die opgeslagen procedure andere parameters en het bijzonder vervelende statement krijgt bij elke uitvoering een nieuw plan.

Hier wordt het echter ook een stuk lastiger. Ik heb vaak stored procedures gezien waar mensen conditionele logica proberen te gebruiken om de verschillende soorten parameters op te splitsen en dit werkt meestal niet zo goed als verwacht (ik zal dit volgende bloggen). En, dit is altijd waar sommigen besluiten dat ze dynamisch het statement willen bouwen dat wordt uitgevoerd – nu moeten we bepalen of we sp_executesql of EXEC moeten gebruiken. En, er zijn echt een paar opties op dit punt. Uiteindelijk, in nog een of twee posts – zal ik laten zien waar EXEC een duidelijke winnaar is boven sp_executesql omdat zelfs de OPTIE (RECOMPILE) niet altijd ALLE soorten plannen helpt (en vooral een van de meer voorkomende soorten plannen die ik zie).