转：SQl大数据量操作效率以及日志测试

/*--SQL   Server   中的最小日志记录操作

原作: happydreamer(小黑)

--整理..邹建--2003.12--*/


/*--控制数据库恢复选项

ALTER   DATABASE   数据库名   SET   RECOVERY   <RECOVERY_OPTIONS>

<RECOVERY_OPTIONS> 的取值:   FULL   |   BULK_LOGGED   |   SIMPLE  

FULL: 系统将对介质错误提供完全保护。
如果数据文件损坏，介质恢复可以还原所有已提交的事务。  

BULK_LOGGED: 将在介质错误保护程度与某些大规模或大容量操作
的最优性能及日志存储空间最少占用量之间进行权衡。
这些操作包括:
SELECT   INTO、
大容量装载操作（bcp   和   BULK   INSERT）、CREATE   INDEX
文本和图象操作（WRITETEXT   和   UPDATETEXT）。

在大容量日志记录恢复模型下，对整个类只做最少的日志记录，
并且无法逐个操作地控制日志记录行为。

SIMPLE: 系统将提供占用日志空间最小的备份策略。
服务器故障恢复不再需要的日志空间可被自动重用。

重要     简单恢复模型比其它两种模型更容易管理，
但数据文件损坏时造成数据丢失的可能性更大。
最近的数据库备份或差异数据库备份之后的更改都将丢失，必须手工重新输入。

默认恢复模型由   model   数据库的恢复模型确定。
若要更改新数据库的默认值，请用   ALTER   DATABASE   设置   model   数据库的恢复选项。
--*/

/*--
三种恢复模型对这些操作记录日志情况比较

使用了一个未公开的函数:   fn_dblog
可以看日志文件的记录数
用法:select   count(*)   from   ::fn_dblog(null,null)  
--*/

--1.简单恢复模型

--创建测试数据库
use   master
go

create   database   test
go

--打开创建的测试数据库
use   test
go

--设置恢复模型为:简单模型
alter   database   test   set   recovery   simple

--插入数据测试
select   top   10000   a.*   into   testtable  
from   master..sysobjects   a   ,   master..syscolumns   b

--查看日志文件中的记录数
select   count(*)   from   ::fn_dblog(null,null)    

--事务日志空间使用情况
dbcc   sqlperf(logspace)
go

--删除测试的数据库
use   master
drop   database   test
go


--2.大容量日志记录恢复模型
/*--说明
    这些大容量复制操作的数据丢失程度要比完全恢复模型严重，
    不允许恢复部分的BULK操作，只能全部恢复
--*/

--创建测试数据库
use   master
go

create   database   test
go

--打开创建的测试数据库
use   test
go

--设置恢复模型为:大容量日志记录
alter   database   test   set   recovery   BULK_LOGGED  

--插入数据测试
select   top   10000   a.*   into   testtable   from   master..sysobjects   a   ,   master..syscolumns   b

--查看日志文件中的记录数
select   count(*)   from   ::fn_dblog(null,null)    

--事务日志空间使用情况
dbcc   sqlperf(logspace)
go

--删除测试的数据库
use   master
drop   database   test
go

--3.完全恢复模型
/*--说明
    记录的日志数要比大容量日志记录恢复模型多，
    将记录每一条BCP   or   BULK   insert,create   index   的每一条index   row,
    对image,text字段   WRITETEXT   or   UPDATETEXT的所有操作
--*/

--创建测试数据库
use   master
go

create   database   test
go

--打开创建的测试数据库
use   test
go

--设置恢复模型为:完全恢复模型
alter   database   test   set   recovery   full  

--插入数据测试
select   top   10000   a.*   into   testtable   from   master..sysobjects   a   ,   master..syscolumns   b

--查看日志文件中的记录数
select   count(*)   from   ::fn_dblog(null,null)    

--事务日志空间使用情况
dbcc   sqlperf(logspace)
go

--删除测试的数据库
use   master
drop   database   test
go

/*--我的电脑上测试结果

恢复模型 速度 日志记录数 日志的可用空间 日志信息占用的百分比(%)
---------------   -------   -----------   ---------------   ------------------------
简单模型 <=1秒 385 0.484375 46.673386
大容量日志记录 <=1秒 408 0.484375 47.177418
完全恢复模型 9秒 560 1.9921875 85.416664

--*/

/*--结论

恢复模型 速度 日志大小 安全性
---------------   -------   -----------   ---------
简单模型 快 很 低
大容量日志记录 快 小 较低
完全恢复模型 慢 大 高
--*/