用SQL Server 2005构建数据仓库（二）

    在上篇中讲述了怎么样创建一个可靠的基础数据仓库，分别从硬件方面、磁盘的配置、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。虽然维度表能够被分离，但是考虑的时候它们通常过小，所以并不适合分离。

进行分离的好处如下：

l         降低数据库维护操作；

l         提高载入的有效性；

l         查询优化器能够消除实际表的很大的一部分分离；

l         使得数据归档更加方便。

（1）      分离的考虑

确定分离要素要考虑的因素如下：

l         知道数据归档的业务需求；

l         确定在分离表上的查询并行化在一个分离的什么级别。

从当前所讲的这些信息看来，让我们考虑分离策略的一些选项。查询模式在不同的环境中变化非常大，因此有些选项应该仅仅只是作为一个输入来识别你的环境的合适的策略。

（2）      分离的载入

你通常会觉得载入实际的数据比从分离表中载入数据更有效率。如果实际表足够大，因而能够保证分离，如果对分离的表执行操作的话，这通常意味着增加的更新将更加快。既然性能根据环境的不同而有不同，因此在实现前要保证对其进行验证。这个可以通过切换当前的分离块到外部的表或者通过使用一个分离策略来保证。

（3）      联合排列的分割区

在数据仓库中，在所有的分离表中提出一致性的分离策略通常是一个很好的方法。如果没有可能连接这些表，SQL Server优化器能够考虑一个可行的计划。这对于连接的实际表是非常相关的，例如有关销售的和有关投资的数据仓库，它们的报表常常是比较销量和股票投资。

（4）      磁盘分配的分离

一旦分离策略确定以后，你需要确定这些分离物将会如何的在磁盘中布局。这里有两种高级别的方法——为一个文件组映射多样的分离物，或者为它们自己的文件组映射单独的分离物。每一种方法的变化也会在此讨论。

一个单独的文件组的策略的最大问题是所有的分离数据将会在磁盘的相同文件中传播。如果在这个文件组中有多个文件，SQL Server使用一个成比例的填充策略来插入数据。这些不可开拓的数据意味着SQL Server连续的检查可能不太有效。这个策略也显示了在基于分离的备份和粉碎的恢复的适应性。

在它们自己的文件组中映射分离区常常是一种更好的策略。每一个文件组应该有一个单独的文件以便对于邻近的数据是可能的。而这些数据是否真正的邻近决定于它是怎样载入的，或者在每一个文件每一个单独策略中使用单独的文件中，集群索引已经经过磁盘碎片整理程序进行了整理。