Jakie są możliwe przyczyny sp_reset_connection zajmujące dużo czasu?

Dlaczego wykonanie sp_reset_connectionprocedury składowanej w systemie trwa dłużej niż kilka milisekund, tak jak to pokazano w programie SQL Server Profiler?

Wziąłem prosty ślad z systemu produkcyjnego za pomocą SQL Server Profiler, a następnie użyłem SqlNexus do jego analizy. SqlNexus wskazuje, że sp_reset_connection ma najwyższy łączny czas trwania - 33% ogólnego śladu. Obserwowany czas trwania wynosi od 0 do 7 sekund (12 do 6,833,270 mikrosekund), ale średnio wynosi 0,956 s.

Rozumiem, że sp_reset_connection jest wywoływany, gdy połączenie w puli zostanie ponownie wykorzystane. Widziałem sugestię, że może się tak dziać z powodu obcych śladów , ale wydaje się, że tak nie jest.

Przeczytałem, co robi serwer, gdy wywoływane jest sproc, ale nie sądzę, aby którykolwiek z nich byłby problematyczny w tym przypadku - kod nie pozostawia otwartej transakcji lub ogromnych tabel tymczasowych, które należałoby wyczyścić.

Spojrzałem również na /server/199974/sp-reset-connection-taking-a-long-time-to-run, ale nie było to pomocne.

EDYCJA (2013-12-23): We wszystkich przypadkach odczyty i zapisy wynoszą 0, a procesor prawie zawsze wynosi 0 (tylko dwa wystąpienia niezerowego procesora, oba przy 16 ms).

Wreszcie miałem trochę czasu na napisanie bardziej szczegółowej odpowiedzi.

Zazwyczaj są trzy główne powody, dla których prosta procedura, jak np. sp_reset_connection, Zajmuje dużo czasu.

1. Czekasz na zasoby procesora
2. Jesteś gdzieś zablokowany na zamku (być może w wyniku DML lub konkurencyjnej transakcji)
3. Twoja sieć działa powoli i zwrócenie wyniku klientowi zajmuje dużo czasu

Ad 1) Jeśli czekasz na zasoby procesora, powinno to pojawić się w oczekiwaniu na sygnał. Zapoznaj się z moim komentarzem do twojego pytania, jak zdiagnozować, czy to jest problem

Ad 2) Jeśli czekasz na blokadę, najlepiej to zdiagnozować, porównując dwie migawki sys.dm_os_wait_stats. Zobacz ten artykuł, jak to zrobić:

• http://www.sqlsoldier.com/wp/sqlserver/sqludbaweekbeanefficientdba

Jeśli widzisz długie oczekiwanie na LCK_ [Coś], zapytaj, sys.dm_tran_locksaby wyśledzić, które obiekty są blokowane. W twoim przypadku spodziewałbym się, że zobaczysz jakąś blokadę SCH- [Coś]> blokującą cię.

Ad 3) Najłatwiejszy sposób na zdiagnozowanie problemów z siecią, aby najpierw poszukać OLEDB i ASYNC_NETWORK_IO, czeka w kroku 2 (jeśli długo czekasz na sieć, jeden z nich się pojawi). Jeśli oczekiwania są duże, użyj xperf -on latencyprogramu monitorującego sieć, takiego jak netmon lub wireshark, aby sprawdzić swoje opóźnienia. Jeśli sieć wygląda na powolną, może to być również spowodowane tym, że serwer aplikacji wywołującej nie reaguje wystarczająco szybko na odzyskane połączenie.

Właśnie natknąłem się na artykuł z bazy wiedzy dotyczący błędu, który może być związany z tym problemem. W POPRAWCE: Problemy z wydajnością występują, gdy aktywność blokady bazy danych wzrasta w SQL Server (KB 2926217), jednym z opisanych symptomów jest to, że sp_reset_connectionwykonanie tego może zająć dużo czasu. Poprawka jest zawarta w następujących aktualizacjach:

• Zbiorcza aktualizacja 17 dla SQL Server 2008 SP3
• Zbiorcza aktualizacja 13 dla SQL Server 2008 R2 z dodatkiem SP2
• Zbiorcza aktualizacja 9 dla programu SQL Server 2012 z dodatkiem SP1
• Zbiorcza aktualizacja 1 dla programu SQL Server 2014

Serwer, na którym zaobserwowałem to zachowanie, działał z programem SQL Server 2008 SP3 z aktualizacją zbiorczą 5, więc możliwe, że wystąpił ten błąd. Nie próbowałem jeszcze aktualizacji zbiorczej (problem nie powtarza się cały czas), więc nie mogę sprawdzić, czy to naprawi, czy nie. Chciałem jednak podać informacje na wypadek, gdyby ktoś miał te same objawy.