Thank you Grant.

I have been investigating, same issue is still occurring off and on, DBCC Freeproccache always resolved it.

I did some profiler tracing, wrote down a list of tables that I can find while the slowness happened. I agree with you, it does appear to be parameter sniffing, however when I did a sp_recompile to every table on the list the last time, it didn't help. Slowness continued. I then ran DBCC Freeproccache, it resolved the slowness again, instantly.

From the profiler trace, I noticed there were a lot of Pagelatch_sh wait during the slowness. My research shows that it has to do with database files, table partitioning, and index selection, and etc. That doesn't explain why DBCC Freeproccache resolves the slowness. Do I have a memory issue? Why was the instant relief when DBCC Freeproccache was issued? Shouldn't a forced recompile only applies to the next execution, and has no effect on the queries that is already running?