Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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..." SQL Log

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

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)... SQL Memory 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... SQL Performance

Once our job finishes, the memory paging stops... No Paging

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?

share|improve this question
2  
Have you restarted SQL Server since making the configuration changes? –  Aaron Bertrand Jul 18 '12 at 18:05
1  
How many connections are you seeing during that time? Also, I don't believe you want to have the min=max for mem config, you should probably set min to be 4GB, and max to be 12GB. –  SQLRockstar Jul 18 '12 at 19:41
add comment

migrated from serverfault.com Jul 18 '12 at 17:58

This question came from our site for professional system and network administrators.

2 Answers

Process Object. Find the counter instance (==process) that has high Page Faults/sec and high Private Bytes/Virtual Bytes when the paging occurs. This will narrow down to the actual process that is faulting (likely to be SQL Server, but is always better to measure and prove rather than guess and assume).

share|improve this answer
add comment

Does the server itself have overnight jobs such as backups or anti-virus scans/updates? It's quite common to have NetBackup or similar running overnight, and that will take memory - and also network bandwidth if it doesn't have a dedicated network.

share|improve this answer
add comment

Your Answer

 
discard

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