Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Long read waits suggests that you have a join that needs an index, presumably on a table that's too big to fit its working set in your memory (or some similar tuning issue). Check out the wait stats by query and see if you have one or a small number of queries generating the majority of the page io latch waits. If this is the case then tuning the query or adding an index will probably fix the issue. –  ConcernedOfTunbridgeWells Sep 3 '12 at 9:09
    
@ConcernedOfTunbridgeWells I have rebuild all indexes, and they are multiple queries which are very slow on concurrent usage. The simple invloice table has 10 lakh records and i am trying to get select sum(amount) where invoicekey='abc1234' and Type='R' it is taking 18728070 total duaration and Total Logical Read 3623267 –  Amritpal Singh Sep 3 '12 at 9:17
1  
The problem could be a poorly tuned query, that an index is missing or that you need to add another index. –  ConcernedOfTunbridgeWells Sep 3 '12 at 9:29
    
@Amritpal: then please edit your question with that query, the table's description, the indexes defined on that table and the query plan so people can provide suggestions on how to fix it. (Side note: Lakh is not well known outside of India and neighbouring countries. A lot of people might not understand it.) –  Mat Sep 3 '12 at 9:32
1  
@Amritpal Singh, Okay, what I don't understand are these 3 things. Server has 64GB, you say 4GB is in use and the DB is only 2GB. Can you run this query and show us the results please: SELECT DB_NAME(database_id) AS [Database Name], COUNT(*) * 8/1024.0 AS [Cached Size (MB)] FROM sys.dm_os_buffer_descriptors WITH (NOLOCK) WHERE database_id > 4 AND database_id <> 32767 GROUP BY DB_NAME(database_id) ORDER BY [Cached Size (MB)] DESC; –  Edward Dortland Sep 3 '12 at 10:50
show 12 more comments

2 Answers

up vote 5 down vote accepted

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.
share|improve this answer
    
following your guidelines i am trying to dix the issue and it seems it will work, hop it will :) –  Amritpal Singh Sep 3 '12 at 10:56
1  
ah the chat room :-), I'll +1 you for that. –  Edward Dortland Sep 3 '12 at 10:59
    
@Mark Storey-Smith Very helpful and think fixed the issue right now. –  Amritpal Singh Sep 3 '12 at 11:45
add comment

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.

share|improve this answer
    
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
    
@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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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