484
Part V: Enterprise Data Management
The memory-confi guration properties, listed in Table 19-2, control how SQL Server uses and
allocates memory.TABLE 19-2 Memory-Confi guration PropertiesProperty Level* Graphic Control Code OptionDynamic Memory
MinimumS Management
StudioEXEC sp_configure 'min server
memory'
Dynamic Memory
MaximumS Management
StudioEXEC sp_configure 'max server
memory'
Fixed Memory Size S Management
StudioEXEC sp_configure 'min server
memory' and EXEC sp_configure
'max server memory'
Minimum Query
MemoryS Management
StudioEXEC sp_configure 'min memory
per query'
Query Wait S Management
StudioEXEC sp_configure 'query wait'AWE Enabled S Management
StudioEXEC sp_configure 'AWE Enabled'Index Create
MemoryS Management
StudioEXEC sp_configure 'index create
memory'* The confi guration level refers to server, database, or connection.The confi guration options set working set size, open objects, and locks are still present in the
sp_configure stored procedure, but their functionality was removed in SQL Server 2008 and is unavailable
in Microsoft SQL Server 2012. These options have no effect. Do not use these options in new development work
because they may be removed in future SQL Server versions.Dynamic Memory
If SQL Server is set to dynamically use memory, then SQL Server’s memory can grow or be
reduced as needed within the minimum and maximum constraints based on the physical
memory available and the workload. SQL Server tries to maintain its requirement of 64MB
and 128MB for the 32-bit and 64-bit versions respectively. The goal is to have enough mem-
ory available while avoiding Windows needing to swap pages from memory to the virtual-
memory support fi le (pagefile.sys).The minimum-memory property prohibits SQL Server from reducing memory below a certain
point and hurting performance, but it does not guarantee that SQL Server will immediatelyc19.indd 484c19.indd 484 7/30/2012 5:42:51 PM7/30/2012 5:42:51 PM
http://www.it-ebooks.info