Sizing guidelines for storage capacity on SQL Server systems

When sizing storage capacity for an SQL Server system, you need to consider the following SQL Server database components:

  • Database files
  • Transaction log files
  • Tempdb and backup

Storage capacity

Database element

Space requirement

20,000 nodes

50,000 nodes

100,000 nodes

150,000 nodes

200,000 nodes

Database files

DB size

20 GB

50 GB

100 GB

150 GB

200 GB

Transaction log "Simple"

DB size * 0.2

4 GB

10 GB

20 GB

30 GB

40 GB

tempdb

DB size * 0.1

2 GB

5 GB

10 GB

15 GB

20 GB

Backup

DB size * 1.0

20 GB

50 GB

100 GB

150 GB

200 GB

Total

46 GB

115 GB

230 GB

345 GB

460 GB

Database volume creation

BMC recommends that you use a basic disk storage type for all volumes. Also use the default disk alignment provided by Windows 2012 or later, and use the NTFS file system with a 64-KB allocation unit for SQL database and log partitions.

For tempdb , Microsoft recommends up to a 1:1 mapping between the number of files and logical CPUs. A more reasonable approach is to have a 1:1 mapping between files and logical CPUs up to eight files. If, for example, you have a server with 4 logical CPUs that manages 20,000 nodes, it creates 4 files of 500 MB for tempdb.

Was this page helpful? Yes No Submitting... Thank you

Comments