SQL Total_pages space is very high than the used_pages

Racheal 226

Hi ,

For an Archival process we have swithced out a partition to a new table . but this table Totalpages used space is very high compare to the usedpages.

what could be the reason for alocated_memory space is too high ?

Accepted answer

Erland Sommarskog 101.4K Reputation points MVP

2023-02-01T22:04:58.3333333+00:00

I doubt that DBCC UPDATEUSAGE will change this. This command could be needed in the dim and distant past of SQL 2000 and older versions.

Why the difference is that big is difficult to say, without a full repro of how you arrived there. But one possibility is that you loaded data with a minimally logged operation, and only a few rows at a time. Every new transaction will start with a new extent.

You say that this table used to be a partition in the old table. So another possibility is that data has been deleted and updated, leaving lots of empty space in it.

In any case, it is not the partition switching that cause this difference - it was there in partitioned table as well.

You could rebuild the indexes to compact the table.
Please sign in to rate this answer.

1 person found this answer helpful.
Racheal 226 Reputation points

2023-02-02T08:09:05.8166667+00:00

Hi @Erland Sommarskog ,

Glad to see you here . we have discussed this topic some time ago here :)

https://learn.microsoft.com/en-us/answers/questions/693622/what-are-the-best-way-of-archiving-table-to-a-new?childtoview=708139#comment-708139 .

coming back to this post , Yes you are right space was there in Partitioned table itself . Before switching out a partition to a new table i have checked the space in Partition table and it is exactly the same space of new table after swicthed out. So few things are clarified here

while switching out a partition to a new table , SQL will not allocate a new space but its jus using the same space in the partitioned table or the same space which is allocated for the Partition has been utilized by new table after switched out

once the Newly switched out table is dropped , allocated space will also be freed

I could guess that reason for the allocated space is high on the partition table is because few partitions were already archived during our recent DB migration and the balance existing few partitions in the beginning has more allocated space.

Partition table doesn't have index so rebuilding index might not help . or do you mean Partition table by default has some indexing and that needs to be rebuild ?

Erland Sommarskog 101.4K Reputation points MVP

2023-02-02T21:49:45.8633333+00:00

I could guess that reason for the allocated space is high on the partition table is because few partitions were already archived during our recent DB migration and the balance existing few partitions in the beginning has more allocated space.

So the key here is that partition switching is a pure metadata operation. That is, nothing will change on the pages themselves.

Partition table doesn't have index so rebuilding index might not help . or do you mean Partition table by default has some indexing and that needs to be rebuild ?

Well, I naïvely assume that the table had indexes. :--) But if it hasn't, or it's a heap, you can also do ALTER TABLE tbl REBUILD.
Sign in to comment

2 additional answers

Olaf Helper 40,901

How do you get the figures? Does DBCC UPDATEUSAGE change it?

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-ver16

Racheal 226

HI Olaf,

I have used below query

select top 10 schema_name(tab.schema_id) + '.' + tab.name as [table], 
    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
    cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables tab
join sys.indexes ind 
     on tab.object_id = ind.object_id
join sys.partitions part 
     on ind.object_id = part.object_id and ind.index_id = part.index_id
join sys.allocation_units spc
     on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc;

table used_mb allocated_mb

log.temppartition 33457.7 217896

Racheal 226 Reputation points

2023-02-01T06:03:29.9633333+00:00

Hi,

any updates on my question ?

What will happen to the used space and allocated space once the table is dropped ? Will that be free ?
Please sign in to rate this answer.
Olaf Helper 40,901 Reputation points

2023-02-01T06:57:07.2466667+00:00

any updates on my question ?

And, any updates on my questions?

Racheal 226 Reputation points

2023-02-01T07:36:23.25+00:00

Hi Olaf,

How do you get the figures?

select top 10 schema_name(tab.schema_id) + '.' + tab.name as [table], cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb, cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb from sys.tables tab join sys.indexes ind on tab.object_id = ind.object_id join sys.partitions part on ind.object_id = part.object_id and ind.index_id = part.index_id join sys.allocation_units spc on part.partition_id = spc.container_id group by schema_name(tab.schema_id) + '.' + tab.name order by sum(spc.used_pages) desc;

I have used the above commented query to get the result.

Does DBCC UPDATEUSAGE change it?

Never used DBCC UPDATEUSAGE before.

Racheal 226 Reputation points

2023-02-01T09:17:37.5966667+00:00

Hi Olaf ,

Did i ans your qns . or is there something that i have missed to point out ? as this is Prod env , we would like to know the reason for the more space in allocatedmb (totalpages) before taking any action against it. so DBCC UPDATEUSAGE option was not tried
Sign in to comment