我有一个程序,可以并行地向 SQL Server 实例发送多个请求。在所有的并发请求中,其中一个请求总是在 db.open() 上阻塞几秒钟。
void MyMethod()
{
var brands = new List<string>{"Chevy", "Honda", "Ford", "GM"};
var foundCars = new ConcurrentBag<Car>();
Parallel.ForEach(brands, brand =>
{
logger.Trace(brand + " before enqueue");
foundsCars.Enqueue(FindCar(brand));
logger.Trace(brand + " after enqueue");
});
}
public Car FindCars(string brand)
{
using (var db = new SqlConnection(connectionString))
{
sqlLogger.Trace("Brand " + " brand " + " before db open");
db.Open();
sqlLogger.Trace("Brand " + " brand " + " after db open");
using (var cmd = new SqlCommand(sqlCmd, db))
{
while (reader.Read())
{
//SQL SELECT Stuff going on
}
}
}
}
如果我查看我的跟踪记录,可能会看到类似这样的内容,顺序取决于数据库响应时间,我想,在这种情况下 Honda 是正在缓慢处理的数据,但并不总是这样:
12:00:00.0000 Chevy before enqueue
12:00:00.0000 Honda before enqueue
12:00:00.0000 Ford before enqueue
12:00:00.0000 GM before enqueue
12:00:00.0200 Brand Chevy before db open
12:00:00.0300 Brand Honda before db open
12:00:00.0200 Brand Ford before db open
12:00:00.0200 Brand GM before db open
12:00:00.0300 Brand Chevy after db open
12:00:00.0300 Brand Ford after db open
12:00:00.0300 Brand GM after db open
12:00:00.0400 Chevy after enqueue
12:00:00.0400 Ford after enqueue
12:00:00.0400 GM after enqueue
12:00:07.0000 Brand Honda after db open <-- usually around 7 seconds late
12:00:07.0100 Honda after enqueue
我无法在我的SQL Server 2008实例上复制此问题,但它在SQL Server 2012实例上总是发生。这个问题似乎是一个配置问题。当我发送并发请求且SELECT请求时,它总是发生。如果我在SQL Server Management中执行阻止查询,则会在子100ms内返回结果。
这是我的连接字符串:
<add key="SqlConnectionString" value="Data Source=10.0.20.20;Initial Catalog=CarsDB;Integrated Security=SSPI;Connection Timeout=240;" />
编辑:
我在连接字符串中插入了“Min Pool Size=30”,似乎只有第一次、第二次或第三次调用该方法时耗时超过15秒,然后所有其他方法调用都在500毫秒以下。
我还在我们的测试环境中运行了SQL Server 2012,并在其中使用与出现问题的表中相同的元数据,但我无法复制此问题。看起来是某个地方的配置问题。
using
语句后的时间。using (var db = new SqlConnection(connectionString)){ /*...*/} sqlLogger.Trace("Brand " + " brand " + " after db close");
然后更新你的问题,列出语句的顺序? - Scott Chamberlain