Scaling SQL Server on Big-Iron (NUMA) Systems
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?
Joe is a freelance consultant specializing in SQL Server scalability. The common approach to database performance tuning seems to be a laundry list of techniques with no unifying theme. Try random items until something works. Over time, one might develop a feel for what is likely to work in a given situation. This is art. Science is about knowledge. This starts with reverse engineering the details of the SQL Server cost based optimizer, and then developing a quantitative model for the true cost of SQL operations, until one can predict performance characteristics in advance. Joe has also develop several free tools (www.qdpma.com
) for SQL Server performance troubleshooting.