We have a dedicated SQL server running Windows Server 2003 R2 Standard x64 SP2 and SQL Server 2005 (64-bit) Standard Edition with 16GB RAM. Every night we're experiencing severe performance issues (timeouts). We do have some long-running SQL job that runs each night to de-normalize data into some "datamart" tables used for reporting. It's during this time that we get a lot of timeouts on other queries.
We use Confio's Ignite product and it reports that most of our waits are due to "resource_semaphore" and "MemoryCPU" issues...
Our SQL Log also reports several of the "A significant part of sql server process memory has been paged out. This may result in performance degradation..."
In looking at some performance counters on our SQL box, it looks like we're having memory paging issues. Our Memory-Pages/Sec is extremely high, and the Memory-Page Reads/sec is about the same as our PhysicalDisk-Disk Reads/sec which I think means our disk is overloaded with memory page reads...
The SQL memory configuration was initially set to have a min of 0 and the default max, but I've since set both the min and max to 12GB (I also even tried setting them both to 4GB thinking that maybe there wasn't enough memory left over for the OS to use)...
Neither of these changes had any effect on the memory paging.
The SQL Specific performance counters look ok. Our buffer cache hit ratio is high, and the page reads/sec and writes/sec are zero...
Once our job finishes, the memory paging stops...
What I don't understand, is why there is so much memory paging when our job runs, but SQL isn't even using all the memory that has been allocated for it (Total Server Memory is much lower than Target Server Memory)
I'm at a loss for what to look at next. Any ideas?
