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.
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.
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.