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到:*田仃多少Tnuisocdon好档传送。 TraiKactionaIReplication设JlQf后，DiMrihUtCr上将门功生成相关的彩个AlEx,如：ReplicationWarning:Subscriptionexpiration（Threshold:expiration）（ReplicationWarning:TransactionalreplicationIalCnCymIrcSlWM:lacy）等.“以将这些AMrtSqDaHabaSCMail进行绑定.“iHl现警告时.自动发出期件.北功能虽然在项目中配11了，但从未正常发出警力加件，始终不知道为什么，祖如有人知道的话可以联系我）.如何Ift行以后的RUeXe Ki先数据库的Relea处般会分为：部分：1.表结构的变更（包括加/剧表.IIW删列:XKB数据的装我（如添加新功能的配置数据）；3.刷函数与存储过程脚本， 对I本项目中的RePliHMl数据阵.在ReIeiUie过程中需留意以下几点：1.衣领加的衣须要进行RTliCXion,除了在土敖第临创建我之外，还需配置此表进行RCPiiCMe，井迸行初始化,2若要删除某处FRcpIicaGon的必需先取消此表的RCPIi皿ion，再在主、从库中dr此表.3.若须要加？JKRCPliEion表的列（此列不f危为主批列）时.叫以干宽花主致拙阵上执行脚本.攵更会自动Rcplicatc到从数第忤,4.配A数第的装袋也只须要在主数据库完成5.两款、存储过程今要在主、从件上想进行刷新.总的与设M此项I找.基本达到了福求所提出的目标,但这只是起先,优化后的结构绘将来系统的扩展怏应了一个渠础. 通过试发发觉.在主/从数据阵上可以©愧不同的素川而不相互干擅（这和RePligli0«的间置相关）.这就可以依据主、从数据库不同的运用模式，创建更优化的索引.我曾在国外某BbS上看到，利用SQ1.S“ver2005的动态视图，自动依据数据库的运用模式火创建索弓I,就像门遭庖索引机样.这也是我格在OhleRqXE数据咋上做的事.将米般如有了乡个SUbSCribCr数据库，还僮做OliKRCPoa的数据库1.OadBUlarWC,"i仃RCPoa求时.系统首先作G各个Subscriber的CPU和MCmOIy的1.oad,lf1.oad较轻的Subscnbcr接受Report恳求.我们还能利用RePliCaleHl的数据体进行BI（商业计依分析与也娜而不会影响到主敞品库的运行.OIitcRcpoa能连接到个由主tJK东乂制出的小独数据库匕这样这两个阵之间的幄就故隔离了.Inl时主数屈庶与从敛械源支配在两台服务器上（项Il中我年】把乂刖出的数据埠放在了日虚拟机I：）.那么CPU资涉1.j内存资源的消忙也被附凛/.须R留意的足图中OhICReIXlrt徐/主耍的读捶作外,还有少次的写徐作（这是闪为用户可以存他RCNrI条件）.我们把这些写指何主数据感，从数据库在下时刻的复制中刘列这些*t>在TraZaiOnaIRmkaUOn中有三个4维角色，面厘目中只才两台服务.我<!如何耒力E这三个嵬角色典？候选的方案书两种：I.土数据库上釐贸Publisher和DRmHnCr.从数据库上间置SUhyriber；2.Eftlt!Ijlft1.FlPubIishcr,从t想库上配ADistribUtOr和SUbMTibcr>冲突的性.点:是DEribtor敢哪电？笈要指出的是Distributor对FReplkalion特别用要.这个角色拉节莉从主数据庶加取Tranaction的工作,在Push,式F,它还须要负责把TnlniaCIio11推送到个Subscriber.这"I：作都公消耗所在玻务器的c和内存资源.我们的项目布未尽可能保证业务模块的性能，所以我们选用了才窠2.把DuUibUUX配困在从数据府上.我幻是选用PUSh横式坯是Pull'式所Puih和PUlIK实是计对DiMribmnrKTnmtactinn到Subscriber的方式IFj；的（这点我是很,Vi面才相识到的，起先始终认为PUSh或PW公影IftDisIrt<x抓取PUWi5”上的侑息，其实不处）.对于DiaribUtOr和SUbSCribc<在台JR务器上,这两种粳式的效果珞本样.我们选挣了Pull枪式.叩DisHibUtx>nAgent运行在Subwriber端从Dislributnr4TruntuclMMi数据.这是为将来扩H电廊.假如以后再加一台服分擀来作为Subscribcrat.Disuibwor不会埒加太乡的性能压力。另一个家要考虐的月“复Mfi什么？TmnsactInHR卬Heab<mi可以送样豆刖毋些表、存姑过程或函数等内容.收简沽的是把壑个攻熨W中的所以元贰部进行红剂.仅这会造成RCPlistiE服务所要监视的对恁许多，同时M洛1：传悔