Votes - 0, Average rating: 0
(
)
)
|
Other manuals for this model:
manual abstract
– Average Log Write Size of >30K can indicate a bottleneck. This may indicate the cache is
disabled, or more log disks or larger cache size are required.
Spread all tables over multiple files and over multiple disks (the more spindles the better).
Do not software stripe LUNs that are already hardware striped. While this makes them easier to
manage, it degrades performance considerably.
Drive considerations:
• If database drive latency > 20ms, more spindles are indicated.
• Keep IOPS to < 120/sec/spindle for 15KRPM drives, 100/sec/spindle for 10KRPM drives.
• For a Gigabit NIC, keep packets below 30K/sec.
Use sp_configure to set lightweight pooling to 1
– When you set lightweight pooling option to 1, SQL Server switches to fiber mode scheduling. In
the event of excessive context switching, lightweight pooling provides better throughput by
performing context switching inline, thus reducing user/kernel mode context transitions.
– By default, SQL Server uses one thread per active SPID or user process. These threads work in a
pooled configuration to keep the number of threads manageable. The advanced configuration
option lightweight pooling (which is sometimes referred to as Fiber mode) uses Windows "fiber"
support to handle several execution contexts with a single thread.
•
Lightweight pooling Restrictions
– SQL Server Agent (Microsoft KB303287): When SQL Server is run in lightweight pooling mode
(fiber mode) and the DTC service is started, unexpected behavior may occur. SQL Server Agent
might not execute any jobs.
– DTC (Microsoft KB303287): If DTC operations are required on the server, the SQL Server
instance should always run in thread mode; in other words, lightweight pooling set to zero(0).
Microsoft strongly recommends that you run the SQL Server instance in thread mode when DTC
is needed. If you use fiber mode on a system that does not have a specific need, it can often
degrade performance.
– sp_xml_* and sp_OA* functionality (Microsoft KB322884): Microsoft does not support the use of
Microsoft Common Language Runtime extended stored procedures or OLE Automation with any
libraries that you must load to run in the SQL Server memory space. CLR only uses thread-
based scheduling and does not support fiber-mode scheduling. In later versions of SQL, you
cannot load CLR by using extended stored procedures or sp_OA stored procedures.
Use NUMA support to reduce remote memory access: This can improve performance up to 60%,
especially when combined with connection affinity. In general, segregate connections and thus data
locality in a given node (e.g., by region or department, or any other logical division that makes sense
for the application). When NUMA support is enabled, SQL attempts to create a thread’s data
structures in the same NUMA node, thus reducing remote memory accesses.
In SQL2005, NUMA is enabled by default. SQL Server 2005 also provides a Soft NUMA feature,
also enabled by default, which enables more precise control as described below.
Page 9 of 13
...