Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have SQL Server 2005 SP3 Enterprise 32-bit running on a Windows server 2003 Enterprise 32-bit with 32GB of RAM and 8 dual core processors. Our CPU, RAM, and I/O are always through the roof and the application is always running slow. We use AWE for buffer cache the maximum memory is 28GB, the PAGEIOLATCH_SH is always high and the procedure cache is always around 700mB. During production hours we have over 6000 connections to SQL Server (500 users). What is wrong here? Please help. Thank you.

share|improve this question

migrated from stackoverflow.com Oct 28 '10 at 5:26

This question came from our site for professional and enthusiast programmers.

3  
SQL Profiler meet user489626, user489626 meet SQL Profiler. msdn.microsoft.com/en-us/library/ms187929.aspx –  jojaba Oct 28 '10 at 3:51
    
How is the database server used? Is it just raw connections or are there applications connecting to the database? Application code might not be closing / terminating all their connections, especially if the code base is older without any built-in connection pooling. (You could cap the connection limit in BOFH style, but I'm guessing that won't really solve the problem.) –  ulty4life Oct 28 '10 at 3:58
1  
How big are your databases and what sort of storage are they on? –  phoebus Oct 28 '10 at 14:18
    
How can you be useig 32gb ram on a 32bit os? –  t1nt1n Jul 28 '12 at 7:49
    
@t1nt1n As he says - using AWS. Welcome to common knowledge: IF THE APPLICATION SUPPORTS IT it can use additional memory in pages via a defined API, and guess what - SQL Server supports it ;) –  TomTom Jul 28 '12 at 8:31
add comment

3 Answers

I'll tell you one thing that is wrong right away: 32bit. Its a dead end. AWE can only help that much. Just give it up and move to 64 bits.

Still, 32 GB with AWE should give plenty of buffer pool. How big is your database? Much larger than 32GB? Why isn't it mostly cached, why do you see PAGEIOLATCH_SH? This implies that the server is doing a large amount of data fetch in from disk, and with 28GB of buffer pool, such a large amount of reads indicate that either your database is truly huge and you just plan need bigger iron (I doubt), or that your query and indexes match so poorly that your forced to read end-to-end large tables constantly (I'm pretty sure of it). A cursory glance over sys.dm_query_exec_stats will reveal the problem query (large total_logical_reads indicates large scans, hence large reads and cache trashing). Similarly sys.dm_db_index_usage_stats will show the problem rowsets. If this is all greek to you, then try using the SQL Server 2005 Performance Dashboard Reports.

share|improve this answer
    
32 bit cana also be the culbprit. With 6000 conections and all the AWE paging area the 32 bit address space must be super tight. A bad / really bad IO subsystem may make the IO pressure bad, too - note how the poster talks about RAM, CPU but says nothing about disc layout. This MAY run on a RAID 5 of slow discs. Seen that. –  TomTom Oct 28 '10 at 6:38
    
@TomTom: right. And given that kernel sees whole 32Gb, it must be that /3gb is not enabled, so the SQL process has a whooping 2Gb for itself. 8x2 cores, 6000 requests, means probably plenty of worker threads, so significant stack reserved size. Add in anything else that consumes VA space (I hope CLR is disabled!), you got a lot going in that 2Gb of address space... And that is before any IO issue comes into picture. –  Remus Rusanu Oct 28 '10 at 16:56
    
Exactly. You are well beyong the limit what 32 bit even with AWE can handle - AWE is not real usable process memory. I suggest upgrading to 64 bit NOW, 64 bit database, then basically it is "flat memory". Some more memory may be nice, too, but at least you will get 32gb USABLE memory, so the 6000 connections etc. dont put pressure on very precious lower address area, which simply does not exist anymore. You may get some nice surprise out of that. –  TomTom Oct 28 '10 at 17:09
add comment

Check Performance Data, Execution Plan, Level of Isolation, Type of Locking, Disk Performance , Indexing etc. It will be easier to suggest more specifically if you tell me the technical architecture/ environment in a bit more detail.

share|improve this answer
add comment

Your Answer

 
discard

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