July 2, 2010 at 4:03 am
I have several repl enviroment ,how to get the repl perf using command or DMV/DMF,thanks a lot!
July 2, 2010 at 4:44 am
Not exactly sure what you are after, but try these..
sys.dm_repl_articles
sys.dm_repl_schemas
sys.dm_repl_traninfo
sys.dm_repl_tranhash
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 7, 2010 at 1:17 am
You can use sys.dm_os_performance_counters from your distributor to access the perfmon counters for replication:
\SQLServer:Replication Logreader\Logreader:Delivery Latency
\SQLServer:Replication Logreader\Logreader:Delivered Cmds/sec
\SQLServer:Replication Logreader\Logreader:Delivered Trans/sec
\SQLServer:Replication Dist.\Dist:Delivery Latency
\SQLServer:Replication Dist.\Dist:Delivered Cmds/sec
\SQLServer:Replication Dist.\Dist:Delivered Trans/sec
\SQLServer:Replication Snapshot\Snapshot:Delivered Cmds/sec
\SQLServer:Replication Snapshot\Snapshot:Delivered Trans/sec
David B.
David B.
July 7, 2010 at 8:20 am
DMVs\DMFs don't tell you anything about latency. Three ways I suggest:
1. Replication monitor. Pretty sure most everyone who works with replication knows this one.
2. Tracer Tokens (See BOL here: http://msdn.microsoft.com/en-us/library/ms188024(SQL.100).aspx)
3. System stored procedures. On the distributor in the distribution database you can run these (undocumented in BOL) procedures:
sp_MSenum_snapshot - to show snapshot agent information
sp_MSenum_logreader - to show logreader agent information
sp_MSenum_distribution - to show distribution agent information
sp_MSenum_merge - to show merge agent information
sp_MSenum_qreader - to show queue reader agent information
The latency information in each of these comes from the most recent entry for each agent in the replication history tables in the distribution database so they are not *always* 100% accurate for a numbe of reasons, but they're close. Latency values are in milliseconds. To display them in hours, minutes, and seconds you can do soething like:
Convert(varchar,((delivery_latency/1000)/60)/60) + ':' + Right('0' + Convert(varchar, ((delivery_latency % 3600000)/1000)/60),2)
I also suggest reading "How to: Programmatically Monitor Replication" in BOL for other stored procedures that you can call: http://msdn.microsoft.com/en-us/library/ms147874.aspx
July 7, 2010 at 8:21 pm
Now,I use system SP :sp_replmonitorhelppublication,that seems work ,but another question comes,this three column is all 'NULL':
worst_latencybest_latencyaverage_latency
NULL NULL NULL
greatly confused!
I use publisher as distributor ,that is the reason?
July 7, 2010 at 8:44 pm
These procedures pull from the distribution database and it's populated by all the various agents the same way regardless of if the publisher and distributor are the same instance.
Have you looked at sp_replmonitorhelpsubscription? If you want subscriber latency information that procedure call will get it for you. Here's an example call to show information about every subscription for transactional publications:
EXEC sys.sp_replmonitorhelpsubscription @publisher = NULL, -- sysname
@publisher_db = NULL, -- sysname
@publication = NULL, -- sysname
@publication_type = 0, -- int
@mode = 0, -- int
@topnum = 0, -- int
@exclude_anonymous = NULL, -- bit
@refreshpolicy = 0 -- tinyint
July 8, 2010 at 1:19 am
Kendal's last answer is what I after!
Thanks to you ,thanks to all .
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply