SQL Server Sizing Consideration on Thin Provisioning

Thin provisioning is a storage technology that enables an array to provision a LUN of a certain size without having the actual physical storage allocated. The physical storage is allocated on demand as more capacity of the LUN is used. The most common deployment practice is to allocate LUNs from a pool of physical disks that are shared by any number of servers. This consolidation approach enables more efficient use of storage.

Consider the following for SQL server sizing when using thin provisioning:

• To take advantage of physical capacity being allocated on demand, 1) NTFS volumes must be formatted using the quick option, and 2) SQL Server databases must be created using instant file initialization.
• Using thin provisioning results in shared disks between any server and the application using the resources. Sizing needs to be done at an aggregate level, considering all consumers of the storage. Pay special attention to latency in these scenarios. Observing high latencies with no change in your particular application’s I/O characteristics generally indicates competition for resources with other users of the storage.
• Thin provisioning can result in much less continuity of data at the physical level because the storage is only committed as it is used. This means that regardless of host access patterns, access patterns at the physical level will be random in nature. This may be suboptimal for scan-intensive workloads.