免费SQLServer系列课程(一)--创建可恢复的数据库.ppt

1、开始,睿智汇海SQL Server免费培训课程,讲师：顾劲鹏,课程大纲,第一天:创建可恢复的数据库 第二天:创建可靠的自动的备份机制 第三天:表设计，数据类型设计 第四天:数据库索引最佳实践 第五天:混合负载数据库，锁和隔离级别机制 第六天:执行计划缓存和存储过程性能优化 第七天:数据加载，表及索引分区 第八天:数据性能监控调优,进度,第一天:创建可恢复的数据库 第二天:创建可靠的自动的备份机制 第三天:表设计，数据类型设计 第四天:数据库索引最佳实践 第五天:混合负载数据库，锁和隔离级别机制 第六天:执行计划缓存和存储过程性能优化 第七天:数据加载，表及索引分区 第八天:数据性能监控调优,本

2、次课程内容,数据结构 数据修改的原子性 数据文件和日志文件 优化数据库中的数据文件 事务和日志 优化数据库中的日志文件 数据库运行环境 数据恢复模式,学习目标,了解SQL Server的基础结构 了解SQL Server的运行原理 了解SQL Server内部资源使用机制 理解SQL Server数据可恢复性的基础 可以根据需求，合理的创建数据库，并进行配置,数据库基础结构,数据结构,数据库,数据文件 *.mdf(1) *.ndf(0-n),日志文件 *.ldf(1-n),MDF:主数据文件 NDF:非主数据文件,LDF:日志数据文件 一般情况下，一个 就够了,数据原子性、持久性 - 1,用户

3、发出数据修改请求 需要修改的数据是高选择性的（5行数据） 索引已经建立，并且能够用户搞笑的搜索到需要修改的数据 修改请求是简单的Update语句，并且并没有包含在任何的事务请求中,数据原子性、持久性 - 2,数据库服务器接收到数据修改请求，在缓存中找到需要修改的数据，或者将磁盘上需要修改的数据读取到缓存中 因为需要修改的数据是高选择性的，所以只有需要修改的数据页被读取到缓存中 假设我们需要修改的五行数据在两个页上面,数据原子性、持久性 - 2,数据库,数据,日志,数据缓存,数据原子性、持久性 - 3,数据库锁定需要修改的数据 锁被用来保证修改数据时的数据一致性 如果存在别的会话，锁定了我们需要

4、修改的数据，那么我们就需要等待，直到我们能够锁定住所有我们需要修改的数据 在我们的这个例子中，由于我们的数据是高选择性的，并且我们存在能够使用的索引，所以SQL Server将采用行级锁来进行数据锁定,数据原子性、持久性 - 3,SQL Server使用行级锁，但是也会使用“意向锁”进行更高级别的“意向”锁定 这次的事务将会使用如下的锁： 5个更新行级锁 3个意向互斥页级锁 1个意向互斥表级锁 1个共享数据库级锁 如果我们会访问索引，还会应用到其他的锁，我们暂时不去关注这些锁,数据原子性、持久性 - 3,缓存,数据页,数据页,数据行,数据行,数据行,数据行,数据行,更新锁,更新锁,更新锁,更新

5、锁,更新锁,数据原子性、持久性 - 4,在获得了所有的五个行更新锁后，SQL Server将开始去更新数据： 升级到更加严格的锁互斥锁 更新锁帮助我们获得更好的并发性能，他和共享锁不会互斥，所以能够在有更新锁的时候，请求共享锁 互斥锁在这里用来保证数据库不会返回脏数据 更新数据 （在缓存中进行更新） 将事务日志写入到日志页 （同样也是在缓存中）,数据原子性、持久性 - 3,缓存,数据页,数据页,数据行,数据行,数据行,数据行,数据行,互斥锁,更新锁,更新锁,更新锁,更新锁,更新锁,互斥锁,互斥锁,互斥锁,互斥锁,数据原子性、持久性 - 5,最后，事务完成： 所有行都已经修改 事务中已经没有别的

6、语句或任务 最终步骤： 将内存中日志页缓存同步到磁盘上 释放所有锁 返回事务执行结果（5行数据已经被更新） 这个里面使用了Write-Ahead Logging技术,关于检查点,为什么会有检查点 最新的日志文件总是在磁盘上 最新的数据在内存中 什么时候数据写入到磁盘上？ 检查点 Check Point 检查点触发时并不只是将已提交的页同步到磁盘上，而是将所有页（不管使用他们的事务的状态）写入到磁盘上,事务恢复和检查点,五个事务,检查点,服务器crash,1,2,3,4,5,重启恢复需要完成的动作,空,前滚,回滚,前滚,回滚,时间线,文件自动增长,配置数据库数据文件和日志文件自动增长 在创建数据

7、库之前，对数据量进行估算，按照估算，设置数据库文件和日志的初始大小 设置最大文件设置，特别是在多数据库使用同一个服务器的情况下，防止磁盘被完全占用 设置合理的自动增长值 1Gigabytes或者一下,数据文件,主要是读操作 大量用户随机读取 和日志文件分开来 优化读取性能 RAID 0+1， RAID 1+0或者RAID5 RAID 0+1 比RAID 1+0更快，但是后者更加安全可靠 RAID 5不常用，而且在写入操作时， 效率不高,数据文件优化,磁盘碎片整理 合理的RAID配置 预分配合理的文件大小 控制住文件自动增长 最重要的是合理的数据库设计,日志文件,有限的（很少量）的读取操作 数据

8、库复制技术 事务回滚 主要优化写操作 使用RAID 0+1或者RAID 1+0 尽量避免使用RAID 5 优化SQL Server的写日志操作 预分配合理的文件大小 清除虚拟日志文件 （VLF）,日志文件工作原理,VLF 1,VLF 2,VLF 3,VLF 4,VLF 5,活动的,非活动的,非活动的,非活动的,非活动的,在事务提交的时候，日志被顺序的写入到日志文件中 在一个VLF被写满后，日志的写入操作会自动的转入到下一个VLF中 自动增长日志文件操作会导致： 短时间的服务暂停 Windows扩展日志文件（可能导致磁盘碎片） 每次自动增长都会添加一个VLF,日志文件优化,预分配合理的文件大小

9、尽量减少自动增长 可能在日志备份后，日志并没有做到0使用率，但是合理的应该是在6%以下 在日志备份的时候，可能会导致数据库性能受到影响 考虑使用RAID 1+0， 而不是RAID 1或者RAID 5 尽量减少VLF数量,减少VLF的数量-1,数量过多的VLF会给跟日志相关操作造成性能上的损耗 日志记录 日志备份 数据库复制（replication） 应该少于50个 如果多余200，就应该考虑进行处理 （可以用工具例如BPA进行监控）,减少VLF的数量-2,运行DBCC LOGINFO 来获得VLF信息 这个语句返回的每一行数据表示一个VLF 如果超过了50个 用事务日志备份来清除日志空间 如果数据库采用的是Full或者Bulk_Logged的恢复设置，日志备份将会清除非活动的VLF的空间 如果采用的是Simple的恢复设置，日志文件将被清空,减少VLF的数量-3,压缩数据库日志 DBCC SHRINKFILE(logfilename, TRUNCATEONLY) 修改数据库，为日志文件设置一个更加合理的大小 ALTER DATABASE dbname MODIFY FILE (