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 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.

share|improve this question
    
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 '12 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 '12 at 15:27
add comment

1 Answer

up vote 5 down vote accepted

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/

share|improve this answer
    
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 '12 at 16:13
    
Yes I think it is just going to be a simple insert statement. –  Aaron Bertrand Jun 28 '12 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 '12 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 '12 at 18:31
    
very cool, thanks for sharing. –  bluehiro Jun 28 '12 at 19:18
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.