>Hi,
>
>I wonder, does reindexing SQL Server database improves performance? If yes, how often do you do it?
>I Googles the steps and it shows the steps for one table. Is there a way to reindex all tables at once?
>
>TIA

Yes, can. Each index can be fragmented. How much? It's depends on list of columns, number of writes, etc.

-- list fragmented indexes
SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, AA.Name, b.name, ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
     INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
     INNER JOIN sys.tables as AA ON b.OBJECT_ID=AA.OBJECT_ID
WHERE ps.database_id = DB_ID() AND ps.avg_fragmentation_in_percent>30 AND NOT b.name IS NULL
ORDER BY 6 DESC
GO

--rebuild fragmented indexes
 DECLARE @lorsData CURSOR
 DECLARE @lcTABNAME VARCHAR(128), @lcNAME VARCHAR(128), @lfDFG FLOAT
 SET @lorsData=CURSOR LOCAL READ_ONLY FOR 
    SELECT AA.Name, b.name,ps.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
         INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
         INNER JOIN sys.tables as AA ON b.OBJECT_ID=AA.OBJECT_ID
    WHERE ps.database_id = DB_ID() AND ps.avg_fragmentation_in_percent>30 AND NOT b.name IS NULL
    ORDER BY 3 DESC

 OPEN @lorsData
 WHILE 0 = 0 BEGIN
   FETCH NEXT FROM @lorsData INTO @lcTABNAME, @lcNAME, @lfDFG
   IF @@FETCH_STATUS <> 0 BREAK

   BEGIN TRY 
    EXEC('ALTER INDEX ['+@lcNAME+'] ON ['+@lcTABNAME+'] REBUILD WITH(ONLINE = ON)')
    PRINT CAST(@lfDFG AS VARCHAR(30))+' ALTER INDEX ['+@lcNAME+'] ON ['+@lcTABNAME+'] REBUILD /*WITH(ONLINE = ON)*/'
   END TRY 
   BEGIN CATCH
     PRINT 'FAILED: '+@lcTABNAME+' '+@lcNAME 
   END CATCH
   
 END
 CLOSE @lorsData
 DEALLOCATE @lorsData
GO

MartinaJ