Blindly Diagnosing a RAM Issue affecting SharePoint 2010 W3Wp.exe and SQL Server 2005 Enterprise Edition

I received limited information about an issue.  Here are some ideas.

Description

A SharePoint Server 2010 Farm with a SQL Server 2005 Enterprise Edition database server has RAM issues which are affecting affecting both the Web front end (w3wp.exe) and SQL Server.

Would be Helpful to Know

Is it 32-bit or 64-bit? How much physical RAM is assigned to each server role? What version and patch level is the OS?  What version and patch level is SQL Server? How is SharePoint being used?  How many users?  Are there custom Web parts? Custom Web pages? What are the SQL Server performance counters reading?  When does a memory spike occur?  How long does it last?  When did it start?  What recently changed in the environment?  Are the systems physical or virtual?  What is the server farm topology?

Possible Causes

Need a Service Pack/Patch for OS:  self-explanatory, but need the current information to know if this is a possibility.

Antivirus:  Investigate antivirus software running on the servers.  IIS Forums reported that McAfee Enterprise V8  had a buffer overflow protection feature that was causing problems with w3wp.exe (IIS Forums, 2008).

Incorrectly disposed objects:  Unmanaged memory can lead to memory problems (MSDN, 2010).

Scheduled jobs:  SQL Server maintenance plans, SQL agent jobs (e.g. backups), SharePoint full or incremental crawl schedules can create noticeable degradation while running.

On the SQL Server side of things:  Resource bottlenecks, TempDB bottlenecks, or a slow running user query (less likely in this situation) (MSDN, 2010).  Query the sys.dm_os_memory_clerks and sys.dm_exec_query_memory_grants DMVs to get an idea how SQL Server is utilizing memory.

Reference

IIS Forums(July, 2008).  W3wp.exe memory usage is out of control.  Retrieved March 10, 2013 from http://forums.iis.net/t/1150494.aspx

Microsoft TechNet (2005).  Troubleshooting Performance Problems in SQL Server 2005.  Retrieved March 10, 2013 from http://technet.microsoft.com/library/Cc96654

MSDN (2010).  Disposing Objects.  Retrieved March 10, 2013 from http://msdn.microsoft.com/en-us/library/ee557362.aspx 

MSDN (2010).  SharePoint Dispose Checker Tool.  Retrieved March 10, 2013 from http://archive.msdn.microsoft.com/SPDisposeCheck

SPS Boston: Search Driven Design Patterns for SharePoint 2013

Event:
SharePoint Saturday Boston
Saturday, April 27, 2013

Session Title:
Search Driven Design Patterns for SharePoint 2013

Abstract:
First, we will step back and examine some SharePoint related deficiencies, common portal design flaws, and challenges that exist in the enterprise.  Next, we will align ourselves with an overarching Enterprise Architecture strategy and redefine the role of SharePoint Search in the organization.  From there, we will design a SharePoint 2013 portal that is optimized and ready for Search Driven design patterns.  Finally, we will construct Search Driven components using creative techniques and reflect on the benefits of the overall approach that we followed.

Slides:

Search Driven Design Patterns for SharePoint 2013.pdf