Ciągły skan zajmuje 0 sekund lub 2-3 minuty

Zapytanie takie jak poniższe, które gwarantuje, że nie zwróci żadnych wierszy, zajmuje od 0 do 160 sekund na jednym z naszych serwerów:

select col1, col2, col3
from tab1
where 0 = 1

Dwa tygodnie temu zdarzyło się to sześć razy w odstępie 48 godzin. W zeszłym tygodniu to samo zapytanie zajęło ~ 0 sekund. Mam dzienniki SQL aplikacji, ale nie znalazłem jeszcze żadnych podejrzanych. Poza tym pomyślałem, że zapytanie typu 0 / gdzie 0 = 1 nigdy nie trafia na strony danych, więc powinno być odporne na blokady danych na poziomie wiersza / strony / tabeli? Schemat nie jest dotykany przez żadne (znane) zapytania SQL.

Ponieważ problem nie jest spójny, a serwer jest bardzo obciążony, chciałbym zrozumieć teorię tego, co się dzieje przed dołączeniem profilera SQL. Inne zapytania działają bezproblemowo podczas tych opóźnień. Znanym problemem w aplikacji jest duża liczba dynamicznie tworzonych zapytań SQL - około 200 000 unikalnych zapytań z 850 000 zapytań ogółem (zarejestrowanych) w ciągu 48 godzin, czy może to powodować takie problemy?

Na serwerze działa wersja standardowa SQL Server 2005, 96 GB pamięci RAM, dyski w sieci SAN i 4 procesory / 16 rdzeni. Pliki baz danych i aplikacjami są dobrze zoptymalizowane i nie powinno stanowić problemu (ale analizujemy to osobno).

Wszelkie wskazówki, gdzie szukać, są bardzo mile widziane.

Edycja: Idealnie! Ponownie odtworzyło zapytanie, aby dodać plan wykonania, i zajęło 1min 35secs. Oto plan wykonania i zrzut ekranu pokazujący czas trwania zapytania:

Edycja 2: szczegóły czasu statystyki dla drugiego przebiegu. Wygląda na to, że teraz jest konsekwentnie wolny, więc dołączymy profiler i perfmon:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 97402 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Wygląda na to, że nawet z ... WHERE 0 = 1klauzulą, że nadal będzie wymaganie celowej ISblokady share ( ) tabeli. Udowodnijmy to:

Zacznę od utworzenia tabeli testowej:

use TestDb1;
go

create table dbo.MyTestTable1
(
    Id int identity(1, 1) not null,
    SomeInt int not null
);
go

insert into dbo.MyTestTable1 (SomeInt)
values (10), (20), (30), (40), (50);
go

Teraz, gdy mam już tabelę testową, w jednej sesji (okno zapytania) wykonam następujące czynności, aby nałożyć Xblokadę wyłączności ( ) dbo.MyTestTable1:

use TestDb1;
go

begin tran;
    select
        Id, SomeInt
    from dbo.MyTestTable1 with (tablockx);
--commit tran;

Mogę zweryfikować wyłączność blokady, patrząc na sys.dm_tran_locksDMV. Następnie w innej sesji (nowe okno zapytania) robię dokładnie to, co robi twoje zapytanie:

use TestDb1;
go

select
    Id, SomeInt
from dbo.MyTestTable1
where 0 = 1;

Na pierwszy rzut oka widzę, że się nie kończy. Patrząc na to sys.dm_exec_requests, dokładnie rozumiem, dlaczego tak jest:

select
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where st.text like '%where 0 = 1%'
and r.session_id <> @@spid;

Widzę tutaj, że moje ... WHERE 0 = 1zapytanie czeka na ISblokadę dla tego obiektu (na który tłumaczy się identyfikator_obiektu dbo.MyTestTable1).

W żadnym wypadku nie mówię, że twoim problemem jest współbieżność , ale po dźwiękach przejawiasz objawy. Powyższy przykład ma udowodnić, że nie jesteś zwolniony z blokowania i blokowania nawet za pomocą WHEREklauzuli, która nigdy nie zwróci danych.

Wszystko, co możemy zrobić, to zgadnąć, więc to, co musisz zrobić, gdy „zajmuje dużo czasu”, to zobaczyć, co dokładnie robi to żądanie, które trwa tak długo. Jeśli na coś czeka, zobacz, na co czeka.

W zależności od tego, jak skomplikowane i wymagające wątku są twoje zapytania, twój system może po prostu umieszczać je w kolejce. Domyślna liczba pracowników (tj. Liczba równoczesnych wątków serwera SQL) dla instalacji powinna wynosić około 700.

Sprawdź sys.dm_os_schedulers i sys.dm_os_waiting_tasks, aby zobaczyć, czy może to stanowić problem.