Home > Storage > PowerVault > Guides > Dell PowerVault ME5 Series: Microsoft SQL Server Best Practices > Creating volumes
Many types of files are part of a SQL Server instance. Those types of data often have different performance requirements. For performance-sensitive applications requiring maximum performance, Dell Technologies recommends creating at least five volumes for an instance of SQL Server as shown in the following table.
File type | Number of volumes | Typical performance requirements |
User DB data | At least 1 per instance | Lower performance may be acceptable |
User DB transaction log | At least 1 per instance | High performance required |
Data root directory (includes system DBs) | 1 per instance | Lower performance may be acceptable |
Tempdb data and transaction log | 1 per instance | High performance required |
Native SQL Server backup | 1 per instance | Lower performance may be acceptable |
Memory-Optimized Filegroup (if used) | At least 1 per instance | High performance required |
When there is one group of databases that require high performance and another group that does not, consider creating a set of volumes for each group of databases. This strategy will make it easier to adjust the storage configuration in the future. It also makes it easier to distribute the I/O load evenly across both controllers. Databases that have very high performance requirements can be spread across two or more data files on separate volumes to leverage resources on both controllers.
For ultimate flexibility, create a volume for each user database file. This allows for independently optimizing the storage for each individual database. With thin provisioning, there is no space penalty for creating numerous volumes. However, a large number of volumes can be difficult to manage, especially in virtualized environments. The DBA or storage administrator must find the right balance between flexibility and manageability when determining the number of volumes to create. Virtualized SQL Server environments are a good example where placing multiple file types on a single volume can make sense. Understanding the underlying storage layout and database I/O patterns is critical to making the best decisions. For VMware environments, see the PowerVault ME5 VMware vSphere Best Practices white paper.
Use a 64KB allocation unit size when formatting volumes that will contain database files (transaction log and data) or database backups. This is a Microsoft best practice for SQL Server and provides the best performance in various SQL Server workloads tested by Dell Technologies.
PowerVault ME5 arrays support Asymmetric Logical Unit Access (ALUA), and when MPIO is configured, the default MPIO policy is Least Queue Depth. This is the recommended setting for all database volumes. This setting works best for most environments because it is easy to manage and performs very well. Use other MPIO policies with caution and remember to review custom MPIO policies when adding or removing volumes from the host.