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
CommitmentsForPosting 1660979 2740336 KB 1857104 KB 312 KB 882920 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 KB 56 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
SELECT s.name
, OBJECT_NAME(o.object_id) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, i.index_id AS IndexID
, i.is_disabled AS IsDisabled
, CASE
WHEN i.data_space_id > 65600 THEN ps.name
ELSE f.name
END AS FGorPartitionName
, p.partition_number AS PartitionNo
, p.[rows] AS [RowCnt]
, p.data_compression_desc AS CompressionType
, au.type_desc AS AllocType
, au.total_pages / 128 AS TotalMBs
, au.used_pages / 128 AS UsedMBs
, au.data_pages / 128 AS 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
dbo CommitmentsForPosting pk_CommitmentsForPosting 1 0 PRIMARY 1 1660979 NONE LOB_DATA 2585 1723 0
dbo CommitmentsForPosting pk_CommitmentsForPosting 1 0 PRIMARY 1 1660979 NONE IN_ROW_DATA 90 90 90
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.
dbo SPS_TEST pk_CommitmentsForPosting1 1 0 PRIMARY 1 1660979 NONE LOB_DATA 169 169 0
dbo SPS_TEST pk_CommitmentsForPosting1 1 0 PRIMARY 1 1660979 NONE IN_ROW_DATA 90 90 90
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