Very odd thing happening. It would appear that all queries against a particular database in our system are periodically "running slow". Ie "normal speed" for 5 minutes then slow for 5 minutes (roughly).
On further investigation (after several days of eliminating the "obvious") it would appear that, sometimes queries are being sent by the client (Sql Server Management studio) multiple times and being received multiple times.
Ie "Bytes sent from client" will double or even treble, and "bytes received from server" will do the same. Obviously similar pattern of increase in"Client processing time" and "total execution time".
This is even happening with a
SELECT * FROM table where the table on has 3 rows of data!
And when one query goes slow they all go slow. Doesn't matter how simple the query / results set, or which "client" is accessing (same in an ADO.NET based console app / MVC web app).
Any suggestions would be greatly appreciated...
- The "multiple queries" thing cannot be recreated. No idea why it happened, seems to have been an anomaly which temporarily lead us down the wrong alleyway
- @Brent Ozar - thank you for your suggestion - we have run these types of tests already with, unfortunately, no useful results!
- @jco360 - thanks for your suggestions. Indexing is a non-starter as performance is slow across the whole database (one slow = all slow then one quick = all quick). I am the developer and I'm seeing the same issue when running simple queries from SSMS. Finally, the hardware issue may be the answer, although 64GB Ram and many processors should suffice for a server which is barely being used, there may be some hard drive corruption or similar?
It's also not network related as tests have been run from the box itself.