August 15, 2014 at 2:59 am
Hi,
I have a table that contains 1.6 million rows, and is about 2.6GB in size
One of the columns is ntext and contained some xml.
Realistically, I don't need the xml for anything older than about 2 months, however, i'd like to keep some of the data in the other fields.
I decided that if I updated the xml to blank, I would see some considerable space savings, however that doesn't appear to be the case.
This is the output of sp_spaceused for my table
name rows reserved data index unused
CommitmentsForPosting1660979 2740336 KB 1857104 KB312 KB882920 KB
After I updated the table to remove the xml, the output of sp_spaceused remained the same.
My first thought was that it was probably statistics, so I updated statistics for the table, and nothing changed.
I then updated statistics for everything in the database, still no change.
I then ran DBCC CLEANTABLE for that table. I didn't really expect it to make a difference, and no surprise, it didn't.
The index on the table is a clustered index on just a GUID column.
I rebuilt the index and again it still made no difference (that's a bit of a lie - index_size changed by a few hundred KB)
my next test was to run 'select * into XXX from YYY' to create a copy of this table with the same data and data types. I also created the same clustered index from the original table onto the new one.
If I then run sp_spaceused on this new copy of the table, I see what I expect to see in sp_spaceused - a table using approximately 256MB of space
name rows reserved data index unused
SPS_TEST 1660979 266400 KB 266400 KB 304 KB56 KB
To be honest, this isn't hugely critical, but I'm just curious as to why the original table is still reporting 2.6GB size, when I think it should probably be nearer 256MB.
Thank you in advance for any help on this
Steve
August 15, 2014 at 3:02 am
Try an index reorganise with LOB Compaction on
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2014 at 3:17 am
Hi,
I knew I'd forgotten to mention something.
I tried that just before I decided to rebuild the index.
I think it is the lob size that is the problem.
If I query sys.dm_db_partition_stats, the lob_reserved_page_count calculates as 2.5GB
I'm going to run the reorganise index again, in case I did something stupid and forgot to execute it last time.
Thank you
Steve
August 15, 2014 at 3:27 am
Just re-ran the index re-organisation, and it made no difference.
Presumably, this is because the ntext column isn't included in the index.
August 15, 2014 at 3:30 am
Reorg of the clustered index, with lob_compaction on?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2014 at 3:43 am
sorry, I should have clarified
I ran the following:
ALTER INDEX [pk_CommitmentsForPosting]
ON dbo.CommitmentsForPosting
REORGANIZE with (LOB_COMPACTION = ON)
That is the only index on the table an has a definition as per below
ALTER TABLE [dbo].[CommitmentsForPosting] ADD CONSTRAINT [pk_CommitmentsForPosting] PRIMARY KEY CLUSTERED
(
[GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
This is from a vendor application so I can't really change the structure too much.
Although, having said that, I'm looking at this in a non-production environment so can pretty much do whatever I want to it.
August 15, 2014 at 4:12 am
Ah. Then out of ideas. Sorry I can't help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2014 at 4:25 am
No Problem.
thanks for the ideas.
August 15, 2014 at 4:28 am
Can you post results of this query please
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
SELECTs.name
, OBJECT_NAME(o.object_id)AS TableName
, ISNULL(i.name, 'HEAP')AS IndexName
, i.index_idAS IndexID
, i.is_disabledAS IsDisabled
, CASE
WHEN i.data_space_id > 65600 THEN ps.name
ELSE f.name
ENDAS FGorPartitionName
, p.partition_numberAS PartitionNo
, p.[rows]AS [RowCnt]
, p.data_compression_descAS CompressionType
, au.type_descAS AllocType
, au.total_pages / 128AS TotalMBs
, au.used_pages/ 128AS UsedMBs
, au.data_pages/ 128AS DataMBs
FROM sys.indexes i
LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.[type] IN (1,3) THEN p.hobt_id
WHEN au.[type] = 2 THEN p.partition_id
END = au.container_id
WHERE o.is_ms_shipped <> 1
AND (OBJECT_NAME(o.object_id) = 'CommitmentsForPosting')
ORDER BY i.index_id
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 15, 2014 at 4:42 am
Hi Perry
The results are below
dboCommitmentsForPostingpk_CommitmentsForPosting10PRIMARY11660979NONELOB_DATA258517230
dboCommitmentsForPostingpk_CommitmentsForPosting10PRIMARY11660979NONEIN_ROW_DATA909090
for reference, I've also included the results for the SPS_TEST table which is the one I populated as a copy of the table above.
dboSPS_TESTpk_CommitmentsForPosting110PRIMARY11660979NONELOB_DATA1691690
dboSPS_TESTpk_CommitmentsForPosting110PRIMARY11660979NONEIN_ROW_DATA909090
To me, this definitely appears to suggest that it's the LOB that still contains all of the size, however I don't understand why.
Thank you
August 15, 2014 at 4:49 am
Instead of rebuilding the clustered index, I would create the clustered index with DROP_EXISTING = ON
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 15, 2014 at 6:09 am
I just tried that using the syntax below.
No change in the size of the table as reported by sp_spaceused
create unique clustered index [pk_CommitmentsForPosting] on dbo.CommitmentsForPosting
(GUID ASC)
with
(DROP_EXISTING = ON) ON [PRIMARY]
August 15, 2014 at 6:55 am
Steve Smith-163358 (8/15/2014)
I then ran DBCC CLEANTABLE for that table.
How long did it take to run?
What command did you use?
Did you get any error messages or info messages?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply