Hi, I have a customer that have a major problem that comes and go for months now. When doing query, all is going well most of the time, the request takes less than a second to return results. Problem is that from time to time the same query takes up to 10 seconds to return results, making the program very frustrating to use if not almost useless. This can happen many times during a day (more than 20 times at the worst days) or not at all. I'm using sql server 2012 running on a windows server 2012 essential. Problem can occur even when running directly on the server, it also happens even if there is just one computer opened.
I was not using indexs (except primary keys) and I thought this might be the problem, I have created some and the problem stopped right away for a month and came back for no reason. I have tried to use the ALTER INDEX... REBUILD command but this had no effect at all.
I have used up pretty much all solutions I had in mind, I don't know where to go from here. If anyone has any idea what this is or any suggestions on tools I can use to monitor this behavior and detect the source, that would be greatly appreciated.