I have a big heap table with 17,093,139 rows. This table is the most heavily used table in the database. Since this is a heap table, there are only non-clustered indexes in this table. I rebuild/reorganize fragmented indexes on this table regularly.
Now we are facing this issue very often: Lots of queries accessing this table will suddenly start taking longer than usual. When I check, I observe that the execution plans for the queries have changed.
I create and drop a random non-clustered index and this fixes the issue.
What I don't get is, what is causing these sudden slowness randomly anytime and what does creating and dropping the index do in the background to the table to fix it which the index rebuild job doesn't do?
I need to find what exactly is triggering these slowdowns so that a permanent solution can be found as I can't just just keeping creating and dropping the index to fix this issue every time.
Any help here would be greatly appreciated.