SQL Server 2008新特性之数据仓库可扩展性

核心提示：1. 导言Microsoft SQL Server 2008提供了一个全面的数据仓库平台，它使得你可以使用一套单独的、整合的产品套件建立和管理你的数据仓库，SQL Server 2008新特性之数据仓库可扩展性，并使你可以为你的用户提供洞察信息，它可以满足最大规模企业的需求，它在空间和时间上的降低是以耗费CPU为代价的

1. 导言

Microsoft SQL Server 2008提供了一个全面的数据仓库平台。它使得你可以使用一套单独的、整合的产品套件建立和管理你的数据仓库，并使你可以为你的用户提供洞察信息。它可以满足最大规模企业的需求，给予你的终端用户和IT员工所需的权利。

在SQL Server 2008版本中部署方面首先要关注的是要改进整个产品套件的可扩展性以充分满足大型企业的需求。这里，我们将介绍我们已经添加的用于改进你的数据仓库体验的特性和改进之处。建立、管理、传送。SQL Server 2008使你很轻松地做到所有这些。

2. 新的数据仓库特性图解

下面的表格显示了SQL Server 2008中新的可扩展特性，以及它们在数据仓库(DW)的哪方面可以提供帮助。

	建立	管理	提供洞察信息
SQL Server关系型数据库管理系统	MERGE语句变化数据捕捉（CDC）最低限度日志记录INSERT	备份压缩	星型关联性能在分区表上更快的并行查询GROUPING SETS
		资源监控器
	数据压缩对齐分区索引视图
集成服务	Lookup性能管道性能
分析服务		备份	MDX查询性能：块计算查询和回写性能
	可扩展的共享数据库
报表服务		报表可扩展性服务器可扩展性

图1:星型关联查询计划，关联降低了有效的数据仓库处理

这篇文章简要地描述了在SQL Server 2008每一个不同组件中的数据仓库改进之处，以及它们怎样帮助你从你的数据仓库获得最大的受益。

3. SQL Server关系型数据库管理系统数据仓库改进之处

SQL Server 2008关系型数据库管理系统与之前的版本相比改进了很多，所以它在你创建、管理和查询大型数据仓库时执行得更为出色。这一章节将详细讲述表1中列出的关系型数据库管理系统数据仓库改进之处。

3.1 星型关联

有了维度模式的数据仓库，你工作的很大一部分就包含了众所周知的星型关联查询。这些查询遵循一个公共模式，它将真实表和一个或多个维表关联起来。此外，星型关联查询通常表达对维表的非主键字段过滤条件，并对真实表的一个字段(叫做measure字段)进行聚合(一般是SUM)。有了SQL Server 2008，你将会体验到许多处理真实表大部分记录的星型关联查询其性能获得了极大的提高。新的技术是基于位图过滤器的，也就是众所周知的Bloom 过滤器。它使得SQL Server 可以在早期查询评估中就除去不具资格的真实表记录从而避免进行进一步的处理。这与SQL Server的竞争产品所使用的处理技术相比节省了大量的CPU时间。你获得的结果可能很多，我们的统计结果是一般情况下，当使用新的星型关联查询处理能力时整个关系型数据仓库查询工作负载的性能提高了15-20%。一些个别的查询速度提高了7倍或更多。

新的星型关联最优化使用了一系列哈希关联，为每一个参与的维表建立一个哈希表。随着这个哈希表的建立，还生成了叫做位图过滤器(bitmap filter)的额外信息。位图过滤器如图1中显示标签为“Join Reduction Info”的框图。这些过滤器被放在事实表的扫描中，有效地将之后会被关联删除的大多数记录移除。这使得之后不必再花费时间拷贝被删除的记录和从它们那里探测哈希表。这个插图显示了在事实表扫描中过滤器的效果。SQL Server 2008查询执行器还可以在执行过程中重新定制位图，将最想选择的放在最先，第二想选择的放在其次，以此类推。这节省了更多CPU时间，因为一旦一个真实表记录对位图检查失败，那么这个记录就会被跳过。

