Indexes in databases work kind of like an index in a book. Instead of having to look at each page in a book for something, you can just go to the index – find your topic in an alphabetized list, and go to the page number indicated. Databases use indexes so they do not have to look at every single row in a table that could contain hundreds of thousands up to billions of rows.

The problem is, with all the constant reading and writing to a live database, the indexes quickly become fragmented as they try to keep up will all the new data coming and going. After a while this fragmentation can start to have an effect on your database’s performance.

Rebuild an Index

***Don’t attempt this on a production database while it is in use. Make sure the database is not being used before trying anything in this lesson.

The act of defragging an index in SQL Server is known as rebuilding. You can do it which just a few mouse clicks.

First, let’s find our indexes. You can find Indexes nested under tables in the Object Explorer. I won’t go in depth on Clustered vs Non-Clustered, only know the each table can only have 1 Clustered Index. You can think of it as the master index for that table if it helps.

Right click on an index and go to Properties

Select Fragmentation from the Select a page window. Note my index is 66.67% fragmented.

Click out of that window and right click on your index again. This time click Rebuild

Click Okay and the window and your Index will be rebuilt. Simple enough.

Rebuild All Indexes in a Table

 

If you want to rebuild all the indexes in a table, you can click on the Index folder and click Rebuild All

Then click okay

Or you can use the follow SQL Code

Reindex Code

DBCC stands for Database Console Commands. It is a list of useful tools you can use to administer a SQL Server. The syntax below is as follows: DBCC DBREINDEX(TABLE NAME, Index you want to rebuild (‘ ‘ = all indexes) , fillfactor)

DBCC DBREINDEX([HumanResources].[Employee],' ',90)

A quick note on fill factor. A fill factor of 0 or 100 tells SQL to fill every index page completely – leave no extra room. If the data was stagnant that could work, but when data is constantly being written and deleted, the indexes need room for correction. That is why you will often see 80 or 90 used as a fill factor. It gives a little wiggle room for the real life functionality of the database.

Reindex All the Tables

If you want to Reindex all the tables in a database, you can do it using a Cursor and While loop. If you do not know cursors in SQL, check out my previous lesson on cursors: SQL: Learn to use Cursors – List table names

The only new elements you will notice here is that I am combining TABLE_SCHEMA+’.’+TABLE_NAME. I give an example below to show you how it works.

Note, this query takes a few seconds (or minutes depending on speed of machine and database size) to run. You will not see anything until the query is completed.

use AdventureWorks2012
go

declare @tableName nvarchar(255)

declare myCursor CURSOR FOR
select TABLE_SCHEMA+'.'+TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'base table'
open myCursor
Fetch next from myCursor into @tableName
While @@FETCH_STATUS = 0
Begin
print 'Working on: '+@tableName
DBCC DBREINDEX(@TableName,' ',90)
Fetch next from myCursor into @tableName
end
close myCursor
Deallocate myCursor

TABLE_SCHEMA+’.’+TABLE_NAME

First result set, schema and table name are different columns. Second result set has them concatenated with a . in between.