Experts, I have an interesting problem that has no easy solution. Furthermore I do not know the exact problem, just the symptoms and what has temporarily resolved it:
Approximately every 2-3 hours our database will run excessively slow.
-every call to the database is through stored procedures.
-web service calls (hitting the DB) that usually take 1-2 seconds now time out (180 seconds)
-windows services that poll the DB now time out
When we run the command 'DBCC FLUSHPROCINDB...' to clear the execution plans the database immediately speeds up and we experience no more time outs. However, 2-3 hours later the database slows again and the time outs occur.
My question to you experts, is what steps do I take to find the specific problem? Running 'DBCC FLUSHPROCINDB(...)' every 2-3 hours is not acceptable as it is not resolving the problem. How can I further diagnose and pinpoint the culprit? Could one bad stored procedure execution plan slow down an entire database? Anyone experience something similar with regards to 'DBCC FLUSHPROCINDB(...)' temporarily "fixing" the database?