在 Microsoft SQL Server 2005中的关系型数据仓库分区策略

内容列表

对一个关系型数据仓库进行分区.. 1

关于关系型数据仓库.. 1

分区的好处.. 1

在 SQL Server 7.0/2000中的分区技术.. 1

在SQL Server 2005中的分区技术.. 2

在SQL Server 2005中分区的优势.. 2

标识一个查询计划中的 Demand Parallelism. 3

从SQL Server 2000的分区视图迁移到 SQL Server 2005 分区表/索引.. 4

影响关系型数据仓库分区的因素.. 4

数据量.. 4

数据导入.. 4

索引.. 5

数据老化.. 5

数据存档.. 5

查询性能.. 5

滑动窗口实现.. 5

交换分区的最佳实践.. 8

将数据存储到一个性价比高I/O子系统的技术.. 9

关系型数据仓库的分区策略.. 9

策略 I – 将一个分区绑定到它自己的文件组.. 9

策略Strategy II – 将两个或更多分区绑定到同样的文件组.. 10

哪个策略更好? 10

结论 11

附录 A: 性能数值.. 12

批量插入性能.. 12

转换性能.. 12

索引构建性能.. 13

数据库备份性能.. 14

老化数据到ATA 磁盘.. 14

附录 B: 平台列表.. 14

Microsoft 软件.. 14

服务器平台.. 14

存储.. 15

主机总线适配卡.. 15

存储管理软件.. 15

附录 C: 服务器体系结构.. 15

附录 D: EMC CLARiiON 存储.. 15

拓朴.. 16

附录 E: 存储隔离.. 17

配置你的存储.. 18

附录 F: 脚本.. 18

对一个关系型数据仓库进行分区

以下的部份将会简要的解释关系型数据仓库的概念，为关系型数据仓库进行分区的好处，以及迁移到Microsoft® SQL Server™ 2005分区的好处。

关于关系型数据仓库

关系型数据仓库提供了一个广泛的数据来源以及一个用来构建业务智能（BI）解决方案的体系结构。另外，关系型数据仓库可以为报表应用程序以及复杂且专用的SQL查询所用。

一个典型的关系型数据仓库是由维度表以及事实表组成的。维度表通常会比事实表小一些并且其中提供了关于解释事实的属性的详细信息。一个维度的例子是货物，商店和时间。事实表提供了对商业记录的描述，比如在所有商店中货物销售的信息。事实表通过最近收集到的数据进行不断的更新。

一个成功的关系型数据仓库解决方案的实现包括细致而长期的规划。以下列出了在构建一个关系型数据仓库时要考虑的要素：

· 数据量

· 数据导入窗口

· 索引维护窗口

· 工作负载特征

· 数据老化策略

· 存档和备份策略

· 硬件特征

这个文档后面的部份将会有对以上要素的详细讨论。

一个关系型数据仓库在实现时可以采用分区的方法或者一个（巨大）事实表的方法。对于使用分区还是不分区方式的设计选择主要依赖于前面列出的各个要素。关系型数据仓库可以从数据分区中获益。以下部份着重谈到了分区为关系型数据仓库带来的好处。

分区的好处

当组织中的数据库向上扩展并且包含了大量的数据时，非常关键的是保持其高可用性并同时适应对小的数据库维护窗口的需要。这些需求使得分区成为对于超大型数据库而言的一个量身定制的技术。分区技术所强调的关键问题是——通过将非常大的表分解成相对较小的分区从而使诸如数据导入，老化以及存档等重要任务的管理更易于进行。Microsoft SQL Server通过在SQL Server 7.0/2000中的分区视图以及在SQL Server 2005中添加的对分区表的支持提供了分区技术。

在 SQL Server 7.0/2000中的分区技术

SQL Server 7.0通过分区视图引入了对分区技术的支持。在SQL Server 2000中，这一功能进行了增强支持了可更新的分区视图。当事实表可以被自然的分割或者根据数据范围划分成单独的表时，对于关系型数据仓库而言分区视图技术是再合适不过的了。分区视图的基表可以被UNION来表示成一个统一的数据集。分区视图大大降低成本应用程序的复杂性，原因是物理实现被从应用程序数据访问方式中抽象了出来。

在SQL Server 2000中，分区视图可以被扩展到包括分布式分区视图，从而启用跨多个服务器/实例的数据库联合。有关分布式分区视图的讨论超出了本文的范围。对此更详细的讨论，请参阅微软开发人员网络（MSDN）上的“分布式分区视图”，地址是

