ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL Server 存储内幕系列之一:基础知识

SQL Server 存储内幕系列之一:基础知识

原创 Linux操作系统 作者:bigholy 时间:2011-01-02 21:16:53 0 删除 编辑

在学习查询调优知识时,有必要对数据页和不同类型的索引页加以理解.这有助于对基本概念的更好理解以及日后更好地性能调优.这里采用"一步一步"的教程与初学者分享我的知识.

建议SQL Server DBA花一些时间学习不同类型页的结构,这有助于DBA在运行DBCC CHECKDB而得到的警告信息可以更好的做出判断或提供更好的调优方案等等。

在写本文时,不可避免地要参考一些未归档的命令,强烈建议初学者在本地或开发环境进行脚本的测试。对于不熟悉SQL Server 2005/2008系统视图中的系统表的初学者,可以参考“Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views”。掌握这些系统视图使你的日常工作变得容易。

本篇内容适用于了解SQL Server存储内幕的初学者,长话短说,下面我们进入示例。

首先我们需要创建一个数据库,这里取名为LearningInternals,脚本如下:

CREATE DATABASE LearningInternals 
GO
USE LearningInternals GO

知识点:数据文件和日志文件

一个数据库有3种文件类型:

1. 主数据文件(扩展名为.MDF)-每个数据库有一个主数据文件,如果没有辅助数据文件时,所有用户表均存储在PRIMARY文件组

2. 辅助数据文件(扩展名为.NDF)-每个数据库可以有0个或多个辅助数据文件,这有助于提高性能和有效管理表。

3. 事务日志文件(扩展名为.LDF)-默认数据库有一个日志文件,用于记录表的记录修改历史,有助于数据的备份和灾难恢复

下面的命令是查询并列出当前数据库所有的文件组信息(包括主文件、辅助文件信息)

select * from sys.filegroups 
go

以下查询将列出与其相关的所有文件的信息:

select database_id, file_id, type, type_desc, name, physical_name, state_desc, size, is_read_only, backup_lsn, differential_base_guid from sys.master_files where DB_NAME(database_id) = 'LearningInternals'
go

提示:

   建议将数据文件和日志文件存储在不同的驱动器上,这有助于提高性能,此外不要把TEMPDB数据库与用户数据库存储在同一磁盘。

 

在继续学习之前,需要了解一些关键知识点:

页面(Page)----SQL Server中数据存储的基础单位,数据库创建后,主文件和辅助文件中的数据空间逻辑上被划分为8K的数据页。页号引用数据文件号,例如:主文件组的第1页命名为1:0(这里的1表示文件组,0表示页号)。

盘区(Extent)----盘区由8个数据页组成(每页8K,即64KB),可以存储1到8个表。

    有2种类型的盘区:

     1)统一盘区(Uniform),所有8个数据页分配给一个对象,如表或索引;

     2)混合盘区(Mixed),可以存储表和索引

当创建表,然后插入一行数据时,表就从混合盘区中获取一个页面,随着表中记录的增加,表由混合盘区移入统一盘区,这样做有助于空间得到有效的管理。

为方便起见,逻辑上我们把页划分为如下:

1. 系统页-存储引擎用于管理用户数据

2. 数据页或索引页-用户创建的表或索引

系统表:

 

序号 页面 描述
1 Header页 存储数据文件的详细信息,如大小、类型等等
2 GAM Global Allocation Map,GAM是数据文件的第3页(即1:2),一个GAM可以容纳4GB的数据空间,超过4GB,则会产生另一个GAM
3 SGAM Shared Global Allocation Map.SGAM是数据文件的第4页(即1:3),一个SGAM可以容纳4GB的数据空间,超过4GB,则会产生另一个SGAM。
4 IAM Index Allocation Map,IAM页可以找出与某表关联的页面(对某表来说,如果是分区表或表中存储了overflow页(LOB数据等)则可能有多个IAM
5 PFS Page Free Space.PFS表示每个页面上可用的空间,每页面代表一个字节,对于8000个页面,仅只有一个PFS。
6 DCM Differential Change Map.
7 BCM Bulk change Map.

数据页和索引页

序号 页面 描述
1 数据页 容纳记录行,当执行INSERT语句时,PFS找出保存该记录的正确页
2 行溢出页 自SQL2005以来,可以使用4个长度为5000的varchar字段,也就是说不受每行最大8000个字节的限制,允许行溢出页,该页仅用于记录大小超过8000字节
3 大对象LOB LOB数据,如text,varchar(max)页面,这些页面则会与数据页或索引页分开存储

 

数据库创建时,数据文件大致包含如下的页面结构:

Page Header PFS GAM SGAM Unused Unused DCM BCM Data Data Data Data Data Data Data
Page
No.

1:0

1:1 1:2 1:3 1:4 1:5 1:6 1:7 1:15001 1:15002 1:15003 1:15004 1:15005 1:15006 1:15007

 

下面的查询列出所有表与其表对应的页面信息:

select so.name, so.object_id, sp.index_id, sp.partition_id, sp.hobt_id, sa.container_id, internals.total_pages, internals.used_pages, internals.data_pages, first_page, root_page, first_iam_page from sys.objects so inner join                  on so.object_id = sp.object_id inner join sys.allocation_units sa on sa.container_id = sp.hobt_id inner join sys.system_internals_allocation_units internals on internals.container_id = sa.container_id where so.name not like 'sys%' 

小结:

   当数据库创建时,默认会有一个主数据文件和一个日志文件。数据文件逻辑上分成数百个8K的页面,一些与系统相关的页面,如GAM,SGAM,IAM和PFS用于管理数据页。

PS:以上源自http://www.sqlservercentral.com/blogs/livingforsqlserver/archive/2010/12/13/sql-server-storage-internals-part-1-basics.aspx,如有翻译不当,请指正!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/355374/viewspace-683165/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-08

  • 博文量
    43
  • 访问量
    83541