网络是多少 - 确定 SQL Server 中的网络开销 [英] How much is the network - determing network overhead in SQL Server

问题描述

我们有一个运行 C# 的开发服务器，并在同一台机器上与 SQL 服务器通信.

We have a dev server running C# and talking to SQL server on the same machine.

我们有另一台服务器运行相同的代码并与另一台机器上的 SQL 服务器通信.

We have another server running the same code and talking to SQL server on another machine.

一项作业执行 60,000 次读取(即它调用存储过程 60,000 次 - 每次读取返回一行).

A job does 60,000 reads (that is it calls a stored procedure 60,000 times - each read returns one row).

与在第二台服务器上运行相比，该作业在第一台服务器上的运行时间是其运行时间的 1/40.

The job runs in 1/40th of the time on the first server compared to it running on the second server.

我们已经在研究两个 SQL Server 之间的内部"差异(碎片、tempdb、内存等)，但是确定第二个配置慢多少的好方法仅仅是因为它必须通过网络?

We're already looking at the 'internal' differences between the two SQL Servers (fragmentation, tempdb, memory etc) but what's a good way to determine how much slower the second config is simply because it has to go over the network ?

[相当令人困惑的是，我发现了一个SQL Server Ping"工具，但它实际上并没有尝试任何时间测量，据我所知，这是我们需要的]

[rather confusingly I found a 'SQL Server Ping' tool but it doesn't actually attempt any timing measurement which, as far as I can see, is what we need]

推荐答案

在远程计算机上打开 SQL Server 管理服务器.开始新的查询.单击查询、包括客户端统计信息.运行您的存储过程.在结果的客户端统计"选项卡中，您将看到有关发回多少数据包的一些基本信息.通过网络传播.我的猜测是，对于一次阅读，您不会看到那么多开销.

Open SQL Server Management Server on the remote machine. Start a new query. Click Query, Include Client Statistics. Run your stored procedure. In the Client Statistics tab of the results, you'll see some basic information about how many packets were sent back & forth over the network. My guess is that for one read, you're not going to see that much overhead.

为了获得更好的主意，我会尝试从远程计算机通过网络简单地选择 60,000 条记录(因为您说它会一条一条地返回 60,000 条记录).同样，这并没有让您了解存储过程的开销，但可以让您快速了解机器之间的网络速度.

To get a better idea, I'd try doing a plain select of 60,000 records (since you said it's returning 60,000 records one by one) over the network from your remote machine. Again, that doesn't give you an idea of the stored procedure overhead, but it'll give you a quick seat-of-the-pants idea of the network speed between machines.

这篇关于网络是多少 - 确定 SQL Server 中的网络开销的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！