MSSQL defrag/re-index.

stlhost
stlhost used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Senior .Net Developer
Commented:
This script (free) will rebuild the indexes for you:
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

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:
https://msdn.microsoft.com/en-us/library/ms189858.aspx
stlhost

Author

Commented:
Thanks!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
FYI
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:
http://www.brentozar.com/blitzindex/

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