Hi to all,
In my company we are dealing with a very strange problem in the last couple of week in a SQL 2012 server. And maybe you could give us an hit.
We have database that supports an ASP.NET application. We have about 500 users, and we use IIS 7 with Web Garden. Usually everything is ok, but 3-5 times a day there is one, and just one, query that takes some minutes to be executed, and usually it takes about (500 ms).
This represents about 0,1% of the times the same query is called.
Using profiler, I see that usually the query takes 10k to 20k of reads, but when the problem occurs we get 1.5 Million reads.
The stored procedure is dynamic, we send the query as a string. If I copy the query and execute it in MSSMS it is always fast.
We update indexes and statics every week.
We tried already the
• WITH RECOMPILE option,
• copy the parameters to local variables because of parameter sniffing
• free cache.
• WITH REPAST
• WITH NOLOCK
Nothing seems do work and the problem seems to be random.
Did you ever come across with something like this? We didn’t detect locks that explain this, because the same query is called almost at the same time by other users with normal responses. CPU and Memory also seems to be ok.
Any kind of help would be appreciated.