Database Reference
In-Depth Information
Figure 14-7.
Output from sys.dm_exec_query_optimizer_info
Running this query before and after another query can show you the changes that have occurred in the number
and type of optimizations completed. Although, if you can isolate your queries on a test box, you can be more sure
that you get before and after differences that are directly related only to the query you're attempting to measure.
Parallel Plan Optimization
The optimizer considers various factors while evaluating the cost of processing a query using a parallel plan. Some of
these factors are as follows:
•
Number of CPUs available to SQL Server
•
SQL Server edition
•
Available memory
•
Cost threshold for parallelism
•
Type of query being executed
•
Number of rows to be processed in a given stream
•
Number of active concurrent connections
If only one CPU is available to SQL Server, then the optimizer won't consider a parallel plan. The number of CPUs
available to SQL Server can be restricted using the
affinity
setting of the SQL Server configuration. The affinity value is
set to either specific CPUs, or, to specific NUMA nodes. You can also set it to ranges. For example, to allow SQL Server
to use only CPUO to CPU3 in an eight-way box, execute these statements:
USE master;
EXEC sp_configure 'show advanced option','1';
RECONFIGURE;
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0 TO 3;
GO