http://www.microsoft.com/sql/evaluation/features/distpart.asp!href(http://www.microsoft.com/sql/evaluation/features/distpart.asp).

在SQL Server 2005中的分区技术

在SQL Server 2005中通过使用表和索引的分区，可以降低在使用分区视图管理非常大的数据库时的复杂性。SQL Server 2005提供了用数据行作为最小的分区单位的水平范围分区功能。可以被分区的对象有：

· 基表

· 索引（聚簇和非聚簇的）

· 索引视图

范围分区是通过自定义的数据范围定义的表的分区。用户通过分界值，一个使用文件组映射的分区架构，以及映射到分区架构的表来定义分区函数。一个分区函数决定了一个表或索引中特定的一行所属于的分区。每个分区都是用一个通过一个分区架构映射到某个存储位置（文件组）的分区函数来定义的。对于在SQL Server 2005中实现分区的全面讨论，请参阅MSDN中的"SQL Server 2005 分区表和索引"。

以下部份阐述了SQL Server 2005中分区功能的优势并提供了将分区表迁移到SQL Server 2005的策略。

在SQL Server 2005中分区的优势

在SQL Server 2005中的表和索引的分区功能通过将其分解为更易管理的分区大大方便了对超大型数据库的管理。这一部份涉及了一些在针对关系型数据仓库的使用中分区表相对于分区视图的优势。

管理

一个使用分区视图的缺点是当你使用它的时候，数据库操作必须对单个的对象执行而不是对视图本身。举个例子，如果一个现存的索引必须被删除并且要创建一个新的索引，这些操作必须在每个相关的基表上执行。

在SQL Server 2005中，诸如索引维护这样的数据库操作是对分区表本身而不是底层的相关分区上进行的，因而在管理索引过程中大大减轻了负担。

更好的Parallelism机制

在SQL Server 2000中，操作是在单个表上执行的并且数据在一个分区视图的级别进行聚合。来自于基表中的行通过使用串联运算符进行汇集并显示视图。然后再在结果集数据上执行聚合。

在SQL Server 2005中，对分区表执行的查询使用了一个被称为demand parallelism的新的运算符。Demand parallelism受到系统资源和MAXDOP设置的影响。

使用分区表的查询将会比使用分区视图进行的同样查询更快的进行编译。当使用分区视图时查询的编译时间是与分区的数量成正比的，而使用分区表时查询的编译时间不会受到分区数量的影响。

在某些情况下，对分区视图进行查询可能会更好一些。以下描述了这样的情况：

· 当优化器选择使用demand parallelism时，parallelism的最小单位是一个分区。在SQL Server 2005中对一个分区表中的单个分区进行查询的性能可能不太好，原因是parallelism的级数被限制到了1。同样的查询如果对一个分区视图进行可能会好一些，原因是在一个分区内更好的parallelism

· 当分区的数量少于处理器的数量时使用分区视图会更好一些，原因是通过parallelism可以更好的使用处理器资源。当分区的数量大于处理器数量，而数据并不是在分区间平均分布时，对分区表查询的性能可能仍旧不太好

· 当分区中的数据分布不均时使用对分区视图的查询也会更好一些

标识一个查询计划中的 Demand Parallelism

下面是一个查询计划的示例，它是由一个加法聚合查询产生的

划了红圈的部份标明了在查询计划中出现的demand parallelism。嵌套循环运算符左边的子demand parallelism是用分区ID来表示的。嵌套循环运算符右边的子demand parallelism是用分区表自身来表示的。在这张图表中，对于由左边的子demand parallelism所返回的每一个分区ID，一个并行的索引查找运算符对来自对应的分区中的行进行反复扫描。所有在嵌套循环运算符上进行的操作也受到由demand parallelism所建立的并行线程的数量的影响。左边的子demand parallelism表示了仅当分区剪切生效时，也就是当查询通过分区筛选结果时，被查询所影响的分区ID，。

<shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></path><lock aspectratio="t" v:ext="edit"></lock></shapetype><shape id="_x0000_i1025" style="WIDTH: 445.5pt; HEIGHT: 357pt" type="#_x0000_t75"><imagedata o:href="../../../SQLWPs/Strategies%20for%20data%20warehouse%20IMAGES/Art1-QueryPlan.JPG" src="file:///C:%5CDOCUME~1%5CADMINI~1%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image001.jpg"></imagedata></shape>

