MSSQL defrag/re-index.

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
LVL 2
stlhostAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stlhostAuthor Commented:
Thanks!
0
PortletPaulfreelancerCommented:
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/
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.