April 10, 2008 at 12:50 pm
Hello,
I was wondering if someone can assist with the following issue I'm having with transactional replication on SQL 2005 --> SQL 2005.
I have 3.9million commands sitting in the distribution database since last night with 2 subscribers. Replication latency is 'critical' and I'm getting:
query timed out or
...waiting for a response from the server
I have went thru many configuration changes, articles, and lot of web searches since this morning.
My distribution database initial size is 10GB, grow by 1GB, maxsize is 50GB.
My distribution log file is: 10GB, grow by 1GB, maxsize is 100GB.
The replicating database initial size is: 75GB, grow by 200MB, maxsize is 90GB
Its log file is: 10GB, grow by 500MB, maxsize is 400GB
A MS technet article suggest that my initial size and autogrowth is too small because replication has to read the virtual log. I don't believe this is the case because my settings are pretty damn high.
I'm replicating just one table (which contains the 3.9million commands) to two subscribers. The subscriber's database file options are set to the same as the publisher.
For my distributor agent, I've set:
bcpBatchSize = 5000
commitBatchSize = 500
keepAliveMessageInterval = 4000
maxDeliveredTransaction = 70000
queryTimedout = 65000
transactionsPerHistory = 500
I read from a few places that they dropped or rebuild the clustered index and this solved their problem.
So I did a check on my indexes using dbcc showcontig ('item') and got the following:
DBCC SHOWCONTIG scanning 'item' table...
Table: 'item' (533576939); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 951500
- Extents Scanned..............................: 119355
- Extent Switches..............................: 805587
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 14.76% [118938:805588]
- Logical Scan Fragmentation ..................: 84.72%
- Extent Scan Fragmentation ...................: 46.24%
- Avg. Bytes Free per Page.....................: 4147.9
- Avg. Page Density (full).....................: 48.75%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Fragmentation and scan density is pretty high.
Any suggestion would be great because I'm almost at wits end.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
April 10, 2008 at 1:48 pm
doublecheck with the SQL 2005 system view for fragmentation since dbcc showcontig is horribly inaccurate
and check for maintenance on the subscriber blocking replication. if you have enterprise edition make sure you are using online maintenance
April 10, 2008 at 2:17 pm
no open tran on the subscribers. when I run dbcc opentran() on the published database, I see:
Transaction information for database 'dbName'.
Replicated Transaction Information:
Oldest distributed LSN : (7949:110271:1)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
No open tran in the distribution database.
I looked at the index fragmentation via the system view [sys.dm_db_index_physical_stats(db_id(),null,null,null,default)] and it is about right, high in fragmentation.
I was about to rebuild the index and then realized that I needed to know what the existing fillfactor is. Did a search on sys.indexes where name like 'item%' and found that the fillfactor is '0'.
I don't know if that is correct or not but the table has 1332423 records and the fillfactor is '0' ?
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
April 10, 2008 at 10:47 pm
well, my replication was working correctly last night until I stopped the subscription and used a new profile which has:
maxDeliveredTransaction = 70000
I read multiple places about this parameter. The distribution agent would send out 70k commands and then stopped. No where did I read about this or it stating that it will cause replication to stop after that many commands was sent thru.
Distrib [-MaxDeliveredTransactions]: This sets the maximum number of transactions
that are sent to the subscribing server during one synchronization. A value of 0 means an
infinite number of transactions can be sent. We should consider resetting this to an optimum
value, such as 1000, to shorten
Since I have continous transactional replication, I assumed that when it is set to continous, until I stop the subscription manually, the stream will remain or consider 'one synchronization.' But this isn't the case.
That is why I was seeing it stop at 70k. I have changed this back to 0 and replication is moving slowly but surely.
Wasted a whole day.... :sigh:
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
April 14, 2008 at 9:43 am
1. as you do I/U/D statements a db will incur fragmentation [akin to breathing]
2. replication is chronological replay of what happened at pub on each sub
-> hence you can also expect similar levels of fragmentation on each sub
3. optional to have the same indexes on the sub as you do on the pub
- indeed often a sub is used for reporting so will need different indexes to support workload
-> hence frag levels on the sub may differ (better/worse) than at pub
4. DBA will normally have a reindex maintplan (e.g. Sundays) to sharpen up the indexes
5. a reindex does not change the actual data values [but will change the leaf data for CI]
-> hence reindex will not generate repl traffic to sub(s)
-> reindex on pub will not sharpen up the sub, so you need equivalent reindex maintplan !
if you have significantly different indexes at sub, or do not conduct reindexes there, you can expect reducing performance.
6. having a BEGIN TRAN .. 3.9M I/U/D .. COMMIT is going to be expensive on tranlog etc
-> WHERE POSSIBLE, break operation into smaller blocks
- also shorter transactions = better concurrency
7. since repl handles row-at-once and has network to cross, I/O to sub may be slower than pub
-> you should pick a profile [or roll your own] so that you don't get timeouts [raising alerts]
8. sometimes the purpose of the sub is for reporting and may have a superset of the data held at the pub (optimised for OLTP behaviour), and various partitioning schemes may be used at sub
-> DBA needs to assess what the business needs and take appropriate steps to make it happen
HTH
Dick
April 14, 2008 at 11:44 pm
Thanks Dick. I have plans for all those items you mentioned in the pipe line. Hopefully, I'll have it all done tomorrow.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
December 9, 2008 at 1:43 pm
😀
I have a question about the DBReINDEX.
We have a really nice MS Access DB that uses linked tables to SQL Server 2005. Having just started, I ran a dbcc showcontig script on my database.
e.g. dbcc showcontig (969770512,1) -- tblDays.PK_tDays
The results were:
DBCC SHOWCONTIG scanning 'tblDays' table...
Table: 'tblDays' (969770512); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 27
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.5
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 29.63%
- Extent Scan Fragmentation ...................: 63.64%
- Avg. Bytes Free per Page.....................: 885.3
- Avg. Page Density (full).....................: 50.00%
So, I ran a DBCCREINDEX - and included a leaf value of 90%
DBCC DBREINDEX (tblDays, '', 90)
The Question is:
My Scan Density did not change at all! Why? :w00t:
My Avg.Page Density did change to 90% 😎
December 9, 2008 at 3:10 pm
Rob X Miller (12/9/2008)
😀I have a question about the DBReINDEX.
We have a really nice MS Access DB that uses linked tables to SQL Server 2005. Having just started, I ran a dbcc showcontig script on my database.
e.g. dbcc showcontig (969770512,1) -- tblDays.PK_tDays
The results were:
DBCC SHOWCONTIG scanning 'tblDays' table...
Table: 'tblDays' (969770512); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 27
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.5
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 29.63%
- Extent Scan Fragmentation ...................: 63.64%
- Avg. Bytes Free per Page.....................: 885.3
- Avg. Page Density (full).....................: 50.00%
So, I ran a DBCCREINDEX - and included a leaf value of 90%
DBCC DBREINDEX (tblDays, '', 90)
The Question is:
My Scan Density did not change at all! Why? :w00t:
My Avg.Page Density did change to 90% 😎
your table is too small -> 27 Pages.
* Noel
December 9, 2008 at 4:20 pm
Your right!
I was touring around the site and found this out a few minutes ago.
followed the thread: http://www.sqlservercentral.com/Forums/Topic458746-146-1.aspx
I should have realized that.
I do have a few tables that will be big enough. The article above also listed an issue with restoring a database from SQL 2000 to SQL 2005.
Since I took over this project, it might actually be something else I need to know.
Thanks so much for the quick response!
December 10, 2008 at 9:46 am
DECLARE @dbname VARCHAR(20)
SET @dbname = 'MyDBName'
--These indexes should be rebuilt, not reorganized
-- Alter Index ... Rebuild
select *
from sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')
where (avg_page_space_used_in_percent < 60
OR avg_fragmentation_in_percent > 15)
and page_count > 27
-- The page count shortened my list to about 1/3 the size
How do I get a match with my TSQL script that produces my DBCC shwocontig (ID number)
The script above ID numbers did not match myh ID numbers shown from a script posted below.
dbcc showcontig (395148453,1) -- tScheduleTransportMiscDetails.PK_tScheduleTransportMiscDetails
dbcc showcontig (404196490,1) -- tStorageLocations.PK_tStorageLocations
dbcc showcontig (484196775,1) -- tConfirmationMonthObligations.PK_tConfirmationMonthObligations
Above output created from:
SELECT 'dbcc showcontig (' +
CONVERT(varchar(20),i.id) + ',' + -- table id
CONVERT(varchar(20),i.indid) + ') -- ' + -- index id
object_name(i.id) + '.' + -- table name
i.name -- index name
from sysobjects o
inner join sysindexes i
on (o.id = i.id)
where o.type = 'U'
and i.indid < 2
and
i.id = object_id(o.name)
ORDER BY
object_name(i.id), i.indid
-- The output is the command lines for each table – I had hoped the ID numbers from the first script would match the ID numbers in the second script - this might give me a join to evaluate the DBCC Showcontig for those tabels with a page count >27
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply