June 23, 2010 at 1:15 am
Hi there,
I have a bit of a problem on a SQL 2008 database. I have a table with 38009 rows. The table properties is showing that it is 151 GB is size. This does not make any sense since the source table has exactly the same amount of rows but the table properties of the source displays 27 GB. Am I missing something here? I ran dbcc update usage to display the correct results but it is still showing 151 GB.
Can anyone please assist me with this problem?
Regards
IC
June 23, 2010 at 1:28 am
Are indexes the same in both tables?
BTW, what do you mean with "source" table?
-- Gianluca Sartori
June 23, 2010 at 1:33 am
Yes, the indexes are the same on both tables. The source table comes from the primary database and the data is transferred to the secondary table.
June 23, 2010 at 2:03 am
I tried to search the original source of this script to link it back to you, but I couln't find it.
It's a nice script by Jeff Moden that could help you identifying the space used by db objects:
--===== "Space Used on Steroids"
-- If "UnusedKB" is negative, it's likely you need to run DBCC UpdateUsage on the table.
-- If the RowModCtr is high (contains number of rows inserted/updated/deleted sinse last stats update)
-- you might want to run UPDATE STATISICS on those tables.
-- Jeff Moden
;WITH SpaceUsed AS (
SELECTDBName = DB_NAME(),
Owner = USER_NAME(so.UID),
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1,
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
HAVING SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END) > 100
)
SELECT DBName,
Owner = ISNULL(Owner,'dbo'),
Schema_Name = sch.name,
TableName,
ObjectName = QUOTENAME(DB_NAME()) + '.' + QUOTENAME(ISNULL(Owner,sch.name)) + '.' + QUOTENAME(TableName),
TableID,
MinRowSize,
MaxRowSize,
ReservedKB,
DataKB,
IndexKB,
UnusedKB,
Rows,
RowModCtr,
HasTextImage,
HasClustered
FROM SpaceUsed AS SU
CROSS APPLY ( select b.name
from sys.objects as a
inner join sys.schemas as b
on a.schema_id = b.schema_id
where a.object_id = su.TableID
) as sch
ORDER BY Rows Desc
What does it return for your table?
-- Gianluca Sartori
June 23, 2010 at 2:10 am
Could be pages partially used, could be index space.
What does sp_spaceused return for that table?
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
June 23, 2010 at 2:22 am
The results are as follows:
ObjectNameMinRowSizeMaxRowSizeReservedKBDataKBIndexKBUnusedKB
DestinationTable45 2048 33000 1794414840216
RowsRowModCtrHasTextImageHasClustered
38009 5938 0 1
June 23, 2010 at 2:45 am
sp_spaceused results:
38009 143578912 KB142983688 KB14840 KB580384 KB
June 23, 2010 at 2:55 am
It could definitely be partially used pages.
Try rebuilding / reorganizing the clustered index and all nonclustered indexes.
-- Gianluca Sartori
June 23, 2010 at 4:07 am
Gianluca Sartori (6/23/2010)
It could definitely be partially used pages.Try rebuilding / reorganizing the clustered index and all nonclustered indexes.
Agreed.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply