Q1.5: Trace Flags -- 5101 and 5102
5101
Normally, each engine issues and checks for its own Disk I/O on behalf
of the tasks it runs. In completely symmetric operating systems, this
behavior provides maximum I/O throughput for SQL Server. Some operating
systems are not completely symmetic in their Disk I/O routines. For
these environments, the server can be booted with the 5101 trace flag.
While tasks still request disk I/O from any engine, the actual request
to/from the OS is performed by engine 0. The performance benefit comes
from the reduced or eliminated contention on the locking mechanism
inside the OS kernel. To enable I/O affinity to engine 0, start SQL
Server with the 5101 Trace Flag.
Your errorlog will indicate the use of this option with the message:
Disk I/O affinitied to engine: 0
This trace flag only provides performance gains for servers with 3 or
more dataserver engines configured and being significantly utilized.
Use of this trace flag with fully symmetric operating systems
will degrade performance!
5102
The 5102 trace flag prevents engine 0 from running any non-affinitied
tasks. Normally, this forces engine 0 to perform Network I/O only.
Applications with heavy result set requirements (either large results
or many connections issuing short, fast requests) may benefit. This
effectively eliminates the normal latency for engine 0 to complete
running its user thread before it issues the network I/O to the
underlying network transport driver. If used in conjuction with the 5101
trace flag, engine 0 would perform all Disk I/O and Network I/O. For
environments with heavy disk and network I/O, engine 0 could easily
saturate when only the 5101 flag is in use. This flag allows engine 0 to
concentrate on I/O by not allowing it to run user tasks. To force task
affinity off engine 0, start SQL Server with the 5102 Trace Flag.
Your errorlog will indicate the use of this option with the message:
I/O only enabled for engine: 0
Warning: Not supported by Sybase. Provided here for
your enjoyment.