Load 1TB in less than 1 hour

Microsoft SQL Server Development Customer Advisory Team - Load 1TB in less than 1 hour

Professional Association for SQL Server (PASS) SIG — Mon, 22 May 2006 02:46:10 GMT

SQL Server 2005: Load 1TB in less than 1 hour

STEFANO DEMILIANI WeBlog — Tue, 23 May 2006 12:50:50 GMT

re: Load 1TB in less than 1 hour

Bert — Wed, 24 May 2006 22:45:06 GMT

Interesting read, I look forward to your additional tests ( the SSIS test in particular) . Can you elaborate on one of the best practices you mentioned above?

“Use ROWS PER BATCH = 2500, or something near this if you are importing multiple streams into one table.”

How did you determine that this was the best value to use? Wouldn’t this be specific to the data that you are loading. When I perform migration of large sets of data I can determine what the correct batch size is by looking at the data types in the table. In SQL 2000 the max log block is 60KB. I therefore will insert my data in batches that will size the insert as close to 60KB with out going over.

How many extent allocations does it take to reach this 60KB limit?

In you tests it appears that this would not have mattered much as you state your issues related to waits on extent allocations but on lesser systems it would be good to understand how to size BULK INSERTS around the 60KB log flush.

re: Load 1TB in less than 1 hour

Ted Malone — Thu, 25 May 2006 03:23:22 GMT

Amazing statistics.. Interesting that disk IO on the SAN configuration did NOT turn out to be the bottleneck in this case. What was the memory consumption during this test?

re: Load 1TB in less than 1 hour

kevincox — Thu, 25 May 2006 04:50:01 GMT

Response to Bert: 2500 rows per batch is used to avoid lock escalation.

How many extent allocations get to the 60kb limit on the log flush? I dont know and not sure it's a significant enough delay to worry about.

Response to Ted on the memory consumption: Unfortunately I've deleted the perfmon logs to get more disk space. The machine had 256GB RAM and I remember that it wasnt getting close. I would have paid more attention to it if there were any memory waits.

re: Load 1TB in less than 1 hour

jchang6 — Mon, 29 May 2006 19:46:05 GMT

I am curious on the NUMA affects,
how many log write threads can SQL Server use?
If more than 1, can affinity bw controlled?
Is there any benefit for the log write thread to be on the same NUMA node, or even the same processor/core,
Penalty for context switch vs. comminication between threads
If the system has hyper-threading, is there any benefit to having the log thread be on the other logical proc as the thread that did the insert

re: Load 1TB in less than 1 hour

Francisco — Tue, 30 May 2006 16:29:19 GMT

What is the Maximum Page Size in SQL Server 2005?
Is posible modificate?

Hypergene Developer Blog » Blog Archive » Tips f??r bulk-copy till SQL Server

Hypergene Developer Blog » Blog Archive » Tips f??r bulk-copy till SQL Server — Tue, 30 May 2006 21:52:05 GMT

PingBack from http://www.hypergene.com/blog/?p=10

re: Load 1TB in less than 1 hour

--Jeff Moden — Wed, 31 May 2006 02:34:03 GMT

Very nicely done... It does sound a bit like an advertisment for HP and pretty much demonstrates that you can do just about anything if the hardware is there...

Still, you did a nice job writing this up and I appreciate your sharing your hard earned findings with us.

re: 20万行/秒, 40MB/秒を達成 by SqlBulkCopy

河端善博の .TEXT でウェブログ — Wed, 27 Sep 2006 09:52:37 GMT

re: 20万行/秒, 40MB/秒を達成 by SqlBulkCopy

SQL Server 2005 VLDB 支援

Robert Hu's Blog — Tue, 21 Nov 2006 18:32:46 GMT

最近在協助客戶驗證使用 SQL Server 2005 來處理超過 10 億筆的資料匯入並建立索引。要處理這麼大量資料的例子還是第一次，所以做了一些功課，透過這個 Blog 整理以下給有需的的人參考。

SSIS, Bulk Loading, 1GB in an hour??