在Microsoft SQL Server 2008企业版中可以使用新的星型关联最优化。在SQL Server中的查询处理器会自动对查询按照星型关联模式应用最优化，在这样的评估查询成本较低的情况下。你不需要对你的应用程序做任何修改以获得这个显著的性能改进。

3.2 分区表并行

你不想从你所拥有的硬件获得最高的性能吗?在SQL Server 2008中的分区表并行(partitioned table parallelism，PTP)特性能够帮助你。数据仓库应用程序一般收集大量的事实表历史数据，这些数据通常按日期分区。在SQL Server 2005中，接触不只一个分区的查询对每个分区使用一个进程(并因此一个处理器内核)。这有时会限制涉及分区表的查询性能，特别是当运行在具有多个处理器内核的并行共享内存多处理器(SMP)计算机的时候。分区表并行通过更好地利用现有硬件的处理器能力，改进了分区表的并行查询计划性能，无论一个查询接触多少分区。这个功能默认执行，不需要手动调整或配置。下图显示了在一个典型数据仓库场景中分区表并行的影响。

图 2: 分区表并行

假设我们有一个真实表，它显示四个分区中按照销售日期组织的销售数据，每一个都包含七天的数据，如图表的上部分所显示。查询Q 是过去七天的销售总和。这个查询取决于它执行的时间可以作用于不同的分区。如查询Q1所示，它接触一个单独的分区P2并被Q2接触，而Q2接触两个分区，因为相关的数据在执行时跨过了P3和P4。

在SQL Server 2005中执行Q1和Q2可能会产生一些意料外的活动。因为有一个可以分派所有线程到一个单独的分区查询上的特殊情况逻辑，Q1的结果是由所有可用线程处理、围绕P3的并行计划(执行没有在图中显示)。但是在Q2的情况下，执行器将每一个单独线程分派到分区P3和P4，即使后台硬件拥有可用的额外线程。因此在8-way计算机上，Q2只利用可用CPU的2/8(25%)，而且很可能比Q1执行得要慢得多。

在SQL Server 2008中执行Q1和Q2会更好地利用可用硬件，因此具有更好的性能和更可预测的动作。在Q1的情况下，执行器再一次分配所有可用的线程来处理P2中的数据(没有显示)。Q2生成一个并行计划，其中执行器以轮流方式指派所有可用线程到P3和P4，它产生的作用在图中New Allocation下面做了显示说明。CPU仍然是完全利用，而Q1和Q2的性能是差不多的。在这种新的线程轮流指派方式下，分区表并行提供的性能提高就变得很明显了，而且更多的处理器内核与受一个查询影响的分区数目是可比的。当一个查询访问的所有数据是在主内存缓冲池中——这对于最近的分区来说是种典型情况，我们在对接触两个分区的查询进行的内部测试中获得了16倍或更快的速度。而实际结果取决于查询、数据组织和硬件配置。

3.3 对齐分区索引视图

对齐分区索引视图使你能够更有效地创建和管理在你关系型数据仓库中的聚合，并能够在以前不能有效使用它们的场合中使用它们，改进了查询性能。在一个典型场景中，你有一个事实表，它是按日期分区的。索引视图(聚合)定义在这个表上，以帮助加快查询。当你转到一个新的表分区时，定义在分区表上的对齐分区索引视图的匹配分区就也转过去了，并且是自动这么做的。

这与SQL Server 2005相比是个显著的提高，在SQL Server 2005中你必须在使用ALTER TABLE SWITCH操作以转入或转出一个分区之前，删除所有定义在一个分区表上的索引视图。SQL Server 2008中的对齐分区索引视图特性使你受益于大型分区表上的索引视图，同时节省了在整个分区表上重建聚合的成本。这些受益包括自动维护聚合，以及索引视图匹配(自动查询重写以利用聚合解决只涉及基础表而不涉及聚合的查询)。

下图显示了在一个分区里转向时聚合怎样随着基础表分区移动。

图 3: 对齐分区索引视图

3.4 GROUPING SETS

