September 7, 2007 at 1:23 pm
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
September 7, 2007 at 2:02 pm
Try running DBCC UPDATEUSAGE
before.
sp_spaceused depends on up to date statistics
Regards,
Andras
September 7, 2007 at 4:03 pm
Andras,
Thank you for the suggestion. Once I ran the DBCC UPDATEUSAGE('DbName'), the situation seems to have improved, but not completely fixed. That is, the difference in space used by indexes has shrunk to only 87MB. As for the space used by data, the difference is still > 200MB, which I believe is too much, considering the total table size of ~800MB.
We run index defragmentation daily. However, we never defragmented the data file. Could defragmentation be the reason and if so, what is the best way to first identify if it is and then fix it?
Thanks a lot!
September 7, 2007 at 4:11 pm
you should also run UPDATE STATISTICS ...
* Noel
September 9, 2007 at 4:22 pm
We run UPDATE STATISTICS daily (sometimes multiple times a day). Would it be important to run it right before I execute the query to get the space usage results?
Thank you
September 10, 2007 at 12:00 am
Good guess/plan, I'd check fragmentation on each the of servers for sure.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply