库性能调优技巧.docx

1、库性能调优技巧IMES 資料庫性能調優技巧1.建议半年进行一次DB维护（IP：10.99.252.65）2.压缩日志文档（shrink） （1）收缩HPIMES_Rep库，右键-task-Shrink-File （2）File type一定要选择log，然后选择Shrink action中的第二项，输入想要压 缩的最小值（例512），点OK 注：1）即使收缩不到设置的最小值也没关系，系统会自动收缩到能达到的最小值 2）File type不能选择data，因为如果收缩了data，保存的数据顺序就会被变动 （3）检查收缩结果，和前面收缩一样打开页面，Currently allocated spac

2、e就是 收缩后的文件大小注：SQL Command做shrinkuse HPReportDSDBCC SHRINKFILE (HPReportDS_log, 1024);3.历史资料库维护（1）历史数据库定时新建HPDocking_2012,HPDocking_2013每年6月1日需要添加新的备份库例HPDocking_2014HPEDI_2012,HPEDI_2013,HPEDI_2014 每年1月最后一天需要添加新的备份库HPEDI_2015HPIMES_2013,HPIMES_20130515 每年6月1日需要添加新的备份库例HPIMES_2014（2）定期收缩注：1）新建历史资料库时R

3、ecover model设置为Simple2）根据相应正式库的大小去设置备份库 Data：Initial Size为30G，Autogrowth为5G Log: Initial Size为2G，Autogrowth为512M4.定期检查Missing Index (1)点选SQL Server Reports 自訂報表(Customer Reports.) or performance_dashboard_main，即可產生 performance_dashboard 報表。 (2)点击左下角的Missing Indexes (3)看最后一列Proposed Index，就是所缺失的Index

4、，可copy出来新建Index 注：或者也可以执行sp:sp_missing_index，也会整理出需要建立Index的语句5.修改DB 数据文件(.mdf)和日志文件(.ldf)的存放位置 (1)先用语句查看DB文件的当前存储位置 注：一定要在数据库没有任何人在用的情况下，做位置转移，要不然会造成 数据丢失Script # 1: Capture database and transaction log file informationUSE SkodaOA3GOsp_helpfileGO 查询结果： (2)Detach DB(分离数据库后DB会不见（offline）)，有两种方法都可以： 方

5、法一：sql语句Script # 2: Set database to single user mode and detach databaseUse masterGO- Set database to single user modeALTER DATABASE SkodaOA3SET SINGLE_USERGO - Detach the databasesp_detach_db SkodaOA3GO 方法二：Tasks-Detach，如果status为Ready，则直接点击OK；如果status为 Not Ready，则要在drop前打勾，再点击OK (3)将数据文件和日志文件copy到需

6、要存放的位置，然后将之前位置的文件删除 原文件存放位置：E:SQLDATA 需要移动到的存放位置：F:SQLDATA(4)Attach DB（附加数据库） 1）点击Add,在弹出的对话框中选择目标路径下的mdf文件，点击OK 2）点击OK即可(5)最后用(1)的语句查看DB文件的当前存储位置是不是已经变更注：tempdb位置移动，需要下语句，然后重启SQL ServiceUSE master GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = T:TempDBtempdb.mdf) GO ALTER DATABASE

7、 tempdb MODIFY FILE (NAME = templog, FILENAME = T:TempDBtemplog.ldf) GO 改變位置後，要重啟SQL Server ,tempdb的名字要對喔6.新建FullBackUp维护计划 （1）右键Maintenance Plans-New Maintenance Plan （2）选择Toolbox里的Back Up Database Task,拖到执行框里， 然后双击打开，进行下图设置 （3）选择Toolbox里的Maintenance Cleanup Task,拖到执行框里， 然后双击打开，进行下图设置 （4）做一下步骤执行的先后

8、顺序，Back Up Database Task-Maintenance Cleanup Task （5）排程，然后保存即可7.SQL Server 2008 DB Mail异常处理 (1)重启Database Mail的语句 exec msdb.dbo.sysmail_start_sp (2)记录所有的Mail信息的系统表 select * from msdb.dbo.sysmail_allitems (3)删除sysmail_allitems表的信息SP EXECUTE msdb.dbo.sysmail_delete_mailitems_sp sent_status = unsent ;

9、GO (4)记录DB Mail的事件日志的系统表 select * from sysmail_event_log (5)数据库邮件故障排除网址 (6)ServiceBrokerQueue（服务代理队列)8.SQL Server Maintenance Plans(维护计划) (1)sysmaintplan_plans: 維護計畫主表,每個維護計畫一筆資料 select * from sysmaintplan_plans (2)sysmaintplan_subplans：記錄維護計畫的子計畫資訊 select * from sysmaintplan_subplans (3)sysmaintpla

10、n_log：維護計畫執行記錄 select * from sysmaintplan_log order by start_time desc (4)sysjobs_view：相關的作業資訊select * from sysjobs_view order by date_created desc (5)如果维护计划有设置好排程，则在SQL Server Agent里就可以看得到排程 (6)如果Job有出现如下报错，则将之前的Package “Maintenance PlansFullBackUp”修改为“Maintenance PlansFullBackUp” Message Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.9.修改DB Owner ALTER AUTHORIZATION ON DATABASE:DatabaseNameTO UserNameGO/*DatabaseName就是数据库名称UserName是用户名称*/10.ALTER LOGIN TEST DISABLEALTER LOGIN TEST ENABLE 11.