数据仓库优化.docx
- 文档编号:29030324
- 上传时间:2023-07-20
- 格式:DOCX
- 页数:15
- 大小:25.21KB
数据仓库优化.docx
《数据仓库优化.docx》由会员分享,可在线阅读,更多相关《数据仓库优化.docx(15页珍藏版)》请在冰豆网上搜索。
数据仓库优化
数据仓库优化
软件测试中数据仓库的RDBMS性能优化指南
入软件测试论坛讨论
磁盘I/O并行度
为了改善存储在多个磁盘驱动器上的大型SQLServer数据库的性能,一个有效的方法是创建磁盘I/O并行机制,该机制同时对多个磁盘驱动器进行读写操作。
RAID通过硬件和软件实现磁盘I/O并行度。
下一个主题讨论使用分区来组织SQLServer数据以进一步增加磁盘I/O并行度。
使用分区来提高性能
对于存储在多个磁盘驱动器上的SQLServer数据库,可通过对数据进行分区以增加磁盘I/O并行度来改善性能。
可使用多种方法来进行分区。
分区的创建和管理方法包括配置存储子系统(磁盘、RAID分区)和在SQLServer中应用各种数据配置机制(例如,文件、文件组、表和视图)。
虽然本节重点介绍一些与性能相关的分区功能,但是第18章“在SQLServer2000数据仓库中使用分区”也特别介绍了分区主题。
创建磁盘I/O并行度的最简单方法是,使用硬件分区并创建一个为所有的SQLServer数据库文件(事务日志文件除外,它们总是应当存储在从物理上分开且仅专用于日志文件的磁盘驱动器上)提供服务的驱动器池。
驱动器池可以是一个RAID阵列,它在Window中呈现为一个物理驱动器。
可以使用多个RAID阵列和SQL
数据仓库的优化
Server文件/文件组来设置较大的池。
可以将一个SQLServer文件与每个RAID阵列相关联,并将这些文件组合成一个SQLServer文件组。
然后,可基于该文件组构建一个数据库,以便将数据均匀地分布到所有的驱动器和RAID控制器上。
驱动器池方法依赖RAID在所有的物理驱动器之间划分数据,这样有助于确保在数据库服务器操作过程中对该数据进行并行访问。
该驱动器池方法简化了SQLServerI/O的性能优化,因为数据库管理员知道只有一个物理位置可供创建数据库对象。
可监视单个驱动器池的磁盘队列情况,必要时可向该池中添加更多的硬盘驱动器以防出现磁盘排队现象。
一般情况下,无法确定数据库哪些部分的利用率最高,此时使用该方法有助于优化性能。
最好不要只是因为SQLServer可能要用5%的时间来对另一磁盘分区进行I/O而将总体可用I/O能力的一部分隔离到该磁盘分区上。
“单个驱动器池”方法有助于使所有可用的I/O能力对于SQLServer操作“始终”可用。
它还允许I/O操作分布到最大数量的可用磁盘上。
SQLServer日志文件始终都应该从物理上分散到不同的硬盘驱动器,与所有其他SQLServer数据库文件分开。
对于管理多个繁忙数据库的极其繁忙的SQLServer来说,每个数据库的事务日志文件应当在物理上互相分离,以减少争用现象。
由于事务日志记录主要是顺序写入I/O,所以将日志文件分开往往会显著提高I/O性能。
包含日志文件的磁盘驱动器可以非常高效地执行这些顺序写入操作,但前提是这些操作不被其他I/O请求中断。
有时,将需要在SQLServer操作(例如,复制、回滚和延迟更新)过程中读取事务日志。
有些实现通过将新数据几乎实时地装载到数据仓库中,将复制用作其数据转换实用工具的前端。
参与复制的SQLServer
数据仓库的优化
的管理员需要确保所有用于事务日志文件的磁盘都有足够的I/O处理能力,以便处理除正常日志事务写入之外需要发生的读取操作。
物理上分割的文件和文件组需要额外的管理工作。
事实证明,为了隔离和改善对非常活动的表或索引的访问而进行分割时,这些额外的工作是值得的。
下面列出了一些益处:
对于特定对象的I/O需求,可以进行更准确的评估,而如果所有数据库对象都放在一个大驱动器池中,进行这种评估就不那么容易了。
使用文件和文件组对数据和索引进行分区,可以增强管理员创建粒度更细的
备份和恢复策略的能力。
文件和文件组可用于维护数据在磁盘上的顺序放置,从而减少或消除非顺序
的I/O活动。
如果数据装载到数据仓库的可用时间窗口要求并行执行处理以满足最终期限,则该功能就变得尤其重要。
在数据库开发和基准检验阶段,可能适于对文件和文件组进行物理分割,这
样可收集数据库I/O信息并将其应用于生产数据库服务器环境的容量计划。
有关对象分区的注意事项
可以在不同的硬盘驱动器、RAID控制器和PCI通道(或者三者的组合)之间分隔以下方面的SQLServer活动:
事务日志tempdb数据库表
数据仓库的优化
非聚集索引注意在SQLServer2000中,Microoft增强了分布式分区视图,使用这种视图可以创建联合数据库(通常称作扩展),这种数据库会将资源负荷和I/O活动分布到多个服务器上。
联合数据库适于某些高端联机分析处理(OLTP)应用程序,但是建议不要使用该方法来解决数据仓库的需求。
使用硬件RAID控制器、RAID热插拔驱动器和联机RAID扩展功能可以轻松实现对SQLServerI/O活动的物理分割。
最灵活的方法是排列RAID控制器,让单独的RAID通道与上述不同活动方面相关联。
同样,应当将每个RAID通道连接到一个单独的RAID热插拔机柜,以便充分利用联机RAID扩展功能(如果可通过RAID控制器使用该功能)。
随后,Window逻辑驱动器盘符将会与每个RAID阵列相关联,并且SQLServer文件会基于已知的I/O使用模式在不同的RAID阵列之间被分隔开。
使用这种配置,有可能将与每个活动相关联的磁盘队列重新与一个不同的RAID通道及其驱动器机柜相关联。
如果某个RAID控制器及其驱动器阵列机柜均支持联机RAID扩展功能,而且机柜中有热插拔硬盘驱动器的插槽,则只需向RAID阵列中添加更多的驱动器,直到系统监视器报告该RAID阵列的磁盘队列已经达到可接受的程度(对于SQLServer文件最好少于两个),即可解决该RAID阵列的磁盘队列问题。
这可以在SQLServer联机时完成。
分离事务日志
维护事务日志文件的存储设备应该在物理上与数据文件所在的设备分开。
根据您的数据库恢复模型设置不同,大多数更新活动既产生数据设备活动又产生日志活动。
数据仓库的优化
如果将这两个活动设置为共享同一个设备,则要执行的操作将争用同一个有限资源。
大多数安装都受益于将这些竞争I/O活动分开。
分离tempdb
SQLServer会在每个服务器实例上创建一个名为tempdb的数据库,以供服务器用作各种不同活动的共享工作区,这些活动包括:
临时表、排序、处理子查询、生成聚合以支持GROUPBY或ORDERBY子句、使用DISTINCT的查询(必须创建临时工作表才能删除重复行)、游标,以及哈希联接。
通过将tempdb分割到其自己的RAID通道上,我们使tempdbI/O操作能够与它们的相关事务的I/O操作并行发生。
由于tempdb实际上是一个草稿区域,而且更新频繁,所以RAID5对于tempdb并不是好的选择,而RAID1或0+1提供的性能更好。
虽然Raid0不提供容错功能,但可以考虑将它用于tempdb,因为每次重新启动数据库服务器时都会重新生成tempdb。
RAID0使用最少的物理驱动器为tempdb带来了最佳的RAID性能,但在生产环境中将RAID0用于tempdb时主要的顾虑是:
如果有物理驱动器(包括用于tempdb的驱动器)出现故障,就可能影响到SQLServer的可用性。
如果将tempdb放在具备容错能力的RAID配置上,就可以避免这一点。
要移动tempdb数据库,请使用ALTERDATABASE命令更改与tempdb相关联的SQLServer逻辑文件名的物理文件位置。
例如,要将tempdb以及与之相关联的日志移到新文件位置E:
\mql7和C:
\temp,请使用以下命令:
alterdatabaetempdbmodifyfile(name='tempdev',filename=
'e:
\mql7\tempnew_location.mDF')
数据仓库的优化
alterdatabaetempdbmodifyfile(name='templog',filename=
'c:
\temp\tempnew_loglocation.mDF')
与用户数据库相比,mater数据库mdb和model数据库在生产过程中很少使用,因此,在考虑优化I/O性能时,通常不必考虑它们。
mater数据库通常只用于添加新登录、数据库、设备和其他系统对象。
数据库分区
可以使用文件和/或文件组对数据库进行分区。
文件组只是为管理目的而将多个单独的文件组合在一起的命名集合。
一个文件不能是多个文件组的成员。
表、索引、te某t、nte某t和image数据都可以与一个特定的文件组相关联。
这就是说,它们所有的页都是从该文件组中的文件中分配而来的。
下面介绍三种类型的文件组。
主文件组
该文件组包含主数据文件以及未放到另一个文件组中的所有其他文件。
系统表的所有页都是从主文件组分配的。
用户定义的文件组
该文件组是使用CREATEDATABASE或ALTERDATABASEfilegroup语句中的
FILEGROUP关键字或者在SQLServer企业管理器中的属性对话框上指定的任何文件组。
默认文件组
数据仓库的优化
默认文件组包含在创建时未指定文件组的所有表和索引的页。
在每个数据库中,每次只能有一个文件组是默认文件组。
如果未指定默认文件组,则主文件组就是默认文件组。
文件和文件组对于控制数据和索引的位置以及消除设备争用现象很有用。
有相当一部分安装还将文件和文件组用作一种比数据库粒度更细的机制,以便对它们的数据库备份/恢复策略进行更多的控制。
水平分区(表)
水平分区将一个表分割成多个表,每个表都包含相同的列数,但是行数会减少。
怎样对表进行水平分区要根据分析数据的方式而定。
根据一般经验,在对表进行分区时,应当使查询引用的表尽可能少。
否则,用于在查询时按逻辑合并表的UNION查询就会过多,从而会影响性能。
例如,假定企业要求规定:
我们要将十年来不断滚动的事务数据存储到我们数据仓库的中央事实表中。
我们公司十年来的事务数据意味着数据会超过十亿行。
数量达到十亿的任何内容管理起来都会很困难。
现在,请考虑每年我们都必须除去第十年的数据,然后装载最新一年的数据。
管理员通常采用的方法是:
创建十个独立但结构相同的表,每个表中存放一年的数据。
然后,管理员在这十个表的基础上定义一个联合视图,以便让最终用户看到所有数据都放在一个表中。
实际上并非如此。
针对该视图执行的任何查询都被优化成只搜索指定的年份(和相应的表)。
不过,管理员确实获得了管理能力。
现在,管理员能够以粒度方式单独管理每年的数据。
每年的数据都可以单独装载、索引或维
数据仓库的优化
护。
添加新年份就是这样简单:
除去该视图,除去包含第十年数据的表,装载和索引新年份的数据,然后重新定义新视图以包括新年份的数据。
当您在多个表或多个服务器之间对数据进行分区时,只访问部分数据的查询运行得更快,因为要扫描的数据比较少。
如果这些表位于不同的服务器上,或者在一台具有多个处理器的计算机上,还可以并行扫描查询所涉及的每个表,从而改善查询性能。
另外,维护任务(例如,重建索引或备份表)的执行速度会更快。
通过使用分区视图,数据仍显示为一个表,而且在查询数据时可以不必手动引用相应的基础表。
如果满足下列任一条件,分区视图就可以进行更新。
有关分区视图及其限制的详细信息,请参阅“SQLServer联机丛书”。
在该视图上用可支持INSERT、UPDATE和DELETE语句的逻辑定义了
INSTEADOF触发器。
该视图以及INSERT、UPDATE和DELETE语句遵循为可更新的分区视图定义
的规则。
分离非聚集索引
索引驻留在B型树结构中,通过使用ALTERDATABASE命令来设置一个不同的文件组,这些索引可以与它们的相关数据库表分开(聚集索引除外)。
在下面的示例中,第一个ALTERDATABASE创建一个文件组。
第二个ALTERDATABASE向新创建的文件组中添加一个文件。
alterdatabaetetdbaddfilegrouptetgroup1
alterdatabaetetdbaddfile(name='tetfile',
filename='e:
\mql7\tet1.ndf')tofilegrouptetgroup1
数据仓库的优化
在创建了文件组及其关联的文件之后,可以在创建索引时指定该文件组,从而使用该文件组来存储索引。
createtabletet1(col1char(8))
createinde某inde某1ontet1(col1)ontetgroup1
SP_HELPFILE会将有关给定数据库中文件和文件组的信息反馈回来。
SP_HELP<表名>的输出结果中有一节,该节提供有关表的索引及其文件组关系的信息。
p_helpfile
p_helptet1
并行数据检索
SQLServer在具有多个处理器的计算机上运行时可以并行扫描数据。
如果一个表在包含多个文件的文件组中,则可以对该表执行多个并行扫描。
只要按顺序访问某个表,就会创建一个独立线程来并行读取每个文件。
例如,如果完全扫描在包含四个文件的文件组上创建的表,将会使用四个独立线程来并行读取数据。
因此,为每个文件组创建多个文件会有助于提高性能,因为这样会使用独立的线程来并行扫描每个文件。
同样,当某个查询联接着不同文件组上的表时,可以并行读取每个表,从而改进查询性能。
另外,表中的任何te某t、nte某t或image列都可以在除基表所在文件组以外的文件组上创建。
最终,文件过多会导致并行线程过多,进而导致磁盘I/O子系统中出现瓶颈,这时就会达到饱和点。
通过使用系统监视器来监视PhyicalDik对象和磁盘队列长度
数据仓库的优化
计数器,可以确定这些瓶颈。
如果磁盘队列长度计数器大于3,请考虑减少文件数量。
为了通过使用多个文件并行访问数据来提高吞吐量,将尽可能多的数据分布到尽可能多的物理驱动器上是很有益处的。
要将数据均匀地分布到所有磁盘上,请首先设置基于硬件的磁盘条带化,然后根据需要使用文件组将数据分布到多个硬件条带集上。
并行查询建议
SQLServer可自动以并行方式执行查询。
这样就会对在多处理器计算机上执行查询进行优化。
工作会细分为多个线程(受线程和内存的可用性影响),而不是一个查询用一个操作系统线程执行,这样,完成复杂查询时就会速度更快,效率更高。
SQLServer中的优化器会为查询生成计划并确定将在何时并行执行查询。
确定时会依据以下条件:
计算机是否有多个处理器?
是否有足够的内存来并行执行查询?
服务器上的CPU负荷是多少?
正在运行哪种类型的查询?
如果允许SQLServer以并行方式运行并行操作(例如DBCC和创建索引),对服务器资源的压力就会变重,而且在执行繁重的并行操作任务时,您可能会看到警告信息。
如果服务器错误日志中经常出现有关资源不足的警告消息,请考虑使用系统监视器来调查哪些资源(例如,内存、CPU使用率和I/O使用率)可用。
数据仓库的优化
当服务器上有活动用户时,请不要并行运行大量查询。
请尝试在没有负载的时间段中执行维护作业(例如,DBCC和创建索引)。
这些作业可以并行执行。
监视磁盘I/O性能。
观察系统监视器(在WindowNT4.0中为性能监视器)中的磁盘队列长度,确定是升级硬盘还是将数据库重新分布到不同的磁盘上。
如果CPU的使用率非常高,请升级或添加更多的处理器。
下列服务器配置选项可能会影响查询的并行执行:
并行度的成本阈值最大并行度最大工作线程查询调控器成本限制
优化数据负荷在加速数据装载活动时,一定要记住多种提示和方法。
根据您执行的是初始数据装载还是增量数据装载,这些方法可能会有所不同。
一般来说,增量装载更复杂且限制性更强。
您选择的方法可能还基于您无法控制的因素。
处理窗口要求、所选存储配置、服务器硬件的限制等都会影响可供您使用的选项。
在执行初始数据装载和增量数据装载时,有一些共同的要点需要记住。
下面将详细讨论以下主题:
选择适当的数据库恢复模型使用bcp、BULKINSERT或大容量复制API控制锁定行为并行装载数据
数据仓库的优化
o
o
o杂项,其中包括:
绕过引用完整性检查(约束和触发器)装载预先排序的数据删除索引带来的影响选择适当的数据库恢复模型
我们已在“影响性能的配置选项”一节中讨论了数据库恢复模型。
一定要记住所选恢复模型对执行数据装载所需的时间可能会有很大的影响。
这些恢复模型主要控制将写出到事务日志中的数据量。
因为对事务日志执行写入操作基本上会使工作负荷加倍,所以这非常重要。
日志记录和最小日志记录大容量复制操作
在使用完全恢复模型时,由某个大容量数据装载机制(将在下面讨论)执行的所有插入行操作都记录到事务日志中。
对于大型数据装载,这可能会导致快速填充事务日志。
为了帮助防止事务日志的空间不足,可执行最小日志记录大容量复制操作。
是以日志记录还是以无日志记录形式执行大容量复制不作为大容量复制操作的一部分来指定;它取决于大容量复制中涉及到的数据库和表的状态。
如果符合以下所有的条件,将进行无日志记录的大容量复制:
恢复模型是“简单”或“大容量日志记录的”,或者数据库选项elect
into/bulkcopy设置为真。
目标表未在进行复制。
目标表没有索引,或者如果目标表有索引,在开始大容量复制时它也是空的。
数据仓库的优化
TABLOCK提示是在将eOption设置为BCPHINTS的情况下使用
bcp_control指定的。
任何不满足上述条件的向SQLServer实例中进行的大容量复制将完全记录下来。
在执行初始数据装载时,应当总是在“大容量日志记录的”或“简单”恢复模型下运行。
对于增量数据装载,只要数据丢失的可能性很低,就考虑使用“大容量日志记录的”模型。
因为许多数据仓库基本上都是只读的或者事务活动的数量很少,所以将数据库恢复模型设置为“大容量日志记录的”不会产生任何问题。
使用bcp、BULKINSERT或大容量复制API
SQLServer内部存在两个机制,用来解决大容量移动数据的需求。
第一个机制是bcp实用工具。
第二个机制是BULKINSERT语句。
bcp是一个命令提示符实用工具,它既将数据复制到SQLServer中又从其中复制数据。
在SQLServer2000中,bcp实用工具是用ODBC大容量复制应用程序编程接口(API)重新编写的。
bcp实用工具的早期版本是使用DB-Library大容量复制API编写的。
BULKINSERT是SQLServer附带的Tranact-SQL语句,该语句可从数据库环境内执行。
与bcp不同的是,BULKINSERT只能将数据拉入SQLServer中。
它不能将数据推出。
使用BULKINSERT的一个好处在于,它可以使用Tranact-SQL语句将数据复制到SQLServer的实例中,而不必退出解释器转到命令提示符中。
第三个选项是大容量复制API,程序员通常对该选项很感兴趣。
有了这些API,程序员就能够使用ODBC、OLEDB、SQL-DMO或者甚至基于DB库的应用程序将数据移入或移出SQLServer。
数据仓库的优化
所有这些选项都使您能够对批处理大小进行控制。
除非您使用的是小容量数据,否则最好习惯于指定批处理大小以进行恢复。
如果未指定批处理大小,则SQLServer将所有要装载的行作为一批提交。
例如,您尝试将1,000,000行新数据装载到某个表中。
服务器在处理完第999,999行后突然断电。
当服务器恢复时,将需要从数据库中回滚处理完的999,999行,然后再尝试重新装载数据。
您可以通过将批处理大小指定为10,000来大大节省自己的恢复时间,这是由于您已经将1到990,000行提交到数据库中,因此将只需回滚9,999行(而不是999,999行)。
同样,如果未指定批处理大小,则将必须从第1行重新启动装载处理才能重新装载数据。
如果将批处理大小指定为10,000行,则只需从第990,001行重新启动装载处理,这样就高效地绕过了已经提交的990,000行。
控制锁定行为
bcp实用工具和BULKINSERT语句接受TABLOCK提示,该提示允许用户指定要使用的锁定行为。
TABLOCK指定在大容量复制操作过程中将采用大容量更新表级锁。
使用TABLOCK可以减少表上对锁的争用,从而改进大容量复制操作的性能。
当针对单个表处理并行装载时,该设置有非常重要的含义(将在下一节讨论)。
例如,要将Author.t某t数据文件中的数据大容量复制到pub数据库中的author2表中,请指定表级锁,并从以下命令行提示符执行:
bcppub..author2inauthor.t某t-c-t,-Servername-Ua-Ppaword-h"TABLOCK"
数据仓库的优化
或者,您可以从查询工具(如SQL查询分析器),使用BULKINSERT语句来大容量复制数据,如下例所示:
BULKINSERTpub..author2FROM'c:
\author.t某t'
WITH(
DATAFILETYPE='char',
FIELDTERMINATOR=',',
TABLOCK
)
如果未指定TABLOCK,除非对于表将tablelockonbulkload选项设置为on,否则默认锁定会使用行级锁。
将tablelockonbulkload选项与p_tableoption命令一起使用,也可以设置大容量装载操作过程中表的锁定行为。
注意如果指定了TABLOCK提示,则在大容量装载过程中,它将替代使用p_tableoption声明的设置。
并行装载数据
并行装载—非分区表
使用SQLServer中的任一大容量数据装载机制,都可以将数据并行装载到一个非分区表中。
这是通过同时运行多个数据装载来完成的。
在开始装载之前,需要将要
数据仓库的优化
并行装载的数据拆分成多个独立文件(大容量插入API的数据源)。
然后,可同时启动所有的独立装载操作,以便并行装载数据。
例如,假设您需要为在全球四个地区运作的服务公司装载合并的数据库,每个地区每个月都报告寄给客户的帐单上的报告时间(小时)。
对于大型服务组织,这可能表示需要合并大量事务数据。
如果这四个报告地区都分别提供独立文件,则可以使用上面介绍的方法将这四个文件同时装载到一个表中。
注意并行处理的并行线程(装载)的数量不应超过SQLServer的可用处理器数。
下面的插图说明了对非分区表进行的并行装载。
如果您的浏览器不支持嵌入式框架,请单击此处在单独的页中查看。
并行装载:
水平分区(表)
本节重点介绍如何使用水平分区表来提高数据装载的速度。
在上一节中,我们讨论了将数据从多个文件装载到一个(非分区)表中。
如果对表进行水平分区,则可以减少设备争用现象,从而有机会改善数据的连续性并加速装载过程。
虽然上图显示的是数据装载到了表的不同部分中,但这样的表述可能不准确。
如果上述装载中的
数据仓库的优化
所有三个线程是同时处理的,为该表提取的扩展盘区最后就可能会是混合状态。
在数据混合后,可能导致在检索数据时无法实现最佳性能。
这是由于数据不是按物理上连续的顺序存储的,从而可能导致系统使用不连续的I/O访问它。
在该表基础上生成聚集索引将解决上述问题,因为数据会按连续顺序被读入、按键顺序排序,并被回写。
但是,读取、排序、删除旧数据以及将新排序的数据回写可能是一项非常耗时的任务(请参阅下面的装载预先排序的数据)。
为避免出现这种混合的情形,请考虑使用文件组在可以存储大表的位置保留多块连续空间。
许多安装还使用文件组将索引数据与表数据分开。
为便于阐述,假定有一个数据仓库分配在一个大型物理分区上。
任何对该数据库并行执行的装载操作都有可能导致以非连续(混合)状态存储受影响的数据/索引页。
将执行哪种操作?
任何对数据进行修改的操作都将导致数据变得不连续。
为了满足处理窗口的要求,用户可能会尝试并行执行初始数据装载、增量数据装载、索引创建、索引维
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据仓库 优化