April 28, 2011 at 1:34 pm
Can we rebuild indexes ONLINE on Primary DB configured with Mirroring ? Does it impact mirroring process by any chance ? Do we have any consequences in doing this ? Also can we check the fragmentation info using DMV's with out any impact on this ?
April 28, 2011 at 2:07 pm
Yes (providing the index satisfies the requirements)
Yes (Lots of log records to be transmitted and redone)
Yes (potential switch to synchronising)
Yes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2011 at 2:22 pm
Thaks allot for your reply Gail shaw, I am executing the below query to check the fragmentation level in SQL server 2005.We need to follow with Average_Fragmentation_In_Percent
*Index should be rebuild when Average_Fragmentation_In_Percent is greater than 40%.
*Index should be reorganized when Average_Fragmentation_In_Percent is between 10% to 40%.
use databasename
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
is this right process? please let me know
April 28, 2011 at 11:32 pm
I suggest you look for one of the available index rebuild scripts and don't try to write your own.
Maybe the one at http://www.sqlfool.com/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2011 at 2:48 am
also bear in mind you will need to be using Enterprise edition to use the Online indexing feature
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 29, 2011 at 8:03 am
be careful if you are running that mirror with a witness server. I have seen mirrors fail over during such routines. re-index is extremely log intensive. if your network / disk can't handle it, the witness will initiate a fail over.
we used to 'pause' mirroring just before a re-index. let the job run to completion, then start the mirroring again.
April 29, 2011 at 1:16 pm
fragmentation percentage < 30 then ReOrganize
fragmentation percentage > 30 then Rebuild
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 29, 2011 at 2:57 pm
Geoff A (4/29/2011)
be careful if you are running that mirror with a witness server. I have seen mirrors fail over during such routines. re-index is extremely log intensive. if your network / disk can't handle it, the witness will initiate a fail over.we used to 'pause' mirroring just before a re-index. let the job run to completion, then start the mirroring again.
I would recommend pausing mirroring before reindexing only when your are configured for high safety (regardless of whether or not you have a witness). If you are using high performance, there is no need to pause mirroring because the transactions will queue up anyways.
In high safety, if you don't pause - it will cause the reindexing processes to take much longer to process because of the two-phase commit requirement.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2011 at 9:53 am
I just finised my first version of c# application to solve this issue. It monitors log utilization while rebuilding the indexes and will wait for the log utilization to be low before moving on to the next index. If anyone is interested send me an email and I will post it out on codeplex. jruddyse_at_hotmail.
Reegards,
Jim
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply