I have an ASP.NET MVC-4 application, Entity Framework 5, running on IIS7, with a SQL Server 2012 database.
Every day, between 7:45 and 8:15, several hundred users perform the same everyday daily tasks. And the performance is terrible. We get timeouts everywhere.
So, today I was sitting with several diagnostic tools. In SQL Server Profiler, I found that hundreds of queries synchronized in 30 seconds. I copied the query and placed it in SQL Sentry to see what happens in the query plan. After 30 minutes, the request was finally completed, showing a completely inaccurate, ineffective execution plan. The index scans the return of tens of millions of records that should have been filtered to tens, or maybe hundreds of records, by an earlier join.
Meanwhile, I checked Performance Monitor for other diagnostic programs. The processor worked at 20-30%, we have 256 GB of RAM, of which about 200 are reserved for SQL Server, and there is a lack of working memory. No significant db locking, no deadlocks. Just awful, awful performance.
So, I tried to run the same query. He returned after about 0.1 seconds. And the query plan looked completely different: everything was a distortion of the index, beautiful thin lines and everything else. Every other query that I captured in SQL Profiler, which took more than 5 seconds between 7: 45-8: 15, now returned after 0.1 seconds.
We have the same problem every day, at the same time. How can I solve the problem? I think there might be some kind of clue from different query plans when the requests are executed at different times; Could this be related to SQL statistics? If so, how do we resolve this?
EDIT: I took @t_m advice and deployed a DMV script. Now I can see for sure which execution plan was executed when the request timed out, depending on which plan it followed when it was running.