W przypadku jednego z naszych klientów mieliśmy problemy z wydajnością naszej aplikacji. Jest to aplikacja internetowa .NET 3.5, która zużywa i aktualizuje dane w bazie danych SQL Server. Obecnie nasze środowisko produkcyjne składa się z komputera z systemem Windows 2008 R2 jako interfejsu użytkownika i klastra SQL Server 2008 R2 z zaplecza. Nasza aplikacja używa COM + i MSDTC do łączenia się z bazą danych.
Oto, co się dzieje: nasi użytkownicy końcowi czasami narzekają na powolność działania aplikacji. Niektóre strony ładują się dłużej niż można by się spodziewać. Próbując dowiedzieć się, co się dzieje, udało mi się znaleźć dziwne zachowanie po stronie bazy danych, które może być przyczyną obniżenia wydajności. Zauważyłem, że czasami są pewne instrukcje SQL, których uruchomienie zajmuje dużo więcej czasu, niż można by się spodziewać. Udało mi się zidentyfikować niektóre z tych instrukcji (głównie wywołania niektórych procedur przechowywanych naszej aplikacji) za pomocą śledzenia profilera (z szablonem TSQL_Duration) w celu zidentyfikowania długo działających zapytań.
Problem polega na tym, że kiedy uruchamiam te procedury składowane bezpośrednio w bazie danych w SQL Management Studio, czasem zabierają one dużo czasu (około 7/8 sekund), innym razem są szybkie (poniżej 1 sekundy). Nie wiem, dlaczego tak się dzieje i doprowadza mnie to do szału, ponieważ maszyna SQL (4 rdzenie, 32 GB) nie jest używana przez inne aplikacje, a te zapytania nie powinny trwać tak długo.
Nie będąc guru DBA ani SQL Server, próbowałem przyjrzeć się niektórym rzeczom, które mogą pomóc mi zrozumieć problem. Oto kroki, które podjąłem, aby rozwiązać problem i czego do tej pory się dowiedziałem:
- Cały kod TSQL wywoływany przez aplikację jest zapisywany w procedurach przechowywanych.
- Zidentyfikowałem niektóre z długo działających zapytań w programie SQL Server do profilowania, jednak kiedy uruchamiam je w Management Studio, ich uruchomienie trwa długo (od 4 do 10 sekund) lub szybko (poniżej 1 sekundy). Korzystam z tych samych zapytań z tymi samymi danymi przekazanymi w parametrach. Te zapytania są głównie procedurami składowanymi, w których znajdują się instrukcje select.
- Próbowałem przyjrzeć się statystykom oczekiwania i kolejek, aby dowiedzieć się, czy niektóre zasoby oczekują na procesy. Uruchomiłem następujące zapytanie:
WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GOOto, co odkryłem:
- Po zresetowaniu statystyk za pomocą DBCC SQLPERF (około 1 lub 2 godziny później), typy oczekiwania, które mam najwięcej, to SOS_SCHEDULER_YIELD i WRITELOG
- Z czasem (po około 1-dniowym wykonaniu) typami oczekiwania, które najczęściej występują w bazie danych, są CXPACKET (67%) i OLEDB (17%), mimo że średni czas oczekiwania dla każdego z nich nie jest długi. Zauważyłem również, że dłużej działające instrukcje zidentyfikowane w SQL Profiler są wywołaniami procedur przechowywanych, które zwracają więcej niż jeden zestaw wyników (często 3). Czy może tu występować problem paralelizmu? Czy jest jakiś sposób, aby spróbować ustalić, czy to jest przyczyną problemu?
- Czytałem gdzieś, że oczekiwania OLEDB mogą być spowodowane wywołaniami do zasobów OLEDB, takich jak połączone serwery. Mamy połączony serwer do połączenia z maszyną usług indeksujących (MSIDXS), jednak żadna z instrukcji określanych jako długo działające nie korzysta z tego połączonego serwera.
- Wyższy średni czas oczekiwania mam na oczekiwania typu LCK_M_X (średnio około 1,5 sekundy), ale te typy oczekiwania nie zdarzają się często w porównaniu z innymi typami (na przykład 64 LCK_M_X czeka na 10,823 CXPACKET czeka na ten sam okres czasu ).
- Zauważyłem tylko, że usługa MSDTC nie jest klastrowana. Usługa SQL Server jest klastrowana, ale nie MSDTC. Czy może być z tego powodu hit wydajności? Korzystamy z MSDTC, ponieważ nasza aplikacja korzysta z usług Enterprise Services (DCOM) w celu uzyskania dostępu do bazy danych, ale serwery nie zostały zainstalowane i skonfigurowane przez nas, ale przez naszego klienta.
Czy ktoś może mi pomóc lepiej zrozumieć te dane? Czy ktoś może mi pomóc zrozumieć, co się dzieje? Czy jest coś, co mogę zrobić na serwerze, aby spróbować to rozgryźć? Czy powinienem porozmawiać z zespołem programistów?
exec()funkcji wyjaśniłoby zaobserwowane zachowanie. W takim przypadku użyciesp_executesqlnormalnie rozwiązuje problemy z dynamicznymi instrukcjami SQL.