看資料表在 data file 上的分佈

感謝 James Fu 的問題與解答,簡單測試的 script

CREATE DATABASE [test]
ON  PRIMARY
( NAME = N’test’, FILENAME = N’C:\temp\test.mdf’  ),
( NAME = N’f1′, FILENAME = N’C:\temp\f1.ndf’  ),
( NAME = N’f2′, FILENAME = N’C:\temp\f2.ndf’ )
LOG ON
( NAME = N’test_log’, FILENAME = N’C:\temp\test_log.ldf’)
GO

use test
go
create table t(c1 int)
insert t values(1)
go
insert t select * from t
go 13

dbcc ind(‘test’,’t’,0)  — heap 時第三個參數是 0 或 1 似乎沒差,但若是 clustered index 需要 1 才會 show 出所有的 leaf 和 non-leaf(index level>0) 的 page

image

select *
from sys.dm_db_database_page_allocations( DB_ID(‘test’), OBJECT_ID(‘t’),1, NULL,NULL) a
join sys.master_files m ON a.database_id = m.database_id AND a.allocated_page_file_id = m.file_id
order by allocated_page_file_id

image

sys.dm_db_database_page_allocations 可以呈現未使用的空間

參考資料

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/12/13/more-undocumented-fun_3a00_-dbcc-ind_2c00_-dbcc-page_2c00_-and-off_2d00_row-columns.aspx

http://www.dotblogs.com.tw/jamesfu/archive/2014/09/17/dbccind.aspx

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: