>>>>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.
>>>
>>>
>>>
>>>
>>>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
>
>>>
>>
>>Martina,
>>
>>I ran the above SQL Select on my SSMS and it shows some numbers: two records both show the FRAGMENTATION of greater than 30 (one 80 and another 52).
>>
>>But when I ran this SQL Select on the SSMS of two customer SQL Servers, no records shown (no records created). I even lowered the 30 in your SQL select to 0 (zero); still no records.
>>
>>What does it mean, if you could, please input.
>>
>>TIA
>
>Hmm, you have not access to view sys.dm_db_index_physical_stats.
>
>MartinaJ
Sorry, me again. The DBA ran the query you suggested and sent me results in an excel file.
1. 133 indexes show numbers higher than 30. Many as high as 98.
2. So, my understanding that reindexing should be done. I will suggest to him to run the other query you posted.
His results actually show the table name (4th column) and the index name (5th column). Which is very nice.
Thank you for your help!
UPDATE. I ran in my SSMS the second query you posted and it did the trick. The fragmentation is gone (from my tables; which were minor). Hopefully it does the same thing on the customer DB.
Thank you!
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham