Chris Woods

Thanks!

I will definitely let them know, knowing them, they just may take you up on it

haha

Have a wonderful day!

Yep agreed and the reasoning behind it makes sense.

Excellent. I'm good with this reasoning on the difference. I really appreciate all your help today!

Well that brought it to 31841mb now. I ended up just taking the number of LOB columns (max_length = -1) which was 43, multiplying that by the number of rows (1.7mm) and got 1431mb. so now only 8% off lol.

haha, yeah welcome to my world. I'll post on here with results. I really appreciate all your time on this!

ok it'll take a bit to do that (we have 207 columns)

so do that for each LOB field? non-LOBs I don't want to add 20, right?

haha, could it make up a majority of that 11.7%?

these rows have a lot of LOB data, how much space is a pointer? anyway to figure out how much of the 34440 is pointers?

yep getting closer :) where is that 11.7%?

Sorry not sure I'm following. I completely agree that the DM gives me 34440, but datalength gives me 30410. And adding any of those types in the DM does not get me the 30410 (or close to it)

Right, and sum of all my DATALENGTHs gives me 30410

yep I ran that query and it returned results that still don't get me close to the 30410mb DATALENGTH gets me

(changed to MBs)

So when I use DATALENGTH I get 30410mb, but that query you returned shows:

ie free space?

what is the nulll type?

ok and so the Text_tree_page is the b-tree for the clustered index? ie, non leaf level pages?

NULL	NULL	                3405
1	        DATA_PAGE	        1026147
10        IAM_PAGE	                58
2	        INDEX_PAGE	        1925
3	        TEXT_MIX_PAGE	3455069
4	        TEXT_TREE_PAGE	69961

HAHA

just that one table (replaced dbo.test in your example with my schema.table)

Mine (as I was afraid of) is still running. Once it completes I'll let you know. I really appreciate all your help on this!

testing...

just to confirm, datalength() will give me in bytes, right? so I take that sum and divide by 1024 twice to get MBs to match to your query

closer, now its 13% off

whew ok :) I thought 0 was heap, but I am beginning to question everything I know lol

ok riddle me this, Batman... the query you provided me doesn't work. But when I removed the clustered index criteria it did. It looks like all my data is in Index_id = 1.

oh gotcha, I hadn't even considered the non-leaf nodes. Could that be where most of the 15% is coming from? I forget how to get the # of pages in the non-leaf nodes to check.

If we are only looking at the clustered index (using the joins/criteria), then the only index that would be included in used_pages woudl be the clustered index right? I'm ok with that

2012, but we'll be moving to 2014 I'm hoping in the next 3-6 months if that helps

@PaulWhite thanks for condensing the comments into the original question and moving this to chat. Never had a question go on like this so I wasn't sure of the protocols and all.

@srutzky, Thanks for the updated query, but now the totalactualdatamb is way below what the sum(datalength()) is showing (like 25%).

@Frisbee LOL yeah, I like that answer :) If I cannot determine where the 15% is coming from I had always planned on doing the methodology presented in the other answer and just extrapolating my 85% out to the full page size summation. But I know if I don't do my do diligence to figure out where the missing percent is coming from, management won't even listen to me. So I really appreciate everyone's time on helping me figure out where I'm missing storage :)

@srutzky, there are no SPARSE columns.

I rebuilt the clustered index and the size did not change much (I am still 15% difference). which other reason above could be accounting for such a large difference. Someone had asked if I am using nullable or sparse columns (but that comment seems to have disappeared), yes I have nullable columns, not sure how to tell if any of the columns are sparse, though).

@srutzky, my FILLFACTOR is 100

@AaronBertrand I completely agree. And we wont' be rebuilding it each month to do the billing. I just want to do it once here to see if that accounts for most of the 15%. If there is still a lot of that 15% left, I'd like to get your ideas on what else may be accounting for that 15%.

@AaronBertrand you misunderstand. I want to be able to show my management team where the 15% difference comes from. So my idea is to rebuild it and if that removes most of that 15% then I can show that to them and we go forward with just doing a percent allocation (as described in the other answer). However, if we are still off by a large percentage we have concern. So question again :) would rebuilding clean up most of that 15% (knowing there are a lot of small pointers, headers, etc, but on a very large table it should account for only a small percentage of difference).

Yes, I hadn't considered that one is the actual size of the data (DATALENGTH), while the other query is the size in pages. I see why they do not tie out exactly. But to be 15% off? What is making up most of that 15%? Fill Factor and rows marked for deletion are probably the largest component (if we even have any of those), right? And if I rebuilt the clustered index I would clean that up and supposedly that 15% would be smaller, right?