• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 850
  • Last Modified:

Index Rebuild on DB configured with Mirroring

Hi,

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 DBCC SHOWCONTIG with out any impact on this ?

Could some one give me a clear picture here??

Thanks
0
parpaa
Asked:
parpaa
  • 2
  • 2
1 Solution
 
dbaSQLCommented:
yes.  
>>Rebuilding an index takes longer with safety FULL than it does with safety OFF. Synchronous mirroring introduces more overhead when rebuilding a clustered index than when rebuilding a nonclustered index. Figure 2 illustrates the index rebuilding time for a clustered and a nonclustered index for various safety levels. Figure 2 also compares the performance of ONLINE and OFFLINE index rebuilding for various safety levels. During an ONLINE index rebuild, the table and the index are available for queries and data modification, whereas during an OFFLINE index rebuild, a table lock is acquired and no other operation on the table is allowed.

see here:
http://www.sqlskills.com/blogs/paul/post/More-on-Database-Mirroring-performance-and-index-maintenance.aspx

and here:
http://technet.microsoft.com/en-us/library/cc917681.aspx

in all cases, the maintenance action on the mirrored db is going to impact performance, and the runtimes are likely to be longer than doing the same, on the non-mirrored database.  regardless, it can be done.
0
 
parpaaAuthor Commented:
Thanks dbsQL :)

This is very helpful
0
 
parpaaAuthor Commented:
Helpful
0
 
dbaSQLCommented:
No problem at all, parpaa.  I'm glad to have been able to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now