DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 970070 - Last Review: June 13, 2013 - Revision: 6.0

Hotfix Download Available
View and request hotfix downloads
 

VSTS bug number: 302851
VSTS bug number: 302848
CI 17820 (http://bemis/ContentIdea/Lists/Request/DispForm.aspx?ID=17820)

On This Page

Symptoms

The Standard 64-bit editions of Microsoft SQL Server 2005 and later versions that have the Lock Pages in Memory privilege set for the service account of the SQL Server Engine do not use Address Windowing Extensions (AWE) APIs for buffer pool memory allocation. The use of AWE APIs on 64-bit editions of SQL Server for buffer pool memory allocation is known as Locked Pages. Cumulative Update package 2 for SQL Server 2008 Service Pack 1 and cumulative update package 4 for SQL Server 2005 Service Pack 3 provide support for Locked Pages.

Note By default, Enterprise and Developer 64-bit editions of SQL Server 2005 and later versions support Locked Pages. For more information, see the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/en-us/library/ms187499.aspx (http://msdn.microsoft.com/en-us/library/ms187499.aspx)

More information

The cumulative updates introduce trace flag 845 and allow SQL Server 2008 64-bit Standard Edition and SQL Server 2005 64-bit Standard Edition to enable Locked Pages if the Lock Pages in Memory privilege is set for the service account of the SQL Server Engine Service.

Note This trace flag is not required for customers who use Enterprise 64-bit editions or Developer 64-bit editions of SQL Server. Enabling this trace flag on these editions has no effect.

To use this trace flag, you must set the flag as a startup option for SQL Server by using the –T parameter. For more information about how to enable a trace flag as a startup option, visit the following MSDN Web site:

http://msdn.microsoft.com/en-us/library/ms190737.aspx (http://msdn.microsoft.com/en-us/library/ms190737.aspx)

To make sure that the trace flag has enabled Locked Pages in 64-bit editions of SQL Server, verify that the following message is written in the SQL Server error log file at startup:

Using locked pages for buffer pool

If you do not see this message in the error log file, do the following:
  • Make sure that you have trace flag 845 set correctly. Trace flags that are set as startup options are printed at the top of the error log file.
  • Make sure that the service account for SQL Server has the          Lock Pages in Memory privilege enabled. You do not have to set these permissions if the SQL Server Service Account is the LOCAL SYSTEM account. For more information, visit the following MSDN Web site:

    http://msdn.microsoft.com/en-us/library/ms190730.aspx (http://msdn.microsoft.com/en-us/library/ms190730.aspx)
Enabling Locked Pages may have a negative performance impact on your system. You should carefully consider any potential effects before you use Locked Pages, even on Enterprise editions and Developer editions.

Note about SQL Server 2008 R2 Standard Edition (64-bit): Microsoft SQL Server 2008 R2 Standard Edition (64-bit, all versions RTM and later) also requires trace flag 845 to be added as a startup parameter so that SQL Server can use locked pages for the Buffer Pool when the SQL Server service account is granted the Lock Pages in Memory security privilege.

Note about SQL Server 2012 Standard Edition (64-bit): Microsoft SQL Server 2012 Standard Edition (64-bit) does not require you to enable any trace flag to allow SQL Server use locked pages for the Buffer pool when the SQL Server service account is granted the Lock Pages in Memory security privilege.

More information about cumulative update package 4 for SQL Server 2005 Service Pack 3

For more information about what files are changed, and for information about any prerequisites to apply the cumulative update package that contains the hotfix that is described in this Microsoft Knowledge Base article, click the following article number to view the article in the Microsoft Knowledge Base:
970279  (http://support.microsoft.com/kb/970279/LN/ ) Cumulative update package 4 for SQL Server 2005 Service Pack 3

More information about cumulative update package 2 for SQL Server 2008 Service Pack 1

For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
970315  (http://support.microsoft.com/kb/970315/ ) Cumulative update package 2 for SQL Server 2008 Service Pack 1




For more information about the effects of using Locked Pages with SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
918483  (http://support.microsoft.com/kb/918483/ ) How to reduce paging of buffer pool memory in the 64-bit version of SQL Server

For additional information on enabling “locked pages” features in SQL Server 2012 and other versions, click the following article number to view the article in the Microsoft Knowledge Base:
2659143  (http://support.microsoft.com/kb/2659143/ ) How to enable the "locked pages" feature in SQL Server 2012



Applies to
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2005 Standard Edition
Keywords: 
kbautohotfix kbhotfixserver kbexpertiseadvanced kbsurveynew kbprb KB970070
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support