SQL Server Performance Forum – Threads Archive
Rebuild indexes or /and udatete statistics?
I notices that all our servers have scheduled update statisticsbut never rebuild indexes. Is there a reason for that? I always thought that i have to rebuild indexes and update statistics – not "or"
If you choose to rebuild indexes then no need to specifically scheduling update statistics, as it takes care of re-initializing the indexes/stats. If not you can manually execute update stats to get optimum performance due to inserts/updates on that table. ITs better to schedule a window for rebuilding indexes. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I found very detailed article that clear my question. http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#XSLTsection122121120120
Keep in mind DBCC INDEXDEFRAG is no efficient than DBREINDEX whereas the former is online operation with no interruption. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Sorry I don’t understand.
You mean I have to do rebuild not defrag? Or you mean that rebuild and defrag shouldn’t be running during operation
hours?
DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index. And comments about both the statements from thishttp://www.sql-server-performance.com/dbcc_commands.asp link. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
thi is note from the article I refeded earlier: DBCC INDEXDEFRAG does not help to untangle indexes that have become interleaved within a data file. Likewise, DBCC INDEXDEFRAG does not correct extent fragmentation on indexes.
Even with this limitation, the tests showed that DBCC INDEXDEFRAG can be as effective at improving performance of workloads as DBCC DBREINDEX. In fact, the tests showed that even if you can rebuild indexes so that there is minimal interleaving, this does not have a significant effect on performance. Reducing the logical fragmentation levels had a much greater impact on workload performance. This is why it is recommended that you focus on logical fragmentation and page density levels when examining fragmentation on your indexes. So based on what I’ve read I made a conclusion to use Defragm.
Fine, no worries if possible get a window for DBCC DBREINDEX also say once in a week or so. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Well the database is 50 g
So I guess because of that there is only update statistic running every
night no rebuild indexes of defrag. I plan to defrag on saturday once (not to schedule)
and then just check Logical scan fragmentation for indexes with 50000 and
more pages and do defrag manually as needed.
OK, the defragmentation of a very fragmented index can generate more log than even a fully logged index creation. The defragmentation, however, is performed as a series of short transactions and thus does not require a large log if log backups are taken frequently or if the recovery model setting is SIMPLE. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I don’t know that I would do DBCC DBREINDEX on a manual basis only. Satya is right that you need to run it at least once a week. If you don’t want to run it for every table, at least write a proc to loop through your tables and test for fragmentation. If it’s above a certain threshold reindex the table. This can have a huge performance increase on a high transaction system. Derrick Leggett
]]>