SQL Server性能调优-报表数据库与业务数据库分离.docx

SQ1.Server性能调优报表数据库与业务数据库分别2011330来源IM络9Bisa在H常的开发过程中,功能总是先于性能被考电，R有当用户理恐熏统性能时,我们才起先头癌医头.押浦悌脚地来解决这些性能问题.公司的CRMfilERPBflOlitc,完全是我们如开发的从无到有，功能不断犷展，M先只有CRMHJWt.后来加入了ERP校块.ACewHi变功能和RCPOrl功能.近来出现的状猊是当某些用户跑一个大RePOrt时,正在进行业务操作的用户腐觉系统响应将别慢.通过对系统的性能监视发觉.在这些时刻.数据库中产生了大果的锁，同时取务器上出现了CPU和内存费源消耗的尖稣，件能向5SiSI系统的郎体姑构和发展过祝.Oliie系统的APPilcli31IJE.NEr平台的McbFsm程序,数据除为SQ1.SerVrr2005.其上体玷构如下图所示：共APPIicMkm州包括何个M站：OIiicBascfiOliicRcport,但连接的祢足网个数抠原.OHlC的APPliCUliOn湘其实很薄，而把大同的业逻用包装在存砧过程中，放在效相阵域来运行，这种结构在过初性能很好,而此供应应用户的Report是实时的业务数据.怛的希供应的业务模块,将烁是RCpcC的增多（Rcpon时应的存储过程连推的表乡,计Wlrt大，输出的结果第大）.效揖咋就成为了瓶知.首先,我们做了存储过科的优化.通过创建TraCV捕注性族龙的存储过程.并前非进行优化.我们这么做r段时间但获得的收效并不大我们在优化以往存储过程的同时两花系统新功能的上线,乂行新的存彷过肝进入须要优化的列衣中.其次,他改£据库设计,我中包括然改我结构和优化索引.在系统局罂圾构表姑构与关系对于性能的提升还是比较即显的,但这样的外改会造成Application罐的大显磁通,Ifl*,<l.所以不能大规横实施.对于索引优化又存在冲臾.业务校块(OliteBaM!)要求数鼎陈中的点里不要太多，以支井高效的插入、修改和用除,而报表模块(OlileRCPon，则布史在数据库中有更多的索引，以支持高效谀.m.我们还试图供应晚一天的RePOn职务.未分流上数据宙的压力,每天通过把时一天的备份数据库夏像在另一台胆务懦匕并在此故分俎上供应OIiICRePon2站点,好用户供应RCPOnttI务.似川户并不喜发运用OIitCRCan2.缥山分析下震在3个方面：其、行时用户的阿次要实时的Report.K二.0IikRew”谎存儡用户的RcKm条件.而OliIeReP»心由F好大都会被Hl新.无法像留这名条件,K.用户更习惯打开朦来的RCPOC柢接.目求上述的各种优化方案：没有根本性的解决系统的性税何题.在这种的背景下我们有了杷报表也也.库与业务数据库分别的也法“此事目的求， 1.提Ift用户对整个系统性能的感受，RePCn模块不要影响到业务模块的运行. 2.M户可以和原先一样运用Rcf>11模块.即不掳加新的Rcpon咕点， 3.用户可以和原先样存他埴”的RePon条件,以供币史运用. 4.尽可能供应最小延时的RqX)«.需求1是这个顼H的主要H标.富求2、3.4是尽可能保证Jfi日所带架的变更时用户是造亮的，方案途鼻可J原亲的系统结构,我AmiGMQn端已经是两个独立的站点OlikBaSe却OIiIeRePOH.所以只要把OIitCB由和QIitCRePOn的数据席逆行分别,在分别后的两数据库何道行数据的同步就行了，这里的关位在于如何进行««库间的同步.一找供应了许多种数据同步的选抨：1.系群:Z1.ogShipping:3.Replication；4.Mirror：5lntcj11ti<>nService.惫软供应的这些方案中大部分都是用于做数据岸的施可用性的,而我们的项目是以i性能为H标的.为了港童我们自己的需求,应选挣那种方案,并做出些悔改爬？i.×9t立是外一个被我们推翻的方窠.配贸SQ1.SerVCr数幅库蛆群，对硬件有校与限制,而H配?I相对其他方案困推.我们的项Il总共的服务器费做过用分.除欣先十数MM:服务拓外,另fWH.X1.ogShipping1.OgShiPPing把主i据库的H苕1送到从数州【也并在从数据库上进行何放未供证生从数幅库间数据的样,从*找作为只it.1.ogShpi咤而H还有归竹而油的特点.起先时是我们的个候送方案.但在进一步的试验过程中发觉了两个何8.第一、1.oSShiPPng可设置的时间间隔最小单位为分他,其次、当从政品件进行日志同放的，煌接此数推陈的连接很妥被断开.其中其次个何圆是难以再忍的.这个方案也被泡法了.ReplicationR叩IitiUig的IS理和【偎ShiPPinjJitHS,但其供血了更多的攸捷性，.RFiCatinn可以只多土数据堆的T衣、函数或存储过程进行，强至可以对某些符合条件的记票进行.除此之外丸立利出来的数据库可写,而旦复制的最小时间间隔可配取为COneITCm<测试FjR的时何证迟为秒嫁别），而且其间就也核为简洁.经过一些试验.我们最终选样J它.后面会对其朦理和周世进一步探讨.4.Mir11>rMirrorJ1.SQ1.Sencr2005供虎的强大的足可用性方案,其Ift像数据库不能ItiiB<.这和我打的需求场景不符合,所以被舍了.SJntegratIOnSerticeIntegnniOnService具仃足火的敏捷性,共可以为数据化库进行数据抽取,转换和装领.（H.iflInicgrabonSenice须要fi大城的开发9测试工作,所以我们也没选用.Replication方煞分RCPliCMion方案又可以分为SnapshnlReplication.Trn11*urti<>11i1Rcplicatinn.Pecr-2-PcerReplication.MergeRePIiEk>n.SnapshotRcplicaiiontKIj：时于数妪感的一次性的完全乂凡TranWCiEUlRerlid沏：用于土数据昨向从数据蟀的通向复制.Jer-2-lerRePiIC"ion：解进行二个或多个数鼎年之间的相互北制.即从数据库也能向主数据球Uj.这个功能很强大，但可能会引起冲突，须要特殊关注保证各库的数幅完整性，Mc115eRcp辰;“沁n：可以把多个敢粼呼中的数据进行合并后，复制到目标数据库.对下我们的需求,我们选用j½Hr/TnmxauioialReplication.TrunsuctionulRepIicatiiMiMS在TtansactionalRcplicaiion中行3个角（IrPublisher（发布者）.Distributor（分发有）.Subscriber（iTftJ）.成花辑图图下：4泌行增M的TnInsaCl沁IUlRcplkuiionZfi.Subscriber须要进行树妫化,使其包含和Publisher样的次结构和初始数据.TransaaionalReplication出功之Vi,DiMributor上的1.ogRcudcrAgent会将读取Publisher的1.og信息,并分饰出极标识为Micmion的INSERT.1.PDATE.DE1.ETEifi句.此石乂M这垓TransactionfDislribukir.并写入diNritmlig敢摄峰.tDislribuliixiAgE把IhMnbutor上的Tran»cliun运5tJ,JSubscriber迸行玳放.WS：在图中DMiblHmAsE运行在DEritHHOrI：.这是在PUal（推）模K下的状况.可以（VT?为PUH（14）模式,DEribWlkmAgE格运行在SUbSCribCr上e事目中的“与*在时文的系统结内小1。iHl/原先的系统结构.我们拈里通过这次的项Il得到如下所示的系统结惭的信息M也很大,项H中我/奴终确定只复制全部的&,这样做他出去性能的考小，这样做公对科来的rdwe产生影响.须要招总.下文会进行探讨.还有一个分疗虚的是如何进行从JHl鼻的物妁化？在TmnMaiOnttIRcPliaMion起先之毓，首先要对从数据库进行切蛤化，使共狭G'，卜依如“样的衣结构和初始检械.在配WTransactionalRcplicuiion中会行,个选项来进行创始化（UISnaPSbOlAgenI完成）.但在我们的试验中初始化机费/几个小时.所以我们没仃运用Tmn3IiUgIRePIiCiaMI我认的柯蛤化方式,而是通过数据库;份还归来完成初始化，要这样做就尔察变更配置的-线选现，下文还会涉及.TninsactionalRcpknIkmfi些什么前提条件？ftUtrt-IFJCompatibilitykvcl<.Jft容性等侬）须要达到SQ1.SCrVCr2005（907我们运用的是SQ1.Servw2005.当微容性被别为80时.而置过程中会出现林杆）.牧械所:的RccoVaymOdd<（ffi）须要足FUll（完用）.全flCW.Subicriber时.sp_;UkhUhsip<itm的参_“pe=N'replicatMuh11OnIy'表示从政据昨的初始化完全由外部未完成IWsukcripiionjypc=NPUIr表示运用拉模式，:须要RCPliCale的我必需具有主谊，（这应当是天知地义的，f!在辽次配寅中意然发觉一用芋那可耻”的东西）存赭迨程戌其也脚本中,不能对进行RCPIicatC的表进行tncaw.需把相应存储过程中的语句改为Ikleie-这是因为Replication是4E对1.ug的机取与解析似Uiuitrale不产生1.oy.假如Rcptigte的元素还包括存储过村或阳依.姓片仃典他线前搬条件,我们不在这里绽开，可以苑看11s.如何来配JtTransactionalReplication?微软供应了特1分用的图形化界面可以进行RCPbCMion的配置.但图形化配贸的敏捷性是“双的.有些配置选项在图形化界Ihi卜无法完成.我的建议是先用图形化配WRCPIKMion并生成相应的yripu北£依品需求法及WriP1.并用9则进行配置，在我I（J的项目中也是这么做的.立认的状况下,1.hMributor服务戏的DAPmgramFiMsvMicrosonSQ1.Srtvw1SSQ1.l!SSQ1.repldata会存放implicate数柢.由我们要支持Pull横式.须要共享送工件夹，并给此文件先设Pl个其有FUllCOmZ权眼的域账户”并把此域联户设置为SUtMaikr胆务那上SQ1.SCrVaAgCntitI务的运行账户,此服务同时须要被设为AUtOmMiC启动方区，在GadMMpuMKher的WUtking_ilircclory参数设为此共享HIi的IIq络路径.配W.Publisher时,Sp-Mdpublicaiion的卷数sync_mcihod=N'concurran：rcpl_frCq=Na«ninix>u4保证fReplicate傥尽可傥实时：3luw_iniIlahZe_ftwmJxnrkup-N-tne'表示通过备份还腴来进行从数据豚的初始化.后期举lIaffltWReplication的性俺耳鼻得？ 做校供ReplicationMnnilor,这个工具还是比较好用的，可以ft11到PubIilration和Subscription的状态,汪健作i到:*田仃多少Tnu