Index Rebuild vs Index Reorganize

         To improve the performance of SQL access queries, Indexing the table(s) is the foremost option. But after some time, due to Insert/Update/Delete operations those indexes get Fragmented, which in cause decline in performance. To avoid such performance decline; those indexes need to be Reorganized or Rebuilt.

Index Rebuild : This process drops the existing Index and Recreates a new index with same name.

Syntax:
ALTER INDEX ALL ON Production.TrnBSETrades REBUILD

Index Reorganize : This process does not drop the existing Index but physically reorganizes the leaf nodes of the index.

Syntax:
ALTER INDEX ALL ON Production.TrnBSETrades REORGANIZE

To get the details of the index fragmentations status, execute following DBCC Command :

DBCC SHOWCONTIG  ('Production.dbo.TrnBSETrades','NCLIX_TrnBSETrades_UserID_ClientCode_SaudaDate')
As a result, you will get the below output :

DBCC SHOWCONTIG scanning 'TrnBSETrades' table...
Table: 'TrnBSETrades' (548275418); index ID: 10, database ID: 25
LEAF level scan performed.
- Pages Scanned................................: 3120
- Extents Scanned..............................: 421
- Extent Switches..............................: 3212
- Avg. Pages per Extent........................: 9.5
- Scan Density [Best Count:Actual Count].......: 13.21% [458:3253]
- Logical Scan Fragmentation ..................: 97.12%
- Extent Scan Fragmentation ...................: 25.78%
- Avg. Bytes Free per Page.....................: 3845.9
- Avg. Page Density (full).....................: 58.79%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

You can also execute the the above command without mentioning the Index name as:
DBCC SHOWCONTIG ('Production.dbo.TrnBSETrades')

Also you can get the fragmentation using below query:

Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.