Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Thank you.

More detail:

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)
share|improve this question

migrated from stackoverflow.com Nov 2 '12 at 13:03

This question came from our site for professional and enthusiast programmers.

    
Can you supply a simple repro demonstrating the issue? –  Martin Smith Nov 2 '12 at 13:03
    
What are you doing to copy the database? Are you doing a full refresh of each table? Are you doing any index maintenance or updating stats before setting the database to read-only? Does your denormalized design contain the appropriate covering indexes? –  brian Nov 2 '12 at 13:44
    
The copy is not the issue the copyied data are fine, indexes are rebuild and statistics updated before setting to read-only mode. The issue is the denormalization process that get slow and it does not refere anytime to the read-only DB. They just share the same server. –  Kilren Nov 2 '12 at 14:06
    
@MartinSmith : I'm not sure how to supply a repro. I'll try to detail more. –  Kilren Nov 2 '12 at 14:13
    
Many things could be causing this. Is the file system ok? Have you recently run a DBCC CHECKDB on the database(s)? –  user1240 Oct 3 '13 at 14:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.