SQLBits 2010

Scaling SQL Server on Big-Iron (NUMA) Systems

A number of techniques have been discussed for scaling SQL Server on big-iron systems. Some apply to transaction processing, others to data warehouses. However there is very little available guidance on the impact of each for specific application characteristics. Learn which techniques are absolutely essential and which contribute a only few percent.
Scaling up SQL Server performance is easier than it used to be in the days of SQL Server 2000. This is due across board, from the server system, the Windows operating system and the SQL Server engine.  A number of techniques, such as port affinities, hyper-threading, trace flag T2301, etc have been discussed, as these are elements of the published performance benchmarking reports. However there is very little available guidance on the impact of each for specific application characteristics.  Some techniques are absolutely critical essential and others contribute a few percent. Certain tuning methods are essential in high call-volume (chatty) applications. Data warehouses are highly dependent on effective use of parallelism, not to mention a powerful storage system. In certain circumstances, parallelism may have negative impact. In others, it may be difficult to get a parallel execution plan. Finally, why is Big-Iron better than Oracle RAC-style scaling?