数据库优化报告

报告背景

　 　为了确保业务数据库系统的稳定、高效的运行，需定期进行数据库性能健康性检查与优化，目的是了解现有数据库系统的运行和维护状态、发现潜在的数据库性能问题，提出数据库系统调整的建议，以提升数据库服务器性能，保障业务数据库的稳定、高效运行。建议每年可按季度进行一次全面的数据库系统性能检查，主要关注数据库服务器的详细运行状态和性能相关方面。

优化分析

SQL优化

当数据库运行到一定阶段，随着业务的长久运作，数据量日积月累，数据库的性能影响性会逐渐增大；例如：数据库索引的缺失，导致数据物理访问路径变得繁琐，统计信息陈旧，导致执行计划的异常等等，这个时候需要进行数据库跟踪，找出数据库的瓶颈并做优化处理，下面是介绍profile的跟踪与分析。

Profile跟踪分析：

分析：经过多次数据库profile跟踪（均采自工作时间段），跟踪时间段为早点9：00到12：00，此时间段为业务正常运作时期，经过性能优化建议工具的采样分析，预计可以提高32%-50%的数据库性能，其中建议案包括：陈旧的统计信息收集和索引的缺失。下面是数据库优化前后各项性能指标分析。

优化前性能指标：

分析：可见，优化前，服务器业务平锋期的CPU持续30%左右，如出现业务高峰，CPU使用率可达60%，磁盘负荷也比较频繁，资源竞争比较剧烈。

优化后同比时间性能指标：

分析：经过两次跟踪与全面优化后，同比每天同一时间的业务高峰期，CPU使用率已经没有出现持续30%以上的状态，普遍都在30%以下的负载下波动，从性能监视器来看，CPU与IO的波动幅度在30%以下，CPU没有持续高负荷占用，性能稳定良好。

日志收缩优化

分析：经检查，目前数据库dnb_2016_test的事务日志异常大，已占用475G的空间，这给数据库性能和磁盘空间占用带来较大的影响，为了解决以上问题，需要对该事务日志进行收缩处理，下面是操作步骤：

use dnb_2016_test

declare @bakfile nvarchar(100)   --@bakfile备份文件名   

set @bakfile='e:\database_bak\log_bak_'+convert(nvarchar(8),getdate(),112)+'.log'   

BACKUP LOG   dnb_2016_test TO DISK= @bakfile WITH RETAINDAYS= 1,COMPRESSION   

dbcc shrinkfile(erp_db_Log,100) -- dnb_2016_test_log为数据库文件逻辑名称，100为希望日志收缩到的MB数   

go   

收缩优化后：

分析：收缩后，E盘的数据库文件所在磁盘文件空间，从原来500G占用，缩减到100G内，大大节省了磁盘空间的同时，对性能提高有积极意义。

错误连接优化

由于目前数据库日志存在大量的连接报错信息，每月产生接近400W的日志记录，这不仅给磁盘IO增加写入的压力，还积累了大量的错误日志，占用磁盘空间的同时，让排查问题变得复杂，所以需要彻底迅速排查日志暴增的原因。

从日志的报错内容来看：

分析：经排查，产生这种错误的原因：主要是外网IP通过sa管理员账号不断尝试登录，但由于密码错误，不断报登录失败的错误提示，经分析，该数据库是存在对外网提供业务的需求，所以图上显示连接数据库的IP为外网IP，但由于程序配置错误，或该IP来自其他程序的攻击导致，频繁产生日志报错；为了彻底解决该问题，建议对外网程序配置进行检查，确保数据库连接信息正确，避免这种频繁报错导致写日志的IO压力。

备份策略优化

目前由于数据库处于简单模式，数据库只能做单一的完整备份，目前设置的备份策略是每日进行两次完整备份，分别是深夜的0点和白天中午12点，这种备份策略的缺陷是：只能恢复到当时备份时刻的那一刻，这就出现很大问题，如数据库在某一刻发生损坏或进行误操作，恢复到最新备份时刻，那损坏前产生的新数据，将会丢失，无法恢复，为了弥补这种缺陷，下面对备份策略进行优化。

先设置数据库dnb_2016和dnb_it为完整模式，如下图：

如上图，先把数据库的恢复模式从简单改为：“完整”。

将原来的全备作业的调度时间改为：每周日凌晨1点进行一次全备：

增加事务日志备份，调度时间设置为每日凌晨1点（周一至六）；

分析：增加事务日志备份后，优势在于：

恢复策略灵活，可以做指定时间点的恢复；

节省磁盘空间，每周做一次完整备份，其他时间做日志备份，可以大大节省磁盘使用空间。

数据库优化总结

　　本次优化，针对SQL优化部分，经过多次的数据库profile跟踪与服务器的各项性能指标收集，结合优化管理器的各项建议，评估对陈旧统计信息的收集和缺失索引的创建对象，最终确定了实施方案，数据库优化效果较明显，服务器的各项性能指标有明显改善效果；

　　经过对数据库的日志收缩优化，大大节省了服务器的磁盘容量，同时对数据库性能的提示起到积极作用；

　　数据库日志不断出现频繁连接报错，建议对外网程序配置进行检查，确保数据库连接信息正确，避免这种频繁报错导致写日志的IO压力；目前已对数据库备份策略进行优化，优化后，节省磁盘空间的同时，可满足恢复至任意时间点的数据恢复需求。