roy ashbrook — Sat, 05 May 2007 02:12:56 GMT

OK, so several times recently I have heard about SSIS being able to load 1TB in an hour. These comments

Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

Top 10 Lists — Mon, 11 Feb 2008 17:29:23 GMT

Contributors : Stuart Ozer, with Prem Mehra and Kevin Cox Technical Reviewers : Lubor Kollar, Thomas

构建大型关系数据仓库的十大最佳实践

中文（简体）白皮书 — Sat, 18 Oct 2008 06:01:49 GMT

撰稿人： Stuart Ozer、Prem Mehra 和 Kevin Cox 技术审阅人： Lubor Kollar、Thomas Kejser、Denny Lee、Jimmy May、Michael

大規模リレーショナルデータウェアハウスを構築するためのベストプラクティストップ 10

ホワイトペーパー（日本語） — Sat, 18 Oct 2008 07:59:47 GMT

寄稿者 : Stuart Ozer、Prem Mehra、Kevin Cox テクニカルレビュー担当者 : Lubor Kollar、Thomas Kejser、Denny Lee、Jimmy May

10 рекомендаций по созданию крупномасштабного реляционного хранилища данных

техническая документация на русском языке — Thu, 30 Oct 2008 22:54:58 GMT

Авторы : Стюарт Озер ( Stuart Ozer ), при участии Према Мехры ( Prem Mehra ) и Кевина Кокса ( Kevin Cox

Bulk Insert performance | keyongtech

Bulk Insert performance | keyongtech — Sun, 18 Jan 2009 19:21:51 GMT

PingBack from http://www.keyongtech.com/3166221-bulk-insert-performance

Number of servers required to process half TB of data | keyongtech

Number of servers required to process half TB of data | keyongtech — Thu, 22 Jan 2009 11:01:38 GMT

PingBack from http://www.keyongtech.com/2069166-number-of-servers-required-to

构建大型关系数据仓库的十大最佳实践--SQLCat

Alexwei — Tue, 24 Feb 2009 05:25:00 GMT

构建大型关系数据仓库的十大最佳实践撰稿人：StuartOzer、PremMehra和KevinCox

技术审阅人：LuborKollar、ThomasKejser、Denny...

Microsoft SQL Server Development Customer Advisory Team Load 1TB in | Insomnia Cure

Tue, 09 Jun 2009 01:12:51 GMT

PingBack from http://insomniacuresite.info/story.php?id=7985

Microsoft SQL Server Development Customer Advisory Team Load 1TB in | Menopause Relief

Tue, 09 Jun 2009 03:07:23 GMT

PingBack from http://menopausereliefsite.info/story.php?id=1222

Load 1TB in less than 1 hour

Microsoft SQL Server Development Customer Advisory Team - Load 1TB in less than 1 hour

SQL Server 2005: Load 1TB in less than 1 hour

re: Load 1TB in less than 1 hour

re: Load 1TB in less than 1 hour

re: Load 1TB in less than 1 hour

re: Load 1TB in less than 1 hour

re: Load 1TB in less than 1 hour

Hypergene Developer Blog » Blog Archive » Tips f??r bulk-copy till SQL Server

re: Load 1TB in less than 1 hour

re: 20万行/秒, 40MB/秒を達成 by SqlBulkCopy

SQL Server 2005 VLDB 支援

SSIS, Bulk Loading, 1GB in an hour??

Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

构建大型关系数据仓库的十大最佳实践

大規模リレーショナル データ ウェアハウスを構築するためのベスト プラクティス トップ 10

10 рекомендаций по созданию крупномасштабного реляционного хранилища данных

Bulk Insert performance | keyongtech

Number of servers required to process half TB of data | keyongtech

构建大型关系数据仓库的十大最佳实践--SQLCat

Microsoft SQL Server Development Customer Advisory Team Load 1TB in | Insomnia Cure

Microsoft SQL Server Development Customer Advisory Team Load 1TB in | Menopause Relief

大規模リレーショナルデータウェアハウスを構築するためのベストプラクティストップ 10