table size difference in two DB

Question

Post reply

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 reserved data index_size unused

TableA 1439999 695416 KB 507048 KB 182912 KB 5456 KB

Reporting Server

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

name rows reserved data index_size unused

TableA 1439999 656904 KB 483664 KB 172680 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: 183686 More actions · Answer 1

Luis Cazares

SSC Guru

Points: 183686

July 20, 2015 at 8:06 am

#1813662

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: 11572 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

Jayanth_Kurup

SSC-Insane

Points: 22967

July 20, 2015 at 8:12 am

#1813665

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: 233792 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 reserved data index_size unused

TableA 1439999 695416 KB 507048 KB 182912 KB 5456 KB

Reporting Server

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

name rows reserved data index_size unused

TableA 1439999 656904 KB 483664 KB 172680 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

SELECT		DB_NAME()					AS DBName

		, s.name					AS SchemaName

		, OBJECT_NAME(o.OBJECT_ID)			AS TableName

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

		, i.index_id					AS IndexID

		, CASE i.[type]

		WHEN 0 THEN 'HEAP'

		WHEN 1 THEN 'Clustered'

		WHEN 2 THEN 'NonClustered'

		WHEN 3 THEN 'XML'

		WHEN 4 THEN 'Spatial'

		END						AS IndexType

		, i.is_disabled					AS IsDisabled

		, CASE

		WHEN i.data_space_id > 65600 THEN ps.name

		ELSE f.name

		END						AS FG_or_PartitionName

		, p.partition_number				AS PartitionNo

		, p.[rows]					AS [RowCnt]

		, p.data_compression_desc			AS CompressionType

		, au.type_desc					AS AllocType

		, au.total_pages / 128				AS TotalMBs

		, au.used_pages	/ 128				AS UsedMBs

		, au.data_pages	/ 128				AS 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: 61136 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 reserved data index_size unused

TableA 1439999 695416 KB 507048 KB 182912 KB 5456 KB

Reporting Server

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

name rows reserved data index_size unused

TableA 1439999 656904 KB 483664 KB 172680 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