数据库系统的维护与优化

数据库系统的维护与优化(一)
    管理一个系统是对其的硬件,软件,可用性和安全负责.管理还包括跟踪并管理系统的增长情况。
    维护不只是备份数据库。这只是工作的一部分，你还需要完成一些周期性的工作以保证系统的可用性和响应速度。需要监视日志，创建容量计划，管理用户账户，以及为不可避免的紧急情况做好准备。
    维护清单：
    1.每日
    执行备份（完整，差异，文件组或日志）
    检查服务和实例以确保它们在运行
    检查sql server数据库日志（引擎和sql server代理）
    检查windows事件日志中sql server或sql server代理的记录
    验证备份和维护步骤
    
    2。每周
    检查作业历史记录以及它们创建的日志
    审核安全性更改
    性能优化
    检查微软以及其他网站的更新和问题

    3。每季度
    审核数据库增长（如果增长很快，需要每月进行）
    进行一次灾难还原测试

    4。按需
    执行备份（完整，差异，文件组或日志）
    执行索引和表维护
    添加并编辑用户
    还原数据库
    创建数据库
    调整系统
    应用服务包

一、数据库系统的维护

1. 用户信息访问表：

位置	类型	数量	时间段	负荷概要	所访问的功能

 

2. 负荷信息表：
负荷                                值                单位
CPU负荷(500个用户，每个事务0.7%)        350                处理能力百分比
内存(500个用户，每个事务100KB)        48.83            MB
网络带宽(500个用户，每事务25KB)        12                MB
I/O负荷（每个事务0.5%）                2.5                处理能力百分比

3. 检查数据库一致性
DBCC CHECKDB(数据库名)


4. I/O
sys.dm_db_index_operational_stats
它显示了索引的当前I/O性能统计.
select * from sys.dm_db_index_operational_stats(db_id(N'dbamdt'),object_id(N'dbamdt.dbo.serverinfo',null,null)
索引：
sys.dm_db_index_usage_stats
这个视图处理的性能问题，它显示了索引类型，碎片百分比和程度，可以定期读取这些信息以发现索引多久会变得零碎。
如果发现在生产环境索引每天会产生10%的碎片，则可能会每隔两到三天进行一个优化
select * from sys.dm_db_index_usage_stats

5. 索引碎片整理
dbcc indexdefrag (数据库名，表名，索引名)
dbcc indexdefrag命令对索引进行碎片整理，并且在索引维护阶段对系统产生的影响最少。

dbcc dbreindex(表名，索引名，填充因子)

6. 删除并重建索引。

sys.dm_db_index_physical_stats
这个视图显示了特定表所包含数据和索引的大小以及碎片信息。
select * from sys.dm_db_index_physical_stats(db_id(N'dbamdt'),object_id(N'dbamdt.dbo.serverinfo',null,null)

 

数据库系统的维护与优化(二)  
在上一节中，简单介绍了两个工具，下面使用一个简单的实例来说明如何使用这两个工具，来进行数据库优化。

二、启动sql server profiler
   1) 启动方法如下图，在SQL Server Management Studio中-->“工具”菜单上选择“sql server profilter”
   
   2) 在“开始”菜单上，选择“所有程序”——>“microsoft sql server 2005”——>“性能工具”——>“sql server profilter”命令。


三、创建和修改跟踪
1）在“文件”菜单上，选择“新建跟踪”命令，或者单击工具栏上的“新建跟踪”按钮。如下图。

2）如下图，在弹出的sql server连接服务器窗口，输入数据库服务器、用户名和密码。

3）在“跟踪名称”文本框中，输入跟踪的名称。如下图

4）在“使用模板”下拉列表中，为此跟踪选择一个跟踪模板。
5）选择保存跟踪结果的方法。
    1）保存到文件，将跟踪数据保存到.trc文件。同时指定“设置最大文件大小”的值，默认值为5MB。
    说明：还可以选择“启用文件滚动更新”选项，选择此选项之后，允许在达到最大文件大小时创建其他文件来接受跟踪数据。每个新文件名

都由原始.trc文件名按顺序编号而成。
    选项“服务器处理跟踪数据”，指定由运行跟踪的服务器来处理跟踪数据。使用此选项可降低跟踪所需的性能开销。
    2）“保存到表”，将跟踪结果保存到数据库表中，根据需要，可以选择“设置最大行数”，也可以选择“启用跟踪停止时间”。
5）在“事件选择”选项卡界面中选择要监控的事件。如下图

    我选择了存储完成调用时事件与TSQL语句完成时事件。这两个事件是查看Tsql执行性能损耗的最重要的两个事件，如下图，
    
   最后的事件选择的结果为下图：

6) 点击“列筛选器”，启动“编辑筛选器”对话框。可以使用此对话框编辑数据列筛选器。
     1)设置Tsql或存储过程执行时间超过5秒的sql语句或存储过程。

    2) 设置所要跟踪的数据库名称

   还可以点击“组织列”，更新跟踪中列的顺序，并按一列或多列对结果分组。

7) 一切都设置好了，就可以点击“运行”按钮就可创建一个跟踪。sql server profilter会自动运行此跟踪。如下图。


8)点击其中的行，在下半部的窗口中会显示，这个事件所执行的具体代码。如下图，这些代码会在下一节中用到。


9) 如上让sql server profilter运行一定时间，一般是4小时以上，最好是24小时。然后把收集到的事件数据存储到表或文件中。
    建议存储到表中，如果准备做事件重现，则存储到文件中。