This problem is turning out to be like a bad penny.
Started happening about 3 months ago, so we adjusted our maintenance plan to run nightly and rebuild indexes on ShipDtl table. We did identify that when the situation occurred, you could immediately inspect the server logs and see the POGetDtlList transaction go from 40 to 80 ms to 8,000 to 64,000 ms. If we rebuilt indexes in middle of day, problem vanished immediately.
You can run this query and see large fragmentation on several of the indexes.
select OBJECT_NAME(ips.OBJECT_ID), i.NAME ,ips.index_id ,index_type_desc ,avg_fragmentation_in_percent ,avg_page_space_used_in_percent ,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N’erp.ShipDtl’), NULL, NULL, ‘SAMPLED’) ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC
I then started playing with the freespace on several of the indexes that were fragmenting during the day. The following seems to have reduced or eliminated the fragmentation:
alter index IX_ShipDtl_SysIndex on erp.ShipDtl rebuild with (FILLFACTOR = 90)
alter index IX_ShipDtl_CustNumOurPart on erp.ShipDtl rebuild with (FILLFACTOR = 90)
alter index IX_ShipDtl_CustNumCustPart on erp.ShipDtl rebuild with (FILLFACTOR = 90)
alter index IX_ShipDtl_UnShippped on erp.ShipDtl rebuild with (FILLFACTOR = 90)
alter index IX_ShipDtl_WarrEffDate on erp.ShipDtl rebuild with (FILLFACTOR = 90)
alter index IX_ShipDtl_WarrLabExp on erp.ShipDtl rebuild with (FILLFACTOR = 90)
alter index IX_ShipDtl_WarrMatExp on erp.ShipDtl rebuild with (FILLFACTOR = 90)
alter index IX_ShipDtl_LastExpiration on erp.ShipDtl rebuild with (FILLFACTOR = 90)
alter index IX_ShipDtl_ShpConNum on erp.ShipDtl rebuild with (FILLFACTOR = 90)
alter index IX_ShipDtl_PartNum on erp.ShipDtl rebuild with (FILLFACTOR = 90)
Now we’re on to a new culprit causing the slow down and I haven’t yet identified which function is running slow. I suspect the table is now the issue as it approaches 1 million rows.
Several questions for the community:
How many rows do you have in erp.ShipDtl table?
Has anyone changed freespace on the table itself?
Anyone run a purge of rows on an annual basis to try to keep the table smaller?
Would it benefit from rebuild of the table?
Any ideas on what else to look for?
Thanks in advance.