SQL Server Performance Forum – Threads Archive
Indexing recommendationI have a sql server 2005 database with tables of varying size..from million rows to hundred rows. I want to reduce the maintanence window needed for the index rebuild.
So I’m thinking of using the ONLINE option for some tables , so that I can reindex those tables during the day.
But I can’t do that for all the tables since "ONLINE option" is not a good option for rebuilding the large tables.So I need some recommendations for choosing the "ideal candidates" for online indexing. Any help in this regard is appreciated!
Online index create or rebuild (ONLINE=ON) provides maximum concurrency, but uses more resources and takes longer to complete.
You can also SORT_IN_TEMPDB option in your statement…if you use this option sorting will be done in tempdb… There are some restrictions for online indexing so… Read SQL Server 2005 Books Online "Guidelines for Performing Online Index Operations " for more info…
Seehttp://www.microsoft.com/technet/prodtechnol/sql/2005/scddrtng.mspx link. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
You could change from reindexing to defrag + update statistics, so that there is no offline time. I just made this change to my system and it’s working well. I have two jobs: 1. Selective index defrag — this one makes a list of indexes with fragmentation level above a certain value, and it has a time limit. During the time allowed, it defrags the indexes in order from the biggest/worst to the smallest/best until either the time runs out or it has defragged the whole list. 2. Because defrag, rather than reindex, does not update stats, I also have a job with the same structure that will update statistics with fullscan, working from the stats objects with the most changed rows back to the ones with fewer changed rows, also with a time limit. Both jobs run during the night when there’s less traffic on the system, but they are both online operations, so it remains available the whole time. If they don’t finish completely, no problem; they just start up again the next night. And nothing happens during peak hours when the system is working hard.
That sounds like a good plan. Could you share how you perform those two jobs? If they are custom scripts, is there any chance you could share them?
Check: http://www.sql-server-performance.com/tp_automatic_reindexing.asp Luis Martin
SQL-Server-Performance.com All in Love is Fair
All postings are provided â€œAS ISâ€ with no warranties for accuracy.
Here is one more down load zip file…. http://blogs.digineer.com/blogs/lar…consolidated-sql-server-2005-environment.aspx