原文:
《SQL Server企业级平台管理实践》读书笔记——SQL Server数据库文件分配方式
1、文件分配方式以及文件空间检查方法
最常用的检查数据文件和表大小的命令就是：sp_spaceused
此命令有三个缺陷：1、无法直观的看出每个数据文件和日志文件的使用情况。2、这个存储过程依赖SQL Server存储在一些系统视图里的空间使用统计信息计算出的结果，如果没有更新空间统计信息，比如刚刚发生大数据插入，sp_spaceused的结果就不准确。3、这个命令主要是针对普通用户的数据库，对于tempdb数据库里存储的一些系统临时数据对象，是无法用这个存储过程来统计的。
可以通过“sp_helptext sp_spaceused”命令查看该存储过程明细。
SQL Server的文件分为数据文件（.mdf,.ndf)和日志文件（.ldf)两种。不同的文件查看方式不同。
数据内容以页形式存储，可以通过DBCC PAGE命令查看页内容
DBCC PAGE(<db_id>,<file_id>,<page_id>,<forma_id>)
比如我们要看AdventureWorks这个数据库里面的AdventureWorks_Data数据文件里的第3230页。我们先从db_id可以从sp_helpdb的结果中得到；file_id可以从sp_helpfile的结果中得到。
Format_id是指定的输出格式，有1、2、3三个值。一般来讲，3这个输出格式比较直观。在运行DBCC PAGE之前，还需要打开跟踪标识（trace flag）3604.
我们来查看明细：
这里有几个信息比较重要，比如ObjectID=133575514、IndexId=1
它告诉我们，这个页面属于133575514这个对象，ID为1的索引（index)。通过下列语句可以得到它属于什么对象。
select s.name,o.name
from sys.sysobjects o
inner join sys.schemas s
on o.uid=s.schema_id
where o.id=133575514
go
select id,indid,name
from sys.sysindexes
where id=133575514 and indid=1
go
exec sp_helpindex N'DimProduct'
go
我们可以看到此页面属于DimProduct表、然后这个表下面的索引：PK_DimProduct_ProductKey，这个索引建立在ProductKey列上
可以通过下面的命令来查看数据库中某张表有那些区，该区中第一个页面的ID是多少：
上面的这张表用到了这么多区的内容，并且每个区中大部分存储为8个的连续页，只有第一个为1个页，页面的ID为217。
大型行的支持
在SQL Server中，行不能跨页，属于同一行的所有字段的数据都要放在同一个页面里，页的最大数据量是8064B(8KB)。所以一般数据类型字段所组成的一行，最长加起来不能超过8KB，但这个限制不包括varchar(max)、nvarchar(max)、varbinary(max)这样的数据类型，在2005以后版本中这种大类型数据字段突破了8KB的限制，方便了用户的使用，如果不超过8KB,数据还是会一起存放在普通数据页里面。如果总长度超过了8KB,SQL Server就会把这些字段分开，单独存放在一种叫做Row-Overflow(行溢出）的页面里。
数据文件空间使用的计算方法
查看数据库使用的最简单的方法，就是在Management Studio中，右键点击数据库名称，选择报表项中的标准报表进行查看：
这里的统计结果和sp_spaceused统计的结果稍有不同，原因是这里是按照区统计的，由于每个区里面就有8个页，而这8个页不一定全部被使用到了，但是如果按照区统计默认的就是全部被使用了，而按照页统计的结果就不一样了。
两种统计方法不同，使用场景也不一样，按照区统计结果速度快
按照区统计：
如果按照区统计我们使用一个命令就可以实现
由于SQL Server在绝大多数时间都是按照区为单位进行分配新空间的，而系统分配页上的信息永远是实时更新的，所以按照这种统计方法比较准确可靠。在服务器负载很高的情况下也能安全执行，不会增加额外的系统负担。所以要考察数据库数据文件级的使用情况，它是个比较好的选择。
按照页面统计
如果想要知道某个具体的表格或者索引使用了多少空间，就要从页面级别进行分析。这里我们就可以选择sp_spaceused或者DBCC SHOWCONTIG
当然在SQL Server2005以后新加了一个sys.dm_db_index_physical_stats来查看索引的存储明细，包括页多少，锁片百分比等
sp_spaceused是根据sys.allocation_units和sys.partitions这两张管理视图来计算存储空间的。当删除索引后、表的空间信息不实当前信息时，这两张表可能不能及时反映出数据库的准确信息。
可以加入Undateusage这个参数，要求SQL Server为这句指令更新管理视图里的统计信息。这样做会件消耗资源的工作。在生产坏境下建议不要轻易使用。
sp_spaceused的另一个缺点一次只能查询一个对象，不能对所有表一次性查找，我们可以通过sys.dm_db_partition_stats视图来实现这件事情：
select o.name,
SUM(p.reserved_page_count) as reserved_page_count,
SUM(p.used_page_count) as used_page_count,
SUM(
case when (p.index_id<2) then (p.in_row_data_page_count+
p.lob_used_page_count+p.row_overflow_used_page_count)
else p.lob_used_page_count+p.row_overflow_used_page_count
end
)as DataPages,
SUM(
case when (p.index_id<2) then row_count
else 0
end
) as rowCounts
from sys.dm_db_partition_stats p inner join sys.objects o
on p.object_id=o.object_id
group by o.name
sp_spaceused方法简单，但功能比较脆弱，也不是最准确的方法。不特别推荐使用。
sys.dm_db_partition_stats会来的更直接，对系统性能也没有多少影响。
DBCC SHOWCONTIG(或者sys.dm_db_index_physical_stats)是检查数据库空间分配最准确的方法，它可以展现用了多少页面、多少区、甚至页面上的平均数据量。但是也会付出性能上的代价，SQL Server从整体性能角度出发，不可能一直维护这样的底层统计信息。为了完成这个命令，就必须对库进行扫描。总之得到的结果越精确，扫描的范围就越大。
总之：如果管理者只要看数据文件的整体使用情况，DBCC SHOWFILESTATS是比较好的选择。如果要看每个对象的空间使用情况，可以使用动态管理视图sys.dm_db_partition_stats。如果想要了解每个页、每个区的使用情况、碎片程度，那DBCC SHOWCONTIG是比较好的选择。
日志文件
数据库的日志文件是包含用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件，当然也可以有多个。日志文件的推荐文件扩展名是.ldf。与数据库文件按8KB组织不同，日志文件不是按照页、区来组织的。
SQL Server数据库引擎在内部将每一物理日志文件分成多个虚拟日志单元。虚拟日志单元没有固定大小，且一个物理日志文件所包含的虚拟日志单元数不固定。管理员不能配置或设置虚拟日志单元的大小或数量。但SQL Server会尝试控制虚拟日志单元的数目，把它限制在一个合理的范围内，但是有种情况特殊，日志文件每自动增长一次，会至少增加一个虚拟日志单元。所以，如果一个日志文件经历了多次小的自动增长，里面的虚拟日志单元数目会比正常的日志文件多很多。这种情况会影响到日志文件管理的效率，甚至造成数据库启动要花很长时间。
事务日志是一种回绕的文件。例如，假设有一个数据库，它包含一个分成5个虚拟日志单元的物理日志文件。当创建数据库时，逻辑日志文件从物理日志文件的始端开始。新日志记录被添加到逻辑日志的末端，然后向物理日志的末端扩展。
当逻辑日志的末端到达物理日志文件的末端时，新的日志记录将回绕到物理日志文件的始端，继续向后写
查看日志文件的使用情况非常简单。我们通过下面这个语句查看：
上图显示了SQL Server上所有数据库的日志大小，以及使用比率。语句的执行不会对SQL Server带来负担，这个语句返回的结果总是正确的。可以随时在SQL Server上运行这个命令。
