SQLServer性能调优-报表数据库与业务数据库分离

1、SQL Server性能调优报表数据库与业务数据库分离2011-3-3 0來源：网络项目起源在日常的开发过程中.功能总是先于性能被考虑。只有十用户抱怨系统性能时.我们才开始头痛 医头脚痛医脚地來解决这些性能问趣。公司的CRM和ERP系统叫作O】i©完全是我们组开发的。从无到有.功能不断扩展原先只有 CRM模块.后來加入/ ERP模块.Accounting功能和Report功能。近來出现的情况是十某些用户 跑一个大Report时，正在进行业务操作的用户感觉系统响应非常慢。通过对系统的性能监视发现. 在这些时刻，数据库中产生了大虽的锁，同时服务器上出现f CPU和内存资源消耗的尖峰。系统

2、结构性能问題源于系统的整休结构和发展过程。Olile系统的Application是基于NET平台的Web Form 程序，数据库为SQL Server 2005c其主体结构如下图所示：OliteaseOliteRoport其Application端包括两个网站：OliteBase和OliwRepom但连接的都是同一个数据库。Olile的Application 这种结构在起初性能很好，而且提供给用户的Report是实时的业务数据°但随肴提供的业务模块, 特别是Report的増多（Report首先.我们做/存储过程的优化，通过创建Trace捕获性能差的存储过程，并对其进行优化。我 们这么

3、做了一段时间，但获得的收效并不大°我们在优化以往存储过程的同时.随着系统新功能的上线.又有新的存储过程进入需要优化的列表中。其次.修改数据库设计，其中包括修改表结构和优化索引。在系统局部重构表结构与关系对于性 能的提升还是比较明显的，但这样的修改会Application端的大址修改，I：作虽大，风险大.所 以不能大规模实施。对于索引优化又存在矛盾.业务模块(OMeBase)要求数据库中的索引不要太 欽 以支持商效的插入、修改和删除，而报表模块(OliteReport)则希望在数据库中有更多的索引. 以支持高效读。最后.我们还试图提供晚一天的Report服务.來分流主数据库的压力。每天

4、通过把前一天的备份 数据库恢复在另一台服务器上，并在此服务器上提供OIiteReport2站点.给用户提供Report服务。 但用户并不喜欢使用OliteReport2.原因分析下來有3个方面:其一，有时用户确实需要实时的Reportc 其二OliteReport能存储用户的Report条件，而0liteReport?由于每天都会被刷新.无法保留这些 条件。其三，用户更习惯打开原來的Report 接。项目需求上述的备种优化方案都没有根木性的解决系统的性能问题。在这种的背景下我们有了把报表数据 库与业务数据库分离的想法。此项目的需求： 1提岛用户对整个系统性能的感受，Report模块不要彩响到业

5、务模块的运行。 2用户可以和原先一样使用Report模块即不增加新的Report站点。 3.用户可以和原先一样存储填写的Report条件，以供重复使用。 4尽可能提供最小延时的Reporto需求1是这个项目的主要目标.需求2、3. 4是尽可能保证项目所帶來的改变对用户是透明的。方案选择对于原來的系统结构.其Application端已经是两个独立的站点OliieBase和0liteReporto所以只 要把OliteBase和OliteReport的数据库进行分离.在分离后的两数据库间进行数据的同步就行C这 里的关键在于如何进行数据库间的同步。微软提供了很多种数据同步的选择：1 集群:2.Log

6、 Shipping： .Replication： 4.Mirror： 5.1ntegration Senice o微软提供的这些方案中大部分都是用于做数据库的商可用性的.而我们的项目是以商性能为目标 的。为了满足我们自己的需求，应选择那种方案，并做哪些修改呢？1集群这是第一个被我们否决的方案C配a SQL Server数据库集群.对硕件有较女限制，而且配迓相对 其他方案复朵。我们的项目总共的服务辭资源就两台除原先主数据库服务器外.另一台是虚拟机。2Log ShippingLog Shipping把主数据库的日吉传送到从数据库，并在从数据库上进行回放來保证主.从数据库 间数据的一致.从数据库为只

