May 21, 2012 at 1:38 am
Hi,
we have a table that has 714k rows. The table takes up 850Gb, where each rows actual data is no more than 2-4k. It has a few int, nvarchar fields and some varbinary(max). I definitely know that the varbinary fields don't use 1mb. When the database was at 200k rows it only would use 15-20 Gb and then it suddenly started to grow amazingly fast. I have deleted a few varbinary columns and did run DBCC Cleantable, but nothing changed.
This is the result from a query I used to check the size
name rows reserved data index_sizeunuseddata_per_rowwaste_per_row
Players714031843716656 KB843514984 KB88304 KB113368 KB1181.47 KB 1181.78 KB
Any ideas?
Thanks in advance!
May 21, 2012 at 2:33 am
If I remember correctly DBCC cleantable reclaims disk space only on tables that varying length columns were dropped from. It doesn't reclaim disk space if the column still exists but the value was deleted. Did you try rebuilding the table? I think this should give you better results. Take into consideration that this will lock your table.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2012 at 2:36 am
Assuming the table is a heap, you need ALTER TABLE REBUILD. SQL Server does not properly clean up removed columns from heaps, even with DBCC CLEANTABLE and ALTER INDEX ALL:
USE Sandpit;
SET NOCOUNT ON;
DECLARE
@counter integer = 0,
@sql nvarchar(max);
IF OBJECT_ID(N'dbo.Test', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.Test;
END;
CREATE TABLE dbo.Test (id integer NOT NULL);
-- Add and remove a varbinary(max) column 307 times
WHILE @counter < 307
BEGIN
SET @sql = N'ALTER TABLE dbo.test ADD id' + CAST(@counter as nvarchar(12)) + N' varbinary(max)';
EXECUTE (@sql);
SET @sql = N'ALTER TABLE dbo.Test DROP COLUMN id' + CAST(@counter as nvarchar(12));
EXECUTE (@sql);
SET @counter += 1;
END;
-- Rebuild using ALTER INDEX ALL
ALTER INDEX ALL ON dbo.Test REBUILD;
DBCC CLEANTABLE (0,'dbo.Test');
-- Warning: The table "Test" has been created,
-- but its maximum row size exceeds the allowed maximum of 8060 bytes.
ALTER TABLE dbo.Test ADD id1 varbinary(max) NOT NULL;
-- Rebuild using ALTER TABLE
ALTER TABLE dbo.Test REBUILD;
-- No warning now
ALTER TABLE dbo.Test ADD id2 varbinary(max) NOT NULL;
May 22, 2012 at 2:57 am
nsi (5/21/2012)
Hi,we have a table that has 714k rows. The table takes up 850Gb, where each rows actual data is no more than 2-4k. It has a few int, nvarchar fields and some varbinary(max). I definitely know that the varbinary fields don't use 1mb. When the database was at 200k rows it only would use 15-20 Gb and then it suddenly started to grow amazingly fast. I have deleted a few varbinary columns and did run DBCC Cleantable, but nothing changed.
This is the result from a query I used to check the size
name rows reserved data index_sizeunuseddata_per_rowwaste_per_row
Players714031843716656 KB843514984 KB88304 KB113368 KB1181.47 KB 1181.78 KB
Any ideas?
Thanks in advance!
this usually occurs when your table is a heap (ie no clustered index on the table)
when you perform deletes on a heap the space is not reclaimed. (does not apply to truncates)
you can do several things to get round this, but the easiest one is to add a clustered index to the table
MVDBA
May 22, 2012 at 8:28 am
My first guess is that it would be the result of index fragmentation or page splits.
SQL Server Fragmentation The Basics
http://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-1-the-basics/
Also, you may want to consider moving your LOB data off row.
Importance of choosing the right LOB storage technique
http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-choosing-the-right-LOB-storage-technique.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 22, 2012 at 8:32 am
michael vessey (5/22/2012)
when you perform deletes on a heap the space is not reclaimed.
Unless a table lock is taken (and/or specified).
May 22, 2012 at 8:36 am
SQL Kiwi (5/22/2012)
michael vessey (5/22/2012)
when you perform deletes on a heap the space is not reclaimed.Unless a table lock is taken (and/or specified).
ah yes - i forgot to mention that - but how many people who have heaps also specify TABLOCK? 😀
MVDBA
May 22, 2012 at 8:46 am
michael vessey (5/22/2012)
SQL Kiwi (5/22/2012)
michael vessey (5/22/2012)
when you perform deletes on a heap the space is not reclaimed.Unless a table lock is taken (and/or specified).
ah yes - i forgot to mention that - but how many people who have heaps also specify TABLOCK? 😀
Not as many as should?
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply