Replication Performance Degrade In Unidirectional Direction And Lock Time Out (Update Are High Than Inserts)
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.
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 would like to share with you the following performance issue:
Configuration-Server
SQL 2005 workgroup edition
Windows 2003 server Small business
2 cpu
3.5 Gbytes Ram
RAID 0
boot.ini /3Gb userva=2560
Page file 2-4 Gbytes to each drive (two drives)
mdf file ~ 300 Mbytes
SQL dedicated server
Configuration-Clients
Windows XP
1 Gbyte RAM
Visual studio 2005 application
9 users
The problem
The users work smoothly for several days. The sql service is running continuously. After few days we have complaints from the users that the system is slow, SPECIALLY when they execute specific queries.
What we have done
1. We refine several queries.
2. We monitored several counters, specially those that reveal performance problems. They all return reasonable values.
3. defragmentation of Hard discs.
4. Stopped the services that are not required.
5. Reindex and update statistics every night.
What we found
When the users are complaining we monitored cpu spikes for sqlserv. We tried to find the reason but failed. The PF is around 2.1 Gbytes, sqlsrv Memory is around 1.5 Gbytes.
What we do not understand is why the system is not slow when we restart the sqlserv service. Also why after several days is becoming slow ??
Hello Guys, I have a Question. I want to set up a sql alert that will monitor a particular counter(Eg. Memory:pages/sec) and send me an email when it reaches a particular threshold. My question is if i set up this, Will sql will start running a perfmon on the background and degrade server's performance OR Will it just read it from some system file or something to to get the values.
I dont know if the perfmon counter values are stored in any system files or not.
I realise this is a stupid quesiton but i cannot really find any confirmation of this in BOL.
If you are running High Safety with automatic failover, when failover occurs does this automatically change to High Performance mode. SInce for failover to occur something has happen with the primary , it will be impossible to commit transactions on the new primary and mirror asyncronously since 1 of them is no longer available.
So am i correct in assuming that automatic failover also automatically changes the mode to High Performacne for that session?
one production server DB is getting slower... (SQL2K sp4 ent) 1. dbcc checkdb(abc) with 0 errors 2. no blocking 3. no abnormal in sql log and event system log 4. only found one crypt32 auto update fail last night.
the Lock Requests/sec is kind high from 5400 to 15400 on 0.01 scale. max is from 57611 to 155411.
Could I increase the locks assigned to sql and what is the best way for it?
I'm relatively new to SQL and I've come across something that doesn't seem quite right. When an insert becomes part of an transaction I notice an exclusive KEY lock in Enterprise Manager. The table in question was using a Clustered index but I changed that, dropped the table and brought it back in but I still get the lock which keeps all others out of the table. Is this the expected behavior or is there something I am missing? Could the size of the tabe affect things? This is a very small table currently. I'm using MSSQL 7 sp3.
My company has developed a VB.NET 2003 application which interfaces with the SQL Server database of another software package. We are experiencing serious performance differences at one of our client sites on a particular query that is one of the frequent queries that runs in our software which I will try to outline below.
Our database and the accounting system database are in separate instances on the same SQL Server 2005 box. Our query is a simple SELECT statement with filtering which properly utilizes the built-in indexes for that table. In their case it returns roughly 13000 records (vendor listing). There is no report of slowness regarding other queries as none of them return the volume that this particular query does.
On one offending workstation: if we run this query, it takes less than a second to get results back in SQL Management Studio's query window. This is consistent with the accounting software which takes about 2 seconds to run the query and display the results in a grid (the accounting system is Viewpoint, a leading construction accounting system written in VB6). In our system, attempting to do the same thing as the accounting system, it is taking nearly 16 seconds to retrieve and display the results. This workstation is running on WIndows XP SP2. They are experiencing this same behavior on several workstations.
At the same client site, they have identified two Windows 2000 workstations which can run the query and display the results from our system in approx. 2 seconds - which is what we would expect and is also what we experience internally on our development platform, and 12 other clients who run our software and interface with the same accounting system - regardless of OS on the workstation.
Based on the results in the query window and the results on the 2000 workstations and the success of other clients who have as many records if not more than this particular client - i don't think we have an issue with the query or the code. The results are very consistent on each workstation as well so I don't think it's a network traffic or activity issue. My gut points me at .NET Framework version differences - since the things that would affect this process are framework items - SQL Server OLEDB drivers and objects, DataGrids, Datatables and DataViews,etc.
On one of the XP workstations I had them remove .NET framework 3.0 which had no effect on the problem, but they also have v. 1.0.3705, 1.0 Hotfix (KBB886906), Framework 1.1, 1.1. Hotfix (KBB886903), Framework 2.0 with 2 security updates (KB917823 and BK922770).
Can anyone point me in a direction on things that I can check that might be different between their XP and 2000 workstations which might affect this process? I am working on some caching logic which will reduce the number of times this query is run, but I would like to understand why there is a behavioral difference.
Any help is appreciated. Please let me know if there is any other information I can provide.
I have an user table with a single integer column. No indexes, no identities, nothing. I have to insert 600,000 rows via a client app. In tests, using BCP/Bulk Insert/DTS all runs OK (sub 3 seconds). However the app takes 5000 rows a second [considerably slower]. I can mimic this slow perfomance, within DTS, by removing the 'Fast Load' & 'Batch' options.
Question= why would the SQL insert run slower on one server and as fast as BCP/Bulk Insert/DTS on another? What can I check on the 'slow' running server? May there be a file version anomally ??
Ive got an ETL process I have written which takes about 10 million rows from a staging database and loads it into production database with an INSERT statement. The INSERT statement makes a function call to retrieve the surrogate key for each row. The function looks in a replicated copy of our production database so no load is on our production environment during this time.
So: INSERT INTO foo(...) SELECT name, address, zip, dbo.fnGetSurrKey( name, address)
It took about 12hrs to insert 6 million rows last night and Im wondering if there is a better way of doing this. Maybe a multithreaded way like SSIS might have.
Assuming my function is optimized as much as possible, does anyone have any tips for speeding this up?
Also, the machine this ran on has 16gb of RAM but was setup to use only 2GB during this process. I have already changed it to 12gb and restarted the process a week ago, but the change doesnt take affect until you reboot. Would I see a significant performance increase from that?
Hello ,i am a master student and i am making a seminar about high volume DB performance problems ,example : if i have a table with length of 1000000 record and this length is growing exponentially by the time,what the problems may i face in insertion ,deletion , search,in such table?? and what the problems in processing such DB in general
I posted a link to a prior article in here, that one about highperformance hierarchies, and have the first two parts of a new series.Hopefully this is of value to someone.http://www.yafla.com/papers/SQL_Ser..._sql_server.htmThanks.
I have a SQL Server 2005 database where covering indexes had to be used to improve performance for the heavy amounts of retrievals; however, the inserts into the tables are now very slow of course. Is there any way to improve the performance of the inserts without taking away the indexes.
Would changing locking or partitioning the index help the inserts?
Other databases use a concept of "freespace" to set up in the beginning - making pre-existing space for inserts - is there anything like this in SQL Server 2005?
I am looking to improve the performance of my sql server databases.
I currently have a dual location system, the database server setup is basically a quad xeon with 4gb at my office and a double xeon with 4gb at a remote webhosting location. There are separate application/web/intranet servers at each site. The two databases servers are replicated with the local server publishing to the remote server.
The relational database holds circa 26 million records, growing by a volume of 10,000 per day, there are approximately 50,000 queries performed per day.
My theory is that the replication of the two databases is causing a slowdown; despite fast network connections (averaging 200ms between servers) the replication seems to place a large load on the local server. Would it be sensible to replicate to a second local server and then replicate to the remote server, placing any burden on the second server?
I am planning to upgrade the local server to a high capacity 4+ cpu 64bit server, my problem is that although I have noticed a slow down in performance over time, I am unsure how to go about measuring and quantifying this in order to diagnose the bottlenecks and ensure that investing in a new server would be worthwhile. Where would one be best advised to start this project?
I recently converted a column that was once an int to an bigint on one of my tables. The modified column provided a generic row id information and there are duplicates within this column. I am trying to perform a self join via the following:
SELECT a.row_id FROM test_db a INNER JOIN test_db b ON b.row_id < a.row_id.
This code use to work when the column was an int but now I am getting high CPU issues since I converted to bigint. I am unsure on why the change to bigint will cause such an issue. The OS/SQL is 64BIT.
I have SQL Server 2005 installed on my machine and I am firing following query to insert 1500 records into a simple table having just on column.
Declare @i int Set @i=0 While (@i<1500) Begin Insert into test2 values (@i) Set @i=@i+1 End
Here goes the table definition,
CREATE TABLE [dbo].[test2]( [int] NULL ) ON [PRIMARY]
Now the problem with this is that on one of my server this query is taking just 500ms to run while on my production and other test server this query is taking more than 25 seconds. Same is the problem with updates. I have checked the configurations of both the servers and found them to be the same. Also there are no indexes defined on either of the tables. I was wondering what can be the possible reason for this to happen. If any of u people has any pointers regarding this, will be really useful
Hi I have a few things to clear up and I hope i can find some answers in here. I have an application written in C# using COM+ components This application is intended to support a few hundred users at the same time and each action user leads to a few updates on several tables. The problem is that 2 or more users might do this thing the same time meaning that each users should update rows that "belong" the other users . I rely on the appearance of deadlocks in order to keep the data consistent, so only one of the users should be able to have this action completed, the transactions for the other concurrent users should abort. Basically the results would be the same no matter which user will complete the transaction so the only issue is to have only one action completed. So far so good, it seems that I have no problems and that the data is consistent.
Now the reason why I'm here would be that lately i see that the number of deadlocks has increased considerably and there should be no reason for this to happen; the situations when the users would modify each others data are somehow rare and I should see a few hundred deadlocks daily So what i think is that SQL might increases the lock granularity to table or page instead of using row locks(I am not sure it's just a wild guess) From what I've read, sql starts with default row lock and it might increase it when necessary.
And now finally the question: can I force sql to use only row lock? and if yes are there any risks involved? Is sql capable to maintain only this kind of lock and still be able to manage the transactions correctly? And another question, how do deadlock decrease overall performance?
I have a situation here. Please advice me on this.
I have a master table and a child table. They have a PK and FK relationship. The master table has an identity column with auto increment set to true. This map as a FK in the child table. My questions are:
Can I have a single form to insert a new record into master and child table at the same time?This has to be accomplished without stored procedures. Can it be done?Is it possible to do this with a single insert query? If yes, can it be done with sql data source or dataset or tableadapters’?Please point me towards appropriate link for doing so.
Here is the situation we are trying to resolve. The client has 2 locations, each has local appliations running on a database. db schema on both locations are the same, data is different and won't overlap.
The requirements are: 1. at each location the application can read and write 2. near zero down time for applications on each site 3. db on one site also has the data from the other site for DR purpose
The client is running on SQL 2005 STD SP2
We looked at the approach of setting up db mirroring on each location + 2-way transactional replication between both locations. The mirroring was fine, and I was able to set up transactional replication from mirrored publisher to a non-mirrored subscriber. But, from what I experienced and from reading, there is no way to have the subscriber db to be mirrored, since Distribution Agents simply doesn't have the option to specify Failover Partnr for mirroring, so I guess it is not supported. Any comments on that?
Assuming that's correct, then the only way of using SQL out-of-box technique seems to be using Clustering on each location instead of mirroring, then the 2-way transactional replication works on clustered subscribers I think (although I haven't tested it). Peer-2-Peer replication would have been a good candidate between sites in this case, but STD version of sql 2005 ruled that out.
Hi!We have SQL Server 2000 on virtual machine (slowly). We run a pair ofstored procedures from ADO.NET 2.0 (WebService):- first procedure: insert into table A a data row with correlationID- next procedure: search for inserted record in tab A bycorrelationID and insert value into tab B with constraint to tab A.The problem is when we try to do this in short time (few minutes)thousands times (5000/6000 pairs in 5/6 minutes) in 100 parallelthreads:query in second procedure cannot find inserted record in firstprocedure.Everything is dirty read. no transactions.Have you any ideas?edi
The server being used is a Intel Xeon E5310 Clovertown 1.6GHz 2 x 4MB L2 Cache Socket 771 80W Quad-Core 2U Passive Processor.
The problem is that this server is slowing down everytime about 1000 users log into a forum which the server is running. I think that the server should be able to handle this many users with no problems but I am not sure if that is the case.. The problem is probably something to do with the SQL of the server I am guessing. The server is not mine but I want to help the owner of the server as well as the users who are trying to access this forum but cant because of this server issue. If I was able to get the SQL would I be able to fix this problem? I doubt you need this but the server url is www.smashboards.com
I am fairly new to servers and have never really set one up myself yet. Forgive me for my lack of knowledge about them.
I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.
Why does it do this?
How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?
I set up a sql server 2005 database as publisher for about 50 sql mobile subscriptions. Yet in the replication monitor windows, it shows in subscription columns is 26549. Does it mean I have 26549 subsriptions or it means 26549 synchronizations?
I have a question for anyone who mas some tips/pointers for optimizing SQL merge replication publications.
The front end web server is running IIS 6.0 on Windows 2003 x86 Server Standard (Server A). The back end database server is running SQL 2000 Standard on Windows 2003 x86 Standard (Server B). The merge replication clients connect via HTTPS over the Internet from a custom C#.NET 2005 application using SQL 2005 Mobile running on Windows Mobile 5.0 (Client).
The publication itself has several filters on it. The entry point uses the user's Windows username to start the filter. Based on the user, it then filters the records in multiple tables. There are 68 articles and 44 filter statements. The filters extend multiple layers deep, in other words they are not all filtering off the HOST_NAME() variable, some tables filter from records in tables that filter from the HOST_NAME() variable. The publication is set to minimize data sent to the clients, and considers a subscription out of date if it has not synced in the last 4 days. All the rowguids are indexed as well.
There are approximately 35 clients actively using the application at any given time. On average, a client will initiate a merge replication 3-4 times per hour from 8am-5pm. Generally, a sync will take between 10 seconds and 2 minutes to complete, with most of them being around 30 seconds on average.
When a client starts a sync, there is a spike to about 50% on the server's CPU graph. If multiple clients attempt to sync at the same time the CPU utilization can be pushed to 100% for extended periods (more than 30 seconds).
I recently completed a project to increase the bandwidth available to the clients, and plan to reduce the number of filters significantly (although this will obviously increase the amount of data going to the clients and the storage needs on the individual devices). I also plan on changing the setting to not minimize the amount of data sent to the clients.
Having said all that, does anyone have any information about how to further optimize merge publications to mobile clients? The next publication will be on SQL 2005 x64 Standard if I can solve the issues in the text environment. I would like to enhance the publication as much as possible to make the end user experience better than it currently is.
I am currently investigating aa high avg write time ms issue (145ms) which seems to be only occuring on the tempdb data files.I have followed the recommended setup of TEMPDB in that
1. Data files = number of physical cores 2. Data files and logfiles are on separate partitions away from the other databases. 3. Tempdb is presized and no incremental file increases look like they are happening with frequency.
We have sharepoint 2012 setup on other sql servers and with TEMPDB setup following the same guidelines, with far more Sharepoint activity on a similary specified hardware which is why its confusing.FileIO auditing on the partitions themselves shows that the FileIO is very fast on the partitions that the tempdb data file which leads me to beleive that Sharepoint may be the culprit perhaps due to excess use of tempdb with operations taking a long time to resolve.
while my db is executing a store procedure i try to view the current activity in the managent but it returns to me 'Lock request time out period exceded'. It happens until the store procedure is finished. After that everything is ok. However i can see the activity executing sp_who and the db seems to work ok, maybe a little slow.
We recently bought a new (sql) database server with 8 gb ram. I started moving the databases from our older 2005-sql to the new one. Everything goes well, except...
Some steps in our jobs do a truncate table of a delete from... When the job is running and it reaches the step where the truncation/deleting starts, it stops its execution and gives the following error :
Code Snippet
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. [SQLSTATE HY000] (Error 1204). The step failed.
When I try to execute the procedure that does the truncation manually, it works perfect. Do you guys have an idea on what the cause of this error might be ?
Thanks a lot in advance !
(FYI: we use the enterprise edition of sql server 2005)
