JrJrDBA requesting feedback..
here's the system info:
--SQL2008R2 enterprise edition | 500GB RAM
--backend storage: SSD
--configured: full recovery mode for SQL transnational replication :150 publications, and log shipping of database to remote site.
--database size around 4.2TB
--temp db: 400GB allocation on SSD, typically in use under 15GB on a day to day basis.
--database usage: almost 24/7/364 in use
The database is for an ERP software. We recently purged historical data (50million+ records) from many of the key tables.
What I am seeking ?
Assistance and best practice suggestions to start index rebuilds.Top 20 tables have 90%+ fragmentation. Rebuilds on these large tables was never done. Most of the top 20 tables have 500million+ rows, each table averaging 25 - 30 indexes, as delivered by the ERP. Given the number of indexes that need to be rebuild, I am trying to figure out how many parallel rebuilds can I launch and not impact log shipping and/or replication, and of course the day to day ERP operation.I have already identified some unused index and will be deleting those as a starting point. There are at least 3 to 5 (unused indexes) for each of the 20 big tables.
My findings so far:
--allocate at least 120% space of original index when rebuilding
--index rebuilds, when done online are slower
--index rebuilds tax tlogs which will impact log shipping and tranactionla replication
--enabling 'sort in tempdb' may take some load off tlog but tempdb needs to have adequte space to support the build
--I am researching some sort of a WAIT parameter for index rebuild but i think it may not be available for SQL2008R2
any feedback/pointers/direction is greatly appreciated that will help towards my task.