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