When you have performance problems in your MSSQL database, one of the first thing you must to verify is that the fragmentation. When the fragmentation is high, SQL Server has got the chance to both reorganize or rebuild indexes. You are able to detect index fragmentation by making use of Dynamic Management View (DMV) sys.dm_db_index_physical_stats and verify the avg_fragmentation_in_percent column.

Use [MyDB];
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
   JOIN sys.indexes AS b ON a.object_id = b.object_id
AND a.index_id = b.index_id
ORDER BY avg_fragmentation_in_percent DESC;

When the value avg_fragmentation_in_percent in among 5% and 30% you ought to perform a reorganize of your indexes, not really a total rebuild. A rebuild you simply need invoked when the fragmentation percentage is more than 30%. No action ought to be taken when the fragmentation proportion is lower than 5% and that is a standard level of fragmentation.

The code below can rebuild all indexes with default fill factor:
Use [MyDB];
EXEC sp_MSforeachtable
  @command1="print '?'",
  @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"

Hope this code will helps you!

As the majority of you will know, an index can enhance the performance of the query most of the time ; internally sql server has to carry out lots of function to keep these indexes in SQL Server 2014. When I started a brand new occupation, I arrived to discover lots of indexes were developed for a few in our production tables. Thus I made a decision to get yourself a script which returns the indexes that’s not active.

Note : it uses the dmv’s so, this isn't a fairly accurate query. However you could use this like a start and apply reasonable just before running the drop index statement.
SELECT  DB_NAME() AS database_name ,
        S.name AS [schema_name] ,
        O.name AS [object_name] ,
        C.name AS column_name ,
        I.name AS index_name ,
        ( CASE WHEN I.is_disabled = 1 THEN 'Yes'
                ELSE 'No'
           END ) AS [disabled] ,
        ( CASE WHEN I.is_hypothetical = 1 THEN 'Yes'
                ELSE 'No'
            END ) AS hypothetical ,
        rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = i.index_id
                        AND p.object_id = i.object_id GROUP BY p.index_id, p.OBJECT_ID),
        N'USE ' + DB_NAME() + N'; DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) +
                    '.' + QUOTENAME(OBJECT_NAME(i.OBJECT_ID)) AS 'drop statement'
FROM    [sys].[indexes] I
       INNER JOIN [sys].[objects] O ON O.[object_id] = I.[object_id]
                                       AND O.[type] = 'U'
                                        AND O.is_ms_shipped = 0
                                        AND O.name <> 'sysdiagrams'
        INNER JOIN [sys].[tables] T ON T.[object_id] = I.[object_id]
        INNER JOIN [sys].[schemas] S ON S.[schema_id] = T.[schema_id]
        INNER JOIN [sys].[index_columns] IC ON IC.[object_id] = I.[object_id]
                                                AND IC.index_id = I.index_id
        INNER JOIN [sys].[columns] C ON C.[object_id] = IC.[object_id]
                                        AND C.column_id = IC.column_id
WHERE   I.[type] > 0
        AND I.is_primary_key = 0
        AND I.is_unique_constraint = 0
        AND NOT EXISTS ( SELECT *
                            FROM   [sys].[index_columns] XIC
                                INNER JOIN [sys].[foreign_key_columns] FKC ON FKC.parent_object_id =
IC.[object_id]
                                                    AND FKC.parent_column_id = XIC.column_id
                            WHERE  XIC.[object_id] = I.[object_id]
                               AND XIC.index_id = I.index_id )
        AND NOT EXISTS ( SELECT *
                            FROM   [master].[sys].[dm_db_index_usage_stats] IUS
                            WHERE  IUS.database_id = DB_ID(DB_NAME())
                                AND IUS.[object_id] = I.[object_id]
                                AND IUS.index_id = I.index_id )