I cannot give you enough data to reproduce the errors, so I am going to give you as much data as I can get away with.
I have a select statement executing from EF core.
var bookings = context.Booking .Where(booking => booking.ConsigneeNumber == customer.GetCustomerTarget().Code && booking.CreatedAt >= from && booking.CreatedAt < to && booking.BookingLine.Any(b => b.BookingLineSpecification .Any(c => c.CurrencyCode == code)) ) .Include(booking => booking.BookingLine) .ThenInclude(bl => bl.BookingLineSpecification) .ThenInclude(bls => bls.UnitType) .Include(booking => booking.BookingLine) .ThenInclude(bl => bl.BookingLineAddress) .ThenInclude(bla => bla.Country) .Include(booking => booking.BookingLine) .ThenInclude(bl => bl.BookingLineAddress) .ThenInclude(bla => bla.PostalCode) .Include(booking => booking.BookingLine) .ThenInclude(bl => bl.BookingLineSpecification) .ThenInclude(bls => bls.RelBookingLineSpecificationSalesInvoiceDetail) .ThenInclude(Rel => Rel.SalesInvoiceDetail);
The SQL query itself being suspended on the MSSQL server becomes:
(@__GetCustomerTarget_Code_0 bigint,@__from_1 datetime2(7),@__to_2 datetime2(7),@__code_3 varchar(255)) SELECT [booking].[Id], [booking].[booking_provider_id], [booking].[booking_status_id], [booking].[consignee_name], [booking].[consignee_number], [booking].[created_at], [booking].[created_by], [booking].[currency_code], [booking].[deliveryNumber], [booking].[description], [booking].[destroyed_at], [booking].[destroyed_by], [booking].[inter_company_number], [booking].[invoicee_name], [booking].[invoicee_number], [booking].[is_create], [booking].[location_id], [booking].[location_name], [booking].[maturity_level_id], [booking].[number], [booking].[order_number], [booking].[provider_key], [booking].[shipment_id], [booking].[system_responsible_id], [booking].[updated_at], [booking].[updated_by] FROM [Integration].[booking] AS [booking] WHERE ((([booking].[consignee_number] = @__GetCustomerTarget_Code_0) AND ([booking].[created_at] >= @__from_1)) AND ([booking].[created_at] < @__to_2)) AND EXISTS ( SELECT 1 FROM [Integration].[booking_line] AS [b] WHERE EXISTS ( SELECT 1 FROM [Integration].[booking_line_specification] AS [c] WHERE ([c].[currency_code] = @__code_3) AND ([b].[Id] = [c].[booking_line_id])) AND ([booking].[Id] = [b].[booking_id]))
This statement executes in zero seconds (but some miliseconds) when executed in MSSQL management studio. However the C# application experiences a timeout.
When I use the internal tools on MSSQL I can see that the spid is suspended, and permanently waiting. However the reason seems to be altering. In the beginning its due to IO_COMPLETION. Then its SOS_YIELD_~something and finally PAGEIOLATCH_SH This final state it stays in
I, for the life of me, cannot figure out why MSSQL can execute the query with no issue at all. But EF seemingly fails to utilize Indexes. Or something else I am missing entirely.
I am simply out of ideas. Can anyone point me in a direction that might help?
I have tried:
Running it in Visual stuido 2017. Running in release mode. I have tried Enabling lazy mode, and not use includes. I have tried removing lazy loads and includes, just to see if I could get the bookings back.
Nope. MSSQL seems to refuse EF Core from utilizing the Indexes.
The thing is, the query only hangs when I provide certain parameters. Other parameters work just fine. Specifically, if I provide different currency codes, this seems to make all the difference for MSSQL if the query gets suspended or not.
I have completely rebuilt the indexes required to execute this query efficiently according to the execution plan in MSSQL management studio.
Any further information that might be required please let me know, and I will see what I can do, to the best of my efforts.
UPDATE Actual execution plan:
UPDATE 2: I would like to point out, that this is currently being used for development, and thus this DB, my software and anything in between, is under my "control".
In so far as my apparently inexperienced mind can control anything :)
So any suggestions on how to better debug the problem, or requests for more data will be met with vigor and appreciation. And will likely be possible, especially if hinted at how to provide it to you! (And me)
SQL Profiler: opening connection to the DB:
set quoted_identifier on set arithabort on set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed
This is our answer for anyone who wishes to know what we did to resolve this issue. It is quite a "hack" to resolve the fact, that entity framework core, does in fact not let you do this by design, or does it by itself by design.
We execute this stored procedure prior to an actual execution on our C# code that asks for this specific data.
This looks for cached execution plans that can be used for a query that looks a lot like the one EF generates. And then it kills them one by one.
It is not a good thing to do.
1, It requires the user to have permissions to do this. No application should have this permission level. You can argue you all you want it "could have", but really, you all know it shouldn't have.
In code, I am executing a stored procedure prior to a select statement to get the application to perform. Really. Really? Well, yes. I am. Is it neat? no. Is it clean? no. I wouldn't even really recommend doing it as such, there are so many possible pitfalls in this, if you change parameters, or alter the query in any way, you have to rebuild the "like" string. I built it in the first place by using SQL Profiler to sniff the query, and then copied the bits that were as unique as possible towards other possible query executions. Could there be an incorrect identification? Yes. If you run the select a million times a second, this is likely not a viable solution. etc.
But it works for me.
EF core, executes its query in a stored procedure. But doesn't work with includes, if you use stored procedures. My brain bleeds from trying to wrap my head around this. So this painful solution is the one I am using.
/****** Object: StoredProcedure [dbo].[WipePlanForDraftLoad] Script Date: 11/01/2019 11:50:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Morten Bork, Henrik Davidsen -- Create date: 2019-01-11 -- Description: Procedure to Wipe a cached plan for a query that sort of matches a specific string -- ============================================= CREATE PROCEDURE [dbo].[WipePlanForDraftLoad] -- Add the parameters for the stored procedure here AS BEGIN declare plans cursor for select t1.plan_handle from sys.dm_exec_cached_plans t1 left outer join sys.dm_exec_query_stats t2 on t1.plan_handle = t2.plan_handle outer apply sys.dm_exec_sql_text(sql_Handle) where text like '%(@__customerCode_0 bigint,@__from_1 datetime2(7),@__to_2 datetime2(7),@__currencyCode_3 varchar(255))%WHERE (\[c\].\[currency_code\] = @__currencyCode_3) AND (\[b\].\[Id\] = \[c\].\[booking_line_id\])) AND (\[booking\].\[Id\] = \[b\].\[booking_id\])%' escape '\' declare @plan_handle varbinary(64) open plans fetch next from plans into @plan_handle while @@FETCH_STATUS = 0 begin dbcc freeproccache(@plan_handle) fetch next from plans into @plan_handle end close plans deallocate plans END GO
I had a similar issue a while back, and it had to do with certain defaults being set in SSMS that EF does not automatically set. If you can, try setting ARITHABORT ON for the EF session from code, and see if the query executes the same as it does in SSMS.