Tuning SQL Server Configuration - Professional SQL Server 2005 Performance Tuning - page 180

Here is a sample script to determine if NUMA is enabled on your system:
select
CASE count( DISTINCT parent_node_id)
WHEN 1 THEN 'NUMA disabled'
ELSE 'NUMA enabled'
END
from
sys.dm_os_schedulers
where parent_node_id <> 32
64-bitvs.32-bit
64-bit boxes appeared in the Windows Server market a few years ago. Now we've reached the tipping-
point where 64-bit servers will gradually replace 32-bit machines. For new projects or server upgrades,
there is really no point in getting 32-bit boxes nowadays. For one thing, 32-bit Windows OS and 64-bit
Windows OS cost the same, as does SQL Server 2005 software. More importantly, with 64-bit machines,
the virtual memory space is much, much larger than that of 32-bit system.
The 32-bit system has a maximum of 4 GB of virtual address space. That leaves 2 GB of virtual memory
space for application use, because the other half is reserved for the operating system. When you have
memory pressure, you sometimes use the /3GB , /PAE , or AWE option to tweak the system so you can
give more memory to SQL Server's data cache. This can help, but it is far from being optimal.
With a 64-bit system, the addressable virtual memory is vastly expanded. To put things into perspective,
the VAS on a 64-bit system is about 10 million times bigger than that of 32-bit. So you will definitely have
more header room to grow, expand, and scale.
ParallelProcessing
Most servers nowadays have more than one physical processor. By default, SQL Server makes the
decision on which processor to use and how many to use. As mentioned earlier, in most cases, this is a
good approach.
However, in some cases, parallel query processing can hurt performance, because of the excessive wait
involved. To verify this, you can run the following query:
select * from sys.dm_os_wait_stats order by wait_time_ms desc
Inspecting the results, if you find wait type CXPACKET within the top 10 rows, it may be a cause of
concern. You will then need to test the degree of parallelism thoroughly to see if that is the cause of your
performance problems.
You can set the max degree of parallelism at the sever level. To do this, you can use the following script:
sp_configure 'max degree of parallelism', SomeInteger
By default, the value for 'max degree of parallelism' is 0, meaning the query processor could
potentially use all available processors. If you set it to 1, then the query processor will not use parallel
processing at all. The recommended approach is to set it to the number of physical processors minus 1.
You will need to conduct some testing to find the optimal value for your application. Note that you can
bypass this setting by using query hints.