I administrate a reporting database.
We run all the denormalization process on a 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 the next day the denormalization 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 is clear that the CPU usage insanely raise 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)