Rebuild or Reorganize the Index in SQL Server using Queries

When the queries are running slow and decrease the performance of the server based on the indexes, we need to flow  rebuild or reorganize the indexes based on the fragmentation levels.

First how to find the fragmentation  percentage on a page, we can use the following DMV.

sys.dm_db_index_physical_stats

The fragmentation percentage is    0-10%  —>Nothing we don’t do any thing.

>10%-30% —> Reorganize the Index

>30%- 100%  —-> Rebuild the Index.

 

Rebuild the Index :

Syntax:

  alter index <index-name> on <database-name>.<schema>.<object-name>  rebuild

Example:

alter  index  empindex   on   mydb.dbo.emp  rebuild


Reorganize the Index :

Syntax:

  alter index <index-name> on <database-name>.<schema>.<object-name>  reorganize

Example:

alter  index  empindex   on   mydb.dbo.emp  reorganize

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s