GROUPING SETS使你可以编写一个生成多个组并返回一个单独结果集的查询。这个结果集等同于对不同的分组记录进行UNION ALL。使用GROUPING SETS，你可以关注于你的业务所需要的不同级别信息(分组)，而不仅仅是结合几个查询结果的机制。GROUPING SETS通过改进的查询性能使你可以很简单地编写具有多个分组的报表。

在这个简单但很典型的例子里，使用AdventureWorksDW样例数据库，你可能会在制作报表阶段想看看下面的聚合：

◆按季度和国家统计的总销售量

◆所有国家按季节统计的总销售量

◆总销售量

如果没有GROUPING SETS ，那么你要获得这个结果就必须运行多个查询，或者如果你想要一个结果集的话，使用UNION ALL 结合这些查询。有了GROUPING SETS ，你的查询可以使用如下形式：

SELECT D.CalendarYear, D.CalendarQuarter, T.SalesTerritoryCountry
　, SUM(F.SalesAmount) AS SalesAmount
FROM dbo.FactResellerSales F　
　INNER JOIN dbo.DimTime D ON F.OrderDateKey = D.TimeKey
　INNER JOIN dbo.DimSalesTerritory T ON
　 F.SalesTerritoryKey = T.SalesTerritoryKey
WHERE D.CalendarYear IN (2003,2004)
GROUP BY GROUPING SETS (
　　(CalendarYear, CalendarQuarter, SalesTerritoryCountry)
　, (CalendarYear, CalendarQuarter)　
　, () )
ORDER BY D.CalendarYear, D.CalendarQuarter, T.SalesTerritoryCountry

一般情况下，你将这个查询的结果显示为枢轴表类型，如下所示：

表2： 一个GROUPING SETS查询的输出，格式化为枢轴表

随着可能的分组数目的增加，GROUPING SETS 所提供的简洁性和性能优势就越来越大了。

3.5 MERGE

MERGE 语句允许你在一个Transact-SQL语句中对一个表或视图执行多个数据库操纵语言(DML)操作(INSERT、UPDATE和DELETE)。目标表或视图与一个数据源关联起来，这些DML操作执行于这个关联的结果。MERGE 语句有三个WHEN 条件子句，每一个都使你可以对结果集中的一个给定记录执行一个专门的DML动作：

· 对于同时存在于目标表和源表中的每一条记录，WHEN MATCHED 条件子句允许你对目标表中的给定记录执行更新或删除。

· 对于存在于源表中而不存在于目标表中的每一条记录，WHEN [TARGET] NOT MATCHED 条件子句允许你插入一条记录到目标表中。

· 对于存在于目标表中而不存在于源表中的每一条记录，WHEN SOURCE NOT MATCHED 条件子句允许你更新或删除目标表中的给定记录。

你还可以对每一个WHEN条件子句指定一个搜索条件来选择要对记录执行哪种类型的DML操作。MERGE语句的OUTPUT条件子句包括一个新的虚拟字段，叫做$action，你可以使用它来标识执行于每一条记录的DML操作。

在数据仓库环境中，MERGE 语句用来执行对缓慢变化维(SCD)有效的插入和删除操作以及在很多普通场景中维护真实表。MERGE 语句比运行单独的插入、更新和删除语句具有更好的性能特性，因为它只要求传递过来数据。

SQL Server 2008还推出了一个对插入语句的强大扩展功能，它允许插入语句使用嵌套INSERT、UPDATE、DELETE或MERGE 语句的OUTPUT 条件子句返回的记录。

假设你有一个DimBook表(ISBN、Price、IsCurrent)，它跟踪一个书库中每一本书的历史价格记录和当前的价格。价格的改变和添加新书是每周进行的。每星期会生成一个源表WeeklyChanges (ISBN、Price)，这些变更会应用于DimBook 表。每一本新书都会插入一条记录。在这一周改变了价格的现有书籍会以IsCurrent=0进行更新，并且会插入一条新记录以反映这个新价格。下面这个Transact-SQL 语句使用新的MERGE和INSERT功能执行了这些操作。

