SQL Server中的关系型数据仓库分区策略（2）

核心提示：哪个策略更好?分区可以通过使用这两个策略之一或者将两个策略有效的结合到一起来实现，关于策略 I 和 策略 II可以参考下面的表格，SQL Server中的关系型数据仓库分区策略（2），其中解释了这两个策略对影响关系型数据仓库分区因素的作用，

哪个策略更好?

分区可以通过使用这两个策略之一或者将两个策略有效的结合到一起来实现。关于策略 I 和 策略 II可以参考下面的表格，其中解释了这两个策略对影响关系型数据仓库分区因素的作用。

	策略 I	策略 II
数据导入	·　　　　 在SELECT/INTO SQL语句中不能明确的指定文件组。由于这一限制，在使用一个SELECT/INTO 语句时无法采用并行方式来完成初始转换 ·　　　　 步进的数据导入不会被分区策略所影响	·　　　　 当所有的分区映射到同一个文件组时转换可以采用并行的方式完成·　　　　 步进的数据导入不会被分区策略所影响
备份/还原	·　　　　 在文件组和分区之间创建一个一一对应的关系让在分区级别的零散备份和还原操作得以进行·　　　　 在备份数据库之前确保分区被标记了只读状态。如果没有，当还原数据库时事务日志必须被前滚	·　　　　 如果所有的分区被映射到了同一个文件组，分区表作为一个整体可以使用单个的命令进行备份和还原·　　　　 此策略在单个分区的粒度下不提供零散备份的灵活性

　

查询性能

·　　　　 如果文件组只包含一个文件，并且表会采用一个接一个的串行方式来进行填充，对这样的对象的扩展盘区的分配是连续的。这意味着SQL Server对于一个连续的扫描可以提供多达256 KB的 I/O (4个扩展盘区)速率·　　　　 因为数据是连续的，因此对于大量的连续扫描可以提供更好的工作负荷

·　　　　 如果文件组是由多个文件组成的，SQL Server使用按比例填充机制，这样会导致扩展盘区出现碎片·　　　　 类似的，在并行操作比如并行数据导入操作期间为对象/分区分配的扩展盘区无法保证是连续的 ·　　　　 当为对象分配的扩展盘区不连续时，对于连续扫描SQL Server可以提供差不多64 KB的 I/O（1个扩展盘区）速率·　　　　 因为数据分布在许多的物理磁盘上，因此它有助于在进行大量的并发随机I/O操作时提供更好的工作负荷性能作为一个替换的方案，可以在启动SQL Server时加上-E开关。当在启动时指定了-E开关时，SQL Server可以分配4个而不是1个扩展盘区。因而-E开关让SQL Server可以提供多达256 KB 的I/O速率，即使存在因使用了按比例填充机制而产生的扩展盘区碎片

结论

本白皮书讨论了影响分区的因素，以及对于设计分区可使用的两个主要策略的正反两面的对比。这里所提供的信息可能会对通过分区更有效的管理你的关系型数据仓库有所帮助。

有关的更多信息，请访问:http://www.microsoft.com/sql/

本文档展示了SQL Server 2005的一些与关系型数据仓库分区的相关特殊的功能。需要更多的信息，请参阅：

◆SQL Server 2005联机丛书提供了一些关于这个主题的有价值的信息，并且它可以作为使用SQL Server 2005实现数据分区的一个不错的起点。

◆CLARiiON CX600 Web站点: http://www.emc.com/products/systems/clariion_cx246.jsp

附录 A: 性能数值

所有在这一部份展示的数值都是在我们使用SQL Server 2005的测试过程中得到的。该测试是在附录B，C以及D中记录的硬件平台上进行的。

批量插入性能

在我们的测试中，我们可以在SQL Server 2005中使用刚刚超过一个小时的时间导入25亿行。事实表的架构中包含了九个整型列，一个日期类型列，以及一个字符型列。

在这里展示的性能数值是在所有的线程以并行的方式执行并且分区架构基于策略I的环境下捕捉的。

# 并行线程	执行时间(在所有的线程执行时)	处理器使用率 (跨8个处理器)	# 行插入	批量拷贝吞吐量/秒	批量拷贝行/秒	磁盘吞吐量
8	52 分钟	88 %	2550835652	44.77 MB/秒	6,53,339	41.17 MB/秒 跨 8 个LUNs on CLARiiON

在这里展示的性能数值是在所有的线程以并行的方式执行并且分区架构基于策略II的环境下捕捉的。

#并行线程	执行时间(在所有的线程执行时)	处理器使用率 (跨8个处理器)	#行插入	批量拷贝吞吐量/秒	批量拷贝行/秒	磁盘吞吐量
8	52分钟	92.625%	2,550,835,652	46.42 MB/秒	677285	44.29 MB/秒 跨 8 个LUNs on CLARiiON

为数据仓库分区所选择的策略并不影响批量导入的吞吐量。

转换性能

在我们的测试中，批量导入的过程后面跟着一个转换过程。转换过程包括将源数据和维度表联接在一起，其目的是为了使用提取的维度键值来填充目标仓库。下面是一段在我们的测试场景中使用的示例代码：

SELECT fact.fact_data_1,
…
sdim.store_key, pdim.product_key, tdim.time_key
INTO sales_transformed
FROM
sales fact,
stores sdim,
products pdim,
time tdim
WHERE fact.store_id = sdim.store_id
AND convert(bigint, (fact.system + fact.generation + fact.vendor + fact.product))
= pdim.productid
AND fact.weekid = tdim.weekid

转换查询是连续运行的，一个查询后面紧接着另一个，以致于在策略I中要保存前面的分区架构设置

