August 27, 2011 at 3:10 am
We have data in our live DB of six regions. We want to shift the load from one DB by making 6 copies of same DB at same Server. We will route the traffic of 6 regions by making changes in Connection String in web.config.
My Question is that is there any performance benefit of making duplicate copies or not? because All Dbs requests will be handeled by Same Server.
Thanks in Advance.
😀
Azhar
August 27, 2011 at 4:09 am
You have a good chance of making everything slower, specially if the data is identical in all the 6 copies. You will also be requiring the hardware to do more. Another aspect is how are you going to keep the data in sync.
If on the other hand the data is naturally segmented to the 6 regions and the databases _only_ contain the relevant data then perhaps-ish. But tuning the queries might be sisimplernd get you bigger gains.
What performance issues are you seeing? Where is the first set of bottlenecks?
August 27, 2011 at 4:50 am
Since it's all on the same server you're sharing, by default, the same memory, CPU and tempdb. The one area of separation you can get is with disks. Not knowing your system can I tell you that separating out the I/O is going to be extremely benefiicial? Nope. You'll have to do testing to understand where your system places the load. Is it primarily on the disk? If so, then this could be a big benefit for you. If not, you might just be shooting yourself in the foot. Testing your system and understanding where your bottlenecks are is the key.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 29, 2011 at 1:41 am
Thanks for your time.
Actually, we will synchronize the data by replicating the all dbs. We dont have extra servers right now to shift all subscriber at the different physicals servers. In this case we will have one synchronized DB at publisher. We have huge reports extracting huge data after calculations. So we want to distribute reporting load. We will just use replicated Dbs for reporting purpose. Currently we have dropped our Regions from 6 to 4 and we will run our publisher at one server and all subscriber at another server.
We face blocking when heavy reports encounter there and Activity monitor shows blocking chain with head blocker.
Furtherore We want to replicate data to other DBs on the basis of RegionID filter.I mean to apply filter at the subscriber that will push data to proper DB on the basis of RegionID Filter. Is it possible or not?
Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply