Database Reference
In-Depth Information
IO efficiency of your database. This will reduce IO bottlenecks and lower latency.
Not all virtual disks will issue enough IOs to fill all of the available queue slots all of
the time. This is why the adapter queue depths are lower than the aggregate total number
of queues per device multiplied by the total number of devices per adapter. PVSCSI, for
example, has 15 virtual disks, and each disk has a queue depth of 64 by default. The
number of devices multiplied by their queue depth would be 960, even though the
adapter default queue depth is only 256.
Tip
To determine the number of IO operations queued to a particular drive or virtual
disk at any particular time, you can use Windows Performance Monitor to track
the Average Disk Queue Length for each device. You should be recording this
parameter as part of your SQL Server baseline and capacity planning to help you
properly design the storage for your virtualized SQL Server systems.
In most cases, the default queue depths are sufficient for even very high performance
SQL Server systems—especially when you are able to add up to four vSCSI adapters
and increase the number of virtual disks per adapter. With LSI Logic SAS, you have a
maximum of 32 queue slots per disk and a maximum of 128 queue slots per adapter.
Neither can be changed. In this case, your only option to scale IO concurrency is by
adding virtual controllers and adding virtual disks. This is a key consideration when
considering AlwaysOn Failover Cluster Instances, where LSI Logic SAS is the only
vSCSI adapter option.
With PVSCSI, you can modify the disk queue depth and the adapter queue depth from
their default settings. This is only required in very rare cases where your database
needs to issue very large amounts of IO in parallel (>1,000). To keep things
standardized and simple, we recommend leaving the default settings in your templates
and only modify them if absolutely necessary. This assumes your underlying disk
subsystems can support the parallelism required at low-enough latency.
Figure 6.11
shows an example of the registry entries configured to increase the
maximum adapter and virtual disk queue depths for a VMware PVSCSI adapter, as
documented in VMware KB 2053145.
