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
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
First result set, schema and table name are different columns. Second result set has them concatenated with a . in between.