用SQL Server 构建高性能数据仓库.docx

1、用SQL Server 构建高性能数据仓库用SQL Server 2005构建高性能数据仓库摘要：本文主要讨论当架构一个很大的、高性能的数据仓库，特别是对那种无法预知有多少查询量的系统时要考虑的一些东西。这个讨论包括SQL Server 2005的一些新的特性以及在使用这些特性的时候需要考虑的一些东西。它同时也包括了创建或存储一个聚合的数据集来使得主流的查询和报表更加容易。一 介绍有一些具有访问数据权限的“超级用户”已经学会了专业的Transact-SQL。SQL Server 2005 报表服务（SSRS）中的报表构造器的便利性扩展到了强大的Transact-SQL查询的创建，使得更多的用户

2、使用它时更加容易。他们这种消耗系统资源的能力是无法超越的，在保持一致的性能方面对数据库管理员（DBA）构成了挑战。但是，当SQL Server的分析服务（SSAS）被提及的时候，需要用不可预知的方式访问数据的用户可能感到他们的查询效率受到阻碍。因此，你怎样使得你的商业用户查询到他们所需要的东西，也跟给予他们性能很好地查询以及始终满足服务级别的协议（SLA）一样重要。在过去的几年中，大量的信息都是围绕数据仓库和商业智能（BI）开展的。很大一部分信息聚焦于构建一个数据仓库或者数据市场来满足或者超过与制定策略决定相关的商业目标。随着SQL Server 2005的发布，1000GB范围内的相关数据仓

3、库的实现在它附加的功能中提供了支持。这份白皮书说明了在SQL Server 2005上用一个性能的视角架构一个相关的数据仓库或者数据市场时包括的最佳实践。在这份白皮书中也讨论了围绕硬件选择方面的一些显而常见的问题、SQL Server实例和数据库配置和table/index的设计，它还介绍了为了交付一个数据市场或数据仓库来平衡查询性能时的整体的策略。相关的数据仓库的性能常常在更新数据库和查询时感觉到。更新操作需要对新的数据进行更多的提取、转换和载入。DBA有责任尽可能更有效率的设计和维护数据库。查询操作也以很多种方式出现，例如终端用户的查询、报表服务等。既然分析服务在各种查询中是最可预测的，白

4、皮书主要聚焦于对相关数据库仓库的直接的查询。在继续讲之前，我们对已提及到的一些东西做一些假设。用来存储信息的一些相关的数据仓库通常是非常大的。数据仓库和数据市场常常被可替换的使用。但是，数据仓库更多的跟信息在所有的企业的信息存储有关。这些数据被清空，并且是以一种正常的方式和很少的直接查询。相反，它常常使用一致的数据源来为一种或多种面向主题的数据仓库直接的服务用户。在数据市场中的数据虽然能够以各种方式来设计，但是常常是用一种空间的方式。在工业中，“数据市场”结合在一起变成了“数据仓库”。但是，从一致性方面考虑，在这份白皮书中，我们使用“数据仓库”这个概念作为包含数据市场的一个通用的概念。这份白皮

5、书假设读者已基本熟悉数据仓库的概念、空间建模、SQL Server 2005表的分离以及索引化的视图。其中很多的查询结果比较测试是在项目REAL的数据库中进行的。项目REAL是一个在SQL Server 2005上的真实的数据仓库的参考实现。二、创建一个可靠的基础创建一个高性能的数据仓库的关键是懂得那些数据，以及知道用户是怎样查询数据库的。后者常常有着很大变化，因此很有必要根据查询模式的改变来持续的进行一些调整。有一些基础组织的决定对系统的性能产生巨大影响。1. 硬件方面的考虑硬件方面的决定常常做得早于系统的任何一个重要的方面。为相关的数据仓库选择硬件是很具有技巧的一件事。这对于满足那些超级用

