Performance Degradation After Replication Rolled Out
Dec 14, 2006
Hi all.
Any assistance would be greatly 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?
Thanks in advance.
View 4 Replies
ADVERTISEMENT
May 20, 2008
Hi,
I have an ASP.NET application used in my university for student management. Any now and then the application throws an error like "DataBinding: 'System.Data.DataRowView' does not contain a property with the name XXXX" where XXX may be different. Of course that this properties exist and they are generated ok by the stored procedures.
After an recopilation everything seems to work nomally for a while.
I did not succeed in identifying the moment when the error is thrown but I suspect some reports in sql reporting services that lead to a server overload ??? that also is strange because the server specifications are quite ok, I think:
- OS: Windows 2003 R2, 64 bit
- SQL Server 2005 Standard, 64 bit
- 8 GB RAM
- Intel Xeon E 5345 2.33 Ghz (2 quad-core)
I would need help for:
- identifying the moment when this error occurs and _why_
- how could I repair it
- how could I distribute the load on more cores (from time to time Process Explorer from Sysinternals shows that only 1 core from 8 is working at full capacity)
Thanks,
Catalin
View 11 Replies
View Related
Apr 11, 2001
Hi all,
Up until recently one of our SQL 7 databases has been running quite happily. However over the last 2 weeks the users have started to complain about performance levles, operations that would normally take 2-3 seconds now run for about 40 seconds. I have rebuilt all indexes and even ran update statistics althought both the 'Auto create Statistics' and 'Auto update statistics' are turned on. This failed to help so I ended up stopping and starting SQL server but still no luck.
I have run a DBCC SHOWCONTIG and the fragmentation is well within acceptable levels, scan desnity is at 94%. The only other thing I can think of is to reboot the NT server as it has been up for 67 days now. Can anyone else think of anything I might have overlooked?
All replies welcome.
Thanks.
View 2 Replies
View Related
Mar 27, 1999
At approximately what db size is sql 6.5 known to degrade in performance?
Also, what is the maximum db size 6.5 can handle?
thanks in advance...
View 1 Replies
View Related
Apr 2, 2008
Thanks in advance !
We are having a situation (PROBLEM) with our DW - SQL SERVER 2000 SP4 ENT X86, it suddenly slowed a lot.
CPU Usage : 20%
IO : AVR DISK QUEUE LENGHT Between 1.5 and 2 (rarelly spikes (20/40)).
MEM : 8 GB, 6GB to SQL SERVER.
All the code that is running against the DB is fined tuned ( Most tables are Index Seeked with Clustered Indexes ), biggest table 450 GB.
No Lock Waits ( Except the main process(spid) that is showed has locked by it's own spid ( latch_xx (sh,ix)).
I guess that the problem is related to storage, because it is only doing 1-3MB sec (sometimes 20MB/sec).
PerfMon ( PROCESS SQLSERVER ( DISK Read Bytes ( between 1-3MB sometimes 20 MB/s), Disk Bytes Write Bytes/Sec same as Reads).
No more sql server instances or other software hogging the server.
I can't understand why the SQL SERVER is never using the IO it is used to use (25MB/S).
I don't see any DISK QUEUE on the Partitions that are used to store the datafiles/logfiles)
No Pages splits in the SQL SERVER ACCESS METHODS.
Sometimes I get about 1 GB in dirty buffers.
98 % Cache Hit Ratio.
ALL points to IO contention, but the counters from windows don't indicate this.
All help is REALLY appreciated.
Best Regards.
View 1 Replies
View Related
Oct 21, 2007
Hello,
Working with SQL Server 2000, I have a table with the following structure:
ID (INT)
userID (INT, foriegn key)
productID (INT)
productQTY (DECIMAL(5,2))
purchaseDate(smalldatetime)
I have about a 1000 users, entering about 20-30 rows per day each, i.e ~20,000 - 30,000 new rows per day. The table might be queried with a simple "SELECT" for the products a user ordered per day or per time frame (purchaseDate column).
My question (finally) is - when should I expect to see performance degradation? Is there anything I can do to prevent it (i.e splitting this table somehow to several tables)?
Thank you all in advance
View 2 Replies
View Related
Jul 20, 2005
Hello guys,Wonder if any of you could help me out here. I have just created annew empty database and imported data from another database into it.This was done with the import wizard from MMC.First thing that I noticed was the size difference, the old databasewas well over 1GB, but the new one was only about 400MB.Second thing I've noticed, and this is the problem, is that accessingthe new (smaller) database instead of the old one causes a huge speeddegradation, about 5 times slower than the old version.We are using MS SQL Server 7. Any help would be very gratefullyreceived.RegardsGethyn
View 1 Replies
View Related
Mar 24, 2008
Hi all,
I am experiencing performance problems with one of my stored procedures. When the stored procedure is first compiled an executed, it behaves as expected (it usually takes 1 or 2 seconds to complete). But its performace it is degradated, so in 1 day, it usually takes 120 seconds to complete !!!. Once the stored procedure is compiled, its performance it is then the expected.
It is a complex stored procedure with two integer parameters with only one select, but composed by multiple views and sub-queries. We have been trying to break the query into small pieces using temporary tables but without success. The SQL Profiler shows an unusual number of reads when it goes wrong (more than a million reads).
I think the problem is in the execution plan. I know than compiling the stored procedure, the problem is fixed, but I do not know exactly when and why it starts to happen.
The stored procedure is running under the following configuration:
- Microsoft SQL Server Standard Edition (64-bit).
- Version: 9.00.1399.06
- RAM 16 MB
- 8 CPUs
Anyone has any ideas or possible solutions?
Thanks in advance,
Carlos.
View 2 Replies
View Related
Oct 11, 2006
I'm trying to install the MSDE2000 on window xp, sp2. During the installing, apperance the percent bar is rolling back, which is not even completed install. I had read the issue from microsoft to restart the server service, and...but no luck. Is there anyone has an ideas.
View 1 Replies
View Related
Mar 4, 2008
Hi,
Is there a way to write to a log-table inside a transaction which is rolled back without rollback of this log-entry.
thanks in advance
Raimund
View 9 Replies
View Related
May 4, 2015
I am confused about save transaction in the below scenario :
begin transaction
save transaction t1
delete from #t1
save transaction t2
begin try
delete from #t2
[Code] ....
If there is error after delete #t2 , transaction t1 is rolled back. But i am not able to understand why i am getting error in the statement 'rollback transaction t2' . I am getting error as 'Cannot roll back t2. No transaction or savepoint of that name was found.'. but save point t2 is mentioned in the code.
View 3 Replies
View Related
Apr 30, 2007
Hi all,
Sometimes when I do "alter database ABCD set partner failover" I get the following message: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
In 99 percent of the cases after such message the first attempt to use an open connection would also raise an error such as "Exception: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
After the first error all subsequent queries would run perfectly.
What am I missing?
Avi
View 4 Replies
View Related
Jun 19, 2015
I have the following scenario. I want to apply some calculations on different levels and then aggregate them up.
First measure calculates at Productgroup,color,store,size level
ProductGroup Color Store Size Amount Quantity
Measure:Amount*Quantity (ProductGroup, Color, Store, Size)
A Blue Store A L 100 6 600
A Red Store A S 150 4 600
A Green Store A M 160 7 1120
B Blue Store A L 300 3 900
[Code] ........
The other measure ignores color
ProductGroup Store Size Amount Quantity
Measure:Amount*Quantity (ProductGroup, Store, Size)
A Store A L 100 6 600
A Store A S 150 4 600
A Store A M 160 7 1120
B Store A L 640 15 9600
[Code] ...
Ignoring that gives another figure for productgroup B. In the pivot, I should see both measures at whatever attribute, except for the measure that excludes color will be null if tried split on color
ProductGroup Amount Quantity (ProductGroup, Color, Store, Size) (ProductGroup, Store, Size)
A 410 17 2320 2320
B 640 15 2820 9600
C 170 5 430 430
How should the two measure be defined:
Measure (ProductGroup,Color,Store,Size)
Measure (ProductGroup,Store,Size)
View 2 Replies
View Related
Jun 25, 2006
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?
View 1 Replies
View Related
Oct 26, 1999
Can anyone suggest hwo can I improve the performance of the replication process and make it faster.
Pran
View 1 Replies
View Related
Mar 9, 2004
Hi all,
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?
Anyone have any thoughts on this?
Thanks in advance.
Cheers
View 6 Replies
View Related
Aug 24, 2006
Hello,could you please advice on how to measure replication performancein 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 theminto databases.Also, I configured the replication.Now I have to do a test with a few kind of replications methodimplemented 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 meanOracle <-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 Managementfor distributed RDMBS, and I think it could be the right one, but Ican'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 mechanismefficiency ?Regards,Mark
View 5 Replies
View Related
Jul 20, 2005
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.
View 1 Replies
View Related
Apr 18, 2006
Hello,
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.
thanks in advance
View 3 Replies
View Related
May 15, 2007
Hello to all,
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.
Can I have any input on this?
Thanks!
View 1 Replies
View Related
May 30, 2007
Since as soon as you extend your mdx datasets manually you can no longer switch back into design mode without losing your changes, right?
If that's the case, is there some way to disable design mode completely? i'm finding that the GUI has the tendency to SILENTLY revert the dataset editor back to design mode while I'm busy editing a layout, thereby losing my carefully crafted MDX.
View 4 Replies
View Related
Feb 25, 2004
Hi
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.
View 1 Replies
View Related
Dec 18, 2006
Hello,
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.
Thanks,
Adrien.
View 6 Replies
View Related
Jun 29, 2006
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.
View 4 Replies
View Related
Sep 27, 2007
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?
Help please.
View 19 Replies
View Related
Jun 7, 2007
Hi All,
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 )
View 6 Replies
View Related
Feb 25, 2008
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?
Thanks for the input.
View 1 Replies
View Related
May 14, 2007
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)
distribution
DB size: 3111 MB
Logging: Simple (at this point)
View 1 Replies
View Related
Jun 10, 2015
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
Any way to turn this into a meaningful metric?
View 0 Replies
View Related
Sep 21, 2007
Hello,
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.
Am I going on the right direction?
View 14 Replies
View Related
Jan 8, 2007
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.
From,
Hans
View 1 Replies
View Related
May 11, 2006
Hi,
I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
Thanks for any help,
KT
View 8 Replies
View Related
Feb 21, 2007
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.
looking for any experieced solution/suggestion.
Thanks in advance.
View 3 Replies
View Related
