sql server中的哪些对象会占用磁盘空间? 看到标题的第一瞬间，让我想到的就是这个问题。下面我们就试着来讲一讲这个问题. 第一个磁盘空间使用大头肯定想到就是表。表只是一个逻辑对象，又没有想过表这个逻辑对象是怎么在磁盘上存储的呢? 《数据库系统实现原
sql server中的哪些对象会占用磁盘空间? 看到标题的第一瞬间，让我想到的就是这个问题。下面我们就试着来讲一讲这个问题.
第一个磁盘空间使用大头肯定想到就是表。表只是一个逻辑对象，又没有想过表这个逻辑对象是怎么在磁盘上存储的呢? 《数据库系统实现原理》或者叫做《database system implementation》一书中对表的存储方式应该有更详尽的描述。我们只讨论sql server的实现，所以不扯那么远。
sql server的空间分配，大的层面上来说，有file group, data file, log file之分。file group是逻辑上对data file和log file做分类。假设我们要新建一个database， 叫做lenistest。这个database 我们要分别将data file和log file归类到不同的file group里面，方便管理与维护。主要区别的是 primary file group和secondary file group,也就是 .mdf和.ndf的区别。
create database [lenistest5]onprimary( name = n'lenistest5',filename = n'e:\data_bu\lenistest5.mdf' ,size = 10240kb ,maxsize = 102400kb ,filegrowth = 1024kb ), filegroup maindatagroup( name = n'lenistest5_data01',filename = n'e:\data_bu\lenistest5_data01.ndf' ,size = 10240kb ,maxsize = 102400kb ,filegrowth = 1024kb ), filegroup backupdatafg( name = n'lenistest5_bk_data01',filename = n'e:\data_bu\lenistest5_bk_data01.ndf' ,size = 10240kb ,maxsize = 10240kb ,filegrowth = 1024kb )log on( name = n'lenistest5_log',filename = n'e:\data_bu\lenistest5_log.ldf' ,size = 10240kb ,maxsize = 10240kb ,filegrowth = 1024kb )go
用上面的这个sql我们可以创建一个具有3个data file group, 和1个log file group的数据库 lenistest5 。.mdf全局唯一 ，不能有多个.mdf文件，但是可以有多个.ndf文件。我们是不是可以看到.mdf到底存储了什么?
select name,recovery_model_desc,is_auto_create_stats_on,is_auto_create_stats_incremental_on,is_auto_update_stats_on,is_auto_update_stats_async_onfrom sys.databases where name = 'lenistest5'
这里可以看到刚创建的数据库有怎么样的恢复计划，这直接影响了日志的存储，还有统计信息更新计划，同样也会影响存储，更会影响执行计划的优劣，所以这也是需要创建数据后核实的。
select name as filegroupname,data_space_id,type_desc,is_defaultfrom sys.filegroupsselect type_desc,data_space_id,name,physical_name,state_desc,size * 8 /1024 as size_mb,max_size * 8 /1024 as max_size_mbfrom sys.database_files
sys.filegroups, sys.database_files是归属于特定数据库的，所以运行的时候需要切换到特定的数据库底下。不象有些dmv是全局性的，不需要指定数据库，在任何数据库根目录下，都能查到一致性的数据，比如 sys.dm_tran_locks.
is_default这里需要特别指出来 ，使因为如果在create table之后没有指定特别的file group，默认这个表就是存在这个file group之下。如果要更改这个default file group，我们可以这么做:
alter database lenistest5modify filegroup maindatagroup default
size, max_size是以page为单位来计算的。一个page的存储大小为8kb ，所以计算起来就是乘以8 ，再除以1024换成mb。
selectisnull(g.filegroupname,'log file group') as filegroupname, isnull(g.type_desc,'log file group') as filegroup_type_description, isnull(g.is_default,0) as defaultfilegroup, f.type_desc as datafile_type_description, f.name as filename, f.physical_name as file_physical_name, f.state_desc as datafilestatus, f.size_mb as datafile_size_mb, f.max_size_mb as datafile_max_size_mbfrom (select name as filegroupname,data_space_id,type_desc,is_defaultfrom sys.filegroups) gright outer join (select type_desc,data_space_id,name,physical_name,state_desc,size * 8 /1024 as size_mb,max_size * 8 /1024 as max_size_mbfrom sys.database_files) f on g.data_space_id = f.data_space_idorder by f.data_space_id asc
将 filegroup 包含的所有 data file归纳起来，包括日志文件 。日志文件没有filegroup.
我们看看当新建一个表的时候，表结构及数据的存储:
create table dbo.sales(transactiondate datetime, amont int)
看表数据存储需要借助 dbcc ind 和 dbcc page. 默认情况下，我们执行这些 dbcc 命令， 输出文件不是我们的ssms console,所以需要将输出重定位，dbcc traceon(3604)可以帮我们把带输出的dbcc命令将结果输出到ssms console；dbcc traceon(3605)可以帮我们把带输出的dbcc命令将结果输出到sql server error log。这里我们选用dbcc tranceon(3604). 命令的有效范围是当前session, 需要关掉的话用dbcc traceoff(3604).
dbcc traceon(3604)dbcc ind(lenistest5,'dbo.sales',0)
当表里没有数据的时候，dbcc ind 是没有数据的，所以只显示:
dbcc execution completed. if dbcc printed error messages, contact your
system administrator.
dbcc ind 的语法是：
dbcc ind ( {dbname}, {table_name},{index_id} )
index_id为0的时候，表示取的是堆表的信息，其他数值，等同于sys.indexes.index_id.
返回结果所包含的列有:
pagefid: page file id. 数据页所在的数据文件的地址。也就是sys.database_files.file_id 的值。
pagepid: page id
iamfid: index allocation map file id. 等同 sys.database_files.file_id.
iampid: index allocation map page id
pagetype : 注明了这个page的用途 :
1 - data page
2 - index page
3 - large object page
4 - large object page
8 - global allocation map page
9 - share global allocation map page
10 - index allocation map page
11 - page free space page
13 - boot page
15 - file header page
16 - differential changed map page
17 - bulk changed map page
其他字段比较容易理解。
既然知道了这一个页，比如iampid， 那我们就可以知道这个页到底存了哪些东西，还可以比较iam page 与普通page的异同。 甚至还可以比较gam, iam, sgam的不同，这放以后讨论。现在我们的表里暂时只有一条数据，所以总共才2个page. 一个iam page,一个data page. 真好用来做比较。要想看一个page的存储内容，dbcc page就该上场了。用法如下：
dbcc page( {dbid|dbname}, pagenum [,print option] [,cache] [,logical] )
也有的是这么介绍的，毕竟这是非官方支持的命令，所以都试试
dbcc page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
the filenum and pagenum parameters are taken from the page ids that come from various system tables and appear in dbcc or other system error messages. a page id of, say, (1:354) has filenum = 1 and pagenum = 354.
the printopt parameter has the following meanings:
0 – print just the page header
1 – page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn’t have one, like allocation bitmaps)
2 – page header plus whole page hex dump
3 – page header plus detailed per-row interpretation
filenum: 对应了dbcc ind结果集里的 pagefid, 数据文件的 id
pagenum：对应了 dbdd ind 结果集里的 pagepid, 数据页的 id
printopt:
0: page头文件信息
1: page头文件信息，加上每一行的16进制信息
2: page头文件信息，加上每一页的16进制信息
3: page头文件信息，加上详细的每一页的每一行的解释信息
似乎这里第二种写法比较靠谱:
dbcc page (lenistest5, 3,9,3)
page: (3:9)
buffer:
buf @0x0000000484e524c0
bpage = 0x00000003f348c000 bhash = 0x0000000000000000 bpageno = (3:9)
bdbid = 35 breferences = 0 bcputicks = 0
bsamplecount = 0 buse1 = 15680 bstat = 0xb
blog = 0x1212121c bnext = 0x0000000000000000
page header:
page @0x00000003f348c000
m_pageid = (3:9) m_headerversion = 1 m_type = 10
m_typeflagbits = 0x0 m_level = 0 m_flagbits = 0x0
m_objid (allocunitid.idobj) = 120 m_indexid (allocunitid.idind) = 256
metadata: allocunitid = 72057594045792256
metadata: partitionid = 72057594040549376 metadata: indexid = 0
metadata: objectid = 245575913 m_prevpage = (0:0) m_nextpage = (0:0)
pminlen = 90 m_slotcnt = 2 m_freecnt = 6
m_freedata = 8182 m_reservedcnt = 0 m_lsn = (35:193:15)
m_xactreserved = 0 m_xdesid = (0:0) m_ghostreccnt = 0
m_tornbits = 0 db frag id = 1
allocation status
gam (3:2) = allocated sgam (3:3) = allocated
pfs (3:1) = 0x70 iam_pg mixed_ext allocated 0_pct_full diff (3:6) =
changed
ml (3:7) = not min_logged
iam: header @0x0000000012dfa064 slot 0, offset 96
sequencenumber = 0 status = 0x0 objectid = 0
indexid = 0 page_count = 0 start_pg = (3:0)
iam: single page allocations @0x0000000012dfa08e
slot 0 = (3:8) slot 1 = (0:0) slot 2 = (0:0)
slot 3 = (0:0) slot 4 = (0:0) slot 5 = (0:0)
slot 6 = (0:0) slot 7 = (0:0)
iam: extent alloc status slot 1 @0x0000000012dfa0c2
(3:0) - (3:1272) = not allocated
dbcc execution completed. if dbcc printed error messages, contact your
system administrator.
有这么一行需要特别注意的:
iam: single page allocations @0x0000000012dfa08e
slot 0 = (3:8)
这是说明iam page 这一页记录了他所能管辖的数据页的分配，slot 0 =(3:8). 8就代表了data page id =8 .
而下面这一行，代表的就是iam page所在的page id
page @0x00000003f348c000
m_pageid = (3:9)
比较下data page 与 iam page 的不同：
dbcc page (lenistest5, 3,8,3)
page: (3:8)
buffer:
buf @0x0000000484e53d80
bpage = 0x00000003f34aa000 bhash = 0x0000000000000000 bpageno = (3:8)
bdbid = 35 breferences = 0 bcputicks = 0
bsamplecount = 0 buse1 = 16691 bstat = 0xb
blog = 0x212121cc bnext = 0x0000000000000000
page header:
page @0x00000003f34aa000
m_pageid = (3:8) m_headerversion = 1 m_type = 1
m_typeflagbits = 0x0 m_level = 0 m_flagbits = 0x8000
m_objid (allocunitid.idobj) = 120 m_indexid (allocunitid.idind) = 256
metadata: allocunitid = 72057594045792256
metadata: partitionid = 72057594040549376 metadata: indexid = 0
metadata: objectid = 245575913 m_prevpage = (0:0) m_nextpage = (0:0)
pminlen = 16 m_slotcnt = 1 m_freecnt = 8075
m_freedata = 115 m_reservedcnt = 0 m_lsn = (35:193:28)
m_xactreserved = 0 m_xdesid = (0:0) m_ghostreccnt = 0
m_tornbits = 0 db frag id = 1
allocation status
gam (3:2) = allocated sgam (3:3) = allocated
pfs (3:1) = 0x61 mixed_ext allocated 50_pct_full diff (3:6) = changed
ml (3:7) = not min_logged
slot 0 offset 0x60 length 19
record type = primary_record record attributes = null_bitmap record
size = 19
memory dump @0x000000001af5a060
0000000000000000: 10001000 bb7d7701 10a60000 01000000 020000
….?}w..|………
slot 0 column 1 offset 0x4 length 8 length (physical) 8
transactiondate = 2016-05-24 22:47:07.290
slot 0 column 2 offset 0xc length 4 length (physical) 4
amont = 1
这页存储的数据一目了然，而且数据类型，字节大小都明白的告诉我们了：
slot 0 column 1 offset 0x4 length 8 length (physical) 8
transactiondate = 2016-05-24 22:47:07.290
slot 0 column 2 offset 0xc length 4 length (physical) 4
amont = 1
到这里我们已经可以用脚本来归纳所有file group, data file,以及table ,index的对应关系了：利用 dbcc ind来获取整个数据库 表和索引的文件对应关系。还有一种方法，使用新增加的dmc来查询，这个dmv是 sys.dm_db_database_page_allocations.分清楚表和索引的存储关系，不仅仅是方便管理，更有利于性能的提高，表和索引分别存储在不同的硬盘驱动器上，有利于并行处理。
use lenistest4godeclare @tablename varchar(200)declare @index_id intdeclare @sqlstatement nvarchar(max)declare @databasename varchar(200) ='lenistest4'declare cur_tables cursorfor (select schema_name(schema_id) +'.'+name as tablenamefrom sys.tables )open cur_tablesfetch next from cur_tables into @tablenameif exists( select 1 from tempdb.sys.tables where upper(name) like upper('%temptabindall%') )drop table #temptabindall ;create table #temptabindall(pagefid bigint, pagepid bigint, iamfid bigint, iampid bigint, objectid bigint, indexid bigint, partitionnumber bigint, partitionid bigint,iam_chain_type varchar(500) , pagetype bigint, indexlevel bigint, nextpagefid bigint, nextpagepid bigint,prevpagefid bigint, prevpagepid bigint)create index idx_pagefid on #temptabindall(pagefid) ;while @@fetch_status = 0begindeclare cur_indexes cursor for(select index_id from sys.indexes where object_id = object_id(@tablename))open cur_indexesfetch next from cur_indexes into @index_idwhile @@fetch_status = 0beginset @sqlstatement = n'insert into #temptabindallexec sp_executesql n''dbcc ind(' + @databasename + ','''''+@tablename+''''',' + convert(varchar(max),@index_id)+')''' ;print @sqlstatementexec sp_executesql @sqlstatementfetch next from cur_indexes into @index_idendclose cur_indexesdeallocate cur_indexesfetch next from cur_tables into @tablenameendclose cur_tablesdeallocate cur_tablesselect distinctobject_name(t.objectid) as tablename, t.indexid, ti.name as indexname, f.filegroupname, f.filegroup_type_description, f.defaultfilegroup, f.datafile_type_description, f.filename, f.file_physical_namefrom #temptabindall tinner join (select distinct object_id,index_id,name from sys.indexes) ti on t.objectid = ti.object_id and t.indexid = ti.index_idleft join (selectisnull(data_file_id,0 ) as data_file_id, isnull(g.filegroupname,'log file group') as filegroupname, isnull(g.type_desc,'log file group') as filegroup_type_description, isnull(g.is_default,0) as defaultfilegroup, f.type_desc as datafile_type_description, f.name as filename, f.physical_name as file_physical_name, f.state_desc as datafilestatus, f.size_mb as datafile_size_mb, f.max_size_mb as datafile_max_size_mbfrom (select name as filegroupname,data_space_id,type_desc,is_defaultfrom sys.filegroups) gright outer join (selectfile_id as data_file_id,type_desc,data_space_id,name,physical_name,state_desc,size * 8 /1024 as size_mb,max_size * 8 /1024 as max_size_mbfrom sys.database_files) f on g.data_space_id = f.data_space_id)f on f.data_file_id = t.pagefidorder by f.file_physical_name asc ,object_name(t.objectid) asc, t.indexid asc