2 added question specific info reguarding table size.
source | link

If sounds to me like your statistics are becoming stale which is causing the SQL Server to use a crappy execution plan to get to the data. Can you post the execution plan for when the query is working well and when it is working poorly?

As the table has 4M rows in it, the statistics won't update automatically until ~800,500 rows have been inserted/update/deleted so if you pump in 600k rows the stats will stay the same and the execution plan won't be correct for the current data distribution anymore.

By dropping the index and recreating it you are effectively updating the statistics.

Next time the problem happens try updating the statistics manually using the UPDATE STATISTICS statement to see if that does the trick. If it does then that's the problem and you need to look into writing a job which updates the statistics on a regular basis.

If sounds to me like your statistics are becoming stale which is causing the SQL Server to use a crappy execution plan to get to the data. Can you post the execution plan for when the query is working well and when it is working poorly?

By dropping the index and recreating it you are effectively updating the statistics.

Next time the problem happens try updating the statistics manually using the UPDATE STATISTICS statement to see if that does the trick. If it does then that's the problem and you need to look into writing a job which updates the statistics on a regular basis.

If sounds to me like your statistics are becoming stale which is causing the SQL Server to use a crappy execution plan to get to the data. Can you post the execution plan for when the query is working well and when it is working poorly?

As the table has 4M rows in it, the statistics won't update automatically until ~800,500 rows have been inserted/update/deleted so if you pump in 600k rows the stats will stay the same and the execution plan won't be correct for the current data distribution anymore.

By dropping the index and recreating it you are effectively updating the statistics.

Next time the problem happens try updating the statistics manually using the UPDATE STATISTICS statement to see if that does the trick. If it does then that's the problem and you need to look into writing a job which updates the statistics on a regular basis.

1
source | link

If sounds to me like your statistics are becoming stale which is causing the SQL Server to use a crappy execution plan to get to the data. Can you post the execution plan for when the query is working well and when it is working poorly?

By dropping the index and recreating it you are effectively updating the statistics.

Next time the problem happens try updating the statistics manually using the UPDATE STATISTICS statement to see if that does the trick. If it does then that's the problem and you need to look into writing a job which updates the statistics on a regular basis.