Presentations from SQL Saturday #96 Washington

These are my presentations from SQL Saturday in Washington. I’ll post a follow up on a couple of the questions I had in the presentation later this week.

What my SQL Family Means to Me

So there are a ton of things I could talk about with this–helping each other remotely, being stuck, bored in a hotel room in another country and assisting with a restore for entertainment, or the fact that if I travel to almost anywhere I have someone to hang out with, but there’s more.

The night I got back from the PASS Summit (after a wonderfully spectacular time), I was greeted by parents’ news, that my dad would be having heart surgery the following week. I was pretty annoyed at the time (they had known for a week and didn’t call), but more concerned.

So I ended up having to fly to New Orleans the next weekend and my dad’s surgery was on the following Tuesday. I tweeted about it, and the outpouring of thoughts, prayers, and emails was incredible. My dad isn’t fully recovered yet, but I’m still thankful for everyone’s thoughts and prayers.

I even spent my birthday, “celebrating” over a pink bubbly beverage with a couple of “family members”.

We truly are a very special community. And one that I am very proud to be a part of.



How to Find Data File Page Counts for a Table

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


drop table #dbcc_indresults


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 as ‘File Name’, count(*) as ‘Pages’  from #dbcc_indresults t2

join sys.database_files as t1 on


group by t2.PageFID,;

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 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


group by t2.extent_file_id,

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.

%d bloggers like this: