No. But it seems like too much processing going on the server during that time. So, could be CPU pressure as well. But I'm not sure exactly to be honest.

Sorry, I attached incomplete plan. I've reattached the complete plan.

That didn't help either.

Thanks for the quick response. Appreciate it. Just out of curiosity, why would proc behaves in different manner when we do select * into.

Also made sure stats are up to date and index are not fragmented.

Thanks. But in the table itself there is data for 1 year only. Wanted to figure out why would it show such a strange behavior?

I've attached the plan from both SSMS and WEB. Since this is a new app, I've not built any indexes yet you'll see table scan.

Thank Grant for the prompt response. I would also avoid them to drop tables. Is it possible?

This is how the SET option is looking

ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: True, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True => Through SSMS

ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True,...

Actually, finally came up. But i see only one plan in the cache. Should I be seeing two for parameter sniffing?

Running sp_whoisactive, it shows reads spiking u, CPU to 368, 163 and used_memory to 131.

As soon as I take the recompile option out, web hangs...Process goes to runnable step and never finishes for me to get to the execution plan.

Or in other words, how do I get the ANSI connection settings from the web app?

Thanks Grant. Why would option recompile would fix it through the web app?

Thanks. Can you post the query to capture the query plan? Appreciate your help.