Disk Space Capacity Planning for a SQL Server Database on a Cluster Enviroment

Disk space capacity planning for SQL Server Database.

I have a task to provide a disk space capacity planning for our databases to enable the network team size the disk appropriately for our SQL database. Just to make sure this is done and done right; I decided to ask for help on this forum.

Please take a look at my current database size and use both compound growth and Compound growth with compounding growth rate to help me determine what amount of space this database will be requiring in the next five years should we grow at 20% each year using the following formula.

Compound growth: This model assumes that the growth rate is based on a percentage growth rate rather than a fixed growth amount. As the size of the database increases, so will its future growth amount because each increment is a percentage of the current database size. A database of 100MB that grows 10 percent per year would be 110MB at the end of the first year but 121MB at the end of the second year. Use the following formula to calculate compound growth:
FDisk = CDisk X (1+Rate) N
In this formula, the terms are defined as follows:
FDisk = future disk space required
CDisk = current disk space used
Rate = percentage growth rate per period
N = number of periods.

Compound growth with compounding growth rate This model assumes that as the database grows, so does the growth rate. For example, suppose we expect the database to grow by 10 percent this year but we expect that the growth rate itself will also increase every year by 20 percent. That means that next year, the growth rate will be 12 percent. The first year, therefore, a 100MB database will grow by 10MB. In this case, the 10MB is called the initial increment and the 20 percent is called the incremental rate of growth. Use the following formula to calculate this model:
FDisk = CDisk + (Init X (1 – IncRate) (N+1))), (1 – IncRate)
In this formula, the terms are defined as follows:
FDisk = future disk space required
CDisk = current disk space used
N = number of periods
Init = initial increment of growth
IncRate = incremental rate of growth per period
Reference book: SQL Server 2008 Administration Instant Reference

Example of my database:

Currently allocated space 77127.00 MB in size
Available free space 11776.31 MB now
Auto growth in Megabytes 1,500 MB or 20% either one is fine
Maximum File size Unlimited

What storage is this going to be running on? If these servers are VMs (they probably should be), or if the storage is SAN based this is a somewhat pointless exercise. Modern storage allows you to grow a disk and expand partitions on the fly. You should also be able to thin provision the storage so that you can ask for say 1 TB and the OS will see all of that but you only consume the amount of storage required to store the blocks that have been written to. Just allocating storage for a system assuming assuming some sort of annual growth rate for X number of years leads to typical utilization rates of less than 40% for traditional storage that can't thin provision.

0

FavorableAuthor Commented:

Yes, these are VM on SAN storage. The issue we have been experiencing is running out of space on our sql data drive and on the backup drive. So to make sure the space we require is provision correctly is why I believe they tasked me with this.

With VM on SAN storage you should be able to grow the storage at any time. It only takes me a few minutes.

Calculating growth is pretty easy. You can see for yourself by creating a table or spreadsheet and start with your initial size, and then multiply by your growth rate, which in your case of 20% is 1.20. That is your result after one year. Take your answer and multiply again by 1.20 to get your capacity after year two. Keep repeating for as many years as you would like. Now you have a chart that shows you what you can expect to need at the end of each year.

0

What are the hacks that forever changed the security industry? To answer that question, we created an exciting new eBook that takes you on a trip through hacking history. It explores the top hacks from the 80s to 2010s, why they mattered, and how the security industry responded.

Doesn't thin provisioning of disk reduce performance some, and since much of SQL is reliant on disk, isn't thin provisioning not recommended?

Regards
David

PS Don't forget to allow for headroom. The disk system aka NTFS and similar, start performing badly when more than 80% used. So I suggest that whatever figure for growth and what you calculate you need, divide that number by 0.8.

0

FavorableAuthor Commented:

Hi David,

You confused me on your second paragraph? Please can you break it down to my level?

If you calculate that on disk x in six months total database files will be 23GB (just making up some numbers here), I'm suggesting that 23 / 0.8 = 28.75GB.

That way, when in six months your disk usage is 23GB, the disk will be at or below 80%, and still appear to be responsive.

It pays to assume that from 80% full time to respond rises very steeply and sharply - in essence leading to timeouts as the disk then takes so long to respond.

HTH
David

0

FavorableAuthor Commented:

I’m trying to use this formula to determine my growth rate per period using 10% and reserving 23% for available database space at any given time to avoid auto growth from kicking in.

FDisk = CDisk X (1+Rate) N
In this formula, the terms are defined as follows:
FDisk = future disk space required
CDisk = current disk space used
Rate = percentage growth rate per period
N = number of periods.

Cdisk = 77127 multiply by 23% available free space on the database at any given time to avoid auto growth from kicking in.

(1 + .1) x 1
1 + .1 x = 1.10
1.10 x 94866.21 = 104352.831 MB
104352.831 MB / 1024 = 102 GB
102 GB is now my future disk space for 1st period

I suggest that you round up everything and stay in GB - the estimation is approximate enough.

Your equation assumes linear growth. That is, the same customers more or less maintain the same transaction volume more or less.

Otherwise if the business is growing, then you need n to be a power instead of a multiplier or something like that.

Current DB = 76GB
Add 23% free space in database => 94GB
at end of first period => 104GB
And the disk to house 105GB of database => 131 of disk.

Note that I've rounded up a few times.

Now, what about your backups - which disk are they on? Are they compressed? Don't forget to take account of them.

Regards
David

0

FavorableAuthor Commented:

Thank you both

0

Featured Post

WatchGuard’s new T15, T35 and T55 tabletop UTMs provide the highest-performing security inspection in their class, allowing users at small offices, home offices and distributed enterprises to experience blazing-fast Internet speeds without sacrificing enterprise-grade security.