SupportSQL

Re-indexing tasks take a heavy toll on performance. Here we can see that, even on a new cluster with ample power, SQL Server is still slowed to a crawl with latch wait times > 4sec and high disk queues even on a new dedicated SAN. Maintenance windows are almost mandatory in most production systems as this type of performance drain is rarely acceptable during regular business hours. In the case of “never-slow” requirements the re-indexing can be managed in a more granular approach as to limit SQL Server from an all out re-indexing of a database. There will be a follow up to this post called “Omnipotent Re-indexing Visualized – Is there a better way?” showing one method of granular re-indexing control.

—by Alek Kirstein – SupportSQL.com