sp_cursorprepexec powoduje 53 miliony odczytów?

Prowadzimy instalację Dynamics AX 2012 z SQL Server 2012. Wiem, że kursory nie powinny być już używane, ale AX używa go i nie możemy zmienić tego zachowania, więc musimy z tym pracować.

Dzisiaj złapałem bardzo złe zapytanie z ponad 53 milionami odczytów i czasem wykonania dłuższym niż 20 minut.

Złapałem to zapytanie za pomocą naszego narzędzia do monitorowania SentryOne.

declare @p1 int
set @p1=1073773227
declare @p2 int
set @p2=180158805
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=2
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 bigint,@P2 nvarchar(5),@P3 bigint,@P4 nvarchar(8),@P5 bigint,@P6 bigint,@P7 bigint,@P8 bigint,@P9 bigint,@P10 bigint,@P11 bigint,@P12 bigint,@P13 bigint,@P14 bigint,@P15 bigint,@P16 bigint,@P17 bigint,@P18 bigint,@P19 nvarchar(5),@P20 bigint,@P21 bigint,@P22 bigint,@P23 bigint,@P24 bigint',N'SELECT T1.PRODUCT,T1.EXTERNALVENDPARTY,T1.LIFECYCLESTATUS,T1.RECID,T2.ECORESPRODUCT,T2.ECORESDISTINCTPRODUCTVARIANT,T2.SGE,T2.ECORESREFORDERNUM,T2.ORDERNUM,T2.RECID,T3.ECORESREFORDERNUM,T3.NAME1,T3.NAME2,T3.NAME3,T3.RECID,T4.ECORESPRODUCT,T4.EXTERNALITEMID,T4.ECORESDISTINCTPRODUCTVARIANT,T4.RECID,T5.RECID,T5.PERSON,T6.RECID,T6.NAME,T6.INSTANCERELATIONTYPE,T7.RECID,T7.NAME,T7.INSTANCERELATIONTYPE,T8.PARTY,T8.ACCOUNTNUM,T8.RECID,T9.RECID,T9.DISPLAYPRODUCTNUMBER,T9.INSTANCERELATIONTYPE,T10.PRODUCT,T10.CATEGORY,T10.RECID,T11.RECID,T11.CODE,T11.NAME,T11.INSTANCERELATIONTYPE FROM INVENTTABLE T1 CROSS JOIN ECORESPRODUCTORDERNUM T2 CROSS JOIN ECORESPRODUCTORDERNUMTRANSLATION T3 LEFT OUTER JOIN VENDEXTERNALITEM T4 ON ((T4.PARTITION=5637144576) AND ((T2.ECORESPRODUCT=T4.ECORESPRODUCT) AND (T4.ECORESDISTINCTPRODUCTVARIANT=@P1))) CROSS JOIN HCMWORKER T5 CROSS JOIN DIRPARTYTABLE T6 CROSS JOIN DIRPARTYTABLE T7 CROSS JOIN VENDTABLE T8 CROSS JOIN ECORESPRODUCT T9 CROSS JOIN ECORESPRODUCTCATEGORY T10 CROSS JOIN ECORESCATEGORY T11 WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N''087'')) AND (T1.DATAAREAID=@P2)) AND ((T2.PARTITION=5637144576) AND ((T2.ECORESPRODUCT=T1.PRODUCT) AND (T2.SGE=@P3))) AND ((T3.PARTITION=5637144576) AND ((T3.ECORESREFORDERNUM=T2.ECORESREFORDERNUM) AND (T3.LANGUAGEID=@P4))) AND ((T5.PARTITION=5637144576) AND (T5.RECID=T2.PRODUCTMANAGER)) AND (((T6.PARTITION=5637144576) AND (T6.INSTANCERELATIONTYPE IN (@P5,@P6,@P7,@P8,@P9,@P10,@P11) )) AND (T6.RECID=T5.PERSON)) AND (((T7.PARTITION=5637144576) AND (T7.INSTANCERELATIONTYPE IN (@P12,@P13,@P14,@P15,@P16,@P17,@P18) )) AND (T1.EXTERNALVENDPARTY=T7.RECID)) AND (((T8.PARTITION=5637144576) AND (T8.DATAAREAID=N''087'')) AND ((T7.RECID=T8.PARTY) AND (T8.DATAAREAID=@P19))) AND (((T9.PARTITION=5637144576) AND (T9.INSTANCERELATIONTYPE IN (@P20,@P21,@P22) )) AND (T9.RECID=T1.PRODUCT)) AND ((T10.PARTITION=5637144576) AND (T10.PRODUCT=T9.RECID)) AND (((T11.PARTITION=5637144576) AND (T11.INSTANCERELATIONTYPE IN (@P23,@P24) )) AND (T11.RECID=T10.CATEGORY))',@p5 output,@p6 output,@p7 output,0,N'087',5637146082,N'de',41,2303,2377,2975,2978,5329,6886,41,2303,2377,2975,2978,5329,6886,N'087',3265,3266,3267,2665,4423
select @p1, @p2, @p5, @p6, @p7

Pierwszą rzeczą, którą zauważyłem, jest to, że w tym zapytaniu był używany kursor. Z ciekawości skopiowałem instrukcję i wykonałem ją w Management Studio bez elementów kursora (muszę przyznać, że zastąpiłem parametry zapytania, aby móc je uruchomić). W SSMS zapytanie zakończyło się w 30 sekund. Niezbyt szybko, ale wciąż szybciej niż alternatywa kursora.

Tutaj zapewniam oba plany:

• Planuj za pomocą kursora: https://www.brentozar.com/pastetheplan/?id=Sk0aMY-Y-
• Plan bez kursora: https://www.brentozar.com/pastetheplan/?id=HJ6ImtWK-

Plan bez kursora jest nadal bardzo złym planem, ale jest znacznie lepszy. Moje pytanie brzmi: czy ktoś może mi wyjaśnić, dlaczego wersja kursora wymaga 53 milionów odczytów?

Statystyki zapytania z kursorem:

Duration    CPU         Reads       Writes  Est Rows    Actual Rows
1.396.212   1.379.157   53.270.895  3.878   30          2

Statystyki dla zapytania bez kursora:

Duration    CPU         Reads       Writes  Est Rows    Actual Rows
23.337      1.703       665.113     13      4.287       34.813

Dziwne wydaje się uzyskanie 34 813 wierszy zamiast 2; ale jestem pewien, że podałem właściwe parametry. Pomyślałem, że to może zabawne dziwactwo z SQL Sentry, skoro właśnie skopiowałem statystyki z tego miejsca.

Mam nadzieję, że mógłbym dostarczyć Ci wszystkie niezbędne informacje. Również jeśli ktoś ma jakieś dobre odczyty, lepiej zrozum kursory, które byłyby świetne.

Przede wszystkim zaskakuje mnie fakt, że faktyczna liczba wierszy dla obu zapytań z SQL Sentry nie jest mniej więcej taka sama.

Druga. Trudno powiedzieć, jak poprawne są twoje prognozy w planie za pomocą kursora bez faktycznego planu, ale niektóre rzeczy mnie wyróżniają. (PS: zapoznaj się z moją odpowiedzią tutaj, aby uzyskać aktualny plan).

Biorąc to pod uwagę, istnieje kilka rzeczy, które można zauważyć z twojego szacunkowego planu.

Jest ostrzeżenie o niedopasowanych indeksach z powodu parametryzacji. Usunięcie parametryzacji, aby program SQL Server mógł używać tych niedopasowanych, może radykalnie poprawić operacje we / wy.

Szacunkowa liczba wierszy między dwoma planami również jest dramatycznie wyłączona. W planie z kursorem masz szacunkową liczbę wierszy z vendexternalitem wynoszącą 11. W swoim planie bez kursora masz szacunkową i rzeczywistą liczbę wierszy prawie 200 tys. Jeśli twoje 200 000 rekordów faktycznie trafi do tego operatora szpuli, może to być bolesne.

Wszyscy operatorzy mają bardzo różne oszacowania (znacznie mniejsze w planie z kursorem), więc może twój plan został skompilowany i zapisany w pamięci podręcznej przy użyciu różnych wartości parametrów niż w zapytaniu bez kursora. (znany jako wąchanie parametrów )

Istnieje również bardzo dziwny wybór w wyszukiwaniu indeksu + wyszukiwaniu klucza w tabeli wynalazków. Plan używa typeIdx, a następnie wyszukuje klucze w indeksie klastrowym (itemidx). Jeśli twoje prognozy są tam niedostępne, a SQL Server musi przeprowadzić wiele kluczowych wyszukiwań, które mogłyby wyjaśnić wiele IO. Nie znam stopidx, który masz w swoim planie bez kursora, ale wygląda na to, że obejmuje, więc prawdopodobnie jest to lepszy wybór dla podanych parametrów. Podejrzewam, że wybrał TypeIDX, ponieważ jest znacznie węższy, ale może to wynikać z innych wartości czasu kompilacji niż w przypadku problematycznego wykonywania.

Krótko mówiąc, usunąłbym parametryzację tego zapytania w AX, aby wygenerowało plan z rzeczywistymi wartościami, aby wybrał indeksy „lepsze”, o czym świadczy plan (i czasy wykonania) w SSMS. Umożliwiłoby to również SQL Serverowi korzystanie z filtrowanych indeksów. Aby to zrobić, poproś programistę o dodanie forceliteralssłowa kluczowego w kodzie aplikacji, w której wykonywane jest to zapytanie, i zobacz, co się stanie.

Prawdopodobnie nadal pozostawałoby ci zapytanie, które zajmuje 30 sekund (podobnie jak w SSMS), ale to tylko kwestia dostrojenia. W twoich planach brakuje ostrzeżeń o indeksach i myślę, że indeks na ecoresproductordernum.sge może na przykład pomóc, ale nie znam tych tabel i myślę, że są one dodawane przez modyfikacje. Pomogłyby w tym ogólne zasady dostrajania, ale byłoby to prawdopodobnie zbyt ogólne dla tej odpowiedzi (obejmującej indeksy, ...)