Optimizing Distributed Transactions

My company is replacing our current single production SQL Server with two production servers. Our databases will be spread across the two servers, and we'll constantly have distributed queries accessing tables between the servers. Will SQL Server optimize distributed queries better if the two new production servers share a disk array or if each server accesses its own dedicated disk array?

SQL Server is virtualized byWindows, so it isn't really aware of the hardware that supports it. The SQL Server Buffer Manager monitors I/O rates so that it can toggle its data pre-fetch appropriately to avoid flooding the hardware.

Once you move to two SQL Server instances and are executing transactions between them, you'll need to enable the distributed transaction coordinator (DTC). On Windows Server 2003, you need to enable the DTC to have network access.Distributed transactions provide a two-phase commit capability—meaning that the data is either committed or rolled back at both servers— which prevents data inconsistencies.Note that transactions can include READ operations.

I strongly recommend that you keep the number of distributed transactions to a minimum and preferably execute them in batch, possibly by using replication to copy data between the systems. DTC protocols are necessarily complex and can considerably add to the CPU requirements and time needed to process a transaction. Just the communication between the machines adds a huge amount of processing.

I also suggest that you review the SQL Server Books Online (BOL) topics related to updateable partitioned views and distributed partitioned views to learn how query optimizer uses remote statistics from the OLE DB provider to optimize performance of distributed queries. If you have a large amount of distributed processing, you should expect less than scalar performance in terms of CPU usage, transaction length, and throughput. For example, 2x machine power is unlikely; plan on 1.6x machine power. You'll also need to manage availability carefully; mean time to failure (MTTF) is faster on two machines.

Patrick Conlan
Platform Program Manager
Microsoft Project Team