>>>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
>
>Thank you, Martina. I will run your code on my SSMS first, before attempting it on the customer SQL Server.
>Two follow-up questions, please.
>1. Should - as far as you know - the defragmentation of indexes (running your code above or another way) be done by a DBA?
>2. Do you think it is a good idea to include the above code in the application (VFP 9) feature? That is, so that the end user can run it when they feel the data is slow?
>Thank you.

Dmitry,

You can write stored procedure a set SQL job with schedule each first day in month in 00:00 which run your procedure.
And if you have stored procedure, you can calls it if you/customer need.

MartinaJ