It’s not an unfamiliar scenario - your production SQL Server is running on a 16-way box with oodles of memory but you’re still not getting the throughput you’re looking for. This tip is aimed at people who have already tried to tune their workload but are still seeing I/O bottlenecks and large lazy writer waits.

If your…

• T-SQL is optimized
• transaction-control is tuned not too commit too often, but just often enough and your
• database configurations are appropriate for the current hardware configuration (especially TempDB)
• disk I/O subsystem is not grossly undersized/slow

…but you’re still seeing I/O bottlenecks then you might want to evaluate SQL Server’s soft-NUMA support.

NUMA stands for Non-Uniform Memory Access and can refer to either Hardware NUMA or Software NUMA. Hardware NUMA tries to solve the problem of CPUs working faster than the memory available by creating affinity between a group of processors and memory; each group of processors is given a set of memory to access “locally” before reverting to “foreign” memory access - foreign being the memory in another group. Each group of processors in Hardware NUMA is referred to as a NUMA Node. Software NUMA is something that NUMA-aware software (like SQL Server 2005 and soon 2008) can leverage. With Software NUMA (soft-NUMA) CPUs are grouped into nodes (Hardware NUMA can further be segmented by using soft-NUMA, but I’m not going to get into that here) that, unlike Hardware NUMA, all share the same set of memory. NOTE: Soft-NUMA in SQL Server only enables the Scheduler and Network Interface (SNI) to leverage soft-NUMA. The benefit of using soft-NUMA with SQL Server is that there is a I/O thread and a lazy writer thread for each NUMA node. So, if you’re in the situation above and you have that 16-way server, using soft-NUMA to split it into four 4-processor NUMA nodes you can create four I/O threads and four lazy writer processes. If you’ve tuned the other aspects of your application, this could be a means of increasing the performance of your system.

For a more detailed look at NUMA in SQL Server 2005, read Scale up with SQL Server 2005 / SQL Server 2008 - NUMA, Configuring SQL Server 2005 for Soft NUMA, and NUMA Scenarios on MSDN.