6、户的报表需求变得更加常用。64位的平台因为它的可扩展的存储架构给数据仓库提供了很多的优点。数据仓库支持读取GB级和TB级数据级的存储要求。这种平台消除了32位的平台上的一些顾虑，但是又具有32位平台的所有优点。对于数据仓库来说更加是这样，因为数据仓库存储和查询非常大的数据库。在选择64位平台时，还需要考虑是使用x64还是IA64。做考虑之前需要考虑很多因素。在SQL Server中完全支持x64，并且为数据仓库提供了很多好的选项。目前，IA64硬件提供更多的可量测性，但是x64平台很快赶上了它。记住IA64是完全不同的一种芯片结构（它支持数据仓库中常见的平行化和改善计算逻辑）是非常重要的。既然

7、这两种芯片架构很快的发展，因此最好还是好好地学习一下当前的提供情况。在TPC的网址（http:/www.tpc.org/tpch/results/tpch_perf_results.asp）上提供了参考。除了芯片架构之外，处理机的数量和速度也是很重要的一个方面。数据仓库需要很大数量的平行化的需求，这与处理器的速度和时钟速度是有很大关系的。在实现数据从仓库之前很难估计实际的需求。一个很好的方法是不仅仅确立一个开始的基线，也要识别硬件和架构。2. 磁盘的配置数据仓库的磁盘的配置也是很重要的一个因素。数据仓库动辄就需要TB级的I/O操作。如果这些信息恰好很少查询，I/O在我们的环境中将会是一个很大的

8、因素。I/O仍然是硬件系统中的一个最慢的方面，因此为了能够正确的购买和配置磁盘子系统，需要进行周密的计划。存储范围网络（Storage Area Network：SAN）仍然是大型数据库（例如数据仓库）的一个理想的选择。在考虑SAN时需要考虑不同的文件类型（tempdb、数据文件和日志文件等）的特征，以便磁盘子系统能够得到正确的设计。磁盘配置的一些基本的最佳实践如下：(1) 创建更多的SAN磁盘组来支持来自SQL Server的多重的和平行的I/O。每一个磁盘组由不同的磁盘数组组成并且基于数据的类型（工作的数据、历史数据、日志和Tempdb）配置；(2) 考虑SAN卖方基于I/O等级的推荐；(

9、3) 为了保证在每一部分失败时能够恢复的分离，需要将数据和日志文件放在不同的磁盘组中进行；(4) 将tempdb数据文件和日志文件放在不同的磁盘组中；(5) 保证数组从大数量的物理磁盘中构建，但是没有充满在控制器中；大表通常经历了大范围的读取，从大数量的磁盘到均匀的分配I/O。当定义RAID需求的时候，OLTP的对于数据仓库环境的一些最佳实践派上了用场。Tempdb是一些重要的数据库，这些数据库必须与在RAID序列的其他的数据库文件分开存放。当你架构一个数据仓库的时候，需要富有创造力的考虑这些数据。特别是当你具有OLTP背景的时候。记住，即使数据库巨大，也很少一部分是很规则的被更新。为了决定创

10、新性的架构需要看看用户的查询需求。网络需求常常有很大不同。虽然查询能在很大数据范围内得到过滤，返回给客户端的最终结果集常常是很小的。当一个分离的应用程序在相关的数据仓库和终端用户数据之间被插入时会发生异常。这个应用程序可能是OLAP服务器，例如分析服务等。这些应用程序趋向于从SQL Server中请求大数量的数据，在两个服务器见请求高速度的网络连接。3. SQL Server的配置一旦购买了合适的硬件，为了充分的发挥SQL Server的潜能，对其进行配置变得很重要。SQL Server是被设置为自和谐的，因此，在很多情况下，要做的事情只是留下不同的配置值。但是这个规则中也有一些例外的情况，例

11、如在数据库布局方面的一些建议等，这些在本小节中将进行讲述。将数据仓库的服务器与其它的应用程序或数据库共用一个服务器是不明智的。资源利用的不一致使得很难对应用程序数据库提供一致的性能。首先考虑一些存储器的配置。在很多情况下，通常选择的是64位的平台，因为相关数据仓库的存储量的高需求。AWE(Address Windowing Extensions)能从逻辑上增加32位平台的存储条，但是它引入了存储映射，这在64位平台上是被避免的。“Max Degree of Parallelism”选项告诉SQL Server在一次单独的查询执行操作中平行化程度的最大程度。默认值0告诉SQL Server在运行

