could you please advice on how to measure replication performance
in Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?
I've got two servers with databases installed and configured,
I prepared set of data using DBGEN from TPC and I already imported them
into databases.Also, I configured the replication.
Now I have to do a test with a few kind of replications method
implemented in these RDMBS, but I don't know which tool or reports or
"v$iews" should I use to measure replication performance.
The replication is configured only between the same RDBMS, I mean
Oracle <-Oracle, DB2 <-DB2 and MSSQL<-MSSQL.
Most of applications are great for checking performance of local DB,
not for replicated/distributed.
I've found description of CA Unicenter Database Performance Management
for distributed RDMBS, and I think it could be the right one, but I
can't find any demo or trial version :(
Could you please advice any place to download it, or other application,
script, description, just whatever.
Perhaps just any other idea how to check the replication mechanism
efficiency ?
I need to create one or two tables in the database. There are a few ways I can design these tables, so I€™d like to measure data loading performance. My primary concern is level of IO generated by SELECT. I don€™t expect CPU utilization to have any meaningful impact, but it would be nice to know how to measure processor utilization as well. PerformanceMonitor application is not really an option, because it measures IO and CPU at SQL Server process level. My development box is not going to match production boxes, so I would like to measure IO in terms of amount of data loaded from disks instead of timing stored procedure execution. The same is true for stored procedure CPU utilization. It is my understanding that SQL Server keeps table content loaded in memory as much as possible to improve performance. How do I negate effect of caching on my IO numbers?
I have application who like to use HTTP Endpoints in our cosolidate enviornment. How I can measure the performance on HTTP Endpoints? I would like to know how much CPU, Memory and IO used by HTTP Endpoints?
In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).
how should I create the calculated measure for that?
I have tried in mgmt studio to plan this but I'm in a loop of errors.
I am wondering how to create a matrix that contains 1 dimension for Top Label (Column), let's say "Year-Month" and then 2 Measure to be in the row format rather than columnar format. Example as below : Year-Month on the column, and the measure is on the row :
2007-04 2007-05 2007-06
Amount Sales 1000 2000 3000
Unit Sales 10 20 30
Total 1010 2020 3030
Please share with me if you have this solution in Reporting services as it works in excel, hyperion brio, bo, cognos but somehow cannot see that function in Reporting Services.
In SSAS, I want to add a calculated measure and set that measure as the Parent of existing measure. For example I have the measures as A and B from the fact table. Now i want to add a calculated measure as C and set this measure as Parent for measures A and B. How to do that in SSAS.
Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.
Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?
I'm having a tough time finding any good resources on Sql Server Replication Performance. Are there any benchmarks / state of any kind? How well does replication scale out?
In my scenario, I have one central publisher and several large tables, all with hundreds of millions of records. Every day I may insert/update millions of records in the publisher, and then I need to replicate the changes (in a few hours at most) onto a pool of subscribers, while they remain online.
Is the replication story robust enough to handle a situation like this?
I am keen to hear peoples perspectives on how much additional load Transactional Replication will have on a server. Obviously this will depend greatly on the level of transactions in the database, but a general indication would be great (eg 10% increase in overheads).
I am thinking of encorperating this into a new server structure which we are going to be setting up and am unsure as to whether to make the primary server BOTH the publisher and distributor; or make the secondary server the distributor to reduce the load on the primary to only being the publisher.
Basically the secondary server will simply be a 'hot swap' of the primary - so i/o load on the secondary is not going to be an issue. There may be 2 primary's (if that makes sense) replicating to the hot swap so that if either primary is dropped the hot swap could take over either servers load/responsibilities - not sure if this will make a difference on where to put the roles?
I'm seearching for information regarding database replicationperformance. We need to compare the performance of replication for SQLServer and Oracle and it is urgent! Anyone who can describe theperformance bottlenecks for each database when performing replication,or can point me to a white paper or webpage.
I am trying some replication sample.I create a table with thousands of records in the publisher side. as I create a subscription(the subscription database is on a remote machine), the whole table is created on the remote database.
I wanted to measure the performence as: 1. how much time was taken in filling the whole table in the subscriber side? 2. If i insert some 10000 records on publisher side, I want to measure, how much time was taken in inserting the same records on the subscriber?
How do I measure this ? Can I use some Log reader stuff.
I have a performance question: we have a cluster with 2 SQL instances on 1 node (another instance is on another node, but no link with my current problem!). Let's call them C1SQL1 and C1SQL2.
This node is a Hyperthreaded Xeon 2.8Ghz with 1 gig of memory.
These 2 instances are using transactionnal replication and are configured as the distributor and publisher. C1SQL1 is not using much power, it's a small replication with around 10 agents. C1SQL2 is a bit heavier, with around 100 distribution agents. C1SQL2 has around 50 subscribers in 12 publications, but not all subscribers are used in each publications.
Once in a while, this cluster node impacts our production environment (since it's also a production server) and we're wondering if performance wise, it's really not powerfull enough to be the distributor?
I've isolated C1SQL2 on it's own logical CPU, and in idle mode, the replication workload (history, checking if new transactions are made) peaks at around 15-50% each 4-10 seconds.
I have sucessfully set up transactional replication, allowing the subscriber to update the publisher. All works well for a while, but after a couple of weeks or so it fails, but always for a different reason !
My question is, is there anything that can be done to help replication stay healthy. I had thought of doing regular backups of the database and the transaction log, and then truncating the transaction log.
Any advice, or links to other troubleshooting resource much appreciated.
We recently created transactional replication to hopefully improve performance issues we were expereincing. The replication is between 2 SQL Servers (2000), and since we have introduced the replication, the performance has degraded considerably.
I will try and explain the scenario.
We have a primary db that our internal users use and we also have the newly replicated db that our website and another application use. The users are complaining that the website and the internal application is extremely slow and I was just wondering if it is possible to do an Index Tuning on both the primary db and replicated db based on trace files so as to create new indexes or would this have an impact on the replication?
I have been experiencing some difficulties with poorly performing synchronizations using replication from SQL Server 2005 to SQL Server Mobile running on Windows Mobile 5 devices. Currently there are two main databases (each client will only use one of them), the 1st one has around 500,000 rows, and the 2nd has about 1,200,000 rows. The initial synchronization for the 1st database takes around 45 minutes, and for the 2nd, around 2.5 hours. This is quite long, but we have comforted our clients by saying that this is a one time delay, and that further synchronizations will be much quicker. Well, synchronizing the data after this is usually quite speedy, however, things get bad rather quickly when the number of changes increases.
In normal cases, the client will have at most a few thousand changes and all is well, the synchronization will typically be under a few minutes - no big deal. Once in a while though, there are a substantial number of changes to the database (from the SQL Server 2005 side), perhaps around 50,000 changes. When this happens, the synchronization process doesn't seem to ever finish (I've left it over the weekend and come back to find it still synchronizing). For the record, there seems to be a level at which the database will finish synchronizing, but be agonizingly slow - around 10,000 to 20,000 records will finish eventually (but take a few hours, at which point it's faster to just blow away the database and start again from scratch). This is obviously not acceptable, and I need to find a way to resolve this. Does anyone have any thoughts?
While on this topic, why does this synchronization process take so long anyways? The snapshot creation (even for the database with millions of rows) finishes in a couple minutes, and the actual transfer of data shouldn't take more than a few minutes. The device can't possibly be storing the database content in memory (the SDF file ends up being between 40MB and 100MB), but when I watch network activity, there tends to be an initial busy period, then a periodic and fairly small spike every few seconds until the process completes, so the connection isn't being saturated at all.
At this point, I am almost considering breaking the nice database design I have and creating combined logical records to see if reducing the number of rows may help. I'd really prefer not to have to go this route though, so if anyone has any suggestions, I'd really appreciate some feedback.
The client production server CPU starts thrashing. Task manager indicates that SQL server is gobbling CPU cycles. Having a look at the replication monitor, it is obvious that indivual synchronisations to the mobile devices are taking significantly longer expected.
Observing an indivual synchronisation attempt, the Upload changes to Publisher rows are very quickly resolved.
The Download changes to Subscriber seems to take up a very long time.
Along the way, the estimated completion does a few interesting things, like going from 100% complete with no estimated time to complete, back to seomthing like 77% with 2 minutes left to complete.
This sort of behaviour occurs when there are only a hundred rows to download.
Synchronisations for minimal amounts of data suddenly taking anywhere from 2 to 15 minutes. Totally unacceptable form the client perspective but seems that 2005 behaves quite different from 2000 and the tricks are yet to reveal themselves.
Note - It is a server hardware issue as there is in excess of 3 GB ram, the database is on a SAN and there are 4 3Ghz CPUS in operation.
Any possible help appreciated as this issue is beginning to drag on.
I have just upgraded from 2000 to 2005 and my transactional replication is running very slow, I already have a latency of 10 mins and its getting worse. I'm just using the default agent profile, is there anything I need to change?
We are developing a system which will have to support more than 3000 subscribers. We will have to support both Transactional replication and Merge replication.
I checked the following document about SQL 2005 replication <http:// www.microsoft.com/technet/prodtechnol/sql/2005/mergrepl.mspx>. The document does not clearly specify what is the maximum number of subscribers supported without a significant performance degradation.
The questions i have are: 1. Given the fact that there will be more than 3000 subscribers, there will be more than 500-1000 subscribers trying to replicate at the same time. Will be there be a performance degradtion in such a scenario
2. Has anyone used SQL Server 2005 in a scenario involving more than 3000 subscribers?
3. Will it be better if we develop our own system to perform replication activity instead of relying on SQL Server 2005?
- Ngm
Mail me atnarasimha (DOT) gm (AT) gmail (DOT) com )
Wondering if anyone has any experience with SQL Server Express Edition (SSEXP). We're looking at a mobile sales force type model, so a local database on a laptop with no real time network connection. So the users would collect data locally, then connect up to the network every few days to replicate the data to a central server. So questions.. Has anyone tried anything similar? How stable/mature is SSEXP? Any other thoughts, alternatives or gotchas anyone can think of?
Summary: Started replication April 1 of 4M xact / day publishing system to subscribing system.
Performance was good. Latency was ~ 5-7 seconds.
May 10 we noticed that the DB was behind (latency was 12 hours).
All performance counters seem good with the exception of the disk.
. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.
. During this period, Disk % Busy (1 - Disk % Idle) is 100%
The publisher DB publishes about 50-52 xacts/sec.
Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.
I know this because several weeks ago, the network went down, we were 24 hours behind.
When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.
What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.
We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).
I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.
My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.
Thanks for any help on this!
Jack Griffith
Configuration:
Subscribing System:
SQL Server: 2000, SP4 - 8.0.2039
CPU - 2.8GHZ Xeon, Quad Dual-core
Memory - 3.5GB RAM
Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:
C: 39GB System and Programs
D: 97GB Log space
E: 982 GB Data space
Replication configuration:
- nosynch, continuous Transactional Replication - Distribution db is on Subscription system - distribution - Publication of approx. 50 transactions / second
Subscriber DB configuration: DB size: 64458 MB Logging: Simple (at this point)
I've been asked to put together an estimation for the performance impact that replication would have on our database server during a particular operation. I know that this depends on a lot of different factors, including:
* Number of articles being replicated * Types of articles being replicated * Number of DML transactions that would result in delivery of replicated data
We previously having two servers A and B. Server A is used for updation of data and the data then replicated to server B. Server B is used for
Server A : purpose : used for database updation/ modification SQL Server version : SQL Server 2000 SP 2
Server Z : purpose : used for Reporting SQL Server version : SQL Server 2000 SP 2
We were doing Transactional replication from Server A to Server B.
Last month we have broght another server (Server B) with same hardware configuration but having SQL SERVER 2005 installed. This is to speed up our database update process. We have moved some of the database on this new server so that we can achieve our deadlines.
Server B : purpose : used for database updation/ modification SQL Server version : SQL Server 2005
I have set up the transactional replication from Server B to Server Z and replication works fine. However, the issue is after it is started replicating from this new server (Server B) performance of all the queries reduced a lot.(making my life harder)
I didnt expected this as our reporting server is still SQL server 2000. I have restored the backup of database which was replicated from server A (sql server 2000) and compared execution plan for one of our common query (which is used in most of the reports and which is now taking longer time to provide results)
I found that database which is replicated from Server B (Sql server 2005) is having primary keys. which was not present in the database which replicated from server A(Sql server 2000).
I have then removed the primary key and make the indexes same as previous copy of database(which was replicated from server A) But still the query takes long time.
Execution plan now shows "Table Spool" which was not present in previous copy of database.
Almost every query for this database is taking longer time now.
Can someone suggest me what is wrong and what should I need to fix.
Hi guys , may I know is there any examples of store procedure/scripts for monitoring replication status and performance ? I just know about sp_replmonitorhelppublisher. Thx for the assistance.
We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.
1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).
2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..
on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.
can any one can also suggest me server level settings for aviding locking time out.
We have a SQLServer 2005 Enterprise merge replication publication with SQL Mobile 3.0 subscribers (Windows Mobile 5.0 and 6.0). We do not use pre-computed partitions due to trigger performance issues with an SSIS/ETL application that supplies data to the merge database. We do use the "Optimize" (=true) option, though we have tried this both ways with no significant differences. We use filters and joins for each worker ID (as HOST_ID) from the subscriptions.
The sync times become increasingly worse after we run the snapshot and bring the publication online. I have tried rerunning the snapshots, this helps little, as it often behaves like the subscription was set to reinitialize and forces a big sync (reload of all data) to the subscriber. We have tried much of the obvious (e.g., flattening filters and joins, adding indexes, etc.).
When users are synchronizing, we watch replication monitor and notice that a lot of time is spent processing "enumerating inserts and updates for article [any article]", especially processing the many generations and batches. This is true for any follow-up syncs after the 1st big sync (initializing the subscription).
I read several posts regarding the batches and generations of changes, and decided to try increasing the €śDownloadGenerationsPerBatch€?. I tried adding this parameter to the snapshot agent job, and the job fails each time with a vague message, even with the default value of 100. How do you change this parameter for SQLServer 2005 Enterprise?
I have a dimension called "Movement rate". The members are "1" to "6". There is a measure called "No. of Products".
What i easily like to do is showing the percentages "No. of Product" for each "Movement rate" of the amount of "No. of Products" without using the frontend-tool.
There is count measure in the cube. I need to create a dimension that should list the range of values based on that count values: Exp:
0-99 100-199 200-399 ... .... .. and so on. User wants to pick the any range level in the dimension and cube should show corresponding cell values of count measure which would fall into range of the dimension level value. For example: if user picks up level 100-199 then cube would filter cell values of measure that has a value between 100 and 199 within 12 month range. So, it would be year to date value of the measure. My problem is how go about creating Dimension that based on measure in the cube and dimension should have level with range of measure values. Any hint would be appreciated greatly. Thank You.
for a calculated measure I need the total sum of a measure in different dimension. So for example I want to get in my first dimesion 1500 as my sum and in my second dimension too. Is there a way to get it? (FirstDimension.CurrentMember.Parent, [Measures].myMeasure) doesn´t work!
Hi, I am newbie in Analysis services. My problem is that I have to show a yearly report for credit expiry. In my fact table the measure is credit rate. I have attributes of start_day_key and end_day_key. Now when I go through analysis services it does not let me create a measure so that I can count end_day_key for a year (end_day_key is linked to day table with daykey. It has date) and show. The thing is the measure I want is not stored (i.e: the count of end days for a year ). How do I go about it?
Should I create a calculated cell? (I tried that, but mdx does not work) I used : Count({[Measures].[End Key]})< 366 Now this is not wha I want, I want the end_key count by matching date in day dimension for the current year? Any help is greatly appreciated.
