transactional replication pages

Last Post 04 Nov 2012 09:58 PM by corusdba. 16 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
corusdba
New Member
New Member

--
31 Oct 2012 04:30 AM

I have setup transactional replication on two machines with sql2008
almost the same rows uses a lot more pages ? whats the reason and how can I shrink this.

DB0

name reservedpages usedpages pages Size(B) rows
---------- -------------------- -------------------- -------------------- -------------------- --------------------
TagData1s 12294265 12292751 12235015 99522112096 22939196

DB1

name reservedpages usedpages pages Size(B) rows
---------- -------------------- -------------------- -------------------- -------------------- --------------------
TagData1s 23047895 23045046 22939245 186572692416 22939245

thank in adv for any help
rm
New Member
New Member

--
31 Oct 2012 07:54 AM
Has clustered index in the table in DB1?
corusdba
New Member
New Member

--
31 Oct 2012 11:06 PM
Hi thank you for your help;

Yes on both there is a clustered index ;

object_id name index_id type type_desc
----------- -------------------------------- -------------------------
1293247662 PK__TagData1__321462176B0FDBE9 1 1 CLUSTERED


and see storage output below;

on the DB0

tableName indexName type_desc partitions rows dataPages rowsPerPage
----------- ------------------------------- ----------- ----------- -------------------- -------------------- --------------------
TagData1s PK__TagData1__321462176B0FDBE9 CLUSTERED 1 23005459 12301278 1

on the DB1

tableName indexName type_desc partitions rows dataPages rowsPerPage
----------- ------------------------------- ----------- ----------- -------------------- -------------------- --------------------
TagData1s PK__TagData1__321462176B0FDBE9 CLUSTERED 1 23005554 23005554 1

rm
New Member
New Member

--
01 Nov 2012 06:19 AM
Then you can rebuild clustered index to defrag table.
corusdba
New Member
New Member

--
01 Nov 2012 06:33 AM
thank you,

but there seems no fragmentation at all;

object_id index_id avg_fragmentation_in_percent page_count
----------- ----------- ---------------------------- --------------------
1293247662 1 0.450429407516095 23033132

and if I reabuild the index should I stop the replciation process ? (how?) because its no EE edition which covers online rebuild.
rm
New Member
New Member

--
01 Nov 2012 04:38 PM
Then why subscriber has so many more pages? Do they have same indices? Did you check with sp_spaceused?
corusdba
New Member
New Member

--
01 Nov 2012 11:13 PM
Hi,

only 1 indexes (the one I showed you) is on the table on both databases.
Show the question why it use so much pages on DB1 while the have both contain same count of rows ?



rm
New Member
New Member

--
02 Nov 2012 04:52 AM
How did you get page counts? Tried with sp_spaceused? It tells data size and index size of the table.
corusdba
New Member
New Member

--
02 Nov 2012 05:02 AM
the page coutn is doen by this script

select o.name
, reservedpages = sum(a.total_pages)
, usedpages = sum(a.used_pages)
, pages = sum(case when a.type <> 1 then a.used_pages
when p.index_id < 2 then a.data_pages else 0 end)
, SUM(a.used_pages)*8096 AS 'Size(B)'
, rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end)
from sys.objects o
join sys.partitions p on p.object_id = o.object_id
join sys.allocation_units a on p.partition_id = a.container_id
where o.type = 'U'
group by o.name
order by 3 desc

but on disk the db file is much a twice so big..
rm
New Member
New Member

--
02 Nov 2012 11:24 AM
Did you confirm it with sp_spaceused?
corusdba
New Member
New Member

--
02 Nov 2012 11:49 AM
result from sp_spaceused

name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
TagData1s 23138544 185985272 KB 185108352 KB 853920 KB 23000 KB

rm
New Member
New Member

--
03 Nov 2012 09:38 AM
Have same results on both servers?
corusdba
New Member
New Member

--
04 Nov 2012 01:25 AM
on server db1

name rows reserved data index_size unused
----------- ----------- ------------------ ------------------ ------------------ ------------------
TagData1s 23138544 185985272 KB 185108352 KB 853920 KB 23000 KB

on server db0

name rows reserved data index_size unused
---------- ----------- ------------------ ------------------ ------------------ ------------------
TagData1s 23277518 101073808 KB 100586696 KB 474656 KB 12456 KB

rm
New Member
New Member

--
04 Nov 2012 09:44 AM
If table has only one clustered index, it's fragmented in dv1 per above. Try run 'sp_spaceused TagData1s, true' under db1, rebuild index if still shows same result.
corusdba
New Member
New Member

--
04 Nov 2012 10:38 AM
thank you I will give it a try.But its a replicated table does this not lock the table during the rebuild process ?
rm
New Member
New Member

--
04 Nov 2012 04:26 PM
It'll not affect replication, may see some delay. By the way, you may able to rebuild index online.
corusdba
New Member
New Member

--
04 Nov 2012 09:58 PM
rebuild online is only a EE function ?
but I will give it a try next couple days (when cust. agrees)
I let you know the outcome. thank you for your time.
You are not authorized to post a reply.

Acceptable Use Policy