486
Part V: Enterprise Data Management
Fixed Memory
Instead of dynamically consuming memory, you can confi gure SQL Server to request a fi xed
amount of memory from the operating system. To set a fi xed amount of memory from code,
set the minimum- and maximum-memory properties to the same value. The following code
sets the SQL Server memory to a fi xed memory of 6144MB.EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'min server memory', 6144;
RECONFIGURE;
EXEC sp_configure 'max server memory', 6144;
RECONFIGURE;Although calculating memory cost, polling the environment, and requesting memory may seem
as if they would require overhead, you aren’t likely to see any performance gains from switch-
ing from dynamic to fi xed memory. The primary purpose of using fi xed memory is to confi gure
a dedicated SQL Server computer to use a fi xed amount of memory after the value is reached.Minimum Query Memory
At times, the SQL Server team amazes me with the level of detailed control it passes to
DBAs. SQL Server can allocate the required memory for each query as needed. The
min memory per query option sets the minimum threshold for the memory (KB) used
by each query. Although increasing this property to a value higher than the default 1MB
may provide slightly better performance for some queries, there is no reason to override SQL
Server automatic memory control and risk causing a memory shortage. If you insist on doing
so, here’s how to do it. The following code increases the minimum query memory to 2MB:EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'min memory per query', 2048;
RECONFIGURE;Query Wait
If the memory is unavailable to execute a large query, SQL Server waits for the estimated
amount of time necessary to execute the query times 25 and then times out.Usually you do not need to change the query wait option, but if you have a valid reason
to change this option, you can either use Management Studio or T-SQL-code. In Management
Studio, you can set the query wait option by entering the value in the query wait box on
the Server Properties Advanced tab (see Figure 19-9 later in this chapter).The following code specifi es that every query either starts executing within 20 seconds or
times out.EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'query wait', 20;
RECONFIGURE;c19.indd 486c19.indd 486 7/30/2012 5:42:52 PM7/30/2012 5:42:52 PM
http://www.it-ebooks.info