sql-server - SQL Server 2012 消耗太多网络流量来获取所有数据

Question

今天我使用 AnyDAC (firedac) 进行了测试，以获取远程 SQL Server 2012 中的所有可用数据。

我从中获取数据的表有这些简单的列:

1. date - (size 3 byte)
2. time - (max 5 byte)
3. int - (4 byte)
4. bit - (1 byte)
5. int - (4 byte)
6. float - (4 byte)
7. float - (4 byte)
8. int - (4 byte)
9. int - (4 byte)

总行大小不得超过 33 字节。

好吧，在获取表中可用的所有行(超过 214 万行)后，我检查了 FireDAC 收到的 tcp 流量，发现它约为 280MB，这意味着每行需要大约 130 字节，而我的预期值接近到 33 字节。

我通过使用在服务器端定义的存储过程进行了另一项测量，该存储过程有一个插入 sql 到上面的同一个表，并且我使用 AnyDAC 的数组 DML 功能调用该存储过程。数组大小为 300K，我使用它总共添加了 1880 万条记录。用于它的流量实现为 2.85 GB。 (所以每行 150 字节)

FireDAC或SQL Server端是否有任何配置来减少流量？显然，这里有问题。有什么建议吗？

谢谢。

信息输出:

================================
Connection definition parameters
================================
User_Name=*****
Password=*******
SERVER=sql.***.gen
ApplicationName=Bist
Workstation=NB
DATABASE=BIST
MARS=yes
DriverID=MSSQL
================================
FireDAC info
================================
Tool = D18 Architect
FireDAC = 8.0.1 (Build 3279)
Platform = Windows 32 bit
Defines = AnyDAC_Unicode;AnyDAC_DBX;AnyDAC_NOLOCALE_META;
  AnyDAC_MONITOR
================================
Client info
================================
Loading driver MSSQL ...
  Loading odbc32.dll driver manager
  Creating ODBC environment handle
  Searching for ODBC driver ...
    Checking for ODBC driver [SQL SERVER NATIVE CLIENT 11.0] ...
      Found [SQL Server Native Client 11.0]
Driver Manager version = 03.80.7601.0000
================================
Session info
================================
Current catalog = 
Current schema = dbo
Driver name = sqlncli11.dll
Driver version = 11.00.2100
Driver conformance = 3
DBMS name = Microsoft SQL Server
DBMS version = 11.00.2100

Answer 1

您到 SQL 数据库服务器的网络流量不仅仅包括您要传输的数据(正如您所观察到的，收到的流量远多于行数据)

在开始之前，请允许我声明一些事情:我绝不是网络流量和协议(protocol)方面的专家，但我花了相当多的时间研究它们，以充分理解它们。我也不太了解特定 DAC 软件、查询组合等将采用什么协议(protocol)来带回您所请求的数据。话虽如此，我无法给出确切的例子。但这些概念仍然适用。

与 SQL Server 的通信通过各种协议(protocol)进行，但为了便于讨论，我们将只关注其中一种:TDS(表格数据流)协议(protocol)。 (您可以在以下位置阅读有关 SQL Server 协议(protocol)的更多信息: 您可以在以下位置阅读有关 SQL Server 协议(protocol)的更多信息:http://msdn.microsoft.com/en-us/library/ff420673(v=sql.105).aspx)。 TDS 使用 TCP 协议(protocol)，因此 TDS 数据包只不过是封装在 TCP 数据包中的规范。

对于 TCP，通信基本上采用请求/响应类型的交换，涉及许多请求/响应/确认数据包。再说一遍，我绝不是这方面的专家，但您可以在http://en.wikipedia.org/wiki/Transmission_Control_Protocol阅读更多内容。

通信的“确认”(或确认)方面本身就造成了相当大的开销，超出了您引用的行大小。您可以在下图中看到一个示例，其中客户端应用程序和 SQL 服务器之间发生通信:

此外，在从数据库服务器发送到应用程序的实际数据包中，TCP 数据包协议(protocol)本身以及 TDS 数据包内存在大量开销。

让我们只关注 TDS 数据包的开销，它会增加您的整体数据传输负载。以下是 TDS 数据包中可找到内容的所有记录 (http://msdn.microsoft.com/en-us/library/dd340794.aspx):

• ALT元数据
• 阿尔特罗
• COLINFO
• COLMETADATA
• 完成
• 在过程中完成
• 完成过程
• 环境变化
• 错误
• 功能扩展
• 信息
• 登录
• NBCROW
• 偏移
• 订购
• 返回状态
• 返回值
• ROW <- 这是您的数据所在的位置
• session 状态
• SSPI
• 选项卡名称
• TVP 行

始终，您的实际数据(在上述情况下)实际上位于数据包的 ROW 部分内。

下面的屏幕截图显示了 1 个 TDS 数据包的 ASCII 版本。数据包帧的突出显示部分(右侧)是实际的行数据。其余的只是开销，使整个通信和辅助事物正常工作。

这甚至不包括 TCP 本身产生的开销(尽管是必要的)。

总结

您正在传输大量数据，并且这些数据始终会产生开销。

• 如果最终要汇总数据，则在发送之前先在数据库服务器上进行汇总。
• 如果要搜索数据，请将搜索卸载到数据库服务器并仅返回您需要的内容
• 如果您需要列出数据(每行)，那么您可能每页仅显示结果的子集 - 仅通过 SELECT TOP 50 之类的方式返回您需要的结果。 .. 哪里....

根据您对数据的实际使用情况，还有许多其他解决方案，但这些只是一些想法。

希望这有帮助!