current community

your communities

more stack exchange communities

Stack Exchange
tour help
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.
up vote 3 down vote favorite
2

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?

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

migration rejected from stackoverflow.com Dec 18 '14 at 19:28

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by Paul White, RolandoMySQLDBA, Mark Storey-Smith, Max Vernon, Shawn Melton Dec 18 '14 at 19:28

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Paul White, RolandoMySQLDBA, Max Vernon, Shawn Melton
If this question can be reworded to fit the rules in the help center, please edit the question.
    
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
2  
Provide a before/after delta snapshot of sys.dm_os_wait_stats, this should allow us to diagnose the issue. –  Thomas Kejser Dec 28 '13 at 14:03

2 Answers 2

up vote 0 down vote

It sounds like you may have overflowed the RAM required for the working set for some of the denormalization operations. High CPU usage often means lots of page reads. Check the cache hit ratio as well as the logical page reads during the denormalization process. Somewhere around when a table gets larger than available memory (or the amount allowed by single operations), the database has to switch to a multi-pass algorithm to complete the JOINs for denormalization.

share
up vote 0 down vote

Just changing the recovery model can take quite a while, depending on the activity in the database.

If you are running a denormalization process, I'm guessing you set the database to read/write as part of the process? Then you would set it back to read only afterwards.

One reason you could be seeing the overall process running much slower is that your script (or whatever) is patiently maintaining consistency by allowing things to commit.

You could always use "WITH ROLLBACK IMMEDIATE;" but that would probably defeat the purpose of the denormalization process...

share

Not the answer you're looking for? Browse other questions tagged or ask your own question.