October 27, 2011 at 9:09 am
HI All,
Wasnt sure where I should post this so settled on here!! Our DBA recently left and I have been trying to manage the servers, we have a lot of big tables and the indexes are really fragged!! Example : I have a 64GB Table with an 11GB index but its never been optimized.
Whats my best course of action to optimize this without impacting uptime or performance??
Thanks
Gavin
October 27, 2011 at 9:25 am
Hello,
I assume by "optimize" you mean maintain. By "maintain" I mean reorganize/rebuild to remove fragmentation from the indexes.
You can't really manage the index without effecting performance of the instance. Your best bet would be to schedule the index maintenance at a time when usage is at its lowest. This means when there is the least user traffic and the least amount of processing (nightly/weekly jobs). This way the end user is impacted by performance degradation the least.
You can rebuild the index to begin with but make sure there is a maintenance plan set up to ... maintain the indexes so they don't get out of hand. There's lots of documentation out there on index maintenance, and certainly ask if you have questions regarding it.
October 27, 2011 at 11:37 am
I'm in agreement with Calvo.
Furthermore, I would suggest to implement a weekly or monthly Maintenance Window during which you would have total control of the database and perform any maintenance that may be needed.
Always, let me stress this, always plan and test your maintenace jobs in advance on a test environemnt.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 3, 2011 at 9:38 pm
Try to compress the table and index which will gain more space and improve performance.
November 3, 2011 at 10:15 pm
KrishDBA (11/3/2011)
Try to compress the table and index which will gain more space and improve performance.
Not possible in SQL Server 2005 and depends on what edition of SQL Server 2008 you are running if you can do this.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply