Identical tables use up different amounts of space

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Identical tables use up different amounts of space

Author

Topic

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-09-07 : 15:11:48

Guys,

We have 3 identical servers. They are used as subscribers in the transactional replication, and are absolutely identical. I ran a query (which is shown on the bottom) to find out the Space (in MB) used by the data and the indexes, for every table in one of the databases. What I found is that the space used by one of the tables is very different between all 3 servers and I do not understand why.

More specifically:

Server 1
--------
Used by Data: 900.17
Used by Indexes: 605.59

Server 2
--------
Used by Data: 920.20
Used by Indexes: 750.67

Server 3
--------
Used by Data: 673.35
Used by Indexes: 391.09

The number of rows in this table, for every server, is identical. The indexes are also identical.

Can someone shed light onto the possible cause of this?

Thanks a lot!

CREATE TABLE Mon_TableSpaceUsage(TableName VARCHAR(255), NumberOfRows INT, [Data_SpaceUsed(MB)] NUMERIC(10,2), [Index_SpaceUsed(MB)] NUMERIC(10,2), CreateTS DATETIME DEFAULT GetDate())
GO

CREATE PROCEDURE Monitor_TableSpaceUsage(@SizeThreshold NUMERIC(10,2) = 1.0)
AS
BEGIN
SET NOCOUNT ON

DECLARE @sql VARCHAR(128)
DECLARE @SourceDB VARCHAR(255)

CREATE TABLE #tables(NAME VARCHAR(128))
SET @SourceDB = (SELECT DB_NAME())

SELECT @sql = 'INSERT #tables SELECT TABLE_NAME FROM ' + @SourceDB + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
EXEC (@sql)

CREATE TABLE #SpaceUsed (name VARCHAR(128), rows VARCHAR(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
DECLARE @Name VARCHAR(128)
SELECT @Name = ''
WHILE EXISTS(SELECT * FROM #tables WHERE Name > @Name)
BEGIN
SELECT @name = MIN(Name) FROM #tables WHERE Name > @Name
SELECT @sql = 'exec ' + @SourceDB + '..sp_executesql N''INSERT #SpaceUsed EXEC sp_spaceused ' + @Name + ''''
EXEC(@sql)
END

INSERT INTO Mon_TableSpaceUsage(TableName, NumberOfRows, [Data_SpaceUsed(MB)], [Index_SpaceUsed(MB)])
SELECT Name, Rows, CONVERT(NUMERIC(10,2),CONVERT(INT,REPLACE(Data,'KB',''))/1024.0) AS 'Data_SpaceUsed(MB)', CONVERT(NUMERIC(10,2),CONVERT(INT,REPLACE(Index_Size,'KB',''))/1024.0) AS 'Index_SpaceUsed(MB)'
FROM #SpaceUsed
WHERE CONVERT(INT,REPLACE(Data,'KB',''))/1024.0 > @SizeThreshold
ORDER BY 'Data_SpaceUsed(MB)' DESC

DROP TABLE #tables
DROP TABLE #SpaceUsed
END

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-07 : 15:53:19

You need to run either DBCC UPDATEUSAGE or add the @UPDATEUSAGE = TRUE option to sp_spaceused to correct any inaccuracies.

If that doesn't fix it, then you need to figure out if they are fragmented.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-09-07 : 17:53:25

Tara,

I did use DBCC UPDATEUSAGE('DBName') now. It seems to have helped somewhat, but not completely. More specifically, the difference in the INDEX_SpaceUsed decreased to 37MB, but for the data, the difference is still 217MB, which I think is a lot considering that the whole table is taking up 706MB (this is the smaller of the 2 numbers, with larger one being 923).

Do you think it could be fragmentation? What could be the reason as to why it is fragmented in one place, but not another. Also, is there a way to find out whether it truly is fragmentation and somehow resolve it?

Thanks a lot!

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 19:18:27

"is there a way to find out whether it truly is fragmentation"

DBCC SHOWCONTIG ??

"and somehow resolve it?"

Reindex ? (assuming you have a CLUSTERED Index on the table)

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-07 : 23:49:44

Do you use same fill factor in both tables?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources