none
Vyprchávání timeoutu v důsledky prudkého nárůstu zámků

    Dotaz

  • Nad MS SQL Serverem 2005 vyvíjíme a provozujeme ASP.NET aplikaci. Systém je provozována na několika SQL serverech (myšleno nezávislých PC) dle zákazníka. V rámci jednoho SQL serveru je provozována vždy pouze jedna nesystémová databáze a to pro naši aplikaci.

    Pouze na některých serverech se nyní potýkáme s vyprcháváním timeoutu SQL příkazů – ASP.NET pak vrací chybu „Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.“

    Při sledování ukazatelů přes perfmon je pak možné v případě výše uvedených problémů pozorovat cca deseti až třicetinásobný nárůst ukazatele „lock request" oproti normálu. Nárůst zámků pak skoro až přímou úměrou kopíruje i nárůst zatížení CPU.

     Snažil jsem se najít slabé místo přes Activity Monitor a různé selecty pracující nad systémovými tabulkami/view/funkcemi pro zámky, ale nějak nejsem úspěšný. Z mého pohledu vypadá vše OK.

    Výše popsaného problému je možné se zbavit okamžitě zavoláním dbcc freeproccache, ale to mně jako řešení vůbec neuspokojuje. Pokud bych tento příkaz nespustil, situace v DB se samovolně nevyřeší a klidně je rozhozena i po několika hodinách.

    Napadá Vás co a jak sledovat, abych se dobral k nějakému zdárnému řešení? Předem díky za jakoukoliv radu.

    • Upravený Tom82 5. března 2010 15:42 překlep
    5. března 2010 15:28

Odpovědi

  • Děláte nějakou údržbu indexů a statistik? Zkuste je rebuildovat.
    Taky si ověřte, že nejde o deadlock (čemuž ale zátěž CPU na 100% neodpovídá).

    Nepoužíváte náhodou storky, které by pro různé hodnoty vstupních parametrů měly různé optimální execution plany? Zkuste si pohrát s volbou OPTION (RECOMPILE).


    Robert Haken, Microsoft MVP ASP/ASP.NET, HAVIT, s.r.o., www.havit.cz, http://knowledge-base.havit.cz
    8. března 2010 22:35
    Moderátor
  • Dobrý den,

    předpokládám, že poslední SP máte naistalovaný. Pro prokazatelné odhalení příčiny doporučuji monitoring pomocí Performance monitoru ve spojení s Profilerem a korelaci naměřených hodnot z těchto dvou nástrojů v prostředí Profileru. Jinak souhlasím s Robertem - to, že vyprázdnění procedurální keše pomůže, by mohlo ukazovat na problém s optimálností nakešovaných plánů pro volání procedur s různými hodnotami parametrů nebo neaktuální statistiky či fragmentaci indexů.

    S pozdravem

    Miloslav Peterka

    9. března 2010 21:34
  • Dobry den,

    otazkou je, jestli je pricina problemu v ulozenych procedurach. Spis bych zpocatku doporucil kompletni performance monitoring serveru.
    Abyste nasel problematickou proceduru(y), je treba zjistit, jake procedury SQL Server vykonava v dobe, kdyz Vam citace v performance monitoru davaji vyrazne vyssi hodnoty, nez jsou ty obvykle. K tomu je treba provadet paralelne monitoring performance monitorem a profilem a pote k odchytanym udalostem v profileru nahrat log z performance monitoru (menu File -> Import Performance Data...) a provest jejich vzajemnou korelaci. V profileru doporucuji odchytavane udalosti co mozna nejvice vyfiltrovat (databaze, trvani udalosti...). Vybral bych nazacatek rozhodne udalost SP:Completed a dale pak SP:CacheHit, aby bylo mozne urcit, ze se pouzil nacacheovany exekucni plan.
    Jinak autor procedury by mel pri jejim programovani vedet, jestli zmena hodnoty parametru muze vyrazne ovlivnit mnozstvi dat, se kterymi procedura pracuje - z toho pak vyplyne pozadavek na vytvoreni procedury s volbou RECOMPILE, aby se pri kazdem spusteni procedury generoval novy exekucni plan optimalni pro dane hodnoty parametru. Alternativou je spustit proceduru s volbou RECOMPILE pri netypickych hodnotach parametru.

    MP.

    15. března 2010 21:25

