Befriending Dragons

Transform Tech with Anti-bullying Cultures


Leave a comment

SQL PASS: All the Magic Knobs

SQL PASS 2011 DBA-319-C #SQLPASS

All the Magic Knobs – Low Effort, High Return Tuning

Key points covered:

  • Power Savings = High Performance
  • Smart Virtualization
  • Enough Hardware
  • Control other apps, filter drivers
  • Optimize for ad hoc workloads = ON
  • Compression = ON
  • Set LPIM + Max Server Memory
  • Pre-size files, avoid shrink and autogrow
  • Fast Tempdb
  • Proper Maintenance

My presentation from 10/13/11 is attached.

AllTheMagicKnobs.pptx


7 Comments

Memory Trimming: A significant part of sql server process memory has been paged out.

I have written about this topic before (in my old SQLCommunity blog) but I still see lots of questions about it. So here is a simplified explanation of this memory trimming issue that is common with SQL Server on Windows 2003.

On Windows 2003 you may occasionally see memory trimming messages in your SQL Server error logs: “A significant part of sql server process memory has been paged out. This may result in a performance degradation.” Generally you will see this at the same time that the performance of your SQL Server becomes very bad. What happens is Windows has to free up memory rapidly and it  cannot wait for the normal, polite “hey, I need some memory, are there any apps willing to give me some”. This can happen due to severe memory pressure, when certain APIs are called, or due to certain Windows 2003 bugs (most of which are fixed in Windows 2003 SP2 or post SP2 hotfixes). Windows 2003 will respond to this pressure by trimming GBs of memory at once. Windows 2008 responds much better in that it trims MBs at a time instead and typically does not cripple running applications such as SQL Server. Therefore it is expected that you will NOT see this scenario on Windows 2008 or later.

 

Ideally you would find and resolve the reason for the external memory pressure, but in many cases that is impractical when a workaround may alleviate the problem. On Windows 2003 you can try combining the “lock pages in memory” (LPIM) setting with a “max server memory” setting such that all instances of SQL added together will still leave “enough” memory for everything else running on the box (SQL Agent, SSIS/DTS, RS, AS, anti-virus, backups, batch processes, services, etc.). LPIM keeps Windows from trimming SQL’s buffer pool. Do NOT ever enable LPIM without also setting “max server memory” for all instances to an appropriate value. There are rare cases where LPIM can cause more problems than it solves. This happens if Windows still faces this type of memory pressure and has to trim SQL Server memory outside the buffer pool which could mean trimming the mapped SQL exes and dlls and causing even bigger performance problems. So monitor the system carefully after you add LPIM and make sure you’ve properly set the “max server memory” setting. The currently recommended fix is to upgrade to Windows 2008 or Windows 2008 R2 where the memory trimming is handled much differently.

 

2001745 Working Set Trimming can negatively impact SQL, Exchange, and Operating System performance under Windows 2003

http://support.microsoft.com/kb/2001745

918483 How to reduce paging of buffer pool memory in the 64-bit version of SQL Server

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483

How to enable the Lock Page in Memory option (Windows)

http://msdn.microsoft.com/en-us/library/ms190730(SQL.90).aspx