12、时才决定其值。当一个平行化的计划为了一个查询产生时，查询优化器基于当前在运行时所获得的处理器决定。一个经常被忽略的SQL Server选项是“query governor cost limit”，这个属性表示当查询超过多少时将拒绝执行。默认值0表示所有的查询都将被执行。CPU和I/O也常常由SQL Server实例指定。不像在服务器上的共享服务资源的其它应用程序， 它们必须不使用。这个配置在一些很小的数据仓库中是强烈推荐不使用的。4. Tempdbtempdb数据库是数据仓库中的重要组件。数据仓库查询趋向于执行分组的、排序的和聚合的大数量的行。依赖于存储资源、索引和查询结构，很多需求是在tem

13、pdb上的。配置tempdb的第一步是尽可能的将其置于最有效的磁盘上。第二步是为tempdb确定一个合适的磁盘容量，使得其能够在查询环境中适应。5. 数据库配置（1） 锁数据库锁为了数据库的一致性而使用，保证数据更新操作是原子的、一致性的、隔离的和持久化的。数据一致性的概念仅仅在更新发生的时候使用。（2） 及时的文件初始化SQL Server 2005能够通过及时的文件初始化的方式快速的创建或者增加数据库的大小。及时的文件初始化也对数据库文件的自动增长有用。但最好还是决定一个大小。（3） 自动收缩关闭自动收缩通常是一个最佳实践。如果新的业务需求降低了需要保持的大量数据，数据库能够手动的收缩。关

14、闭自动收缩阻止了数据库在数据存档后的收缩。（4） 自动更新统计在一个数据仓库中的批量更新很少触发过时的统计。在SQL Server 2005中，统计能够异步的更新，并且查询能够触发它们同时在老的统计中编译和运行。这意味着在老的统计中的查询编译，常常已经足够好。这时候推荐打开“自动更新统计”。（5） 磁盘布局对磁盘上的表进行布局的最佳实践是仅仅对系统对象使用主文件组，并且为剩下的项使用显示定义的文件组。在上篇中讲述了怎么样创建一个可靠的基础数据仓库，分别从硬件方面、磁盘的配置、SQL Server的配置、Tempdb和数据库配置5个方面进行了详细阐述，在接下来的这篇中，将接着详细讲述一下表的设计

15、相关的一些问题，将从索引策略、索引的分片以及快速载入与查询性能的等方面进行详细阐述。三表的设计对于数据仓库（它的数据直接被最终用户消费）的物理设计通常有两种方法，第一种方式是保留源数据的三种通常的表格设计。这个设计对操作性的报表很好。在第三方的源码系统的情况下，这种数据库能够满足应用报表。第二种方式是空间的设计，这通常作为一个星型或者雪花型的方式提及。这种方法的主要好处是简单而且性能良好。这个模型的简单使得它变得更加容易，并且终端用户掌握和浏览它更加快速。空间数据在相关的设计的性能上能够在实际的测试中被观察到。接下来的一个设计问题是实现星型还是雪花型方式的设计。这在过去存在一个具有争论性的辩论

16、，但是雪花型方式获得了越来越多人的支持。传统的对于雪花型的争论是数据模型复杂度和性能缺陷。在SQL Server环境中，星型跟雪花型的设计比起来，性能很少是一个大问题。甚至在更大一点的维度，例如客户的维度，来自雪花型设计的结果的更窄的表通常为额外的连接进行了补偿。这个再一次能够在你自己环境中的测试情景中观察到。1. 声明式的具有参考作用的集成度和限制在数据仓库中，需要维护在数据集成度和性能需要中的很好的平衡。虽然数据集成度极端重要，对数据仓库的更新通常通过周期性（通常为每日或每小时）的批量ETL过程得到很好的控制。星型或雪花型模型严格上意味着那些低级别的维度表包含一个主键，这个主键是一个代理键。源系统没有它们的代理键的信息，因此它必须在ETL中通过寻找业务键来获得。定义声明式的具有参考作用的集成度很大程度上降低了ETL代码的性能，并且在这种情况