3

I have a table in my database that is constantly monitored by an application server for changes. Unfortunately rebuilding the indexes connected with this table breaks the app server's connection. This causes print jobs and various other processes to fail silently on the application server.

I have created a small monitoring SQL job that lets me know when this happens, but I would prefer to prevent it from happening in the first place.

I need to rebuild indexes weekly for performance reasons, but I would be fine with skipping the indexes associated with that table.

How can I rebuild indexes for the rest of the database without touching that table?

Edit: I am currently using an SSIS job (the canned maintenance plan) to rebuild all indexes for all tables in the DB. After that it updates stats and checks integrity.

Improve this question
2
  • Well, how are you rebuilding the indexes now? Are you really just running a loop and saying "rebuild all indexes on all tables"?
    – Aaron Bertrand
    Jun 28, 2012 at 15:26
  • I have been relying on an SSIS job that rebuilds all indexes for all tables in a specific database.
    – bluehiro
    Jun 28, 2012 at 15:27

1 Answer 1

Reset to default
5

You should implement a better solution, such as Michelle Ufford's - it will perform much more intelligent decisions on which indexes to defrag, rebuild or leave alone, and it is very easy to have exclusions:

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

Improve this answer
4
  • That is a seriously powerful script. I've dropped it into my test environment and will begin playing with it right away. My only question (probably a dumb one), to exclude a table I would just add it to the dba_indexDefragExclusion table? I don't see anything in the code stating a best practice for doing that (I'm assuming just an insert statement).
    – bluehiro
    Jun 28, 2012 at 16:13
  • Yes I think it is just going to be a simple insert statement.
    – Aaron Bertrand
    Jun 28, 2012 at 16:16
  • 1
    In conclusion, Michelle Ufford's script has an exclusion table. My pastebin link shows you how to retrieve the data you need to insert exclusion rows into Michelle's exclusion table. Then you simply run her sproc to reindex your database(s) intelligently and with any specified exclusions. It's beautiful.
    – bluehiro
    Jun 28, 2012 at 18:29
  • And I waited until now to mention that my company's product, SQL Sentry Performance Advisor, includes a facility to do this and more: sqlsentry.net/fragmentation-manager/… but it's not free like Michelle's. :-)
    – Aaron Bertrand
    Jun 28, 2012 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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