INSERT INTO DimBook(ISBN, Price, IsCurrent)
　　SELECT ISBN, Price, 1
　　FROM
　　(
　　　　MERGE DimBook as book
　　　　USING WeeklyChanges AS src
　　　　ON (book.ISBN = src.ISBN and book.IsCurrent = 1)
　　　　WHEN MATCHED THEN
　　　　　　UPDATE SET book.IsCurrent = 0
　　　　WHEN NOT MATCHED THEN
　　　　　　INSERT VALUES (src.ISBN, src.Price, 1)
　　　　OUTPUT $action, src.ISBN, src.Price
　　) AS Changes(action, ISBN, Price)
　　WHERE action = 'UPDATE';

3.6 变化数据捕捉

变化数据捕捉(CDC)是SQL Server 2008中推出的一个新的数据跟踪特性。主要是为数据仓库场景设计的，改变数据捕捉提供了一个跟踪和获取对用户表所做的数据改动的有效机制，并使你能够以一种简单使用的关系型格式来访问变更数据。一般情况下，你在一个操作数据库中使用CDC来捕捉变更用于之后转移到你的数据仓库中。在SQL Server中CDC的使用使得不再需要使用插入的方法，例如用户触发器、时间戳字段、以及高昂的查询来确定操作系统中什么发生了改变。

与变化数据一起获得的辅助信息使得CDC可以提供许多问题的答案。例如，这里有一些CDC可以有效提供答案的问题集：

◆我想要所有在12:00 A.M.和12:00 P.M 之间改变了的记录。

◆我想要知道这个改变是插入、更新、还是删除。

◆对于一条更新记录，我想知道哪个(些)字段改变了。

CDC可以极为有用的场景之一是提取、转换和加载(ETL)。随着数据量的增加和由于全局操作使得维护窗口的缩减，优化ETL处理变得尤为重要。变化数据捕捉为你提供了一个非常有用的方法在扩大的基础上提取变化，降低整个ETL处理时间。

下图提供了对变化数据捕捉的组成组件概述。

图 4: 改变数据捕捉

CDC使用一个捕捉工作从SQL Server事务日志中提取变更信息，生成变更表。CDC API使你可以编写一个应用程序用以从变更表中获得信息。你可以在你的ETL包中使用它。CDC清除工作删除了变更表中不再需要的信息。

3.7 最低限度日志记录INSERT

一般情况下，当你往一个数据库中写数据时，你必须将它写到磁盘两次：一次是写到日志，一次是写到它本身数据库上。这是因为数据库系统使用一个undo/redo 日志，所以它可以在需要的情况下回滚或重做事务。但是它只能在某些重要的情况(涉及插入数据到现有的表中，从而加速你的ETL处理速度的情况)下将数据写到磁盘一次。这就是SQL Server 2008中新的最低限度日志记录INSERT特性。

最低限度日志记录包括只记录回滚所不支持实时恢复的事务所需要的信息。最低限度日志记录只在批量日志记录和简单恢复模型情况下可用。当一个最低限度日志记录事务提交时，会发布一个检查点用以将脏数据页面发送到磁盘并截断日志。最低限度日志记录通过提高性能和降低所需日志空间大小，从而极大地改进了大规模INSERT操作。特别是，你必须对目标表使用表锁(TABLOCK)。

在SQL 2005中可以最低限度日志记录的操作包括批量导入操作、SELECT INTO 、以及索引创建和重建。SQL 2008将之扩展到INSERT INTO…SELECT FROM T-SQL 操作，它在满足下列条件之一的情况下插入大量记录到一个已有的表中：

◆插入记录到一个具有集群索引而没有非集群索引的空表

◆插入到一个没有索引但是可以是非空的堆里面

一个使用最低限度日志记录INSERT的主要场景是：你在特定的文件组上创建一个空表，所以你可以控制数据放置的物理位置。然后你使用INSERT INTO…SELECT FROM 来组装它，以一种最低限度日志记录的形式。这将数据放置在你想放置的地方，并且只将它写到磁盘一次。

3.8 数据压缩

