SQL SERVER CAPACITY PLANNING

In any study of SQL capacity, you should consider collecting data, and then analyze them in order to draw conclusions. But it is not clear to know what data to collect, or how to cross these data to obtain useful conclusions. Among the issues to be studied include:

Using Processor (CPU)
Disk Space Usage
RAM Memory Usage
Performance Discs (data transfer rate)
etc.

In this article, we will see some details to consider regarding the disks. When you need to create a study of SQL Server capacity always there is something that does not hurt to know, are some generic rules in the distribution and separation of physical disks to design projects with MS SQL Database Server.

We recommend:

1) Disks for Operating System: The operating system must be housed in their own individual disks, with fault tolerance. Minimum Raid 1 (2 Disks)

2) Data Disks: The "data" should be stored in the number of discs you have determined the previous study of "capacity planning" for today, and consider vegetative growth for 1 year and 3 or 5 years. However, these physical disks must be separated from the rest and with its own fault tolerance. Moreover it must determine whether the BD is the type OLTP or OLAP type, ie if you have a BD with a similar proportion of reads / writes it is said that BD is an OLTP type. On the other hand if the BD has a marked proportion to readings (around 90% or above) is said that BD is OLAP type, ie mostly reading data, perhaps to warehousing or reporting or historical. And finally you must measure the flow of data to serve (in Mbps) and the number of input / output, or IOPS (I / Os per second). After obtaining the data, the disks for OLTP data should be organized in Raid 1 +0, each pair of mirrored disks and these expanded. And the number of arms (or pairs of arms) in raid 0, are determined by the IOps to be achieved.

So if we determine that each disk has a peak performance of 50 IOps (Typical performance of an IBM disk 15k u320), and simultaneously measure the BD has a flow rate of 200 IOps (low transactional basis), then to place the Raid 1 0 data is needed 10 discs, or 5 pairs of disks in raid 1, all pairs expanded in raid 0. In turn, if we determine that the data flow is less than 300 Mbps, with a single U320 SCSI controller card will suffice. With that amount of arms and with that card, we are ensuring that the bottleneck will never be the disk access and queuing will not occur in our BD. (minimum four disks are needed, 2 pairs mirrored and expanded, as long as you reach the minimum amount of space too)

For OLAP data, the disks should be organized simply in Raid 5. Why not use Raid 5 with OLTP type data?, Because there are additional write operations of CRC in raid 5, resulting in failure performance when the heads of the discs have much movement. Instead, as in Raid 5, the reading is free of CRC checking, does not affect reading performance and provides excellent fault tolerance up to 1 disc. Recall that we need at least three physical disks to compose a Raid 5. (* IOps considerations Mbps and can also be done)

3) Disks for the Transaction Log. SQL Server transaction log is written in a sequential manner each time a checkpoint occurs, so it is considered that should also have separate physical disks to prevent the disk head to move from where they left off. Minimum should be considered a fault tolerance of RAID 1 (2 disks). (note that this is necessary only for OLTP)

4) Disks for TEMPDB database: In SQL Server is made full use of this database, in regard to orders and temporary data. In fact all the bases occupy the same TEMPDB instance (and each instance has its own TEMPDB), so it would not hurt to also consider spindles or axes or separate physical disk arms for their exclusive use. Here you must make the same considerations of IOps, and Mbps, to determine the best Raid 1 0. (for what is assumed about 4 disks minimum, 2 mirrored pairs each and Expanded) (* apart is recommended create as many TEMPDB databases as processors have the server, this because is the CPU that performs the sorts). (note that this is only necessary for OLTP)

5) Other Luxuries with OLTP: There are other recommendations regarding separation of physical disks for OLTP, such as separating indexes in their own separate disk spindles to load data discs and maximize the performance of the index seeks. Either the use of partitioned tables in filegroups or perhaps using cheaper disks (as SATA) to store historical data. That's already a designer's criteria.


In summary, we have 2 cases:

1) If  OLTP BD = 2 Disc S.O. (raid1) + 4 discs for Data (raid 10) + 2 TrxLog disks (raid1) + 4 disks for tempdb (raid10) = minimum 12 hard disks + disks required for optimal IOps. -

2) Case if OLAP BD =  2 disks for S.O. (using raid1) + 3 Disks for Data ( using raid 5) = 5 disc

and in both cases we will add the number of discs to balance the physical space and the controller cards for optimal performance of data throughput in Mbps

Here you can download a Complete SQL Server Capacity Planning Real Case Full Explained(spanish)

buy

NEED MORE HELP WITH THIS? QUESTIONS? Then Subscribe with the support service online (Chat) or leave a donation, we will be glad attend you ( click here to go to the subscription link chat support )

I hope this article will be useful, thank you very much friends for reading, and do not forget to support me with your donation!