数据库压缩备份提高备份效率

在数据库的备份过程中有很多参数前几日发现公司的备份数据库job运行的很慢就去研究了一下发现在备份程序中都没有启用压缩加上压缩以后有发现效率提高了不少本篇就几个压缩相关的参数来看一下备份数据库的过程中如何提高备份的效率。代码实现为了更好地了解数据库备份我们首先要知道代码以及参数的含义。普通代码BACKUP DATABASE ‘备份数据库名称’ TO DISK 地址bak文件名称 ;加快备份进度的几个关键参数1.BUFFERCOUNT--指定用于备份操作的 I/O 缓冲区总数。可以指定任何正整数2.MAXTRANSFERSIZE--指定要在 SQL Server 和备份介质之间使用的最大传输单位。可能的值是 64 KB的倍数最大 4 MB。3.COMPRESSION--压缩备份选项。4.CHECKSUM--验证数据选项。5.启用多个备份集。这四个中CHECKSUM是用来保证数据安全的这里我们不做讨论由于一般备份也不会用到多个备份集这里我们主要讨论前三个带来的影响当然对于数据量级比较大的可以采用多备份集尤其不同磁盘的备份集将会大大提高效率。具体对比首先是常规没有压缩的情况下的备份如图1图1数据页数282295总共花费了32.147秒。接下来我们看一下当加了压缩等参数后的表现。增加压缩参数的情况如图2图2页数接近花费的时间是19.524秒单位备份速度大幅提升到112.957/s。添加了COMPRESSION、MAXTRANSFERSIZE、BUFFERCOUNT三个参数的图3这个就快了不少花费了17.6秒写入速度为125.228M/s。适当缩小MAXTRANSFERSIZE、BUFFERCOUNT如图4图4如图所示缩小MAXTRANSFERSIZE、BUFFERCOUNT后相应的写入速度略微下降。时间也略微提高。大幅提升BUFFERCOUNT的数值能够提升写入速度。图5缓冲区使用的总计空间由下面公式确定buffercount*maxtransfersize这里需要知道该公式结果不能大于分配内存所以要注意服务器的最大内存以免内存溢出。除了以上几个条件我也做了几个相关的其他条件的修改来适应不同的备份数据例如数据验证CHECKSUM等都会增大备份数据的时间。注意1 通过压缩前后文件的大小对比可知道到压缩比率。2 对已启用压缩的数据库进行压缩备份,CPU消耗会变得更高压缩主要因素包括1.数据类型。字符数据的压缩率要高于其他类型的数据。2.数据重复的比例越高压缩越好类似于数据库压缩页压缩。通常如果某页包含多个行而其中的某个字段包含相同的值则该值可获得较大的压缩。相反对于包含随机数据或者每页只有一个很大的行的数据库压缩备份的大小几乎与未压缩的备份相同。https://www.hongxiu.com/booklist/1525945https://www.hongxiu.com/booklist/1525944https://www.hongxiu.com/booklist/1525943https://www.hongxiu.com/booklist/1525942https://www.hongxiu.com/booklist/1525941https://www.hongxiu.com/booklist/1525940https://www.hongxiu.com/booklist/1525936https://www.hongxiu.com/booklist/1525935https://www.hongxiu.com/booklist/1525934https://www.hongxiu.com/booklist/1525933https://www.hongxiu.com/booklist/1525932https://www.hongxiu.com/booklist/1525931https://www.hongxiu.com/booklist/1525930https://www.hongxiu.com/booklist/1525928https://www.hongxiu.com/booklist/1525929https://www.hongxiu.com/booklist/1525927https://www.hongxiu.com/booklist/1525926https://www.hongxiu.com/booklist/1525925https://www.hongxiu.com/booklist/1525924https://www.hongxiu.com/booklist/1525923https://www.hongxiu.com/booklist/1525922https://www.hongxiu.com/booklist/1525921https://www.hongxiu.com/booklist/1525920https://www.hongxiu.com/booklist/1525919https://www.hongxiu.com/booklist/1525918https://www.hongxiu.com/booklist/1525917https://www.hongxiu.com/booklist/1525916https://www.hongxiu.com/booklist/1525914https://www.hongxiu.com/booklist/1525915https://www.hongxiu.com/booklist/1525913https://www.hongxiu.com/booklist/1525912https://www.hongxiu.com/booklist/1525911https://www.hongxiu.com/booklist/1525910https://www.hongxiu.com/booklist/1525909https://www.hongxiu.com/booklist/1525908https://www.hongxiu.com/booklist/1525907https://www.hongxiu.com/booklist/1525906https://www.hongxiu.com/booklist/1525905https://www.hongxiu.com/booklist/1525904https://www.hongxiu.com/booklist/1525903https://www.hongxiu.com/booklist/1525902https://www.hongxiu.com/booklist/1525901https://www.hongxiu.com/booklist/1525900https://www.hongxiu.com/booklist/1525899https://www.hongxiu.com/booklist/1525898https://www.hongxiu.com/booklist/1525897https://www.hongxiu.com/booklist/1525896https://www.hongxiu.com/booklist/1525895https://www.hongxiu.com/booklist/1525894https://www.hongxiu.com/booklist/1525893https://www.hongxiu.com/booklist/1525892https://www.hongxiu.com/booklist/1525891https://www.hongxiu.com/booklist/1525890https://www.hongxiu.com/booklist/1525889https://www.hongxiu.com/booklist/1525888https://www.hongxiu.com/booklist/1525887https://www.hongxiu.com/booklist/1525886https://www.hongxiu.com/booklist/1525885https://www.hongxiu.com/booklist/1525882https://www.hongxiu.com/booklist/1525881https://www.hongxiu.com/booklist/1525880https://www.hongxiu.com/booklist/1525879https://www.hongxiu.com/booklist/1525878https://www.hongxiu.com/booklist/1525877https://www.hongxiu.com/booklist/1525876https://www.hongxiu.com/booklist/1525875https://www.hongxiu.com/booklist/1525874https://www.hongxiu.com/booklist/1525873https://www.hongxiu.com/booklist/1525872https://www.hongxiu.com/booklist/1525871https://www.hongxiu.com/booklist/1525870https://www.hongxiu.com/booklist/1525869https://www.hongxiu.com/booklist/1525868https://www.hongxiu.com/booklist/1525867https://www.hongxiu.com/booklist/1525866https://www.hongxiu.com/booklist/1525865https://www.hongxiu.com/booklist/1525864https://www.hongxiu.com/booklist/1525863https://www.hongxiu.com/booklist/1525862https://www.hongxiu.com/booklist/1525861https://www.hongxiu.com/booklist/1525860https://www.hongxiu.com/booklist/1525859https://www.hongxiu.com/booklist/1525858https://www.hongxiu.com/booklist/1525857https://www.hongxiu.com/booklist/1525856https://www.hongxiu.com/booklist/1525855https://www.hongxiu.com/booklist/1525854https://www.hongxiu.com/booklist/1525853https://www.hongxiu.com/booklist/1525852https://www.hongxiu.com/booklist/1525851https://www.hongxiu.com/booklist/1525850https://www.hongxiu.com/booklist/1525849https://www.hongxiu.com/booklist/1525848https://www.hongxiu.com/booklist/1525847https://www.hongxiu.com/booklist/1525846https://www.hongxiu.com/booklist/1525845https://www.hongxiu.com/booklist/1525844https://www.hongxiu.com/booklist/1525843https://www.hongxiu.com/booklist/1525842https://www.hongxiu.com/booklist/1525841https://www.hongxiu.com/booklist/1525840https://www.hongxiu.com/booklist/1525839https://www.hongxiu.com/booklist/1525838https://www.hongxiu.com/booklist/1525837https://www.hongxiu.com/booklist/1525836https://www.hongxiu.com/booklist/1525835https://www.hongxiu.com/booklist/1525834https://www.hongxiu.com/booklist/1525833https://www.hongxiu.com/booklist/1525832https://www.hongxiu.com/booklist/1525831https://www.hongxiu.com/booklist/1525830https://www.hongxiu.com/booklist/1525829https://www.hongxiu.com/booklist/1525828https://www.hongxiu.com/booklist/1525827https://www.hongxiu.com/booklist/1525826https://www.hongxiu.com/booklist/1525825https://www.hongxiu.com/booklist/1525823https://www.hongxiu.com/booklist/1525820https://www.hongxiu.com/booklist/1525819https://www.hongxiu.com/booklist/1525818https://www.hongxiu.com/booklist/1525817https://www.hongxiu.com/booklist/1525816https://www.hongxiu.com/booklist/1525815https://www.hongxiu.com/booklist/1525814https://www.hongxiu.com/booklist/1525813https://www.hongxiu.com/booklist/1525812https://www.hongxiu.com/booklist/1525811https://www.hongxiu.com/booklist/1525810https://www.hongxiu.com/booklist/1525809https://www.hongxiu.com/booklist/1525808https://www.hongxiu.com/booklist/1525807https://www.hongxiu.com/booklist/1525806https://www.hongxiu.com/booklist/1525699https://www.hongxiu.com/booklist/1525698https://www.hongxiu.com/booklist/1525697https://www.hongxiu.com/booklist/1525696https://www.hongxiu.com/booklist/1525695https://www.hongxiu.com/booklist/1525694https://www.hongxiu.com/booklist/1525693https://www.hongxiu.com/booklist/1525692https://www.hongxiu.com/booklist/1525691https://www.hongxiu.com/booklist/1525690https://www.hongxiu.com/booklist/1525689https://www.hongxiu.com/booklist/1525688https://www.hongxiu.com/booklist/1525687https://www.hongxiu.com/booklist/1525686https://www.hongxiu.com/booklist/1525685https://www.hongxiu.com/booklist/1525684https://www.hongxiu.com/booklist/1525683https://www.hongxiu.com/booklist/1525682https://www.hongxiu.com/booklist/1525681https://www.hongxiu.com/booklist/1525680https://www.hongxiu.com/booklist/1525679https://www.hongxiu.com/booklist/1525678https://www.hongxiu.com/booklist/1525677https://www.hongxiu.com/booklist/1525676https://www.hongxiu.com/booklist/1525675https://www.hongxiu.com/booklist/1525674https://www.hongxiu.com/booklist/1525673https://www.hongxiu.com/booklist/1525672https://www.hongxiu.com/booklist/1525671https://www.hongxiu.com/booklist/1525670https://www.hongxiu.com/booklist/1525669https://www.hongxiu.com/booklist/1525668https://www.hongxiu.com/booklist/1525667https://www.hongxiu.com/booklist/1525666https://www.hongxiu.com/booklist/1525665https://www.hongxiu.com/booklist/1525664https://www.hongxiu.com/booklist/1525663https://www.hongxiu.com/booklist/1525662https://www.hongxiu.com/booklist/1525661https://www.hongxiu.com/booklist/1525660https://www.hongxiu.com/booklist/1525659https://www.hongxiu.com/booklist/1525658https://www.hongxiu.com/booklist/1525657https://www.hongxiu.com/booklist/1525656https://www.hongxiu.com/booklist/1525655https://www.hongxiu.com/booklist/1525654https://www.hongxiu.com/booklist/1525653https://www.hongxiu.com/booklist/1525652https://www.hongxiu.com/booklist/1525651https://www.hongxiu.com/booklist/1525650https://www.hongxiu.com/booklist/1525649https://www.hongxiu.com/booklist/1525648https://www.hongxiu.com/booklist/1525647https://www.hongxiu.com/booklist/1525646https://www.hongxiu.com/booklist/1525645https://www.hongxiu.com/booklist/1525644https://www.hongxiu.com/booklist/1525643https://www.hongxiu.com/booklist/1525642https://www.hongxiu.com/booklist/1525641https://www.hongxiu.com/booklist/1525640https://www.hongxiu.com/booklist/1525639https://www.hongxiu.com/booklist/1525638https://www.hongxiu.com/booklist/1525637https://www.hongxiu.com/booklist/1525636https://www.hongxiu.com/booklist/1525635https://www.hongxiu.com/booklist/1525634https://www.hongxiu.com/booklist/1525633https://www.hongxiu.com/booklist/1525632https://www.hongxiu.com/booklist/1525631https://www.hongxiu.com/booklist/1525629https://www.hongxiu.com/booklist/1525630https://www.hongxiu.com/booklist/1525628https://www.hongxiu.com/booklist/1525627https://www.hongxiu.com/booklist/1525626https://www.hongxiu.com/booklist/1525625https://www.hongxiu.com/booklist/1525624https://www.hongxiu.com/booklist/1525623https://www.hongxiu.com/booklist/1525622https://www.hongxiu.com/booklist/1525621https://www.hongxiu.com/booklist/1525620https://www.hongxiu.com/booklist/1525619https://www.hongxiu.com/booklist/1525618https://www.hongxiu.com/booklist/1525617https://www.hongxiu.com/booklist/1525616https://www.hongxiu.com/booklist/1525615https://www.hongxiu.com/booklist/1525614https://www.hongxiu.com/booklist/1525613https://www.hongxiu.com/booklist/1525612https://www.hongxiu.com/booklist/1525611https://www.hongxiu.com/booklist/1525610https://www.hongxiu.com/booklist/1525609https://www.hongxiu.com/booklist/1525608https://www.hongxiu.com/booklist/1525607https://www.hongxiu.com/booklist/1525606https://www.hongxiu.com/booklist/1525605https://www.hongxiu.com/booklist/1525604https://www.hongxiu.com/booklist/1525603https://www.hongxiu.com/booklist/1525602https://www.hongxiu.com/booklist/1525601https://www.hongxiu.com/booklist/1525600https://www.hongxiu.com/booklist/1525599https://www.hongxiu.com/booklist/1525598https://www.hongxiu.com/booklist/1525597https://www.hongxiu.com/booklist/1525596https://www.hongxiu.com/booklist/1525595https://www.hongxiu.com/booklist/1525594https://www.hongxiu.com/booklist/1525593https://www.hongxiu.com/booklist/1525592https://www.hongxiu.com/booklist/1525591https://www.hongxiu.com/booklist/1525590https://www.hongxiu.com/booklist/1525589https://www.hongxiu.com/booklist/1525588https://www.hongxiu.com/booklist/1525587https://www.hongxiu.com/booklist/1525586https://www.hongxiu.com/booklist/1525585https://www.hongxiu.com/booklist/1525584https://www.hongxiu.com/booklist/1525583https://www.hongxiu.com/booklist/1525582https://www.hongxiu.com/booklist/1525581https://www.hongxiu.com/booklist/1525580https://www.hongxiu.com/booklist/1525579https://www.hongxiu.com/booklist/1525578https://www.hongxiu.com/booklist/1525577https://www.hongxiu.com/booklist/1525576https://www.hongxiu.com/booklist/1525575https://www.hongxiu.com/booklist/1525574https://www.hongxiu.com/booklist/1525573https://www.hongxiu.com/booklist/1525572https://www.hongxiu.com/booklist/1525571https://www.hongxiu.com/booklist/1525570https://www.hongxiu.com/booklist/1525569https://www.hongxiu.com/booklist/1525568https://www.hongxiu.com/booklist/1525567https://www.hongxiu.com/booklist/1525566https://www.hongxiu.com/booklist/1525565https://www.hongxiu.com/booklist/1525564https://www.hongxiu.com/booklist/1525563https://www.hongxiu.com/booklist/1525562https://www.hongxiu.com/booklist/1525561https://www.hongxiu.com/booklist/1525560https://www.hongxiu.com/booklist/1525559https://www.hongxiu.com/booklist/1525558https://www.hongxiu.com/booklist/1525557https://www.hongxiu.com/booklist/1525556https://www.hongxiu.com/booklist/1525555https://www.hongxiu.com/booklist/1525554https://www.hongxiu.com/booklist/1525553https://www.hongxiu.com/booklist/1525552https://www.hongxiu.com/booklist/1525551https://www.hongxiu.com/booklist/1525550https://www.hongxiu.com/booklist/1525549https://www.hongxiu.com/booklist/1525548https://www.hongxiu.com/booklist/1525547https://www.hongxiu.com/booklist/1525546https://www.hongxiu.com/booklist/1525545https://www.hongxiu.com/booklist/1525544https://www.hongxiu.com/booklist/1525543https://www.hongxiu.com/booklist/1525542https://www.hongxiu.com/booklist/1525541https://www.hongxiu.com/booklist/1525540https://www.hongxiu.com/booklist/1525539https://www.hongxiu.com/booklist/1525537https://www.hongxiu.com/booklist/1525538https://www.hongxiu.com/booklist/1525536https://www.hongxiu.com/booklist/1525535https://www.hongxiu.com/booklist/1525534https://www.hongxiu.com/booklist/1525533https://www.hongxiu.com/booklist/1525532https://www.hongxiu.com/booklist/1525531https://www.hongxiu.com/booklist/1525530https://www.hongxiu.com/booklist/1525529https://www.hongxiu.com/booklist/1525528https://www.hongxiu.com/booklist/1525527https://www.hongxiu.com/booklist/1525526https://www.hongxiu.com/booklist/1525525https://www.hongxiu.com/booklist/1525524https://www.hongxiu.com/booklist/1525523https://www.hongxiu.com/booklist/1525522https://www.hongxiu.com/booklist/1525521https://www.hongxiu.com/booklist/1525520https://www.hongxiu.com/booklist/1525519https://www.hongxiu.com/booklist/1525518https://www.hongxiu.com/booklist/1525517https://www.hongxiu.com/booklist/1525516https://www.hongxiu.com/booklist/1525515https://www.hongxiu.com/booklist/1525514https://www.hongxiu.com/booklist/1525513https://www.hongxiu.com/booklist/1525512https://www.hongxiu.com/booklist/1525511https://www.hongxiu.com/booklist/1525510https://www.hongxiu.com/booklist/1525509https://www.hongxiu.com/booklist/1525508https://www.hongxiu.com/booklist/1525507https://www.hongxiu.com/booklist/1525506https://www.hongxiu.com/booklist/1525505https://www.hongxiu.com/booklist/1525504https://www.hongxiu.com/booklist/1525503https://www.hongxiu.com/booklist/1525502https://www.hongxiu.com/booklist/1525501https://www.hongxiu.com/booklist/1525500https://www.hongxiu.com/booklist/1525499https://www.hongxiu.com/booklist/1525498https://www.hongxiu.com/booklist/1525497https://www.hongxiu.com/booklist/1525496https://www.hongxiu.com/booklist/1525495https://www.hongxiu.com/booklist/1525494https://www.hongxiu.com/booklist/1525493https://www.hongxiu.com/booklist/1525492https://www.hongxiu.com/booklist/1525491https://www.hongxiu.com/booklist/1525490https://www.hongxiu.com/booklist/1525489https://www.hongxiu.com/booklist/1525488https://www.hongxiu.com/booklist/1525487https://www.hongxiu.com/booklist/1525486https://www.hongxiu.com/booklist/1525485https://www.hongxiu.com/booklist/1525484https://www.hongxiu.com/booklist/1525483https://www.hongxiu.com/booklist/1525482https://www.hongxiu.com/booklist/1525481https://www.hongxiu.com/booklist/1525480https://www.hongxiu.com/booklist/1525479https://www.hongxiu.com/booklist/1525478https://www.hongxiu.com/booklist/1525477https://www.hongxiu.com/booklist/1525476https://www.hongxiu.com/booklist/1525475https://www.hongxiu.com/booklist/1525474https://www.hongxiu.com/booklist/1525473https://www.hongxiu.com/booklist/1525472https://www.hongxiu.com/booklist/1525471https://www.hongxiu.com/booklist/1525470https://www.hongxiu.com/booklist/1525469https://www.hongxiu.com/booklist/1525468https://www.hongxiu.com/booklist/1525467https://www.hongxiu.com/booklist/1525466https://www.hongxiu.com/booklist/1525465https://www.hongxiu.com/booklist/1525464https://www.hongxiu.com/booklist/1525463https://www.hongxiu.com/booklist/1525462https://www.hongxiu.com/booklist/1525461https://www.hongxiu.com/booklist/1525460https://www.hongxiu.com/booklist/1525459https://www.hongxiu.com/booklist/1525458https://www.hongxiu.com/booklist/1525456https://www.hongxiu.com/booklist/1525457https://www.hongxiu.com/booklist/1525455https://www.hongxiu.com/booklist/1525454https://www.hongxiu.com/booklist/1525453https://www.hongxiu.com/booklist/1525452https://www.hongxiu.com/booklist/1525451https://www.hongxiu.com/booklist/1525450https://www.hongxiu.com/booklist/1525449https://www.hongxiu.com/booklist/1525448https://www.hongxiu.com/booklist/1525447https://www.hongxiu.com/booklist/1525446https://www.hongxiu.com/booklist/1525445https://www.hongxiu.com/booklist/1525444https://www.hongxiu.com/booklist/1525443https://www.hongxiu.com/booklist/1525442https://www.hongxiu.com/booklist/1525441https://www.hongxiu.com/booklist/1525439https://www.hongxiu.com/booklist/1525440https://www.hongxiu.com/booklist/1525438https://www.hongxiu.com/booklist/1525437https://www.hongxiu.com/booklist/1525436