Logical Scan Fragmentation | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Logical Scan Fragmentation

I have a very large table (over 30 milion records)
when I run DBCC Show contig on the table using the fast option
I show a Logical Scan Fragmentation of 60% is what is the best way to improve this?
Thanks
You need to rebuild the indexes on this table using an appropriate fillfactor. The downside to this is that the table will be locked during this time, so users won’t be able to access it. Another option, although not as good, is to run DBCC INDEXDEFRAG, if you are running SQL Server 2000. This command will help to make the indexes more efficient, but it won’t be as good as a total index rebuild, but it will allow users to access the table when it is working. Whichever option you choose, you will want to do it during low activity. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
I ran DBCC INDEXDEFRAG
on all of the indexes on the table Except the Cluster Index yet still the Logical Scan Fragmentation is at 60% Im trying to defrag the clustered index now…. any other thoughts[?]
Do a rebuild of the indexes as brad has suggested. I feel this will resolve your problem.
"How do you expect to beat me when I am forever?"
Yes, if yo are running DBCC INDEXDEFRAG, and you still have a fragmentation problem, then rebuilding the indexes is your only choice. DBCC INDEXDEFRAG only partially works, as you have seen. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Amazingly enough defraging the clustered index reduced the Logical Scan Fragmentation to 0% I not sure if this will have any effect on overall performance
It will help, although it probably won’t make a huge difference, but every little bit helps. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
I don’t know your situation, but it is of utmost importance that on some regular schedule you rebuild your indexes. I have seen not only fragmentation problems go away, but other problems as well.
"How do you expect to beat me when I am forever?"
And also should have regular DBCC checks and maintenance tasks on the database which helps the good health of database. Satya SKJ
]]>