AzureSQLDB掛起連接直到EF超時

我有一個間歇性問題，我似乎無法深入了解，其中 .NET Framework 4.6 MVC API (C#) 使用 Entity Framework 6（運行時 v4.0.30319）存盤程序呼叫需要很長時間才能回應。

存盤程序托管在 Azure SQL DB 上。

這是一個不起眼的程序，它詢問幾張表格，為營養師提供他們客戶的最新資料和網站互動。

ALTER PROCEDURE [PORTAL].[GetNutritionistCustomers]

    @id_usernut varchar(20)

AS

    WITH Activity AS (
        SELECT LastActivity = MAX(ExecutionDate),
               ded.UserName
        FROM portal.DailyExecutionData ded
        WHERE ded.ExecutionDate < GETDATE()
        GROUP BY ded.UserName
    ),
    Logging AS (
        SELECT LastLogin = MAX(l.LoggedOnDate),
               l.UserName
        FROM PORTAL.Logins l
        WHERE l.LoginType = 'Direct Client'
        GROUP BY l.UserName
    )
    SELECT unc.CompanyCode,
           a.ACCOUNT,
           ad.ADDRESS1,
           un.ID_UserNut,
           ueu.ExpirationDate,
           Expired = CAST(CASE WHEN ueu.ExpirationDate < GETDATE() THEN 1 ELSE 0 END AS BIT),
           LastActive = la.LastActivity,
           l.LastLogin
    FROM UK_Nutritionist un
    JOIN UK_NutCompany unc ON un.ID_UserNut = unc.ID_UserNut
    JOIN UK_EnabledUsers ueu ON unc.CompanyCode = ueu.UserName
    JOIN Klogix.ACCOUNT a ON ueu.ID_User = a.ACCOUNTID
    LEFT JOIN Klogix.ADDRESS ad ON a.ADDRESSID = ad.ADDRESSID AND ad.IsPrimary = 1
    LEFT JOIN Activity la ON ueu.UserName = la.UserName
    LEFT JOIN Logging l ON ueu.UserName = l.UserName
    WHERE un.ID_UserNut = @id_usernut

在 SSMS 或 ADS 中運行此程序平均需要大約 50-100 毫秒才能完成。這是一致的，表被很好地索引并且查詢計劃是所有索引查找。沒有密鑰或 RID 查找危險信號。

使用探查器進行調查后，我確定會發生什么是 EF 創建連接，呼叫程序。可以看到RPC進入，EF達到連接超時時間，然后我們得到RPC完成，資料回傳給EF，代碼旋轉出一個poc串列回傳給json中的呼叫者。

[HttpGet]
[Route("Customers")]
public HttpResponseMessage Get()
{
    try
    {
        if (IsTokenInvalid(Request))
            return Request.CreateResponse(HttpStatusCode.Unauthorized);

        var nutritionistCustomers = new ExcdbEntities().GetNutritionistCustomers(TokenPayload.Nutritionist).Select(x => new NutritionistCustomers()
        {
            Account = x.ACCOUNT,
            Address = x.ADDRESS1,
            CompanyCode = x.CompanyCode,
            ExpirationDate = x.ExpirationDate,
            expired = x.Expired,
            LastActive = x.LastActive,
            LastLogin = x.LastLogin
            
        }).ToList();
        return Request.CreateResponse(HttpStatusCode.OK, GenerateResponse(nutritionistCustomers))
    }
    catch (Exception e)
    {
        return Request.CreateResponse(HttpStatusCode.InternalServerError, GenerateResponse(e));
    }
}

如果我更改連接的超時時間，則 SQL 在釋放資料之前等待的時間會發生變化。

我認為這可能與托管 API 的 Azure 應用服務有關，但事實證明，在 Visual Studio 中的除錯中運行它也有同樣的問題。

短期修復是重新編譯存盤程序。這將立即回傳資料。但是在未來某個不確定的時刻，同樣的程式會突然再次表現出這種行為。

Only this procedure does this, all other EF interactions, whether linq to sql table or view interrogations, and all other procedures seem to behave well. This has happened before in a now legacy system that had the same architecture, though it was a different procedure on a different database.

Whilst shortening the timeout is a workaround, it's a system wide value, and even 10 seconds, enough to cope with system hiccouphs, is way too long for this customer selection screen, which should be instantaneous. Also I would rather fix the underlying problem, if anyone has any clue what might be going on.

I have considered an OPTION_RECOMPILE on the statement, but it just feels wrong to do so.

If anyone else has experienced this and has any insight I'd be most grateful is it's driving me to distraction.

uj5u.com熱心網友回復：

由于未知原因，出現此問題時，查詢中的其中一個表存在未提交的事務。雖然 SSMS 不關心使用其默認背景關系設定，但 EF 設定的背景關系設定意味著執行正在等待鎖定釋放。我不確定我明白為什么當 EF 連接超時時會回傳。也許這只是巧合，或者不是。

無論是在 sproc 編譯背景關系中更改事務隔離設定，還是在 select 陳述句上放置 (NOLOCK) 提示，都可以解決當前的問題。

當然，發現未提交事務的原因更重要。

顯然自言自語是我的人生故事，是極客的負擔，這就是我不去參加派對的原因。

標籤：c# sql-server azure entity-framework 存储过程

上一篇：使用OrderBy對集合進行排序不起作用

下一篇：我想從圖片的行和列創建一個串列