We have our application running on sql server 2005. Recently we started slowing down of the database and timeouts. Once we clear the SQL cache, it turns normal again. What all can be the causes of this issue we are facing. And what can we do to avoid this. Please help!
We ran into this exact situation not long ago, and what I found was SQL created different cached execution plans based on the parameters we were passing into a procedure, and in some cases the cached execution plan, though ran speedy for some parameters ran very inefficiency with other parameters.
Once I found the troublesome procedure my fix was to view the execution plan and create a few indexes so the same, more efficient cached execution plan was created regardless of parameters used, which worked.
The first thing you need to do is determine what process is taking the longest to run. You may be able to monitor your processes and see which ones are taking a long time to run and go from there.
Let me know if you need more details and I can send you some of what we did to find the troublesome procedure then remedy the latency.
Take care --