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

Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,开始,睿智汇海,SQL Server,免费培训课程,讲师：顾劲鹏,课程大纲,第一天,:,创建可恢复的数据库,第二天,:,创建可靠的自动的备份机制,第三天,:,表设计，数据类型设计,第四天,:,数据库索引最佳实践,第五天,:,混合负载数据库，锁和隔离级别机制,第六天,:,执行计划缓存和存储过程性能优化,第七天,:,数据加载，表及索引分区,第八天,:,数据性能监控调优,进度,第一天,:,创建可恢复的数据库,第二天,:,创建可靠的自动的备份机制,第三天,:,表设计，数据类型设计,第四天,:,数据库索引最佳实践,第五天,:,混合负载数据库，锁和隔离级别机制,第六天,:,执行计划缓存和存储过程性能优化,第七天,:,数据加载，表及索引分区,第八天,:,数据性能监控调优,本次课程内容,数据结构,数据修改的原子性,数据文件和日志文件,优化数据库中的数据文件,事务和日志,优化数据库中的日志文件,数据库运行环境,数据恢复模式,学习目标,了解,SQL Server,的基础结构,了解,SQL Server,的运行原理,了解,SQL Server,内部资源使用机制,理解,SQL Server,数据可恢复性的基础,可以根据需求，合理的创建数据库并进行配置,数据库基础结构,数据结构,数据库,数据文件,*.mdf(1),*.ndf(0-n),日志,文件,*.ldf(1-n),MDF:,主数据文件,NDF:,非主数据文件,LDF:,日志数据文件,一般情况下，一个,就够了,数据原子性、持久性,-1,用户发出数据修改请求,需要修改的数据是高选择性的（,5,行数据）,索引已经建立，并且能够用户搞笑的搜索到需要修改的数据,修改请求是简单的,Update,语句，并且并没有包含在任何的事务请求中,数据原子性、持久性,-2,数据库服务器接收到数据修改请求，在缓存中找到需要修改的数据，或者将磁盘上需要修改的数据读取到缓存中,因为需要修改的数据是高选择性的，所以只有需要修改的数据页被读取到缓存中,假设我们需要修改的五行数据在两个页上面,数据原子性、持久性,-2,数据库,数据,日志,数据缓存,数据原子性、持久性,-3,数据库锁定需要修改的数据,锁被用来保证修改数据时的数据一致性,如果存在别的会话，锁定了我们需要修改的数据，那么我们就需要等待，直到我们能够锁定住所有我们需要修改的数据,在我们的这个例子中，由于我们的数据是高选择性的，并且我们存在能够使用的索引，所以,SQL Server,将采用行级锁来进行数据锁定,数据原子性、持久性,-3,SQL Server,使用行级锁，但是也会使用“意向锁”进行更高级别的“意向”锁定,这次的事务将会使用如下的锁：,5,个更新行级锁,3,个意向互斥页级锁,1,个意向互斥表级锁,1,个共享数据库级锁,如果我们会访问索引，还会应用到其他的锁，我们暂时不去关注这些锁,数据原子性、持久性,-3,缓存,数据页,数据页,数据行,数据行,数据行,数据行,数据行,更新锁,更新锁,更新锁,更新锁,更新锁,数据原子性、持久性,-4,在获得了所有的五个行更新锁后，,SQL Server,将开始去更新数据：,升级到更加严格的锁,互斥锁,更新锁帮助我们获得更好的并发性能，他和共享锁不会互斥，所以能够在有更新锁的时候，请求共享锁,互斥锁在这里用来保证数据库不会返回脏数据,更新数据（在缓存中进行更新）,将事务日志写入到日志页（同样也是在缓存中）,数据原子性、持久性,-3,缓存,数据页,数据页,数据行,数据行,数据行,数据行,数据行,互斥锁,更新锁,更新锁,更新锁,更新锁,更新锁,互斥锁,互斥锁,互斥锁,互斥锁,数据原子性、持久性,-5,最后，事务完成：,所有行都已经修改,事务中已经没有别的语句或任务,最终步骤：,将内存中日志页缓存同步到磁盘上,释放所有锁,返回事务执行结果（,5,行数据已经被更新）,这个里面使用了,Write-Ahead Logging,技术,关于检查点,为什么会有检查点,最新的日志文件总是在磁盘上,最新的数据在内存中,什么时候数据写入到磁盘上？,检查点,Check Point,检查点触发时并不只是将已提交的页同步到磁盘上，而是将所有页（不管使用他们的事务的状态）写入到磁盘上,事务恢复和检查点,五个事务,检查点,服务器,crash,1,2,3,4,5,重启恢复需要完成的动作,空,前滚,回滚,前滚,回滚,时间线,文件自动增长,配置数据库数据文件和日志文件自动增长,在创建数据库之前，对数据量进行估算，按照估算，设置数据库文件和日志的初始大小,设置最大文件设置，特别是在多数据库使用同一个服务器的情况下，防止磁盘被完全占用,设置合理的自动增长值,1Gigabytes,或者一下,数据文件,主要是读操作,大量用户随机读取,和日志文件分开来,优化读取性能,RAID 0+1,，,RAID 1+0,或者,RAID5,RAID 0+1,比,RAID 1+0,更快，但是后者更加安全可靠,RAID 5,不常用，而且在写入操作时，效率不高,数据文件优化,磁盘碎片整理,合理的,RAID,配置,预分配合理的文件大小,控制住文件自动增长,最重要的是合理的数据库设计,日志文件,有限的（很少量）的读取操作,数据库复制技术,事务回滚,主要优化写操作,使用,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,日志文件优化,预分配合理的文件大小,尽量减少自动增长,可能在日志备份后，日志并没有做到,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,(NAME=name,SIZE=new_size),日志文件优化,将事务日志文件安放在独立的磁盘上,磁盘碎片整理,合理的,RAID,配置,预分配合理的大小,不要让自动化增长失去控制,总结,数据库结构,数据修改的原子性,数据文件和日志文件,优化数据文件,事务和日志,优化日志文件,数据库恢复基础原理,现场交流,