[MSSQL] alter procedure maakt het weer snel

[foske]

Allo,

Ik zit met een probleempje, maar misschien is er een hele logische verklaring voor.

Ik heb een SP die ik run vanaf mijn webserver, gaat allemaal snel genoeg (parsetime: 0,1406 secs) alleen vandaag, en dit is de 2x dat het me overkomt, duurde de query ineens 10sec... Dus dacht hej, das niet goed
Refresh, en hij duurde nog steeds 4 sec. nog een paar geklikt en bleef zo rond de 4 sec hangen.

maar zodra ik de SP, met dezelfde code, 'alter', dan is de query weer snel.

Volgens mij heeft dit iets te maken met dat query van te voren al een queryplan heeft, maar wat kan dit probleem veroorzaken?

[whoami]

Je procedure houdt executions plans bij van de queries die moeten uitgevoerd worden.
Die execution plans zijn gebaseerd op de tabelstatistieken die op het moment van creatie van je procedure geldig zijn.
Als je iets aan je tabellen veranderd nadat je die procedure hebt aangemaakt (bv, je maakt een nieuwe index, etc.....), of er wijzigt veel data in de tabellen, dan zijn die execution plans niet echt meer optimaal te noemen.

Door dat je een ALTER PROCEDURE doet, wordt je procedure opnieuw gemaakt, en worden er nieuwe execution plans gemaakt voor die SP. Die plans zijn dan gebaseerd op de huidige statistics, en daardoor is je procedure opnieuw performant.

Je kan hetzelfde effect bekomen dmv de system stored procedure sp_recompile uit te voeren.

[ Voor 21% gewijzigd door whoami op 31-01-2004 14:25 ]

[foske]

wat valt er allemaal onder het veranderen van tabellen, valt hieronder ook dbcc dbreindex of truncate table ? Want voor de rest laat de db met rust...

[whoami]

Fossie schreef op 31 januari 2004 @ 14:25:
wat valt er allemaal onder het veranderen van tabellen, valt hieronder ook dbcc dbreindex of truncate table ? Want voor de rest laat de db met rust...

Door een truncate table, gaan al je data verloren, waardoor de statistieken ook aangepast worden dacht ik.
Door een reindex te doen, gaan je statistieken ook aangepast worden.

[foske]

Ja leek mij ook, ik stop die sp_recompile eens onderaan mijn onderhouds sp. Bedankt voor de tip

[P_de_B]

je kunt ook een UPDATE STATISTICS doen voor de betreffende tabellen, controleer ook of automatisch statistieken genereren wel aan staat (met sp_autostats)

Als er zovaak iets in de tabellen verandert dat de statistics niet up to date kunnen zijn kun je ook de WITH RECOMPILE optie in de proc gebruiken, dan zal elke keer een nieuw executieplan worden gemaakt

[whoami]

P_de_B schreef op 31 januari 2004 @ 15:56:
je kunt ook een UPDATE STATISTICS doen voor de betreffende tabellen, controleer ook of automatisch statistieken genereren wel aan staat (met sp_autostats)

Het updaten van de statistieken zal er voor zorgen dat queries sneller gaan, maar dat zal niet altijd helpen voor SP's, omdat die hun execution plan cachen.
Een update statistics gaat er niet voor zorgen dat die SP's hun plan hermaken.

[P_de_B]

whoami schreef op 31 januari 2004 @ 15:57:
[...]

Een update statistics gaat er niet voor zorgen dat die SP's hun plan hermaken.

Dat klopt, maar zorgt er wel voor dat er niet weer een verkeerd plan komt na een recompile. Om de een of andere reden zijn de statistics niet up to date waardoor er een verkeerd plan wordt bewaard.

[whoami]

P_de_B schreef op 31 januari 2004 @ 16:16:
[...]

Dat klopt, maar zorgt er wel voor dat er niet weer een verkeerd plan komt na een recompile. Om de een of andere reden zijn de statistics niet up to date waardoor er een verkeerd plan wordt bewaard.

Nee, de TS zijn procedure werkte wel goed en snel enzo, maar hij heeft ondertussen wat gewijzigd aan z'n data of tabelstructuur. Daardoor zijn de statistics niet meer up to date, en het bewaarde plan van de SP was samengesteld met -inmiddels- verouderde statistics informatie.
Door de SP te recompilen, wordt het execution plan opnieuw bepaald adhv de nieuwe statistieken.
Als je enkel de statistieken updated, zonder de SP te recompilen, zal dat waarschijnlijk geen soelaas bieden voor dit specifieke probleem.

[P_de_B]

whoami schreef op 31 januari 2004 @ 16:19:
[...]
Nee, de TS zijn procedure werkte wel goed en snel enzo, maar hij heeft ondertussen wat gewijzigd aan z'n data of tabelstructuur. Daardoor zijn de statistics niet meer up to date, en het bewaarde plan van de SP was samengesteld met -inmiddels- verouderde statistics informatie.
Door de SP te recompilen, wordt het execution plan opnieuw bepaald adhv de nieuwe statistieken.
Als je enkel de statistieken updated, zonder de SP te recompilen, zal dat waarschijnlijk geen soelaas bieden voor dit specifieke probleem.

Ok, ik heb het verkeerd begrepen, ik dacht dat het een terugkerend probleem was vandaar mijn suggestie om de statistieken te updaten en even naar de instelling van het automatisch bijwerken van de stats te kijken.