6

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...

EDIT

  1. 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
  2. @Brent Ozar - thank you for your suggestion - we have run these types of tests already with, unfortunately, no useful results!
  3. @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.

Improve this question
14
  • 3
    Have you done any monitoring from the network level? Have you confirmed that users aren't clicking execute/F5 multiple times or have GO 2 or GO 3 at the end of the batch, or some other looping going on? This does not sound to me like a problem with the database or the tools - I've never heard of SSMS sending the same query multiple times unless the user made it do that somehow.
    – Aaron Bertrand
    Commented Apr 24, 2012 at 17:55
  • As stated, start sniffing the network packets and sniff SQL to get the real SQL sentences executed.
    – David Oliván
    Commented Apr 25, 2012 at 7:09
  • We've done network checks etc. And this isn't "users" - this is us (web developers) running tests!
    – LiverpoolsNumber9
    Commented Apr 25, 2012 at 9:33
  • 1
    please check the programmers code.... the 70% of the speed it's up how developer create the query, probably they are doing some crazies queries that can be improve or check how it's the DB design(tables, relations, index, PK, FK). 25% it's up the DBMS, how the DB is configure, 15% it's the hardware (someone tell me if I'm wrong...)
    – jcho360
    Commented May 8, 2012 at 20:15
  • 1
    @LiverpoolsNumber9 I read the edit, but I'm not convinced. One terrible query can slow down a server, I know from experience. Just because everything gets slow at once doesn't mean it's a server issue.
    – JNK
    Commented May 10, 2012 at 16:31

3 Answers 3

Reset to default
1

Try running a sql profile trace on the server activity (http://technet.microsoft.com/en-us/library/ms175047.aspx) - you may have some table locking issues caused by concurrently running queries locking tables/pages/indexes.

Try running a perfmon - there may be something/s running that is using all the available disk/IO/CPU/Memory resources when things slow down. Look for any memory swapping - this will slow things down. If it is one of these then you will need to track down the cause. Note CPU at 100% for short period of times is OK (actually can indicate optimum performance).

Also check for fragmentation and consider rebuilding indexes.

Turn off services/applications that are not needed - they use up resources that are better used for your application.

Improve this answer
0

If everything is slow and it is really consistent in timing then my guess would be a service on the server that is at fault.

An obvious choice is virus protection because some flavors will hook into I/O to catch stuff at a low level.

It could also be as simple as an update checker that is messing with your network stack.

A backup system could also be at fault.

Have you looked into these things?

Improve this answer
0

Just out of curiousity, what are your wait stats? Have you tried to run the queries from SSMS yourself and have the execution plan show, then compared the slow vs the fast one? How about extended events to record what the wait stats are per execution, and you can see what goes up.

Or how about: SELECT * FROM SYS.DM_OS_WAITING_STATS WHERE Session_id = (spid) (I wrote this from memory, should work).

I'm really curious what it's waiting on. Also, how many cores/tempdb physical files do you have?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.