第三章Databases and Database Files(3)

来源:互联网 发布:太空地图软件 编辑:程序博客网 时间:2022/05/27 13:11

《Microsoft Sql server 2008 Internals》读书笔记--目录索引

在上篇文章中,主要简单介绍了数据库的存放机制和snapshot的简单应用。下来我们看看一个基本的系统数据库Tempdb,关于tempdb,MSDN有详细的描述,

请参考;http://msdn.microsoft.com/zh-cn/library/ms190768.aspx

一些关于tempdb的基本常识:

 

view plaincopy to clipboardprint?
  1. tempdb 系统数据库是一个全局资源,可供连接到 SQL Server 实例的所有用户使用,并可用于保存下列各项:  
  2.     * 显式创建的临时用户对象,例如全局或局部临时表、临时存储过程、表变量或游标。  
  3.     * SQL Server 数据库引擎创建的内部对象,例如,用于存储假脱机或排序的中间结果的工作表。  
  4.     * 由使用已提交读(使用行版本控制隔离或快照隔离事务)的数据库中数据修改事务生成的行版本。  
  5.     * 由数据修改事务为实现联机索引操作、多个活动的结果集 (MARS) 以及 AFTER 触发器等功能而生成的行版本。  
  6. tempdb 中的操作是最小日志记录操作。这将使事务产生回滚。每次启动 SQL Server 时都会重新创建 tempdb,从而在系统启动时总是保持一个干净的数据库副本。在断开联接时会自动删除临时表和存储过程,并且在系统关闭后没有活动连接。因此 tempdb 中不会有什么内容从一个 SQL Server 会话保存到另一个会话。不允许对 tempdb 进行备份和还原操作。   
  7. tmpdb的主数据逻辑名为tempdev,文件名为tempdb.mdf,10%自增长到硬盘空间满。日志逻辑名为templog,文件名为templog.ldf,10%自增长到2TB。  
  8. tempdb 的大小可以影响系统性能。例如,如果 tempdb 的大小太小,则每次启动 SQL Server 时,系统处理可能忙于数据库的自动增长,而不能支持工作负荷要求。可以通过增加 tempdb 的大小来避免此开销。  
  9. 不能对 tempdb 数据库执行以下操作:  
  10.     * 添加文件组。  
  11.     * 备份或还原数据库。  
  12.     * 更改排序规则。默认排序规则为服务器排序规则。  
  13.     * 更改数据库所有者。tempdb 的所有者是 dbo。  
  14.     * 创建数据库快照。  
  15.     * 删除数据库。  
  16.     * 从数据库中删除 guest 用户。  
  17.     * 启用变更数据捕获。  
  18.     * 参与数据库镜像。  
  19.     * 删除主文件组、主数据文件或日志文件。  
  20.     * 重命名数据库或主文件组。  
  21.     * 运行 DBCC CHECKALLOC。  
  22.     * 运行 DBCC CHECKCATALOG。  
  23.     * 将数据库设置为 OFFLINE。  
  24.     * 将数据库或主文件组设置为 READ_ONLY。  

 


以上部分来自MSDN,现在补充一些:

1、tempdb和其他数据库最大的不同是:它每次在SQL Server启动时是被重建(Re-Create) 的, 而不是被复原(Recovered)的 。我们可以把它看成一个工作空间,有点像Eclipse或powerDesigner的工作空间(workspace),这个workspace存放了临 时的用户对象和内部对象(internal Objects),这些对象被SQL自身显式创建。

2、 每次tempdb被重建时,它会从model数据库继承大多数的数据库选项 ,但是还原模式下不会copy这些选项,因为 tempdb总是使用简单恢复(Simple recovery)。

3、在简单恢复模式(SIMPLE recovery model)下 ,tempdb的日志不断被清空,并且不能被恢复。因为每次SQL重新启动时,前一个用户创建的所有临时对象都消失了。

4、日志文件与其他日志不同,仅仅保存了一些用于回滚事务的必要信息,而不能恢复非事务以外的其他信息,

5、 还原一个正在运行的数据库的第一步,是创建快照。我们无法还原tempdb,是因为我们无法创建它的一个快照。 这意味着我们不能使用DBCC CheckDB选项,另外一个区别是DBCC收缩时,SQL Server将跳过所有的分配(Allocation)和分类(Catalog)检查。

tempdb的对象(Objects)

tempdb存储的对象包括:用户对象、内部对象和一些版本存储,主要是用于快照隔离。

用户对象(user Objects)包括所有以#或##开头的临时表,还有表变量(table variables)和表值函数(table-valued function) ,所有这些需要空间来物理存放。

