April 20, 2016 at 10:11 pm
Hi all. We have a SQL 2008 server with an OLTP db that gets hit throughout the day with a combination of reads/writes and heavy reads for reporting (crystal reports, SSRS and our internal OLTP desktop application lets the users run inquiries (i.e. constrained ad-hoc queries). We get some blocking and deadlocks here and there, between the heavy read SELECTs and the IUDs, where one custom select could hang up the IUD query(s). We've tried optimizing the SELECT's with indexes and refactoring, but we don't want to slow down the IUDs with a ton of indexes to handle the custom SELECTs.
We're planning to replicate (transactional) the data to a secondary server and point all/most of those heavy reads to the secondary server. Won't we run into the same types of blocking problems on the secondary server, where the distributed replication transaction IUDs could block or be blocked by the heavy read SELECTs, or vice-versa?
Thanks!
April 20, 2016 at 10:50 pm
Gary Semel (4/20/2016)
Hi all. We have a SQL 2008 server with an OLTP db that gets hit throughout the day with a combination of reads/writes and heavy reads for reporting (crystal reports, SSRS and our internal OLTP desktop application lets the users run inquiries (i.e. constrained ad-hoc queries). We get some blocking and deadlocks here and there, between the heavy read SELECTs and the IUDs, where one custom select could hang up the IUD query(s). We've tried optimizing the SELECT's with indexes and refactoring, but we don't want to slow down the IUDs with a ton of indexes to handle the custom SELECTs.We're planning to replicate (transactional) the data to a secondary server and point all/most of those heavy reads to the secondary server. Won't we run into the same types of blocking problems on the secondary server, where the distributed replication transaction IUDs could block or be blocked by the heavy read SELECTs, or vice-versa?
Thanks!
The OLTP system will also be doing a bunch of SELECTs to show information on screens and to validate the intended transaction. Hence, the overall load on the replica is likely to be lower because that SELECT activity is not being done.
Depending on the nature of the IUD's, there may be joins etc involved - these are not part of what replication needs to worry about. It only gets things like "UPDATE TABLE SET columns = values where PrimaryColumn = "the one PK value". So, this may also be a potential for less load on the replica.
If your application updates data with sprocs, there is also the potential to replicate the EXEC sproc statement instead of the resultant IUDs - this may or may not be a benefit, depending on how many records a sproc is expected to update - replicating the EXEC call may significantly reduce the number of IUD statements that may be sent to the replica (I,e, one EXEC sproc instead of 100s or 1,000s of individual IUD statements).
You can also set up indexes that are a better fit for your reporting load and can be different to the OLTP database.
So, overall, I would expect that the base workload on the replica would, most likely, be lower on the replica than the source.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply