DBCC DBREINDEX

When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered in the database. Fragmented data can cause SQL Server to perform unnecessary data reads, so a queries performance against a heavy fragmented table can be very poor.

Rebuilding an index is a more efficient way to reduce fragmentation in comparison with dropping and re-creating an index, this is because rebuilding an index is done by one statement.

The DBCC DBREINDEX statement cannot automatically rebuild all of the indexes on all the tables in a database it can only work on one table at a time. You can write your own script to rebuild all the indexes on all the tables in a database

TableName - Is the name of the table in your DataBase.
Index_name - Is the name of the index to rebuild. (refer to image below)
Fillfactor - Is the percentage of space on each index page to be used for storing data when the index is created or rebuilt. Default is 100.

Syntax
DBCC DBREINDEX ('TableName','Index_Name',fillfactor)

Example
DBCC DBREINDEX ('Categories')
DBCC DBREINDEX ('Categories','Categoryname',80)

- It Can improve your database perfomance.

WITH RECOMPILE - Re Compile Execution Query Plan

WITH RECOMPILE is specified SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Ideally, you should not use this option because by using this option, you lose most of the advantages you get by substituting SQL queries with the stored procedures. However, you can use this option during early development stages of the project where database structure is still evolving and stored procedures should be recompiled as a result of these changes.

Below is the simple example for using With Recompile in creating STORE PROCEDURE
CREATE PROCEDURE sp_MyTable WITH RECOMPILE
AS
Select Column1, column2 from Table
GO

Except the above method, you also can using with Recompile when u Execute the Store procedure in Query Analyzer.

Below is the example for using With Recompile in Query analyzer
EXEC sp_MyTable1 'Parameter1', 'Parameter2' WITH RECOMPILE

This tells SQL Server to throw away the existing query plan or do not cache the previous execution query plan and build another one query plan but only this once.