用相同的查询计划相同的查询需要〜10倍时长从ADO.NET与SMSS执行 [英] Same query with the same query plan takes ~10x longer when executed from ADO.NET vs. SMSS

问题描述

我的查询是相当复杂的，但我已经简化它弄清楚​​这个问题，现在是一个简单的连接是我在SQL Server 2014年数据库上运行。查询是：

My query is fairly complex, but I have simplified it to figure out this problem and now it is a simple JOIN that I'm running on a SQL Server 2014 database. The query is:

SELECT * FROM SportsCars as sc INNER JOIN Cars AS c ON c.CarID = sc.CarID WHERE c.Type = 1

当我运行SMSS此查询，看它在SQL事件探查器，大约需要350毫秒来执行。当我运行相同的查询使用实体框架或ADO.NET我的应用程序中（我都试过）。这需要4500ms执行。

When I run this query from SMSS and watch it in SQL Profiler, it takes around 350ms to execute. When I run the same query inside my application using Entity Framework or ADO.NET (I've tried both). It takes 4500ms to execute.

ADO.NET code：

ADO.NET Code:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var cmdA = new SqlCommand("SET ARITHABORT ON", connection);
    cmdA.ExecuteNonQuery();

    var query = "SELECT * FROM SportsCars as sc INNER JOIN Cars AS c ON c.CarID = sc.CarID WHERE c.Type = 1";
    var cmd = new SqlCommand(query, connection);
    cmd.ExecuteNonQuery()
}

我已经做了广泛的谷歌搜索，发现这真棒文章和几个StackOverflow的问题（<一href="http://stackoverflow.com/questions/1750369/sql-server-query-runs-slower-from-ado-net-than-in-ssms">here和<一href="http://stackoverflow.com/questions/2736638/sql-query-slow-in-net-application-but-instantaneous-in-sql-server-management-st">here).为了使会话参数相同的两个疑问我称之为 SET ARITHABORT ON ADO.NET中，并没有什么区别，这是一个直接的SQL查询，因此不存在一个参数嗅探问题。我已经简化查询并下降到其对于此测试的最基本形式的索引。没有别的运行在服务器上并没有什么在测试过程中其他访问数据库。中有没有计算列在汽车或跑车表，只是类型为int和VARCHAR处理。

I've done an extensive Google search and found this awesome article and several StackOverflow questions (here and here). In order to make the session parameters identical for both queries I call SET ARITHABORT ON in ADO.NET and it makes no difference. This is a straight SQL query, so there is not a parameter sniffing problem. I've simplified the query and the indexes down to their most basic form for this test. There is nothing else running on the server and there is nothing else accessing the database during the test. There are no computed columns in the Cars or SportsCars table, just INTs and VARCHARs.

该跑车表中有大约170,000条记录和4列，而汽车表有大约120万的记录和7列。将得到的数据组（类型的跑车= 1）具有大约2600的记录和11列。我有车表在一个非聚集索引，在[类型]列，其中包括车表的所有列。而这两个表对CarID列一个聚集索引。没有其他任何索引表上不存在。我运行在两种情况下相同的数据库用户

The SportsCars table has about 170k records and 4 columns, and the Cars table has about 1.2M records and 7 columns. The resulting data set (SportsCars of Type=1) has about 2600 records and 11 columns. I have a single non-clustered index on the Cars table, on the [Type] column that includes all the columns of the cars table. And both tables have a clustered index on the CarID column. No other indexes exist on either table. I'm running as the same database user in both cases.

当我查看SQL事件探查器中的数据，我看到两个查询使用的是完全相同的，很简单的查询计划。在SQL事件探查器，我使用的性能事件类和显示计划XML统计资料，我认为是正确的事件来监视和获取的实际执行计划。该＃的读取是相同的两个查询（2596）。

When I view the data in SQL Profiler, I see that both queries are using the exact same, very simple query plan. In SQL Profiler, I'm using the Performance Event Class and the ShowPlan XML Statistics Profile, which I believe to be the proper event to monitor and capture the actual execution plan. The # of reads is the same for both queries (2596).

如何使用完全相同的查询计划中，两个完全相同的查询需要10倍更长的ADO.NET与SMSS？

How can two exact same queries with the exact same query plan take 10x longer in ADO.NET vs. SMSS?

推荐答案

想通了：

由于我使用的是实体框架，在我的应用程序 MultipleActiveResultSets = TRUE 连接字符串。当我从连接字符串中删除这一点，查询在ADO.NET和SSMS相同的性能。

Because I'm using Entity Framework, the connection string in my application has MultipleActiveResultSets=True. When I remove this from the connection string, the queries have the same performance in ADO.NET and SSMS.

显然有一个问题导致查询时，通过广域网连接到SQL Server的响应速度很慢此设置。我发现<一href="https://connect.microsoft.com/SQLServer/feedback/details/603043/multipleactiveresultsets-true-mars-causes-slow-performance-over-wan-connections-to-database"相对=nofollow>这个链接这评论：

Apparently there is an issue with this setting causing queries to respond slowly when connected to SQL Server via WAN. I found this link and this comment:

MARS使用流水模式来检索数据。流水模式意味着   服务器将尽可能快地产生数据。这也意味着，   您的客户端应用程序必须以相同的速度接受入站数据   它的用武之地，如果它在服务器上而不是数据存储缓冲区将   填满，处理将停止，直到这些缓冲区空。

MARS uses "firehose mode" to retrieve data. Firehose mode means that the server will produce data as fast as possible. This also means that your client application must receive inbound data at the same speed as it comes in. If it doesn't the data storage buffers on the server will fill up and the processing will stop until those buffers empty.

还等什么？你可能会问...但只要处理被停止   在SQL服务器上的资源都在使用，并捆绑起来。这包括   工作线程，架构和数据锁，存储器等，所以它是   至关重要的是，您的客户端应用程序消耗的入结果   很快，他们到达。

So what? You may ask... But as long as the processing is stopped the resources on the SQL server are in use and are tied up. This includes the worker thread, schema and data locks, memory, etc. So it is crucial that your client application consumes the inbound results as quickly as they arrive.

我必须使用与实体框架，否则延迟加载该设置会产生异常。所以我不得不想出一些其他的解决办法。但至少我现在明白了这个问题。

I have to use this setting with Entity Framework otherwise lazy loading will generate exceptions. So I'm going to have to figure out some other workaround. But at least I understand the issue now.

这篇关于用相同的查询计划相同的查询需要〜10倍时长从ADO.NET与SMSS执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！