Storage Systems - SQL Server 2012 Internals and Troubleshooting - page 87

his storage. Bob notes that never again will he come up short on storage and go through the pain of
expanding his system.
Moving forward Bob always asks for 10 times his original estimate. In his next venture Bob i nds
that his database will also grow at 10GB per year over 5 years but this time Bob, having “learned
his lesson” asks for 10GB a month. Unfortunately Bob's actual storage requirement was closer to
5GB per year.
Bob has unwittingly become his own worst enemy. When Bob needs storage for his second applica-
tion there isn't any storage available because Bob is simultaneously holding on to unused storage for
his i rst application. He has underprovisioned his storage requirements for his second application
while massively overprovisioning his i rst.
Bob's example is not unique. IT shops the world over consistently overprovision storage. Imagine
the implications; over the life of the storage and server, companies purchase a signii cant amount of
excess storage that requires powering and servicing. To combat this wasteful use of space, several
storage array manufacturers now sell a technology called thin provisioning .
Thin provisioning uses the concept of just-in-time storage allocation within storage pools, whereby
many physical disk drives are amalgamated into one large pool. Appropriate RAID protection is
applied to the disk drives within the pool. Many volumes can be created from each pool. Synthetic
or virtual volumes are presented to the host servers.
When a volume is created as a thin device it allocates only a minimum amount of storage. From the
perspective of the operating system, the volume is a certain set size, but the actual data usage within
the thin pool closely matches the size of written data. As new data is written to the volume, the stor-
age array allocates more physical storage to the device. This enables the storage administrator to
directly regulate the amount of storage that is used within the storage environment.
Because over-forecasting is no longer under-utilizing space, the database administrator can focus
on easing operational complexity, versus trying to optimally forecast storage. Creating a single data
i le within a i le group and later adding i les while maintaining performance is an extremely painful
operation. If a database is built without planning for growth and instead is concatenated over time
by adding i les, then data access is not uniform.
One possible growth-planning solution is to create several data i les on the same volume. If that volume
becomes full, the original data i les can be moved. This data i le movement will require downtime, but
it is preferable to reloading the entire dataset. When utilizing storage pools it is possible to create large
thin volumes that may never be fully utilized. This is possible because the storage systems are provi-
sioning storage only as it is needed. Many SAN array controllers also facilitate online volume growth.
Unfortunately, many DBAs subvert the concept of thin provisioning by fully allocating their database at
creation time. Most database administrators realize that growing a data i le can be a painful operation,
so they often allocate all the space they will ever need when the database is created. Unfortunately, for
our thin pool, SQL Server allocates data and log i les by writing zeros to the entire data i le.
If the Windows Server is set to use instant i le initialization, the i le will be created in a thin-
pool-friendly way. New storage will be allocated in the pool only as the data actually increases
( http://msdn.microsoft.com/en-us/library/ms175935(v=SQL.105).aspx) . The DBA can also
ensure that the i le is thin-pool-friendly by creating data and log i les that are only as large or slightly
larger than the data the i le will contain.