up vote 0 down vote favorite
share [g+] share [fb]

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.

link|improve this question
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
feedback

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

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

3 Answers

up vote 8 down vote

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.

link|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
feedback
up vote 0 down vote

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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard