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.

Where can I find resources on how to better move to a 24x7 operation? How do the big companies with big databases accomplish this? Our nightly jobs such as

  1. purge old data
  2. reindex
  3. update statistics

all seem to cause critical impact to our system (i.e. online users and real time data feeds). I've looked on Amazon for any book related to this subject, and so far haven't found anything.

share|improve this question
    
Are you looking to migrate databases from one server to another or better ways to manage the impact of your nightly jobs? –  Mike Fal Jan 13 at 17:40
    
How to manage impact of nightly jobs? I.e. how to reduce or eliminate the nightly "batch window". –  NealWalters Jan 13 at 17:43
2  
@NealWalters What edition of SQL Server are you on? (Online index rebuilds and table partitioning to switch out old data are available in Enterprise Edition) –  Martin Smith Jan 13 at 17:54
1  
What version of sql server are you using ? Enterprise / standard ? Enterprise has certain features that can allow you to do some operations as ONLINE with minimal user impact. –  Kin Jan 13 at 17:54
1  
@NealWalters Check out Dividing DBCC CHECKDB over multiple days gives more details. Though it is more for CHECKDB, but will help to get you more idea. Let us know what edition you are using, so people over here can help you better. –  Kin Jan 13 at 18:09
show 1 more comment

1 Answer

up vote 24 down vote accepted

Maintaining 24x7 databases is a fairly large topic with lots of options to consider. This broad topic has many items to consider, but we can try and touch base on some of the high points.

What you first will want to identify is, while many operations are 24x7, there are usually times of low activity. You can leverage these times for running your maintenance so you reduce the interference you will have on the database. The second is you will need to reserve some time for complete outages (for things like service packs or database migrations), so you will need to negotiate full maintenance windows with your management. For specific items you will need to consider and plan for each one, as well as leverage your tools appropriately. The important piece is you must PLAN each of these, any examples I provide are very much "your miles may vary".

Backups

Backups commonly won't have a huge impact on workloads, but must be accounted for as they can consume a lot of I/O. You will want to schedule these appropriately and monitor the amount of time it takes to complete. The biggest hurdle here is that in a 24x7 operation, you will likely not be able to conduct full nightly backups every night of the week. You will want to plan out when you can take fulls, when you are taking differentials, and retention periods for both of these in combination with your log backups.

As an example, I run full backups of all my databases on Sunday night (lowest activity), differentials on all other nights (Monday-Saturday). I keep the last two weeks of fulls and diffs on disk, logs for the last two days. This gives me enough flexibility for recovery, but I might have to recover backups from tape if necessary.

Index/Statistics Maintenance

This is the most common type of active maintenance you will have to deal with. You can't avoid it, but you can mitigate the impact. The initial rule of thumb is that you should only do maintenance on objects that need it. General guidelines are to only rebuild indexes that are greater than 30% fragmented and larger than 1000 pages. If you have auto-update statistics, this will handle most of your statistics maintenance, but a nightly job to keep things in sync isn't a bad idea.

If you have Enterprise Edition, you also have access to some other options for managing maintenance. Foremost is Online Index Rebuilds, which will allow you to rebuild indexes while they are still in use (essentially, it builds the index side by side, then swaps it in). You also can leverage partitioning for "large" tables to reduce the amount of rebuild time necessary.

Your best bet for this type of maintenance, if you don't have custom scripts that handle these best practices, is to use Ola Hallengren's Maintenance scripts. These are fairly easy to setup and configure and have many of these guidelines built in.

DBCC Consistency Checks

Depending on your overall workload, you might find DBCC checks to be to disruptive for your operation. There are two common ways to minimize your DBCC impact for your databases:

  • PHYSICAL_ONLY - Running this option will check your databases at a physical page level and avoid the more invasive full check. This will cover identifying the most likely types of corruption.
  • Checking a restored copy - If you have the space, you can restore the database to another instance and run a DBCC check against the restored copy. This will tell the same tale about your live database, but you obviously won't interfere with the activity. Some other alternatives here are running DBCC against a log shipped copy or a mirrored db.

This blog post provides more detail on your options.

Batch jobs/ETL

This really comes down to how you design your processes. Your ETL can always interfere with live OLTP tables (just as any other application), so some keys to keep in mind:

  • Schedule such work around your other maintenance and in low activity periods.
  • Right size the work so that it's batched for both performance and so that the batch is not so big that it locks your table for hours. Examples of the ends of the spectrum: Row-by-agonizing-row (RBAR) versus a single million row delete.
  • Use stage tables and offline your data processing where appropriate. Only touch the live stuff when absolutely necessary.

Conclusion

Again, there's a lot of ground to cover here. This is not a comprehensive guide, but a high level overview of some approaches. I haven't even discussed high-availability options (such as Availability Groups and Failover Clustering). You will need to review each item and build out a plan for how to handle it. In many ways, you will also need to iterate and refine your work as you move forward.

Additional resources:

SQL Skills VLDB maintenance best practices

share|improve this answer
3  
+1 very well written. –  Kermit Jan 13 at 19:38
    
Agreed, excellent, helpful, detailed response. Thanks! We are working through it. –  NealWalters Jan 13 at 22:40
    
One other factor we are working on is moving a lot of the stale data to an ODS (Operational Data Store) to keep the main database more trim. We also find the "Update Statistics" is running about 2 to 2.5 hours each morning, and that seems to slow down general performance. Is is "best practice" to run Update-Stats each day? –  NealWalters Jan 15 at 16:55
    
I will usually do it, but YMMV. The risk of not updating stats is stats become stale and you start having bad query plans. You'll have to analyze whether or not this is a problem if you don't run update stats every night. You could see about spacing out your stats jobs more and see how your queries perform in general. –  Mike Fal Jan 15 at 17:07
add comment

Your Answer

 
discard

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

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