System.OutOfMemoryExceptions - SQL Server Performance Analysis

Solution

All Gimmal Solutions

Product

All Gimmal Products

Product Version

(FA/PH)

Issue

System.OutOfMemoryExceptions are logged in the SharePoint ULS logs, and there may be SharePoint functionality that is impacted, either with a UI component throwing an exception or a SharePoint timer job failing with exceptions.

System.OutOfMemoryExceptions logged to the SharePoint ULS logs may be the result of a range of underlying issues, including processes exceeding physical server memory, processes exceeding allocated thread memory or may indicate performance issues with the SQL Server instance hosting SharePoint databases.  This article provides an overview of best practices for SQL Server performance analysis to ensure that System.OutOfMemoryExceptions are not the result of SQL Server performance.

Root Cause

System.OutOfMemoryExceptions may be logged to the SharePoint ULS logs if there are performance issues with the SQL Server instance hosting SharePoint databases, and SQL requests from the SharePoint servers become significantly delayed or timeout.  This is generally identified with system.outofmemoryexceptions in the SharePoint ULS logs and performance related errors in the SQL Server logs during the same periods. 

SQL Performance issues may cause this exception to be thrown during SQL intensive processes such as scripts or SharePoint timer jobs that perform extensive processing such as iterating site collections, sites and document libraries.  This is an indication that SQL Server performance should be monitored and appropriate steps for performance mitigation should be executed.

Resolution

SQL Server performance should be monitored in any SharePoint environment with alerts established on performance criteria to notify administrators when alert thresholds are exceeded.  As SQL Server configuration and performance monitoring is an extensive topic, selected links at the end of this article provide best practices.

SQL Server performance issues may be identified through the configuration of performance counters for SQL.  System operations components such as Microsoft System Center Operations Management (SCOM) monitors performance counters at specific intervals and can log events and send email notifications for configurable event parameters.  If a systems operations component is not utilized, SQL performance counters can be manually configured and analyzed to assess performance against acceptable baselines.  It is recommended to run and analyze baseline performance counters with in any production environment, configured to log counters every 15 seconds, and create a performance log file each day so performance can be analyzed through varying loads and operations.

Review Disk IO for SQL, and review and follow recommendations for separating different databases onto separate physical disks.  Analyze the disk IO performance counters to guide where disk IO is an issue, separate databases onto separate physical disks, and prioritize databases on faster drives or SSD storage.  TempDB and transaction logs should ALWAYS be on separate disks from content DBs, recommended that SharePoint databases (search, profile, etc.) also be on separate disks.

  • The highest ranked item should be in the fastest drives.
    1. tempdb data files and transaction logs
    2. Content database transaction log files
    3. Search databases, except for the Search administration database
    4. Content database data files
  • In a heavily read-oriented portal site, prioritize data and search over transaction logs as follows.

The highest ranked item should be in the fastest drives.

    1. tempdb data files and transaction logs
    2. Content database data files
    3. Search databases, except for the Search administration database
    4. Content database transaction log files

Set autogrowth for databases to at least 10% of database size.  Default is 1MB increment, which causes thrashing on DBs that can grow rapidly.

Ensure backups are scheduled during non-business hours, and queued backups are not impacting performance.

Run SQL Database Maintenance Plan to run DBCC checks, defrag databases, rebuild indexes, etc. on a periodic basis.

Best Practices for SQL Server in a SharePoint server farm

https://technet.microsoft.com/en-us/library/hh292622.aspx

Maximizing SQL 2012 Performance for SharePoint 2013 Whitepaper

http://www.stuartroberts.net/wp-content/uploads/2014/07/Maximizing-SQL-2012-Performance-for-SharePoint-2013-Final.pdf

Performance Counters for SharePoint and SQL Server

https://technet.microsoft.com/en-us/library/ff758658.aspx

SQL Database Maintenance Plan

https://www.techtask.com/sql-database-maintenance-plan-for-sharepoint/

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk