7

I have a Microsoft SQL Server running on an IBM server with 10 cores and 64 GB RAM.

I have from 100 to 300 users working concurrently on the server.

Users complain about slowness of the application. I've checked the server and found CPU usage is 6%, RAM usage is 4GB, and network usage is up to 1Mbps. I have a 4Mbps dedicated line.

I've tried to collect other information on the server such as Blocking and Wait Statistics:

enter image description here

enter image description here

The Average resource wait time is 40 sec.

I am unable to interpret the cause. Can anyone suggest a way to resolve this?

If any further information is required, I can provide that.

EDIT:

As suggested, I've added the following items:

Page Life Expectancy 3

enter image description here

IO stats Snapshot

enter image description here

Wait Statistics

enter image description here

Server information

enter image description here

Memory status

enter image description here

Query Execution List

enter image description here

7

Following discussions in chat, we've established:

  • 32bit Enterprise SQL Server.
  • 3.5GB of memory in use (no AWE/PAE).
  • 2.1GB database with 1.3GB buffer pool.
  • Page life expectancy of 3 seconds.

Suggested fixes are:

  • AWE/PAE as a quick fix so the database fits in memory.
  • Investigate the frequently called, highest IO queries identified.
  • Consider rebuild to 64bit.
3

I think main problem is that SQL Server is not using all RAM you have (32 bit windows?). you can look here. You should make sure sql server ir using much more than 4 GB of ram (in your case- up to 60 GB (recomendations)).

After you enable server to use RAM and if that still not enough, you could probably apply some (or all) of these steps:

  • optimise tempdb (file location = different disk/file count = CPU cores/2)
  • optimise log files (separate disk)
  • use read only file groups (less locking)
  • depending on disks you have- create more files per file group and put them on seperate disks
  • If that is OLTP system- set Max degree of parallelism = 1

With all server settings be careful and think twice before apply them.

  • 1
    The last advice is actually a non-advice. Who said parallelism is bad and needs to be disabled?! – Marian Sep 3 '12 at 13:34
  • @Marian Paralelism not bad per se. but there are specific situations when configuring MAXDOP to 1 helps a lot. From question i saw a lot of CXPACKET waits, so that makes me suspicious about this setting. – Jānis Sep 3 '12 at 14:06
  • @Jãnis, Large cxpacket waits in combination with high pageiolatch_xx could indicates large or many table/ clustered index scans. in this case viable explanation. Be careful, a lot of the time Cxpackets waits hides the true root cause of the problem. – Edward Dortland Sep 3 '12 at 14:27
  • 1
    @Jānis: That's exactly what I was afraid of. Lots of CXPacket waits is not that bad as it sounds :-). Usually you should ignore that wait type unless there's a situation like the following: Parallel parallelism :D. Some info: Hands off Maxdop, Hurry up and wait, Stop waiting. – Marian Sep 3 '12 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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