I administrate a reporting database. We run all the de-normalization process on a database DB1 daily, then we copy all the data to an another database DB2 on the same server which is queried by web applications and reporting tools.
In order to improve performance I decided to set DB2 as Read-only after copying the data. Performance increased a lot in my case probably due to some improvement in caching with read-only DB2.
But from the next day the de-normalization process run really slow from 6 hours, usually it went to 15 hours.
The CPU usage top 98-100% during the whole process.
After some testing, it was clear that the CPU usage insanely was raised because of the read-only DB2.
I can't figure why.
Can someone help me to understand this please? Is there a way to control this?
DB1 run every night :
- Download Data from external DBs
- proc data to get denormalize data using heavy stored proc on 200GB of data
- copy denormalized data to DB2 (100GB)
DB2 is accessed by Web application and reporting Tools.
When I set DB2 to Read-Only Mode :
- All stored proc run faster on DB2 so Web application and reporting Tools has better performances from 5% to 70% faster.
- When denormalized process run CPU usage raise to 98-100% (instead of 40-70%)
- Denormalize process took 15 hours (instead of 6H)