Database Reference
In-Depth Information
Memory Configuration Options
As explained in the “SQL Server Memory Management” section of Chapter 2, it is strongly recommended that the
max
server memory
setting be configured to a nondefault value determined by the system configuration. These memory
configurations of SQL Server are explained in detail in the “Memory Bottleneck Analysis” and “Memory Bottleneck
Resolutions” sections of Chapter 2.
Cost Threshold for Parallelism
On systems with multiple processors, the parallel execution of queries is possible. The default value for parallelism is
5. This represents a cost estimate by the optimizer of a five-second execution on the query. In most circumstances, I've
found this value to be too low; in other words, a higher threshold for parallelism results in better performance. Testing
on your system will help you determine the appropriate value. Suggesting a value for this can be considered somewhat
dangerous, but I'm going to do it anyway. I'd begin testing with a value of 35 and see where things go from there.
Max Degree of Parallelism
When a system has multiple processors available, by default SQL Server will use all of them during parallel executions.
To better control the load on the machine, you may find it useful to limit the number of processors used by parallel
executions. Further, you may need to set the affinity so that certain processors are reserved for the operating system
and other services running alongside SQL Server. OLTP systems may receive a benefit from disabling parallelism
entirely. Try increasing the cost threshold for parallelism first because, even in OLTP systems, there are queries that
will benefit from parallel execution. You may also explore the possibility of using the Resource Governor to control
some workloads.
Optimize for Ad Hoc Workloads
If the primary calls being made to your system come in as ad hoc or dynamic SQL instead of through well-defined
stored procedures or parameterized queries, such as you might find in some of the implementation of object
relational mapping (ORM) software, then turning on the
optimize for ad hoc workloads
setting will reduce the
consumption of procedure cache because plan stubs are created for initial query calls instead of full execution plans.
This is covered in detail in Chapter 17.
Blocked Process Threshold
The
blocked process threshold
setting defines in seconds when a blocked process report is fired. When a query
runs and exceeds the threshold, the report is fired. An alert, which can be used to send an e-mail or a text message,
is also fired. Testing an individual system determines what value to set this to. You can monitor for this using events
within traces defined by SQL Profiler.
