MSSQL defrag/re-index.

stlhost used Ask the Experts™
After a year or so our programs that rely on MSSQL get slow and sluggish and the CPU stays fairly busy, most of the time around 100%. About once a year our SQL databases get re-indexed by an outside company. Whatever it is they do it does reduce CPU load significantly. They call it re-indexing but we assume what they are doing is defragging the tables. We would like to do this internally so we are not relying on a third party anymore. Would anyone be willing to educate me on what is being done basically and how you can tell if it needs it?

Windows 2008/SQL 2008
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Developer
This script (free) will rebuild the indexes for you:

Essentially non clustered indexes get fragmented after long use, by rebuilding them you keep everything organized and easily accessed.

And everything you ever wanted to know about indexes:


PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

many DBAs I trust highly recommend Ola's scripts (nb: I am not a DBA)

here is some additional information you may find helpful

This conference video is a useful introduction as Ola explains his approach and answers questions: recommended video

An alternative that many others recommend is:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial