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.
-
3SQL Profiler meet user489626, user489626 meet SQL Profiler. msdn.microsoft.com/en-us/library/ms187929.aspx– jojabaOct 28, 2010 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.)– ulty4lifeOct 28, 2010 at 3:58
-
1How big are your databases and what sort of storage are they on?– phoebusOct 28, 2010 at 14:18
-
How can you be useig 32gb ram on a 32bit os?– ZaptoJul 28, 2012 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 ;)– TomTomJul 28, 2012 at 8:31
Add a 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.
answered Oct 28, 2010 at 4:12
-
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.– TomTomOct 28, 2010 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. Oct 28, 2010 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.– TomTomOct 28, 2010 at 17:09
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.
