Home > Database > Mysql Tutorial > SQL Server Storage

SQL Server Storage

WBOY
Release: 2016-06-07 14:49:44
Original
1462 people have browsed it

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的区别。

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">DATABASE</span> [lenistest5]

<span class="hljs-keyword">ON</span>

<span class="hljs-keyword">PRIMARY</span>

( NAME = N<span class="hljs-string">'lenistest5'</span>,

FILENAME = N<span class="hljs-string">'E:\Data_BU\lenistest5.mdf'</span> ,

<span class="hljs-keyword">SIZE</span> = <span class="hljs-number">10240</span>KB ,

MAXSIZE = <span class="hljs-number">102400</span>KB ,

FILEGROWTH = <span class="hljs-number">1024</span>KB )

, filegroup maindatagroup

( NAME = N<span class="hljs-string">'lenistest5_data01'</span>,

FILENAME = N<span class="hljs-string">'E:\Data_BU\lenistest5_data01.ndf'</span> ,

<span class="hljs-keyword">SIZE</span> = <span class="hljs-number">10240</span>KB ,

MAXSIZE = <span class="hljs-number">102400</span>KB ,

FILEGROWTH = <span class="hljs-number">1024</span>KB )

, filegroup backupdatafg

( NAME = N<span class="hljs-string">'lenistest5_bk_data01'</span>,

FILENAME = N<span class="hljs-string">'E:\Data_BU\lenistest5_bk_data01.ndf'</span> ,

<span class="hljs-keyword">SIZE</span> = <span class="hljs-number">10240</span>KB ,

MAXSIZE = <span class="hljs-number">10240</span>KB ,

FILEGROWTH = <span class="hljs-number">1024</span>KB )

LOG <span class="hljs-keyword">ON</span>

( NAME = N<span class="hljs-string">'lenistest5_log'</span>,

FILENAME = N<span class="hljs-string">'E:\Data_BU\lenistest5_log.ldf'</span> ,

<span class="hljs-keyword">SIZE</span> = <span class="hljs-number">10240</span>KB ,

MAXSIZE = <span class="hljs-number">10240</span>KB ,

FILEGROWTH = <span class="hljs-number">1024</span>KB )

<span class="hljs-keyword">GO</span></span></code>
Copy after login

用上面的这个SQL我们可以创建一个具有3个data file group, 和1个log file group的数据库 lenistest5 。.mdf全局唯一 ,不能有多个.mdf文件,但是可以有多个.ndf文件。我们是不是可以看到.mdf到底存储了什么?

<code class=" hljs cs"><span class="hljs-keyword">select</span> 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_on

<span class="hljs-keyword">from</span> sys.databases <span class="hljs-keyword">where</span> name = <span class="hljs-string">'lenistest5'</span></code>
Copy after login

这里可以看到刚创建的数据库有怎么样的恢复计划,这直接影响了日志的存储,还有统计信息更新计划,同样也会影响存储,更会影响执行计划的优劣,所以这也是需要创建数据后核实的。

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">select</span> name <span class="hljs-keyword">as</span> FileGroupName

,data_space_id

,type_desc

,is_default

<span class="hljs-keyword">from</span> sys.filegroups

<span class="hljs-keyword">select</span> type_desc

,data_space_id

,name

,physical_name

,state_desc

,<span class="hljs-keyword">size</span> * <span class="hljs-number">8</span> /<span class="hljs-number">1024</span> <span class="hljs-keyword">as</span> size_mb

,max_size * <span class="hljs-number">8</span> /<span class="hljs-number">1024</span> <span class="hljs-keyword">as</span> max_size_mb

<span class="hljs-keyword">from</span> sys.database_files</span></code>
Copy after login

sys.filegroups, sys.database_files是归属于特定数据库的,所以运行的时候需要切换到特定的数据库底下。不象有些DMV是全局性的,不需要指定数据库,在任何数据库根目录下,都能查到一致性的数据,比如 sys.dm_tran_locks.

Is_default这里需要特别指出来 ,使因为如果在create table之后没有指定特别的file group,默认这个表就是存在这个file group之下。如果要更改这个default file group,我们可以这么做:

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">alter</span> <span class="hljs-keyword">database</span> lenistest5

modify filegroup maindatagroup <span class="hljs-keyword">default</span></span></code>
Copy after login

Size, max_size是以PAGE为单位来计算的。一个page的存储大小为8KB ,所以计算起来就是乘以8 ,再除以1024换成MB。

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">select</span>

isnull(g.FileGroupName,<span class="hljs-string">'LOG File Group'</span>) <span class="hljs-keyword">as</span> FileGroupName

, isnull(g.type_desc,<span class="hljs-string">'LOG FILE GROUP'</span>) <span class="hljs-keyword">as</span> Filegroup_type_description

, isnull(g.is_default,<span class="hljs-number">0</span>) <span class="hljs-keyword">as</span> DefaultFileGroup

, f.type_desc <span class="hljs-keyword">as</span> datafile_type_description

, f.name <span class="hljs-keyword">as</span> fileName

, f.physical_name <span class="hljs-keyword">as</span> file_physical_name

, f.state_desc <span class="hljs-keyword">as</span> datafilestatus

, f.size_mb <span class="hljs-keyword">as</span> datafile_size_mb

, f.max_size_mb <span class="hljs-keyword">as</span> datafile_max_size_mb

<span class="hljs-keyword">from</span> (

<span class="hljs-keyword">select</span> name <span class="hljs-keyword">as</span> FileGroupName

,data_space_id

,type_desc

,is_default

<span class="hljs-keyword">from</span> sys.filegroups

) g

<span class="hljs-keyword">right</span> <span class="hljs-keyword">outer</span> <span class="hljs-keyword">join</span> (

<span class="hljs-keyword">select</span> type_desc

,data_space_id

,name

,physical_name

,state_desc

,<span class="hljs-keyword">size</span> * <span class="hljs-number">8</span> /<span class="hljs-number">1024</span> <span class="hljs-keyword">as</span> size_mb

,max_size * <span class="hljs-number">8</span> /<span class="hljs-number">1024</span> <span class="hljs-keyword">as</span> max_size_mb

<span class="hljs-keyword">from</span> sys.database_files

) f <span class="hljs-keyword">on</span> g.data_space_id = f.data_space_id

<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> f.data_space_id <span class="hljs-keyword">asc</span></span></code>
Copy after login

将 Filegroup 包含的所有 data file归纳起来,包括日志文件 。日志文件没有filegroup.

我们看看当新建一个表的时候,表结构及数据的存储:

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> dbo.sales(transactionDate datetime, amont <span class="hljs-keyword">int</span>)</span></code>
Copy after login

看表数据存储需要借助 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).

<code class=" hljs scss">DBCC <span class="hljs-function">TraceOn(<span class="hljs-number">3604</span>)</span>

DBCC <span class="hljs-function">IND(lenistest5,<span class="hljs-string">'dbo.sales'</span>,<span class="hljs-number">0</span>)</span></code>
Copy after login

当表里没有数据的时候,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] )

也有的是这么介绍的,毕竟这是非官方支持的命令,所以都试试

<code class=" hljs mathematica">dbcc page ( <span class="hljs-list">{‘dbname’ | dbid}</span>, filenum, pagenum [, printopt=<span class="hljs-list">{0|1|2|3}</span> ])</code>
Copy after login

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.分清楚表和索引的存储关系,不仅仅是方便管理,更有利于性能的提高,表和索引分别存储在不同的硬盘驱动器上,有利于并行处理。

<code class=" hljs sql">use lenistest4

go

declare @tablename varchar(200)

declare @index_Id int

declare @sqlstatement nvarchar(max)

declare @databasename varchar(200) ='lenistest4'

declare cur_tables cursor

for (<span class="hljs-operator"><span class="hljs-keyword">select</span> schema_name(schema_id) +<span class="hljs-string">'.'</span>+name <span class="hljs-keyword">as</span> tableName

<span class="hljs-keyword">from</span> sys.tables )

<span class="hljs-keyword">open</span> cur_tables

<span class="hljs-keyword">fetch</span> <span class="hljs-keyword">next</span> <span class="hljs-keyword">from</span> cur_tables <span class="hljs-keyword">into</span> @tablename

<span class="hljs-keyword">if</span> <span class="hljs-keyword">exists</span>( <span class="hljs-keyword">select</span> <span class="hljs-number">1</span> <span class="hljs-keyword">from</span> tempdb.sys.tables <span class="hljs-keyword">where</span> upper(name) <span class="hljs-keyword">like</span> upper(<span class="hljs-string">'%tempTabIndall%'</span>) )

<span class="hljs-keyword">drop</span> <span class="hljs-keyword">table</span> #tempTabIndall ;</span>

<span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> #tempTabIndall(PageFID bigint, PagePID bigint, IAMFID bigint, IAMPID bigint, ObjectID bigint, IndexId bigint, PartitionNumber bigint, PartitionID bigint,

iam_chain_type <span class="hljs-keyword">varchar</span>(<span class="hljs-number">500</span>) , PageType bigint, IndexLevel bigint, NextPageFID bigint, NextPagePID bigint,PrevPageFID bigint, PrevPagePID bigint)

<span class="hljs-keyword">create</span> index idx_pagefid <span class="hljs-keyword">on</span> #tempTabIndall(PageFID) ;</span>

while @@FETCH_STATUS = 0

<span class="hljs-operator"><span class="hljs-keyword">begin</span>

<span class="hljs-keyword">declare</span> cur_indexes <span class="hljs-keyword">cursor</span> <span class="hljs-keyword">for</span>

(<span class="hljs-keyword">select</span> index_id <span class="hljs-keyword">from</span> sys.indexes <span class="hljs-keyword">where</span> object_id = object_id(@tablename))

<span class="hljs-keyword">open</span> cur_indexes

<span class="hljs-keyword">fetch</span> <span class="hljs-keyword">next</span> <span class="hljs-keyword">from</span> cur_indexes <span class="hljs-keyword">into</span> @index_Id

while @@FETCH_STATUS = <span class="hljs-number">0</span>

<span class="hljs-keyword">begin</span>

<span class="hljs-keyword">set</span> @sqlstatement = N<span class="hljs-string">'insert into #tempTabIndall

exec sp_executesql N''DBCC IND('</span> + @databasename + <span class="hljs-string">','''''</span>+@tablename+<span class="hljs-string">''''','</span> + convert(<span class="hljs-keyword">varchar</span>(<span class="hljs-aggregate">max</span>),@index_Id)+<span class="hljs-string">')'''</span> ;</span>

print @sqlstatement

exec sp_executesql @sqlstatement

fetch next from cur_indexes into @index_Id

<span class="hljs-operator"><span class="hljs-keyword">end</span>

<span class="hljs-keyword">close</span> cur_indexes

<span class="hljs-keyword">deallocate</span> cur_indexes

<span class="hljs-keyword">fetch</span> <span class="hljs-keyword">next</span> <span class="hljs-keyword">from</span> cur_tables <span class="hljs-keyword">into</span> @tablename

<span class="hljs-keyword">end</span>

<span class="hljs-keyword">close</span> cur_tables

<span class="hljs-keyword">deallocate</span> cur_tables

<span class="hljs-keyword">select</span> <span class="hljs-keyword">distinct</span>

object_name(t.ObjectID) <span class="hljs-keyword">as</span> tablename

, t.IndexId

, ti.name <span class="hljs-keyword">as</span> IndexName

, f.FileGroupName

, f.Filegroup_type_description

, f.DefaultFileGroup

, f.datafile_type_description

, f.fileName

, f.file_physical_name

<span class="hljs-keyword">from</span> #tempTabIndall t

<span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> (<span class="hljs-keyword">select</span> <span class="hljs-keyword">distinct</span> object_id,index_id,name <span class="hljs-keyword">from</span> sys.indexes) ti <span class="hljs-keyword">on</span> t.ObjectID = ti.object_id <span class="hljs-keyword">and</span> t.IndexId = ti.index_id

<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> (

<span class="hljs-keyword">select</span>

isnull(data_file_id,<span class="hljs-number">0</span> ) <span class="hljs-keyword">as</span> data_file_id

, isnull(g.FileGroupName,<span class="hljs-string">'LOG File Group'</span>) <span class="hljs-keyword">as</span> FileGroupName

, isnull(g.type_desc,<span class="hljs-string">'LOG FILE GROUP'</span>) <span class="hljs-keyword">as</span> Filegroup_type_description

, isnull(g.is_default,<span class="hljs-number">0</span>) <span class="hljs-keyword">as</span> DefaultFileGroup

, f.type_desc <span class="hljs-keyword">as</span> datafile_type_description

, f.name <span class="hljs-keyword">as</span> fileName

, f.physical_name <span class="hljs-keyword">as</span> file_physical_name

, f.state_desc <span class="hljs-keyword">as</span> datafilestatus

, f.size_mb <span class="hljs-keyword">as</span> datafile_size_mb

, f.max_size_mb <span class="hljs-keyword">as</span> datafile_max_size_mb

<span class="hljs-keyword">from</span> (

<span class="hljs-keyword">select</span> name <span class="hljs-keyword">as</span> FileGroupName

,data_space_id

,type_desc

,is_default

<span class="hljs-keyword">from</span> sys.filegroups

) g

<span class="hljs-keyword">right</span> <span class="hljs-keyword">outer</span> <span class="hljs-keyword">join</span> (

<span class="hljs-keyword">select</span>

file_id <span class="hljs-keyword">as</span> data_file_id

,type_desc

,data_space_id

,name

,physical_name

,state_desc

,<span class="hljs-keyword">size</span> * <span class="hljs-number">8</span> /<span class="hljs-number">1024</span> <span class="hljs-keyword">as</span> size_mb

,max_size * <span class="hljs-number">8</span> /<span class="hljs-number">1024</span> <span class="hljs-keyword">as</span> max_size_mb

<span class="hljs-keyword">from</span> sys.database_files

) f <span class="hljs-keyword">on</span> g.data_space_id = f.data_space_id

)f <span class="hljs-keyword">on</span> f.data_file_id = t.PageFID

<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> f.file_physical_name <span class="hljs-keyword">asc</span> ,object_name(t.ObjectID) <span class="hljs-keyword">asc</span>, t.IndexId <span class="hljs-keyword">asc</span></span></code>
Copy after login
Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template