April 25, 2019 at 3:00 pm
I'm making some analysis of our databases and found out that we're using replication from our transactional databases to a reporting database to duplicate about 100 tables. The databases are all in the same instance.
I'm wondering if replication is an overkill for this or does it makes sense resource wise instead of some alternative such as triggers (for synchronous process) or ETL jobs (for asynchronous process).
April 25, 2019 at 3:36 pm
I'd lean heavily towards this being overkill. Sure, queries on the one database don't interfere directly with the queries on the other. However, the resource contention is just as large (say the report query on db2 scans an enormous table, flushing memory, that's going to hurt db1). Further, my assumption, so it could be wrong, having all the replication management there on the server is also bleeding a lot of resources away. While I recognize the utility of replication and I used to use it a lot, I've never been a real fan because of the overhead and maintenance it creates. The best way to deal with that was to always have three servers, the source, the replication management server, and the destination. Combining any two of these, let alone all three, always lead to headaches.
"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
April 25, 2019 at 5:40 pm
That seems like it would be over kill if you don't need real time replication just for reporting and an etl job would make more sense. But if you do need real time access in the reporting system then that might not be a bad option, definitely seems more manageable than hundreds of triggers....
April 29, 2019 at 10:39 pm
I've used this and it certainly wasn't overkill. The client had a lot of resources on the SQL Server, but the main database was highly transactional, and performed very poorly for generating reports and documentation. They needed a solution where a trader could do a deal, then report on the current state of their clients portfolio, within a few seconds of finishing the deal, to print and sign.
We replicated a subset of tables relevant to the reporting/documenting process, and designed indexes specific to the reports. This allowed us to have two databases that could be optimized for their specific tasks, and the net result was an improvement on both parts of the application.
In testing, a years worth of reports that previously took 16 hours to generate end to end, were completed in 16 seconds (360 times faster) against the Subscription DB. Dealers were very happy as the whole system worked much better. There was no extra cost for further SQL & Windows licences or hardware. A good solution if used right.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
April 29, 2019 at 10:46 pm
I've used this and it certainly wasn't overkill. The client had a lot of resources on the SQL Server, but the main database was highly transactional, and performed very poorly for generating reports and documentation. They needed a solution where a trader could do a deal, then report on the current state of their clients portfolio, within a few seconds of finishing the deal, to print and sign. We replicated a subset of tables relevant to the reporting/documenting process, and designed indexes specific to the reports. This allowed us to have two databases that could be optimized for their specific tasks, and the net result was an improvement on both parts of the application. In testing, a years worth of reports that previously took 16 hours to generate end to end, were completed in 16 seconds (360 times faster) against the Subscription DB. Dealers were very happy as the whole system worked much better. There was no extra cost for further SQL & Windows licences or hardware. A good solution if used right.
That's quite an improvement, Leo. With that in mind, I have to ask what the "optimizations" you made on the reporting database were.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2019 at 10:26 pm
To optimize the reporting database we removed all indexes except the required Primary Key (for Transactional Replication). We then reviewed the report queries and designed a few select indexes specific to these queries, using INCLUDE clauses where it made sense.
The big issue on the original system as with many historical systems was that as long as things worked no improvements were being made. The slow degradation in report performance wasn't a problem, at least until things got really bad, and by then adding new indexes or redesigning the OLTP database was having a negative impact on the OLTP side. The reports where doing index and tables scans on the old system, but these were cut down to Index Seeks, often without ever needing a Key lookup thanks to the INCLUDE clause. The result was most reports were satisfied by the Index Seek.
Some work was also done on the queries to remove parameter sniffing and poorly placed function calls, etc that were contributing to the performance issues.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply