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

最新推荐文章于 2012-12-18 06:28:52 发布

cuide198456

最新推荐文章于 2012-12-18 06:28:52 发布

阅读量90

点赞数

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

建议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

DCM

BCM

…

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/355374/viewspace-683165/