图表 1. 标识 demand parallelism

<shape id="_x0000_i1026" style="WIDTH: 286.5pt; HEIGHT: 117.75pt" type="#_x0000_t75"><imagedata o:href="../../../SQLWPs/Strategies%20for%20data%20warehouse%20IMAGES/Art2-QueryPlan.JPG" src="file:///C:%5CDOCUME~1%5CADMINI~1%5CLOCALS~1%5CTemp%5Cmsohtml1%5C01%5Cclip_image002.jpg"><font face="Verdana" size="2"><strong></strong></font></imagedata></shape>

从SQL Server 2000的分区视图迁移到 SQL Server 2005 分区表/索引

一个现存的基于单个巨表或者分区视图的应用程序可以被重构或者迁移到一个基于分区的SQL Server 2005解决方案。要作出是重构还是迁移应用程序的决定，必须详细分析在性能，可管理性，以及可用性方面存在的需求。

一个将SQL Server 2000的分区视图迁移到SQL Server 2005分区表的简单路径将包括以下步骤：

· 创建一个分区函数和架构以确定每个分区的分界点和物理存储位置。分界点应当和分区视图的基表的差不多

· 在新建的分区架构上创建一个分区表。该表应当指定与分区视图的基表同样的物理结构，包括索引

· 将分区视图的每个基表交换为新建的分区事实表的一个分区。分区架构所关联的文件组必须与被交换进来的表所属的文件组相匹配。另外，要迁移的表必须符合交换提示的要求。举个例子，目标表不能是一个与架构绑定的视图的部件。关于交换提示的要求列表，请参阅SQL Server 2005联机丛书中的“使用分区交换有效的传递数据”

影响关系型数据仓库分区的因素

对于一个分区的关系型数据仓库的成功实现而言，包括了对数据库增长和易管理性的规划。接下来的部份阐述了影响关系型数据仓库分区的因素以及滑动窗口实现的详细信息。

数据量

当事实表的大小比较小时，分区只会添加更多的管理复杂性而不会带来更多的价值。事实表的大小是基于应用程序的特点并且由每一种实现方式所决定的。通常用户需要事实表在他们实施分区之前至少有100 GB。

数据导入

数据导入是一个数据仓库的核心部份。几乎所有的数据仓库都会周期性的处理最近收集的数据。是否成功的管理数据仓库取决于批量导入进程的效率以及导入过程中现有的数据能否继续使用。

在构建你的事实表时有两个选择：

· 建立一个巨大的表，或者

· 使用分区的方式

使用单个巨表这种方式与使用分区相比会导致较低的可用性，原因是在典型的关系型数据仓库环境中批量导入操作是步进执行的。例如，步进式的批量导入会从对目标表的锁定中获得巨大的好处。当使用单个表时，这样做就会阻止所有其它的用户在表导入的过程中访问它。对于步进导入数据的最佳工作方式是使用一个规划维护窗口。对于使用单个巨表这种方式中批量导入的全面讨论请参阅在http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx 上的“SQL Server 2000步进批量导入案例学习”!href(http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx)

分区方式让数据被批量导入到单个的分段表中，每一段都代表了一个确定的分区范围。分段表随后被添加到分区视图当中或者被当作一个新的分区交换到分区表中。由于每一个分区逻辑上都是由一个单独的分段表来代表的，因此步进的批量导入不会对任何针对现有数据的查询造成可用性和性能上的影响。

一个典型的数据仓库解决方案应当包括在批量导入数据的同时进行数据转换的功能。转换包括对源数据的清除和/或者聚合以产生目标库。

一个转换典型情况下是通过使用象微软系统集成服务这样的工具来完成的。如果过程中不需要一个复杂的工作流，用户可以选择使用SELECT/INTO来完成转换。

索引

在把数据导入到一个关系型数据仓库后，一般就要创建索引来为用户查询提供支持。在对关系型数据仓库体系结构造成影响的各个要素中创建和维护索引扮演了主要角色。

在没有索引时对事实表的查询性能通常比较差。对于使用单个巨大事实表的情况，一个最佳的解决方案是删除所有的索引，导入数据，然后重建索引。这种方法导致可用性的降低并且有一个不断增长的维护窗口，当表的大小增长到一定程度时这种方法可能就不太现实了。

在SQ