table size difference in two DB

Question

table size difference in two DB

mailtoashokberwal

SSC Journeyman

Points: 95
More actions
July 20, 2015 at 7:54 am

#302599

I am having an issue in determining the correct size of a table.
I have a tableA in some DB on transaction server (Enterprise Edition), this table is being replicated in reporting server DB (Standard edition).
when i check the space used by this table in both the databases i see noticeable difference.
I am using EXEC sp_spaceused 'tableA' to determine the space.
Transaction Server
------------------------------------------------------------------------------
name rows reserveddata index_size unused
TableA1439999 695416 KB507048 KB182912 KB 5456 KB
Reporting Server
-------------------------------------------------------------------------------
name rows reserveddata index_size unused
TableA1439999 656904 KB483664 KB172680 KB 560 KB
so i wanted to know what could be the possible reasons for this difference ?
Thank you in advance
Ashok

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Different fill factor? Difference on index fragmentation?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Y.B. SSChampion Points: 11649 More actions · Answer 2

It's not all that strange when you realize how SQL stores data. Several factors can affect size such as fill factor for example. In you case the biggest difference looks like unused space which is fine. Just likely means your space allocation was set differently.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

There are a number of factors fill factor etc as mentioned before , ghost records could also be a factor. Also what kind of autogrowth settings does the database have and what kind of replication are you using ?

Jayanth Kurup[/url]

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

mailtoashokberwal (7/20/2015)
I am having an issue in determining the correct size of a table.
I have a tableA in some DB on transaction server (Enterprise Edition), this table is being replicated in reporting server DB (Standard edition).
when i check the space used by this table in both the databases i see noticeable difference.
I am using EXEC sp_spaceused 'tableA' to determine the space.
Transaction Server
------------------------------------------------------------------------------
name rows reserveddata index_size unused
TableA1439999 695416 KB507048 KB182912 KB 5456 KB
Reporting Server
-------------------------------------------------------------------------------
name rows reserveddata index_size unused
TableA1439999 656904 KB483664 KB172680 KB 560 KB
so i wanted to know what could be the possible reasons for this difference ?
Thank you in advance
Ashok

Please check the output of this query when run against both tables (set your tablename in the WHERE clause)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO

SELECTDB_NAME()AS DBName

, s.nameAS SchemaName

, OBJECT_NAME(o.OBJECT_ID)AS TableName

, ISNULL(i.name, 'HEAP')AS IndexName

, i.index_idAS IndexID

, CASE i.[type]

WHEN 0 THEN 'HEAP'

WHEN 1 THEN 'Clustered'

WHEN 2 THEN 'NonClustered'

WHEN 3 THEN 'XML'

WHEN 4 THEN 'Spatial'

ENDAS IndexType

, i.is_disabledAS IsDisabled

, CASE

WHEN i.data_space_id > 65600 THEN ps.name

ELSE f.name

ENDAS FG_or_PartitionName

, p.partition_numberAS PartitionNo

, p.[rows]AS [RowCnt]

, p.data_compression_descAS CompressionType

, au.type_descAS AllocType

, au.total_pages / 128AS TotalMBs

, au.used_pages/ 128AS UsedMBs

, au.data_pages/ 128AS 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) = 'yourtablename'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

mailtoashokberwal (7/20/2015)
I am having an issue in determining the correct size of a table.
I have a tableA in some DB on transaction server (Enterprise Edition), this table is being replicated in reporting server DB (Standard edition).
when i check the space used by this table in both the databases i see noticeable difference.
I am using EXEC sp_spaceused 'tableA' to determine the space.
Transaction Server
------------------------------------------------------------------------------
name rows reserveddata index_size unused
TableA1439999 695416 KB507048 KB182912 KB 5456 KB
Reporting Server
-------------------------------------------------------------------------------
name rows reserveddata index_size unused
TableA1439999 656904 KB483664 KB172680 KB 560 KB
so i wanted to know what could be the possible reasons for this difference ?
Thank you in advance
Ashok

When dealing with replication my first guess would be indexes. If I remember correctly, Clustered indexes are replicated but Nonclustered indexes are not replicated by default. If you have indexes that are not being replicated that would explain things.

Either way, Perry's query will help you figure out if that's happening.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001