>>>>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
It is possible; Too bad no errors shows. But at least I can send this SQL Select to the customer and ask them to ask the DBA to run this SQL Select. I am guessing that DBA should have access to all system tables.
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