如何确定实体框架是否正在等待来自连接池的连接？ [英] How to determine if the Entity Framework is waiting on a connection from the connection pool?

问题描述

我看到一些间歇性的减速\ sql超时错误，无法确定原因。我已经整理了一些线索，但是我需要一些帮助，以找出可能的下一步。

问题

我们有一个表，其中包含10+百万条记录，这些记录是我们从Web应用程序运行异步计数的结果。该表经常被写入。有时，计数需要2分钟，有时会超时，有时它会在一秒钟之内从同一生成的sql返回所有内容。

我有一个理论，即连接池中的所有连接都很忙，所以它正在等待……但是我不知道一种证明方法。 / p>

我们的设置

• SQL 2012，实体框架5.0和MVC Web API。

线索

• 在响应速度较慢的时候，我可以通过sql
  Management Studio运行同一条sql，它在一秒钟之内返回。


• 在缓慢的响应时间
  站点的所有其他功能均能正常工作，缓慢性仅限于此区域。


• 问题仅在生产数据库中出现。我尝试在开发过程中将其重现，但没有任何运气。

到目前为止我尝试过的一切

• 我们遇到了一个问题，即计数锁定了表并计时了
  
  。由于表被锁定了这么长时间，因此对我们的应用程序造成了
  
  破坏。我们将范围更改为读取未提交的
  ，这似乎已解决了锁定问题，但没有超时的
  
  。




• 介绍了由实体框架生成的查询

详细信息

运行SQL查询。

 -网络协议：LPC 
设置
上的quoted_identifier设置$ a $$$$$$$ b $上设置ansi_padning设置$$$$ concat_null_yields_null on 
 set cursor_close_on_commit off 
 set hidden_​​transactions off 
 set language us_english 
 set dateformat mdy 
 set datefirst 7 
设置事务隔离级别未提交
 
 
 
 exec sp_executesql N'SELECT 
 [GroupBy1]。[A1] AS [C1] 
 FROM（SELECT 
 COUNT（1）AS [ A1] 
来自[dbo]。[CampaignSendResults] AS [Extent1] 
 WHERE（[Exten t1]。[CampaignId] = @ p__linq__0）AND（0 = CAST（[Extent1]。[ResultType] AS int））
）AS [GroupBy1]'，N'@ p__linq__0 uniqueidentifier'，@ p__linq__0 ='0BACD499 -A665-4721-902A-37EAB49A8A8C'
 
 
 exec sp_executesql N'SELECT 
 [GroupBy1]。[A1] AS [C1] 
 FROM（SELECT 
 COUNT（1）AS [A1] 
 FROM [dbo]。[CampaignSendResults] AS [Extent1] 
 WHERE（[Extent1]。[CampaignId] = @ p__linq__0）AND（0 = CAST（[ Extent1]。[ResultType] AS int））AND（CAST（[Extent1]。[SendDate] AS datetime2）> = @ p__linq__1）
）AS [GroupBy1]'，N'@ p__linq__0 uniqueidentifier，@ p__linq__1 datetime2 （7）'，@ p__linq__0 ='0BACD499-A665-4721-902A-37EAB49A8A8C'，@ p__linq__1 ='2013-07-23 00：35：12.6930000'
  

解决方案

问题可能是

 将arithabort关闭
  

应该是

 在
上设置arithabort  

I’m seeing some intermittent slowdown \ sql timeout errors that I’m having trouble determining the cause. I’ve pieced together some clues but I need some help figuring out possible next steps.

The Problem

We have a table that has 10+ million records that we run an async count from our web application. The table is written to often. Occasionally the count takes 2 minutes, sometime it timeouts, and sometimes it returns under a second all from the same generated sql.

I have a theory that the all the connections in the connection pool are busy so it's waiting... but I don't know of a way to prove it.

Our Setup

• SQL 2012, Entity Framework 5.0, and the MVC Web API.

Clues

• During the slow response times I can run the same sql via sql management studio and it returns under a second.
• During the slow response times all other functionality of the site performs fine, slowness is confined to this area.
• Problem only occurs on Production database. I've tried to reproduce it on development without any luck.

What I've tried so far

• We had a problem where the count was locking the table and timing
  out. Because the table was locked for such a long time it caused
  havoc with our application. We changed the scope to "read
  un-committed" and that seems to have fixed locking problem but not
  the timeout.

• Profiled the queries generated by entity framework

Details

Looking at SQL Profiler here are the queries run.

-- network protocol: LPC
set quoted_identifier on
set arithabort off
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 uncommitted



 exec sp_executesql N'SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[CampaignSendResults] AS [Extent1]
        WHERE ([Extent1].[CampaignId] = @p__linq__0) AND (0 =  CAST( [Extent1].[ResultType] AS int))
    )  AS [GroupBy1]',N'@p__linq__0 uniqueidentifier',@p__linq__0='0BACD499-A665-4721-902A-37EAB49A8A8C'


exec sp_executesql N'SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[CampaignSendResults] AS [Extent1]
    WHERE ([Extent1].[CampaignId] = @p__linq__0) AND (0 =  CAST( [Extent1].[ResultType] AS int)) AND ( CAST( [Extent1].[SendDate] AS datetime2) >= @p__linq__1)
)  AS [GroupBy1]',N'@p__linq__0 uniqueidentifier,@p__linq__1 datetime2(7)',@p__linq__0='0BACD499-A665-4721-902A-37EAB49A8A8C',@p__linq__1='2013-07-23 00:35:12.6930000'

解决方案

The problem could be

set arithabort off

it should be

set arithabort on

这篇关于如何确定实体框架是否正在等待来自连接池的连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！