SQL - Database Maintenance

Posted on 2011-10-28
Last Modified: 2012-05-12
I have a reindex and reogranize index maintenance running weekly.  Is it possible to run these tasks during business hours and would that create a huge performance hit to the users?  I looked around but didn't find much about it.  Never tried running it when users are in the database.  Just as a nightly routine.
Question by:holemania
    LVL 7

    Expert Comment

    A reorg is typically faster than a full index rebuild. With SQL 2005 Enterprise Edition keeping indexes online ibecame a possibility. Question I would have is why would you want to rebuild indexes during business hours? - it can be done. During business hours you might be paying a hig price since incoming data has to alter indexes while you are trying to reorganize or rebuild them for greater read performance. It greatly depepnds on the respective database use  - is it read heavy or insert heavy.
    An added benefit of rebuilding indexes during off hours is - you can switch the recovery model of your DB to e.g. simple to minimize log file growth while indexes are being rebuild. Followed by a full backup so yu can switch back to full recvoery mode afterwards.


    Author Comment

    Having an issue with it creating deadlocks.  Might've been a specific table or something that is causing the deadlock.  Everything else seems okay, but whenever someone tries to save something and it hit that particular it would create a deadlock for a long time.  Was hoping I can run it for that table.
    LVL 7

    Expert Comment

    Ok this is a completely different problem - you can possibly solve your problem by using an index but first do some basic deadlock troubleshooting to figure out what's causing the locks. I assume you already have trace 1222 running to possibly capture deadlock information in the SQL server logs. DBCC TRACEON (1222, -1)  Once you have captured information please post it back here.
    LVL 25

    Accepted Solution

    You can perform reorgranize during business hours. Reorganize doesn't affect the system much in compare to rebuild. Pages are suffled and is pretty much online process which if stopped, will start from the state it went upto without any rollbacks.
                 Rebuild on the other hand does recreate the whole index and then link that index pages to the original table. This can be done both online and offline but it does block as it applies locks on the table\pages. It is mostly suggested to do maintenance during weekends or if you have some time frame where end users and not using the sytem. If still you want to do it during business hours, I will suggest using reorganize. You can't save yourself from performance issues doing both which differs in the intensity.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now