如何提高SQL Server的读取速度？ [英] How do I increase SQL Server read speed?
问题描述
我正在尝试将表从一个SQL Server复制到另一个。源是SQL Server 2014，目标是SQL Server2016。我无法获得高于60MB / s的速度。我要复制约15亿行，总表大小约为1TB。
源服务器具有RAID 5的SATA SSD，目标服务器具有RAID 0的NVME SSD。两台机器都通过10GbE直接连接，没有任何交换机。我可以在几秒钟内复制几GB的文件，因此我认为网络和磁盘都已正确配置。
我正在使用SSIS，并且我已经尝试了一个表格源，以及一个 select * 查询。程序包将创建目标表，而我正在使用快速加载。我已经尝试过将oledb用于SQL Server，并将SQL本机客户端用于连接类型。我在数据流中尝试了1到12个并行源/目的地的任何地方，但速度却是从1到12的3倍。但是我没有简单的方法来进一步划分数据以提高并行度。 p>
我玩过缓冲区，数据包大小无济于事。
源表大约有100列。
我期望为此至少获得300-500MB / s的速度，并希望至少饱和源磁盘的读取速度（〜800MB / s）。我是否缺少某些东西，或者这仅仅是SQL Server的限制？
我对这个问题没有特定的答案，但我将尝试提供一些可能提供一些见解的信息：
选择特定列
首先，如果在OLE DB源中不使用某些列，请更改访问模式并使用 SQL命令而不是表或视图，并在选择查询中指定所需的列。例如，如果表包含5列 [Col1]，[Col2]，... [Col5] ，而您只需要 [Col1]， [Col2] 使用以下查询：
从[表]中选择[Col1]，[Col2] ]
而不是选择表名
有关更多信息：
最小化日志记录
您可以尝试使用一种方法，即通过使用聚集索引来最小化日志记录。检查以下链接以获取更多信息：
平衡数据分配器
您也可以从平衡数据分布转换以增强数据加载性能：
有用的文章
2008年，Microsoft发布了一份白皮书，宣布使用Microsoft SQL Server Integration Services（SSIS）打破记录的数据加载：1 TB的数据量减少了超过30分钟。它可能会给您一些洞察力：
我认为有很多方法可以提高INSERT查询的性能，建议阅读以下文章以获取有关数据加载性能的更多信息。
也请检查@billinkc有关最大插入提交大小的评论属性，数据类型，批量插入...
I'm trying to copy a table from one SQL Server to another. Source is SQL Server 2014, destination is SQL Server 2016. I cannot get speeds faster than about 60MB/s. I have about 1.5 billion rows to copy, total table size of roughly 1TB.
The source server has a RAID 5 of SATA SSDs, the destination has a raid 0 of NVME SSDs. Both machines are connected by 10GbE, direct with no switch. I am able to copy a few GB of files in a few seconds, so I think the network and disks are all configured properly.
I'm using SSIS, and I've tried just a "table" source, and a select * query. The package creates the destination table, and I'm using fast load. I've tried oledb for SQL Server, and SQL native client for the connection types. I have tried anywhere from 1 to 12 parallel source/destinations in my data flow, and I get about 3x the speed going from 1 to 12. But I don't have an easy way to divide the data further to increase parallelism.
I've played with buffers, and packet size to no avail.
The source table has about 100 columns.
I was expecting to at least get a 300-500MB/s for this, and hopefully at least saturate the source disk read speed (~800MB/s). Am I missing something or is this just a limitation of SQL Server?
I don't have a specific answer to this question, but i will try to provide some information that may give some insights:
Selecting specific columns
First of all, if there are some columns in the OLE DB Source that wont be used change the Access Mode and use SQL Command instead of Table or View and specify the columns needed in the Select query. As example, if the table contains 5 columns [Col1],[Col2], ... [Col5] and you only need [Col1],[Col2] use the following query:
Select [Col1],[Col2] From [Table]
Instead of of selecting the Table name
For more information:
Minimize logging
There are one method you can try which is minimizing the logging by using clustered index. check the link below for more information:
Balanced Data Distributor
Also you can benefit from Balanced Data Distribution Transformation to enhance data loading performance:
- Parallelize Data Flows with SSIS Balanced Data Distributor Transformation
- Balanced Data Distributor: Improve SSIS Performance with Parallelism
Helpful articles
In 2008 Microsoft published a white paper to announce a record-breaking data load using Microsoft SQL Server Integration Services (SSIS): 1 TB of data in less than 30 minutes. It may give you some insight:
I think there are many way to enhance the performance on the INSERT query, I suggest reading the following article to get more information about data loading performance.
Also check @billinkc comments concerning the Max Insert Commit Size property, data types, bulk insert ...
这篇关于如何提高SQL Server的读取速度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
