>>>>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
>>
>>Martina,
>>
>>Sorry to trouble you again. I sent the code you kindly posted that is supposed to rebuild indexes. I ran it on my SSMS and it work. Although I only had two indexes defragmented.
>>
>>But when the customer system engineer (he is not really DBA) ran the script, he gets error on every index tag:
>>
>>The sample message he gets is:
>>

>>FAILED: invntory inv_descr
>>

>>
>>Can you think of what I or he maybe doing wrong?
>>
>>Thank you.
>
>Dmitry,
>
>I'm sorry. It's my mistake.
>WITH(ONLINE = ON) is for enterprise edition only.
>
>

>    EXEC('ALTER INDEX ['+@lcNAME+'] ON ['+@lcTABNAME+'] REBUILD ')
>

>
>MartinaJ

Could you please if I applied your change correctly?

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 ')
    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

Thank you!