Hello Experts,
I am seeking help to optimize SQL fetch on InventTable.
We have a customer with considerable volume of data (278K records) who is facing Performance issues in Sandbox (SBX) and Prod.
After restoring SBX into DEV, we found that it takes approximately 50 seconds to a minute to fetch 278K records.
While researching across the internet and AI tools, we found below details:
• Verify indexes and statistics status on InventTable.
• Confirm fragmented indexes and outdated statistics can cause slower query execution.
• Recommended performing index health checks and updating statistics on product-related tables.
As part of Query tuning, we continued our analysis with below points:
• Investigated Item master performance using SQL traces and by commenting dependent logic to isolate problem areas.
• Checked SQL execution plan to identify costly operators, table scans, or lookups contributing to slowness.
• Verified overall space utilization of InventTable using sp_spaceused (row count, data size, index size, unused space).
• Checked index fragmentation with sys.dm_db_index_physical_stats.
• Analyzed column-level data size to identify fields consuming more space and contributing to slower performance.
Apart from reviewing existing Clustered indexes on InventTable, is there any other way to speed up SQL fetch?
Thank you.
Arun