Všechny reakce

  • mas u neinvazivnich dotazu kde je mozne "WITH (NOLOCK)" ?

    MP

    5. března 2010 15:43
  • U nejčastěji prováděných, resp. potenciálně "problémových" dotazů toto řešíme. Ne vždy to ale jde a co si budeme povídat, vždy záleží i na zkušenosti toho konkrétního programátora... Nemyslím si však, že toto je správná cesta k řešení příčiny problému.

    Izolace transakcí atd. má určitě vliv na odezvy a vytížení databáze. Ale nevidím důvod, proč by v mém případě měla databáze běžně využívat okolo 10 % CPU a najednou by to mělo prdce vzrůst a dokud to nebudu nějakým způsobem řešit, tak zabere nějakých 70 - 100 % CPU. Stejně tak i v případě zámků - skoro žádné a pak mraky...

    Navíc se mně nedaří vypozorovat ani nějaký klíč podle kterého by se problém vyskytoval. Nastává pouze na 2 z 5 databázových serverů, které jsou přitom všechny užívány přibližbě stejně. Vyskytne se jak při silném, tak i mírném vytížení... Někdy se problém na problémovém serveru nevyskytne měsíc a jindy je to i třeba 6x za den...

    To vše mě prostě vede na něco jiného, jen nevím na co... :-|

    5. března 2010 20:17
  • Děláte nějakou údržbu indexů a statistik? Zkuste je rebuildovat.
    Taky si ověřte, že nejde o deadlock (čemuž ale zátěž CPU na 100% neodpovídá).

    Nepoužíváte náhodou storky, které by pro různé hodnoty vstupních parametrů měly různé optimální execution plany? Zkuste si pohrát s volbou OPTION (RECOMPILE).


    Robert Haken, Microsoft MVP ASP/ASP.NET, HAVIT, s.r.o., www.havit.cz, http://knowledge-base.havit.cz
    8. března 2010 22:35
    Moderátor
  • Dobrý den,

    předpokládám, že poslední SP máte naistalovaný. Pro prokazatelné odhalení příčiny doporučuji monitoring pomocí Performance monitoru ve spojení s Profilerem a korelaci naměřených hodnot z těchto dvou nástrojů v prostředí Profileru. Jinak souhlasím s Robertem - to, že vyprázdnění procedurální keše pomůže, by mohlo ukazovat na problém s optimálností nakešovaných plánů pro volání procedur s různými hodnotami parametrů nebo neaktuální statistiky či fragmentaci indexů.

    S pozdravem

    Miloslav Peterka

    9. března 2010 21:34
  • Děkuji moc za odpovědi. SQL server je průběžně záplatováný. Rebuild indexů probíhá vždy v noci a to při fragmentaci < 30 % REORGANIZE, při více pak REBUILD. Obdobně i RECOMPILE statistik.

    Procedury a funkce, které by mohli mít různé optimální exekuční plány v důsledku vstupů by se nejspíše našly. Moje otázka nyní je, jak je nějak rozumně najít... Můžete prosím doporučit, jaké ukazatele profilerem sledovat? Dotazů proudí do databáze celkem dost, takže nechci být zahlcen zbytečnostmi a přitom bych zase nechtěl nic opomenout...

    Díky! Tom

    11. března 2010 18:44
  • Dobry den,

    otazkou je, jestli je pricina problemu v ulozenych procedurach. Spis bych zpocatku doporucil kompletni performance monitoring serveru.
    Abyste nasel problematickou proceduru(y), je treba zjistit, jake procedury SQL Server vykonava v dobe, kdyz Vam citace v performance monitoru davaji vyrazne vyssi hodnoty, nez jsou ty obvykle. K tomu je treba provadet paralelne monitoring performance monitorem a profilem a pote k odchytanym udalostem v profileru nahrat log z performance monitoru (menu File -> Import Performance Data...) a provest jejich vzajemnou korelaci. V profileru doporucuji odchytavane udalosti co mozna nejvice vyfiltrovat (databaze, trvani udalosti...). Vybral bych nazacatek rozhodne udalost SP:Completed a dale pak SP:CacheHit, aby bylo mozne urcit, ze se pouzil nacacheovany exekucni plan.
    Jinak autor procedury by mel pri jejim programovani vedet, jestli zmena hodnoty parametru muze vyrazne ovlivnit mnozstvi dat, se kterymi procedura pracuje - z toho pak vyplyne pozadavek na vytvoreni procedury s volbou RECOMPILE, aby se pri kazdem spusteni procedury generoval novy exekucni plan optimalni pro dane hodnoty parametru. Alternativou je spustit proceduru s volbou RECOMPILE pri netypickych hodnotach parametru.

    MP.

    15. března 2010 21:25