在SQL Server 2008中新的数据压缩特性通过以可变长度存储的形式存储固定长度的数据，以及降低冗余数据，从而降低了表、索引或它们分区的子集的大小。能够节省的空间大小取决于schema和数据的分布。基于我们使用大量数据仓库数据库进行的测试，我们得到的统计情况是真实的用户数据库的大小会降低到87%(7比1的压缩比)，但是更多情况下，你可能能降低到50-70%的范围(压缩比大约在2比1到3比1之间)。

SQL Server 提供了两种压缩类型，如下所示：

◆行压缩使得可以以可变长度存储格式来存储固定长度类型。所以举例来说，如果你有一个字段数据类型为BIGINT，它固定格式为占据8个字节的存储空间，压缩之后它使用了可变的字节数——从0到8的字节数。因为字段值是以可变长度来存储的，而在一个记录里每个字段会存储一个额外的4比特长度代码。此外，0和NULL值除了这个4比特代码之外不占任何存储空间。

◆页面压缩是建立于行压缩的基础上的。它存储一次页面上普遍使用的字节格式，然后将这些值引用给各自的字段，通过这种方法将冗余数据的存储降低到最小。字节格式标识是不受类型约束的。在页面压缩中，SQL Server使用两种技术优化页面使用的空间。

第一个技术是字段。在这种情景下，系统寻找一个公共字节格式作为页面上记录的一个特定字段所有值的一个前缀。表或索引的所有字段都重复这个过程。计算得来的这个字段前缀值作为一个锚记录存储，数据或索引记录将这个锚记录作为公共前缀参考，如果可能的话，每一个字段都这么做。

第二个技术是页面级字典。这个字典存储字段和行的公共值，并存储在一个字典中。然后字段会被修改以引用字典入口。

压缩伴随着额外的CPU成本。这是在你对压缩数据进行查询或执行DML操作时被耗费的。行压缩耗费的相关CPU成本低于页面压缩，但是页面压缩可以提供更好的压缩。因为有很多种工作负载和数据格式，所以SQL Server 将压缩粒度定为分区级别。你可以选择压缩整个表或索引或分区子集。例如，在一个数据仓库工作负载中，如果CPU是你的工作负载的主要成本，但是你想节省一些磁盘空间，那么你可能希望在不常被访问到的分区上使用页面压缩，而不压缩经常被访问和操纵的当前分区(一个或多个)。这降低了总的CPU成本，而所需的磁盘空间稍稍多了一些。如果I/O成本是你的工作负载的主要成本，或者你需要降低磁盘空间成本，那么使用页面压缩来压缩所有的数据可能是最好的选择。如果压缩使得你经常接触页面的工作集缓存在主要内存缓冲池中，那它可以将速度提高好几倍，而如果它是放在内存中的话就不会这样了。对一个用来测试SQL Server 2008的大型内部数据仓库查询性能基准的初步性能测试结果显示节省了58%的磁盘空间、平均降低了15%的查询运行时间、以及CPU成本平均提高了20%。而一些查询速度提高了七倍。你的结果取决于你的工作负载、数据库和硬件。

压缩数据的命令是在CREATE/ALTER DDL语句中作为选项提供的，并且支持ONLINE和OFFLINE 模式。此外，还提供了一个存储过程用来帮助你在实际压缩前估计能够节省的空间。

3.9 备份压缩

备份压缩帮助你以多种方式节省空间。

通过降低你的SQL备份的大小，你的SQL备份可以节省很多磁盘媒质空间。所有的压缩结果依赖于进行压缩的数据本身，压缩到50%不是很少见的，也有可能压缩到更小。这使得你可以使用较少的存储以保持你的备份在线，或者使用相同的存储保持更多的备份版本在线。

备份压缩还帮助你节省了时间。传统的SQL备份几乎完全是受I/O性能的限制。通过降低备份过程的I/O负载，我们实际上加快了备份和恢复的速度。

当然，没有什么是完全免费的，它在空间和时间上的降低是以耗费CPU为代价的。好消息是I/O时间的节省弥补了CPU时间的增加，而且你可以利用资源监控器控制你的备份以工作负载为代价使用多少CPU。

SQL Server 2008新特性之数据仓库可扩展性(2)