1

I dropped a few (decimal) columns from my table. Truncated the table and then re-inserted the data as before, barring the dropped columns. However I don't notice any perceptible difference in the space occupied by the table. I had expected it to decrease. Why did it not?

Below is the query I'm using to determine the space occupied:

SELECT
b.groupname AS 'File Group',
Name,
[Filename],
CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],
CONVERT (Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],
CONVERT (Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)],
'DBCC SHRINKFILE ('''+Name+''','+CAST(CONVERT (Decimal(15,2),ROUND((FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS VARCHAR(10))+')' QUERY
FROM dbo.SYSFILES a (NOLOCK)
JOIN SYSFILEGROUPS b (NOLOCK)
ON a.groupid = b.groupid
ORDER BY b.groupname

PS. I deemed Rebuilding the table and its indexes unnecessary, as the table was truncated

  • Were you using any kind of compression on the table? – Joe Obbish Jan 13 '17 at 14:46
  • Yes, page compression – Ritesh Bhakre Jan 13 '17 at 14:49
  • Do you mind editing in information from the comments into your question? Saying that the decimal columns had a precision of 33 and that the table used page compression could help others better answer the question. I posted a few thoughts of my own. Thanks! – Joe Obbish Jan 13 '17 at 15:22
0

The first thing I'd ask is are you sure the columns you dropped are decimals?

The reason why I ask is that with variable columns (which Decimal is not), then SQL Server won't automatically reduce space used when these are dropped. To resolve this you can run DBCC CLEANTABLE. You might want to try this anyway.

However, if the columns that you dropped are only decimals, then this probably won't have an effect.

Another possibility is that the decimal columns were just not taking up that much space - depending on the precision, a decimal column can be quite small. You say that there is no perceptible difference, is it just a case that the decimal columns were taking up relatively little space compared to other columns in the table (and rest of the database)?

Check the space used of the actual table before and after the change. I used this query to check table spaced used

SELECT 
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages, 
 SUM(a.used_pages) AS UsedPages, 
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB, 
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB, 
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM 
 sys.tables t
INNER JOIN  
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND  
 i.index_id <= 1
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
 OBJECT_NAME(i.object_id) 

Reference: I got this originally from this SO post.

To note/warning: I'm assuming you're doing this in some sort of development or test environment, doing this sort of thing in production without testing is really not advisable. This earlier post is similar to yours (but is specifically about variable column lengths).

SpaceUsed Your query to find space used looks fine, but there is an inbuilt stored procedure sp_spaceused that you might find easier (but probably doesn't make much of a difference.

  • The columns were Decimal with a precision of 33 each. So dropping them should have freed up substantial amount of space. – Ritesh Bhakre Jan 13 '17 at 10:29
  • @RiteshBhakre Can you run code to check the table size both before and after the change? – Ian_H Jan 13 '17 at 10:39
  • the change has already been implemented. I was just curious as to why this peculiar behaviour has been manifested. – Ritesh Bhakre Jan 13 '17 at 10:57
  • @RiteshBhakre might be tricky to understand if you can't get more info (perhaps from a backup in a test environment)? Unless someone else has witnessed this phenomena. Might be worth running DBCC CLEANTABLE that I mentioned just to see, just follow the links to msdn and the earlier question (I suspect it won't do anything and I wouldn't normally suggest just doing it in production, but seeing as this change has been made just like that I can't see it hurting any more than that. Doing it in test first is always ideal though). – Ian_H Jan 13 '17 at 11:05
  • What you suggested doesn't result in any change either – Ritesh Bhakre Jan 13 '17 at 13:27
0

You indicated in the comments that you dropped decimal columns with a precision of 33. Since the precision was so large you expected a lot of space to be freed. You also said that the table uses page compression. I can think of a few explanations as to why you didn't see a large space reduction after inserting your data again without the columns.

1) The column values mostly did not fill up the full 33 digits available in the precision. Page compression also implements row compression. For row compression:

It uses variable-length storage format for numeric types (for example integer, decimal, and float) and the types that are based on numeric (for example datetime and money).

This means that each value might not take up the full 17 bytes of storage associated with the data type.

2) You had many repeated column values. Page compression can reduce the storage needed for the values for that and a few other cases.

3) It's possible that the presence of those columns caused the data in the pages to be distributed in such a way that page compression could better compress other columns in the table. I suspect that this is unlikely.

4) Depending on how you inserted the data, the new data may be more fragmented than the old data. Also, you are checking space for the entire file group, so there may have been another table that gained space at the same time as your table freed up space. I recommend using sp_spaceused to measure the space for a single table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.