Last week one of the guys on my DBA team, asked me if I knew of a way to identify which data from a table is in a particular data file. SQL Server (when it has multiple data files) typically does a proportional fill of data files—it will try to write an even amount of pages in each of the data files. If a new file is added the engine will continue to add pages there until the fill levels are about even.
The reason this question came up, was that we had a rather large table, and a two data files—one of which was pretty empty, so we wanted to see how much data was left in the second file (these tables are refilled pretty regularly, so it might have been possible to get all of the data out and drop the second data file).
Unfortunately, in SQL 2008 R2 (and below) there is no documented method of doing these checks. The process involves dumping DBCC IND into a temp table and running a select statement against it (see below code):
if object_id(‘#dbcc_indresults’) is not null
begin
drop table #dbcc_indresults
end
create table #dbcc_indresults (
pageFID bigint,
pagePID bigint,
IAMFID bigint,
IAMPID bigint,
ObjectID bigint,
IndexID bigint,
PartitionNumber bigint,
PartitionID bigint,
IAM_CHAIN_TYPE varchar(50),
PageType bigint,
IndexLevel bigint,
NextPageFID bigint,
NextPagePID bigint,
PrevPageFID bigint,
PrevPagePID bigint);
insert into #dbcc_indresults exec(‘dbcc ind (”pagesplittest”, ”testtable”, 1)’);
select t1.name as ‘File Name’, count(*) as ‘Pages’ from #dbcc_indresults t2
join sys.database_files as t1 on
t1.file_id=t2.PageFID
group by t2.PageFID, t1.name;
However, (and I have to credit this find to this weekend’s Northeastern snow storm, as I was without cable and internet, so I didn’t have much to do, except play with my local SQL Server and read), there is a new system function name sys.dm_db_database_page_allocations, built into SQL Server 2012. It accepts the object_id as a parameter (similar to sys.dm_db_physical_index_stats) and brings us a lot of good information about the pages within a given object. This data was available through various commands in older versions of SQL Server, but this brings it together in one place. This code below, will bring back the number of pages per datafile for a given object.
select t1.name as ‘File Name’, count(*) as ‘Pages’ from sys.dm_db_database_page_allocations
(db_id(), object_id(245575913), null,null,’detailed’) as t2
join sys.database_files as t1 on
t1.file_id=t2.extent_file_id
group by t2.extent_file_id, t1.name
Only, one problem I ran into—the page counts didn’t match the older method of using DBCC IND. So I asked Paul Randal (blog|twitter), for his thoughts on the matter—he had me check sys.dm_db_index_physical_stats which matched DBCC IND nearly (DBCC IND accounts for the IAM page)
So either my test data is weird (I did perform this test twice after wiping out and recreating the database) or there is a bug in sys.dm_db_database_page_allocations. I opened a connect bug with Microsoft on it, so hopefully I will get some feedback soon.