487
Chapter 19: Confi guring SQL Server
19
To revert to the default wait time of the estimated execution time times 25, you must spec-
ify the query wait time as -1.AWE-Enabled
On 32-bit operation systems, SQL Server is normally restricted to the standard 2GB physical-
memory limit (or 3GB if /3GB switch is used in boot.ini). On 64-bit operation systems, the awe
enabled option is ignored, even though it is present in the sp_configure stored procedure.SQL Server x86 Standard, Enterprise, and Developer Editions, when running on Windows
Server 2003 or 2008 Enterprise or Datacenter Editions, can use up to 64GB of physical mem-
ory by confi guring SQL Server to address the Address Windowing Extensions (AWE) API. The
AWE-enabled property turns on AWE memory addressing within SQL Server.EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'awe enabled', 1;
RECONFIGURE;In SQL Server 2012, Address Windowing Extensions (AWE) has been deprecated.The Windows privilege LOCK PAGES IN MEMORY must be granted to the SQL Server service account before
enabling awe. A system administrator can use the Windows Group Policy tool (gpedit.msc) to enable this privilege for
the SQL Server service account.The SQL Server instance must be restarted for the awe enabled option to take effect. If the awe enabled
option is confi gured successfully, then the SQL Server Error Log includes an Address Windowing Extensions Enabled
message when the SQL Server restarts.Index Create Memory
The amount of memory SQL Server uses to perform sorts when creating an index is gener-
ally self-confi guring. However, you can control it by using sp_configure to hard-code a
certain memory footprint (KB) for index creation. For example, the following code sets the
memory used to create an index to 8MB:EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'index create memory', 8096;
RECONFIGURE;c19.indd 487c19.indd 487 7/30/2012 5:42:52 PM7/30/2012 5:42:52 PM
http://www.it-ebooks.info