7、读。Log Shipping而且还有配置简讥的持点.开始时是我们的一个候 选方案，但在进一步的实验过程中发现了两个问題。第一、Log Shipping可设置的时间间隔最小収 位为分钟。第二、”I从数据库进行日忐回放时.连接此数据库的连接需要被断开。其中第二个问题 是难以容忍的.这个方案也被洶汰了。3. ReplicationReplication的原理和Log Shipping有些相似但其提供了更零的灵活性。Replication可以只笋主 数据库的一些表、函数或存储过程进行，甚至可以对某些符合条件的记录进行。除此之外.其复制 出來的数据库可写.而且复制的最小时间间隔可配匱为concurren

8、t （测试下來的时间延迟为秒级别）. 而且其配匱也较为简单。经过一些实验，我们最后选择了它。后面会对其原理和配匱进一步讨论。4. MirrorMirror是SQLServer2005提供的强大的商可用性方案。其镜像数据库不能直接读取，这和我们 的需求场景不符合.所以被否了。S.Integration ServiceIntegration Service具有最大的灵活性，其可以为数据仓库进行数据抽取.转换和装载。但使用 Integration Service需要有大址的开发与测试工作.所以我们也没选用。Replication方案细分Replication 方案又可以分为 Snapshot Rep

9、lication. Transactional Replication, Peer-2-Peer Replication. Merge ReplicationSnapshot Replication： 一般用于对于数据库的一次性的完全复制。Transactional Replication：用干主数据库向从数据库的单向复制。Peer-2-Peer Replication：Merge Replication：可以把女个数据库中的数据进行合并后.复制到目标数抿库。对于我们的需求.我们选用了昴单纯的Transactional ReplicationoTransactional Replication

10、 原理在 Transactional Replication 中有 3 个角色：Publisher （发布者,Distributor （分发者）.Subscriber （订阅者。其逻辑图如下:在进行増虽的Transactional Replication之前，Subscriber需要进行初始化，使其包含和Publisher 一样的表结构和初始数据。Transactional Replication 启动之后.Distributor 上的 Log Reader Agent 会将读取 Publisher 的 Log 信息，并分拣出被标识为replication的INSERT, UPDATE. DE

11、LETE语句。此后复制这些Transaction 到 Distributor并写入 distribution 数据库。最后 Distribution Agent 把 Distributor 上的 Transaction 运 送到Subscriber进行重放。注总：在图中Distribution Agent运行在Distributor上.这是在push （推）模式下的情况。可以配 宜为 pull （拉）模式.Distribution Agent 将运行在 Subscriber 上。项目中的配置与考量在前文的系统结构小节.给出J'原先的系统结构。我们希望通过这次的项目得到如下所示的系统结构

12、:OliteBaseOlite ReportOLTPReplicateOliteReport能连接到一个由主数据库复制出的单独数据库上，这样这两个库之间的锁就被隔离C 同时主数据库与从数据库安排在两台服务辭上（项目中我们把复制出的数据库放在了一台虚拟机 上）那么CPU资源与内存资源的消耗也被隔离C需要注总:的是图中OliteReport除了主要的读操 作外.还有少虽的写操作（这是因为用户可以存储Report条件）。我们把这些写指回主数据库.从 数据库在下一时刻的复制中得到这些数据。在Transactional Replication中有三个逻辑角色，而项目中只有两台服务器。我们如何来安排这 三

13、个逻辑角色呢？候选的方案有两种：1 主数据库上配S Publisher和DisUibulor.从数据库上配g Subscriber： 2. 主数据库上只配置Publisher.从数据库上配宜Distributor和Subscribero矛盾的焦点是Distributor放 哪里？需嬰抬出的是Distributor对于Replication非常重婆，这个角色承担着从主数据库抓取 Transaction的匸作.在Push模式下，它还需要负责把Transaction推送到个Subscriber。这些匸作都 会消耗所在服务器的CPU和内存资源。我们的项目希望尽可能保证业务模块的性能.所以找们选用 了方

14、案2,把Dislributor配宜在从数据库上。我们是选用Push模式还是Pull模式呢？ Push和Pull其实是针对Distributor传送Transaction到Subscriber的方式而言的（这点我是很后 面才认识到的.开始一直认为Push或Pull会影响Distributor抓取Publisher上的信息，其实不然。 对于Distributor和Subscriber在一台服务器上，这两种模式的效果基木一样°我们选择f Pull模式. 即Distribution Agent运行在Subscriber端从Distributor拉Transaction数据。这是为了将來扩展考

15、农， 如果以后再加一台服务器來作为Subscriber时，Dislributor不会増加太藝的性能斥力。另一个需要考虑的问题是复制些什么？ TransactionalRephcation可以选择复制哪些表、存储过程或函数等内容。最简笊的是把整个数 据库中的所以元素都进行复制，但这会适成Replication服务所要监视的对彖很勿同时网络上传输 的信息虽也很大。项目中我们最后决定只复制所有的表.这样做是出去性能的考虑。这样做会对将 來的release还有一个需要考虑的是如何进行从数据带的初始化？在Transactional Replication 始之前，首先要对从数据库进行初始化，使其获得与主

16、数据库一 致的表结构和初始数据。在配置Transactional Replication中会有一个选项來进行初始化（由Snapshot Agent完成）。但在我们的实验中初始化耗费几个小时所以我们没有使用Transactional Replication Transactional Replication 有些什么前提条件?数据库的Compatibility level （兼容性等级）需要达到SQL Server 2005（90）（我们使用的是SQL Server 2005.当兼容性级别为80时，配迓过程中会出现异常）。数据库的Recovery model （恢复模式）需要是Full （完整）

17、。所有Replicate的表必须具有主键。（这应该是理所、*1然的.但在这次配宜中竞然发现一 些非常可耻"的东西）存储过程或其他脚木中.不能对进行Replicate的表进行truncate.需把相应存储过程中的语句 改为deletec这是因为Replication是基干对Log的抓取与解析，但truncate不产生Logo如果Replicatemsdn。如何来配置 Transactional Replication?微软提供了非常易用的图形化界面可以进行Replication的配宜。但图形化配宜的灵活性是有限 的，有些配置选项在图形化界面下无法完成°我的建议是先用图形化配S

18、 Replication，并生成相应 的scrip*此后根据需求修改script并用script进行配迓。在我们的项目中也是这么做的。默认的情况下,Distributor 服务器的 D:Program FilesMicrosofl SQLServerMSSQL.lMSSQLrepldata会存放replicate数据。由于我们要支持Pull模式，需要共宇这一文 件夹，并给此文件夹设宜一个具有Full Control权限的域隊户。并把此域账户设置为Subscriber服务 器上SQL Server Agent服务的运行隊户，此服务同时需要被设为Automatic启动方式。在 sp_adddist

19、publisher的working_directory参数设为此共享目录的网络路径。配置 Publisher 时,sp_addpublication 的参数sync.method = N*concurrent repl_freq = Ncontinuous*保证 f Replicate 能尽可能实时：allow_initialize_from_backup = N'tnie*表示通过备份 还原來进行从数据库的初始化c配S Subscriber 时.sp_addsubscription 的参数syncjype = N replication support only*表示从数 据库的初始

20、化完全由外部來完成：subscriplion_type = N'puF表示使用拉模式。后期维护如何监视Replication的性能与异常？微软提供 Replication Moniloro这个匸具还是比较好用的，可以査看到Publication和 Subscription的状态，还能査看到前有藝少Transaction等待传送。 Transactional Replication 设置好后，Distributor 上将自动生成相关的女个 Alerts,如：Replication Warning: Subscription expiration （Threshold: expiration）. Replication Warning: Transactional replication latency （Tlireshold: latency）等。可以将这些Alerts与Database Mail进行绑定。当出现警告时,自动发 出邮件。（此功能虽然在项目中配置但从未正常发出警告邮件，一直不知道为什么.如果有人 知道的话可以联系我）。如何进行以后的Release?原先数据库的Release 一般会分为三部分：1 表结构的变化（包括加/