| UML软件工程组织 | |||
| |
|||
|
|||
用SQL Server 2005构建高性能数据仓库
2008-06-05 来源：IT168 amigo
| 摘要：本文主要讨论当架构一个很大的、高性能的数据仓库，特别是对那种无法预知有多少查询量的系统时要考虑的一些东西。这个讨论包括SQL Server 2005的一些新的特性以及在使用这些特性的时候需要考虑的一些东西。它同时也包括了创建或存储一个聚合的数据集来使得主流的查询和报表更加容易。 一． 介绍 有一些具有访问数据权限的“超级用户”已经学会了专业的Transact-SQL。SQL Server 2005 报表服务（SSRS）中的报表构造器的便利性扩展到了强大的Transact-SQL查询的创建，使得更多的用户使用它时更加容易。他们这种消耗系统资源的能力是无法超越的，在保持一致的性能方面对数据库管理员（DBA）构成了挑战。但是，当SQL Server的分析服务（SSAS）被提及的时候，需要用不可预知的方式访问数据的用户可能感到他们的查询效率受到阻碍。因此，你怎样使得你的商业用户查询到他们所需要的东西，也跟给予他们性能很好地查询以及始终满足服务级别的协议（SLA）一样重要。 在过去的几年中，大量的信息都是围绕数据仓库和商业智能（BI）开展的。很大一部分信息聚焦于构建一个数据仓库或者数据市场来满足或者超过与制定策略决定相关的商业目标。随着SQL Server 2005的发布，1000GB范围内的相关数据仓库的实现在它附加的功能中提供了支持。这份白皮书说明了在SQL Server 2005上用一个性能的视角架构一个相关的数据仓库或者数据市场时包括的最佳实践。 在这份白皮书中也讨论了围绕硬件选择方面的一些显而常见的问题、SQL Server实例和数据库配置和table/index的设计，它还介绍了为了交付一个数据市场或数据仓库来平衡查询性能时的整体的策略。 相关的数据仓库的性能常常在更新数据库和查询时感觉到。更新操作需要对新的数据进行更多的提取、转换和载入。DBA有责任尽可能更有效率的设计和维护数据库。查询操作也以很多种方式出现，例如终端用户的查询、报表服务等。既然分析服务在各种查询中是最可预测的，白皮书主要聚焦于对相关数据库仓库的直接的查询。 在继续讲之前，我们对已提及到的一些东西做一些假设。用来存储信息的一些相关的数据仓库通常是非常大的。数据仓库和数据市场常常被可替换的使用。但是，数据仓库更多的跟信息在所有的企业的信息存储有关。这些数据被清空，并且是以一种正常的方式和很少的直接查询。相反，它常常使用一致的数据源来为一种或多种面向主题的数据仓库直接的服务用户。在数据市场中的数据虽然能够以各种方式来设计，但是常常是用一种空间的方式。在工业中，“数据市场”结合在一起变成了“数据仓库”。 但是，从一致性方面考虑，在这份白皮书中，我们使用“数据仓库”这个概念作为包含数据市场的一个通用的概念。 这份白皮书假设读者已基本熟悉数据仓库的概念、空间建模、SQL Server 2005表的分离以及索引化的视图。其中很多的查询结果比较测试是在项目REAL的数据库中进行的。项目REAL是一个在SQL Server 2005上的真实的数据仓库的参考实现。 二、创建一个可靠的基础 创建一个高性能的数据仓库的关键是懂得那些数据，以及知道用户是怎样查询数据库的。后者常常有着很大变化，因此很有必要根据查询模式的改变来持续的进行一些调整。有一些基础组织的决定对系统的性能产生巨大影响。 1. 硬件方面的考虑 硬件方面的决定常常做得早于系统的任何一个重要的方面。为相关的数据仓库选择硬件是很具有技巧的一件事。这对于满足那些超级用户的报表需求变得更加常用。 64位的平台因为它的可扩展的存储架构给数据仓库提供了很多的优点。数据仓库支持读取GB级和TB级数据级的存储要求。这种平台消除了32位的平台上的一些顾虑，但是又具有32位平台的所有优点。对于数据仓库来说更加是这样，因为数据仓库存储和查询非常大的数据库。 在选择64位平台时，还需要考虑是使用x64还是IA64。做考虑之前需要考虑很多因素。在SQL Server中完全支持x64，并且为数据仓库提供了很多好的选项。目前，IA64硬件提供更多的可量测性，但是x64平台很快赶上了它。记住IA64是完全不同的一种芯片结构（它支持数据仓库中常见的平行化和改善计算逻辑）是非常重要的。既然这两种芯片架构很快的发展，因此最好还是好好地学习一下当前的提供情况。在TPC的网址（http://www.tpc.org/tpch/results/tpch_perf_results.asp）上提供了参考。 除了芯片架构之外，处理机的数量和速度也是很重要的一个方面。数据仓库需要很大数量的平行化的需求，这与处理器的速度和时钟速度是有很大关系的。在实现数据从仓库之前很难估计实际的需求。一个很好的方法是不仅仅确立一个开始的基线，也要识别硬件和架构。 2. 磁盘的配置 数据仓库的磁盘的配置也是很重要的一个因素。数据仓库动辄就需要TB级的I/O操作。如果这些信息恰好很少查询，I/O在我们的环境中将会是一个很大的因素。I/O仍然是硬件系统中的一个最慢的方面，因此为了能够正确的购买和配置磁盘子系统，需要进行周密的计划。存储范围网络（Storage Area Network：SAN）仍然是大型数据库（例如数据仓库）的一个理想的选择。在考虑SAN时需要考虑不同的文件类型（tempdb、数据文件和日志文件等）的特征，以便磁盘子系统能够得到正确的设计。磁盘配置的一些基本的最佳实践如下： (1) 创建更多的SAN磁盘组来支持来自SQL Server的多重的和平行的I/O。每一个磁盘组由不同的磁盘数组组成并且基于数据的类型（工作的数据、历史数据、日志和Tempdb）配置； (2) 考虑SAN卖方基于I/O等级的推荐； (3) 为了保证在每一部分失败时能够恢复的分离，需要将数据和日志文件放在不同的磁盘组中进行； (4) 将tempdb数据文件和日志文件放在不同的磁盘组中； (5) 保证数组从大数量的物理磁盘中构建，但是没有充满在控制器中； 大表通常经历了大范围的读取，从大数量的磁盘到均匀的分配I/O。 当定义RAID需求的时候，OLTP的对于数据仓库环境的一些最佳实践派上了用场。Tempdb是一些重要的数据库，这些数据库必须与在RAID序列的其他的数据库文件分开存放。 当你架构一个数据仓库的时候，需要富有创造力的考虑这些数据。特别是当你具有OLTP背景的时候。记住，即使数据库巨大，也很少一部分是很规则的被更新。 为了决定创新性的架构需要看看用户的查询需求。 网络需求常常有很大不同。虽然查询能在很大数据范围内得到过滤，返回给客户端的最终结果集常常是很小的。当一个分离的应用程序在相关的数据仓库和终端用户数据之间被插入时会发生异常。这个应用程序可能是OLAP服务器，例如分析服务等。这些应用程序趋向于从SQL Server中请求大数量的数据，在两个服务器见请求高速度的网络连接。 3. SQL Server的配置 一旦购买了合适的硬件，为了充分的发挥SQL Server的潜能，对其进行配置变得很重要。SQL Server是被设置为自和谐的，因此，在很多情况下，要做的事情只是留下不同的配置值。但是这个规则中也有一些例外的情况，例如在数据库布局方面的一些建议等，这些在本小节中将进行讲述。 将数据仓库的服务器与其它的应用程序或数据库共用一个服务器是不明智的。资源利用的不一致使得很难对应用程序数据库提供一致的性能。 首先考虑一些存储器的配置。在很多情况下，通常选择的是64位的平台，因为相关数据仓库的存储量的高需求。 AWE(Address Windowing Extensions)能从逻辑上增加32位平台的存储条，但是它引入了存储映射，这在64位平台上是被避免的。 “Max Degree of Parallelism”选项告诉SQL Server在一次单独的查询执行操作中平行化程度的最大程度。默认值0告诉SQL Server在运行时才决定其值。当一个平行化的计划为了一个查询产生时，查询优化器基于当前在运行时所获得的处理器决定。 一个经常被忽略的SQL Server选项是“query governor cost limit”，这个属性表示当查询超过多少时将拒绝执行。默认值0表示所有的查询都将被执行。 CPU和I/O也常常由SQL Server实例指定。不像在服务器上的共享服务资源的其它应用程序， 它们必须不使用。这个配置在一些很小的数据仓库中是强烈推荐不使用的。 4. Tempdb tempdb数据库是数据仓库中的重要组件。数据仓库查询趋向于执行分组的、排序的和聚合的大数量的行。依赖于存储资源、索引和查询结构，很多需求是在tempdb上的。 配置tempdb的第一步是尽可能的将其置于最有效的磁盘上。 第二步是为tempdb确定一个合适的磁盘容量，使得其能够在查询环境中适应。 5. 数据库配置 （1） 锁 数据库锁为了数据库的一致性而使用，保证数据更新操作是原子的、一致性的、隔离的和持久化的。数据一致性的概念仅仅在更新发生的时候使用。 （2） 及时的文件初始化 SQL Server 2005能够通过及时的文件初始化的方式快速的创建或者增加数据库的大小。及时的文件初始化也对数据库文件的自动增长有用。但最好还是决定一个大小。 （3） 自动收缩 关闭自动收缩通常是一个最佳实践。如果新的业务需求降低了需要保持的大量数据，数据库能够手动的收缩。关闭自动收缩阻止了数据库在数据存档后的收缩。 （4） 自动更新统计 在一个数据仓库中的批量更新很少触发过时的统计。在SQL Server 2005中，统计能够异步的更新，并且查询能够触发它们同时在老的统计中编译和运行。这意味着在老的统计中的查询编译，常常已经足够好。这时候推荐打开“自动更新统计”。 （5） 磁盘布局 对磁盘上的表进行布局的最佳实践是仅仅对系统对象使用主文件组，并且为剩下的项使用显示定义的文件组。 在上篇中讲述了怎么样创建一个可靠的基础数据仓库，分别从硬件方面、磁盘的配置、SQL Server的配置、Tempdb和数据库配置5个方面进行了详细阐述，在接下来的这篇中，将接着详细讲述一下表的设计相关的一些问题，将从索引策略、索引的分片以及快速载入与查询性能的等方面进行详细阐述。 三．表的设计 对于数据仓库（它的数据直接被最终用户消费）的物理设计通常有两种方法，第一种方式是保留源数据的三种通常的表格设计。这个设计对操作性的报表很好。在第三方的源码系统的情况下，这种数据库能够满足应用报表。 第二种方式是空间的设计，这通常作为一个星型或者雪花型的方式提及。这种方法的主要好处是简单而且性能良好。这个模型的简单使得它变得更加容易，并且终端用户掌握和浏览它更加快速。空间数据在相关的设计的性能上能够在实际的测试中被观察到。 接下来的一个设计问题是实现星型还是雪花型方式的设计。这在过去存在一个具有争论性的辩论，但是雪花型方式获得了越来越多人的支持。传统的对于雪花型的争论是数据模型复杂度和性能缺陷。 在SQL Server环境中，星型跟雪花型的设计比起来，性能很少是一个大问题。甚至在更大一点的维度，例如客户的维度，来自雪花型设计的结果的更窄的表通常为额外的连接进行了补偿。这个再一次能够在你自己环境中的测试情景中观察到。 1. 声明式的具有参考作用的集成度和限制 在数据仓库中，需要维护在数据集成度和性能需要中的很好的平衡。虽然数据集成度极端重要，对数据仓库的更新通常通过周期性（通常为每日或每小时）的批量ETL过程得到很好的控制。星型或雪花型模型严格上意味着那些低级别的维度表包含一个主键，这个主键是一个代理键。源系统没有它们的代理键的信息，因此它必须在ETL中通过寻找业务键来获得。定义声明式的具有参考作用的集成度很大程度上降低了ETL代码的性能，并且在这种情况下是冗余的。 此外，数据集成度得到了优先权，但是如果更新能够像它们所需要的那样被得到很好的控制，推荐在实际的表和空间的表中，以声明式的外键/主键关系方式出现的具有参考作用的集成度能能够被忽略。 在实际的表中，各项限制也是同样的道理。在维度表中定义的限制更加合理。像PK/FK定义了集成度一样，ETL进程的工作室保证所有被载入数据仓库的数据都是干净的。 注意那些具有参考作用的集成度能够在数据源视图（data source views：DSV）中定义，它允许分析服务和报表构建器在这些内在的关系和明确表达SQL Server的正确的查询之间起到杠杆的作用。 2. 索引策略 正确的索引对SQL Server的数据仓库极其重要。第一种趋势是创建尽可能多的索引来进行方便的动态查询。对使用的索引策略进行周密的设计是相当重要的，为了更完全的懂得在数据仓库中呈现的数据，SQL Server选择帮助索引的方式，以及查询的实质将会是数据仓库的一个问题。而后一项是很难预测的。 （1） 维度表的索引 维度表的索引是相对简单的。即使这个策略偏离了市场，效率低的索引也不会产生极大的影响，因为维度表通常比较小和相对稳定。一个通常的最佳实践是创建一个集群化的、在每一个维度表的代理键的主键。代理键通常是一个IDENTITY列，这常常便利了插入操作。在一个业务键上的一个非集群的索引应该从查询目的考虑，或者如果代理键查找是通过Transact-SQL语句执行的。当使用SSIS来执行查找时，表或者表的子集合将会载入到SSIS服务器的存储器中，并且在一个业务键上的索引通常是没有用的。注意在业务键的一个非集群的索引将不需要实际数据页查找。 （2） 实际表的索引 因为索引的存在并不意味着SQL Server一定要使用它。SQL Server很少使用的索引的创建常常被作为一个入口或者对进程的管理，而不涉及到磁盘资源。 索引创建的一个考虑因素是尽可能紧凑的保持它们。紧凑的索引需要更少的页，从这方面说它提高了性能，特别当具有巨大的实际表时更加如此。 （3） 使用一个灵巧的Date键 为了数据维度，使用smalldatetime数据类型作为代理键是一个有用的技术。这允许你指定直接的数据范围过滤。指定范围直接的断言了实际表的分隔键。这允许SQL Server 2005来消除查询中的分隔。通过在实际的表中指定日期范围过滤器连接数据维度。 （4） 估计索引的使用 在SQL Server 2005中，一个有用的DMV是sys.dm_db_index_usage_stats，它记录了当前的SQL Server运行时各索引的使用次数。 3. 索引的分片 在数据仓库环境中，索引的性能问题能够被忽略。我们已经找到了执行索引维护的客户，因为观察到这儿并没有那么多的批量窗口来支持它！一个索引片段对性能将产生巨大的负面影响。技巧在于通过创建有用的索引来最小化索引的维护工作。并且重新组织那些被分片的索引，这是第一点。 第二点是在没有假设所有索引必须在所有时间重新组织下进行的。小到中型维度的集群索引慢慢的变成分片，因为它们通常通过一个identity列按顺序插入。非集群的维度索引在某种程度上更有问题，但是也更容易重新组织。大型的实际表常常是分区的。所有的索引操作时分开的，包括那些索引的分片和重新组织。 4. 快速载入和查询性能的分离 在数据仓库中表的分离是一个通用的实践，主要是用来方便对大量的实际表进行管理。我们在这小节中聚焦于SQL Server 2005中新表和索引的分离特性。 在这次讨论中，我们假设水平的分离是基于日期的，这对于时间的实际进展是大致平行的，因此对于数据仓库也是一个不错的选择。例如，销售日期在聚焦于销售的数据仓库中将会是一个不错选择。这个想法是选择一个分离的key。虽然维度表能够被分离，但是考虑的时候它们通常过小，所以并不适合分离。 进行分离的好处如下：
（1） 分离的考虑 确定分离要素要考虑的因素如下：
从当前所讲的这些信息看来，让我们考虑分离策略的一些选项。查询模式在不同的环境中变化非常大，因此有些选项应该仅仅只是作为一个输入来识别你的环境的合适的策略。 （2） 分离的载入 你通常会觉得载入实际的数据比从分离表中载入数据更有效率。如果实际表足够大，因而能够保证分离，如果对分离的表执行操作的话，这通常意味着增加的更新将更加快。既然性能根据环境的不同而有不同，因此在实现前要保证对其进行验证。这个可以通过切换当前的分离块到外部的表或者通过使用一个分离策略来保证。 （3） 联合排列的分割区 在数据仓库中，在所有的分离表中提出一致性的分离策略通常是一个很好的方法。如果没有可能连接这些表，SQL Server优化器能够考虑一个可行的计划。这对于连接的实际表是非常相关的，例如有关销售的和有关投资的数据仓库，它们的报表常常是比较销量和股票投资。 （4） 磁盘分配的分离 一旦分离策略确定以后，你需要确定这些分离物将会如何的在磁盘中布局。这里有两种高级别的方法——为一个文件组映射多样的分离物，或者为它们自己的文件组映射单独的分离物。每一种方法的变化也会在此讨论。 一个单独的文件组的策略的最大问题是所有的分离数据将会在磁盘的相同文件中传播。如果在这个文件组中有多个文件，SQL Server使用一个成比例的填充策略来插入数据。这些不可开拓的数据意味着SQL Server连续的检查可能不太有效。这个策略也显示了在基于分离的备份和粉碎的恢复的适应性。 在它们自己的文件组中映射分离区常常是一种更好的策略。每一个文件组应该有一个单独的文件以便对于邻近的数据是可能的。而这些数据是否真正的邻近决定于它是怎样载入的，或者在每一个文件每一个单独策略中使用单独的文件中，集群索引已经经过磁盘碎片整理程序进行了整理。 从索引策略、索引的分片以及快速载入与查询性能的等方面对表的设计进行阐述后后，接下的此篇将讲述怎么样优化数据仓库环境方面的知识。 四．优化数据仓库环境 1．聚合策略 观察那些允许SQL Server直接的、特别的查询的客户通常对临时总结表非常有用处。分析服务怎么样获得性能也不是一件值得惊讶的事情。这个问题在于用户（通常被成为“超级用户”）常常是那些做总结，并且是常常对相同的信息一次一次做总结的人，因此他们没有注意到另外的用户也在做这些操作。然而这对系统资源产生了很大的影响。前聚合在某些地方被存储，或者是在源数据库中，或者是在tempdb中。如果这些信息被重复的存储，磁盘消耗将会是很巨大的。当这些东西在高峰期被构建时，存储需求能够变得非常巨大，因为所有用来支持摘要的详细数据必须读入到内存中。 （1） 设计摘要性的表 一个策略性和高效率的目标是设计和实现永久摘要表的少部分，以便包含前聚合的信息。 下面是设计摘要表的一个实例过程：
（2） 分析服务 那些对SQL Server的分析服务（SSAS）已经习惯的人可能已经注意到在这份白皮书中的很多推荐都是用的SSAS中的默认值。SSAS中的性能的优化直接来自于它的内在的决定对于空间数据的最好的前聚合算法和动态的导航能力。 它的优点如下：
缺点如下：
（3） 设计摘要表的一个过程 设计摘要表并不是一个简单的工作，并且需要从报表和特别的查询所载入的数据的期望和观察到的查询的一个相当大的数量。记住你想要设计尽可能少的摘要表来满足最大数量的查询。 （4） 索引化的视图与摘要表 所有摘要表的维护的自动化是非常有用的。通过使用索引化的视图（SQL Server的特性，表示前聚合的数据）来使得其更大的便利化。这由于索引视图的特征看起来是逻辑性的一步。 这里有一些索引视图必须遵循的一些需求，但是，作为一个摘要实际表的实现来说是比较困难的。下面的需求是与之相关的：
索引视图的一个明显的好处是它们自动的由SQL Server维护。这种好处是非常巨大的，因此索引视图必须明确的被考虑。你能够使用NOEXPAND提示直接的查询索引视图，为了获得索引视图的益处以便进行过程性能改进，需要联合自动的摘要维护的连接。 就像前面所描述的，一个可替换的索引视图是很清楚的摘要表。这些表必须手动的维护并且直接的在查询中使用。前面的问题是一个很大的问题。 为了更好的揭示摘要表的价值，让我们看一个相对简单，但是很现实的查询例子： Avg response time: 1 minute 43 seconds 这个查询直接的引用了详细的实际表。不仅仅使得它运行，而且每一个相关的细节记录被读入到缓冲缓存中，在下面的语句中，查询被重写，为了对我们的摘要表而不是细节进行引用。它改善了87%的查询时间，语句如下： Avg response time: 13 seconds 维护摘要表的花费应该得到小心的控制。ETL进程能够被修改以便能明了更新的日期。这些信息能够被使用来仅仅更新在摘要表中影响的行中的聚合。保证更新的环境被紧紧的控制以便使得摘要表中的数量常常是正确的。它怎么样进行控制取决于ETL过程是如何的被执行。 （5） 改善摘要表的性能 创建的摘要表必须处于一个连接维度表的大部分的情况下。例如，如果详细的实际数据在一个日期维度的天的级别上，摘要的日期可能是周、月等级别。在这个点上，你或者需要为那些级别携带相关的属性，或者对维度连接回去以便获取这些信息。你将常常选择后续的选项，特别是如果这里有一些属性必须的时候。如果这些维度表改变或者也改善了构建和维护的摘要表性能的时候，这些结果将更加便利。如果维度表设计师摘要表的基础，这是用的雪花的方式，这是相当容易的。 （6） 表和报表构造器的总结 我们已经展示了摘要表怎样能够为大部分查询提供强大的便利。写Transact-SQL查询并且能够懂得摘要表的内容，以及怎样使用它们的“超级用户”能够将它们合并到日常的查询工作中来加强性能。典型的商业用户将具有更少的技能集来构建查询代码，并且没有时间或没有意向来学习这项技能。 SQL Server 2005报表服务发布了一个组件来便利特别的查询，它叫做报表构建器，它是为特定类型的用户准备的。为了报表构建器来报告SQL Server相关的数据库，一个或者更多的报表模型必须被定义。一个报表模型能够构建在DSV（data source view）上，并且这两个概念加起来，能够提供工具来插入摘要表到查询中去。 有两种常用的方法来将摘要表合成到报表模型中。第一种方法是为每一个或者大多数摘要表创建一个单独的报表模型，接着为细节化的视图创建一到多个。关键在于报表模型的正确命名以便对那些当创建一个报表的时候需要的用户更加明显。这种方法的最大的问题是：如果用户选择了一个不正确的报表模型，他们将必须重新开始一个新的报表。 另一种方法是将摘要表合并到单个的报表模型中，这些报表模型常常包括细节化的实际的信息。这个将会呈现对于终端用户的数据的多个视图。当在报表模型中使用一个基于摘要的层次时，用户将会自动的接近在摘要表中剩下的维度级别信息。 五. 结论 一个可升级的、高性能的数据仓库能够不顾它下面的数据库的大小来开发。更新和查询者两个操作窗口能够通过如下的可靠的基础来构建：
通过开发一个深思熟虑的数据仓库策略，并且不断的监视你的批量维护和查询环境，你能够传送数据市场，这些数据市场提供了高性能和高实用性以便你的用户能够聚焦于商业智能。 |
| |||
组织简介 | 联系我们 | Copyright 2002 ® UML软件工程组织 京ICP备10020922号 京公海网安备110108001071号 |
