Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLJames
Starting Member
35 Posts |
 Posted - 2013-09-05 : 16:03:01
|
| Greetings and thank you in advance for your time.First, it was not my fault or design. I am the lucky one who gets to work with it.We have a large table with 150M rows. No clustered index. Lots of non-clustered indexes. The table is partitioned on a date column. There are about 150 partitions. Select performance is terrible - big surprise.I ran a select against sys.dm_db_index_physical_stats where avg_fragmentation_in_percent was greater than 20. Almost every partition was returned so about 150 rows. The avg_fragmentation_in_percent is between 30 and 99 percent. Average of about 95% fragmented. Page counts average about 40K per partition.I understand the issue with re-org or re-build of indexes on a table to reduce the fragmentation of the indexes. Since this is a heap, there is no index that I can target to rebuild. How would I go about reducing the avg_fragmentation_in_percent for each of the partition heaps?Thanks for your consideration! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-09-05 : 20:03:22
|
| Reorging and rebuilding indexes to fix fragmentation does not generally help with performance. I would suspect missing indexes, out-of-date statistics, poorly written code, hardware bottleneck or other things before even bothering with fragmentation. What does the execution plans show for the select queries that are performing poorly.Can a clustered index be added? How wide of a table is it?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
Heap Fragmentation