# 并行线程	执行时间	处理器使用率 (跨1个处理器)	# 被转换的行 (每个转换)	磁盘读吞吐量	磁盘写吞吐量
1	每个转换差不多1小时13分钟	100%	差不多3亿行	3.5 MB/秒 跨 1 LUN on CLARiiON (256 KB 读)	2.8 MB/秒 跨1 LUN on CLARiiON (128 KB 写)

以下是来自一个分区架构的性能数值，它是基于策略II的并且所有的线程都是以并行方式执行的。

索引构建性能

在我们的测试中，我们可以用2个小时在跨越拥有25亿行数据的八个分区的三个整型列（维度键）上创建一个聚簇索引。其中的SORT_IN_TEMPDB选项允许tempdb数据库被用来对数据进行排序。在这个测试过程中使用这个选项来隔离在索引建立过程中用来读写的物理磁盘。当'sort_in_tempdb'选项设置为on时，tempdb数据库必须有足够的剩余空间在离线的索引创建过程中用来放置整个的索引。当排序在一个用户数据库中完成时，每个文件组/分区需要有足够的剩余空间来放置相应的分区。

在我们的消费者场景和设计中，每个分区逻辑的代表了一周的有效数据，这些数据带有一个和在任何给定的分区中所有的行相同的键值。对于每个单独的索引建立的parallelism的级数只能是1，因为distinct关键字值的数量是1。因而，在SQL Server 2005中我们在创建了分区表之后再创建起始索引的做法让起始索引的建立更好的利用了可用的处理器资源。

# 并行线程	执行时间	处理器使用率	#行
8	2小时	86%	2,478,765,081

数据库备份性能

在我们的测试中，我们花了一个小时多一点的时间来备份活动分区到4个RAID 3 LUNs on EMC CLARiiON存储系统。活动分区分布在跨越8个LUN's的一个RAID 5 (4+1 10K RPM) EMC CLARiiON阵列上。目标设备是一个RAID 3 (4+1, 5K RPM ATA) CLARiiON阵列。

执行时间	# 备份的页 (8K)	备份/还原吞吐量/秒
1小时20分钟	30,518,330	50.15 MB/秒

老化数据到ATA 磁盘

老化数据到ATA磁盘包括使用SELECT/INTO语句批量导入数据。以下是一段用来实现滑动窗口的代码示例。

ALTER DATABASE [Customer]　
ADD FILEGROUP [ARCHIVE_1]
GO
ALTER DATABASE [Customer]　
ADD FILE (NAME = N'ARCHIVE_1', FILENAME = N'F:ATARAID52ARCHIVE_PARTITION_1.NDF',
SIZE = 100GB, FILEGROWTH = 0)
TO FILEGROUP [ARCHIVE_1]
GO
ALTER DATABASE Customer MODIFY FILEGROUP [ARCHIVE_1] DEFAULT
GO
SELECT * INTO iri..Sales_129_ARCHIVE
FROM VISF
WHERE TIME_KEY = 129
OPTION (MAXDOP 1)

一个等价于在现存的分区表上的聚簇索引的聚簇索引必须在新的表上创建，以便能够随后交换进分区表。在我们的测试中，我们使用了SELECT/INTO来滑出第一个有2.79亿行的老化分区到一个ATA磁盘阵列，整个过程用了不到20分钟。在同样的表上创建聚簇索引用了不到40分钟。

附录 B: 平台列表

以下的硬件和软件部件是用来进行本文中所描述的测试的：

Microsoft 软件

Microsoft Windows Server™ 2003 数据中心版 Build 3790

Microsoft SQL Server 2005 Beta 2

服务器平台

64-位 Unisys ES7000 Orion 130

16 Itanium 2 1.30 GHz CPUs 带有 3 MB 缓存

64 GB 内存

存储

EMC Symmetrix 5.5

-96, 72 GB 10 KB RPM 磁盘 16 GB 读写缓存

EMC CLARiiON FC4700

-155, 133.680 GB, 10 KB RPM磁盘16 GB读写缓存

-30, 344 GB, 5 KB RPM磁盘(ATA)

主机总线适配卡

8 Emulex LP9002L 2 GB/秒光纤通道主机总线适配卡

所有的适配卡映射到CLARiiON 存储阵列，可以通过Powerpath 软件实现负载均衡

存储管理软件

EMC Powerpath v 3.0.5

附录 C: 服务器体系结构

我们使用了一台Unisys ES7000 Orion 130服务器来进行我们的测试。了解你的服务器的体系结构对于确定你的系统的理论上的吞吐量是要素之一。例如，一个100 Mhz PCI总线能够提供800 兆位/秒 的吞吐量。本附录简要的描述了ES7000 Orion的体系结构。

企业服务器ES7000 Orion 130是一个模块化机架固定式系统，它基于一个Intel芯片组以及Intel Itanium 2处理器家族。

在测试中使用的Orion服务器配置了2个服务器模块，16个处理器，以及64 GB内存。每个服务器模块带有一个IOHM（输入/输出集线器模块）。每个IOHM控制了一个由4个PCIAM's (PCI 适配器模块)所组成的I/O子系统。每个PCIAM有两个总线，每个提供2个PCI卡插槽。在每个总线上支持最高到100 Mhz的PCI卡。

那些高亮的槽是当前配置的。在总线BUS 0-0上的槽2以及在PCIAM 0-0/IOHM-0上的总线BUS 0-1是为本地的SCSI磁盘以及网卡配置的，一共给我们留了16个槽。其中的8个使用Powerpath软件映射到了CLARiiON存储阵列。

图表5：本场景中使用的CASSIN 服务器的I/O配置