内部对象(intenal Objects) 用正常的工具看不到,但他们仍然占用空间,这些对象不能被目录视图( catalog View) 列出,因为,它们的元数据是存放在内存中的 ,三种基本类型的内部对象是:工作表(work tables,工件文件(work files),排序单元(sort units)

在执行下列操作时工作表被SQL Server创建:

1、 执行一个大查询(large query)时后台存放中间结果。

2、运行DBCC CHECKDB或DBCC CHECKTABLE

3、与XML变量或Varchar(Max)一起工作时

4、处理数据库中问对象(Broker Objects)时

5、与静态或键集(keyset)的游标一起工作时

工作文件,在SQL Server处理一个哈希操作或关联/聚合查询时被使用。

排序单元在执行一个排序子句时被创建,排序单元保存了一些被排序的数据,通常是order by 或聚合操作的排序结果

版本存储(version store)提供了对Row-Level行级数据的支持,所有已更新的旧行在以下情况下被保留:

1、当一个Alter trigger被触发时

2、在一个允许快照事务的数据库中,当一个DML(Data Modification Language)语句被执行时

3、当一个MARS(multiple active result sets) 被客户端应用程序调用时。

4、创建一个在线索引或索引中有一个并发的DML语句时重建索引。

tempdb的优化

tempdb的优化可以参看MSDN:

 http://msdn.microsoft.com/zh-cn/library/ms175527.aspx

这里再补充一些:

1、tempdb有可能是生产环境中创建或删除新对象最多的数据库,如果可能,请采用多个数据文件的形式,这样.SQL Server会自动进行有效的空间分配。并会计划每个单独文件的自由空间。

2、如果需要删除(drop)一个工作表,或者一个小于8M的用户对象时,一个IAM和一个extent(范围)被存储,这会重新引起空间的再分配,这时需要借助tempdb的cache功能。

3、删除(drop)一个大表时,所有数据库的删除立即进行,不必线程等待,一个后台线程会清除所有分配给已删除表的空间,但此时tempdb的分配空间仍然未变。

 优化有最佳实践:

1、默认情况下,tempdb数据库创建时只有一个数据文件,你可能会发现,用多个文件会使你的I/O性能更佳,并且在全局分配结构(如GAM,SGAM,and PFS页)减少争夺(contention) ,一个推荐的初始化设置是每CPU一个Data File,不过,你最好根据自己的数据量和使用模式(usage pattern)作测试。为了取得计划填充算法的最佳效率,各个文件应该保持一致的大小 。多个文件的副作用是每个对象将拥有多个IAM页,这将增加访问该对象时切换成本。无论一个或多个文件,你都应该把数据文件放在最快的分区 。 一个日志文件足够,但也最好放在最快的分区。注意,邀月提醒:默认文件tempdb位于初始安装目录下,所以SQL 2008强烈不推荐安装在默认的X:/Program Files/Microsoft SQL Server/MSSQL10.AGRONET08/MSSQL/DATA/下,可以像我这样,G:/SQL2008 /MSSQL10.AGRONET08/MSSQL/DATA、这个G盘的性能是最佳的,呵呵。

2、 为了决定teempdb的最优大小。请结合你的数据量和应用程序做测试,但是知道tempdb什么时候和怎样被使用将有助于你作初步的评估。记住:每个SQL实例只有一个tempdb,一个很蹩脚的应用程序将会影响到所有其他应用程序中的所有用户。

3、不建议收缩tempdb数据库,特别是自动收缩选项将直接被忽略。收缩tempdb的最佳方式是alter database改变文件大小 ,然后停止并重启SQL Server以便tempdb被重建到完全的大小。你应该允许tempdb自增长以防止空间不够而出错,但文件的大小完全由自增长来控制是更错误的做法,文件的大小还是应该籍由计划测试得出。

下面是一些最佳做法的小提示:

  • 充分利用tempdb对象缓存
  • 事务尽可能的短,特别是使用事务隔离,MARS或触发器的事务。
  • 如杲你期望更多的分配页面争夺,强制一个查询计划更少的使用tempdb. 邀月对这句不理解? 原文是:If you expect a lot of allocation pages contention,force a query plan that uses tempdb less.
  • 通过保持列固定大小而不是可变大小,避免空间的分配和释放,例如update就比"一个delete紧跟一个insert"要好的多。
  • 在同一个数据库实例中,如果版本(versioning)被使用,不要混合位于不同数据库中的长事务与短事务。

tempdb的空间监测

只有一个视图可以用于tempdb的空间监测,sys.dm_db_file_space_usage

 小结:tempdb是一个很重要也很独特的系统数据库,本文对其基本特性作了一些描述,后面的章节将会继续提到相关的内容。

下面一章将是第五章Tables,更具有实战意义的一章。

邀月注:本文版权由邀月和CSDN共同所有,转载请注明出处。 
助人等于自助!   3w@live.cn