Hi there, How greatly does Extent Scan Fragmentation affect performance? I am running DBCC IndexDefrags and my Showcontig is very cool other than having an increasing Extent Scan fragmentation. Does this factor affect performance? And if so, what use is DBCC IndexDefrag? I'd rather not do a DBCC DBReindex due to the size of my tables, time constraints etc. Thanks.
DBCC Indexdefrag does not perform a 100% defragmentation. If you want to perform a 100% defragmentation you will have to rebuild the indexes. The reason for this, and the pros and cons of this, are that while DBCC Indexdefrag reduces fragmentation, it does not eliminate it, but it will not lock tables and prevent users from accessing the database. On the other hand, rebuilding idexes will perform a 100% defragmention, but it will lock tables for a short time, which could negatively affect user access. So you have to choose which option is best for your situation. You might consider running DBCC Indexdefrag daily or weekly, and only rebuild the indexes completely monthly, or whatever best fits your needs. And to really answer your first question, Extent Fragmentation can hurt performance over time if it gets bad enough, so you will eventually want to rebuild your indexes at some point. ------------------ Brad M. McGehee Webmaster SQL-Server-Performance.Com
