Provisioning SQL Server Sizes for the First Time. RRS feed

  • Question

  • Hi,

    I'm provisioning a SQL Server 2019 build and the whole process of setting up a SQL Server from scratch is something very new to me. I'm in a new role and while the topic is really interesting, it's a bit daunting. I wanted to pick the brains of the community to see what thoughts and opinions there are on my proposed approach for sizing. Any help, information, opinions or experiences shared are greatly appreciated. Pitfalls in particular.

    I'm focusing on the sizing at the moment but appreciate the importance of putting the actual migration plan together and producing checklists, runbooks, etc.

    I've been allocated a virtual server running Windows Server 2016. It has 4 virtual processors and I have 410Gb of drive space to work with (90Gb of the overall 500Gb is already used for C:\ and the rest is on a D:\ drive). The existing, other server where the databases are coming from is running 2008 R2. That was set up with all of the SQL Server files on a single D:\ drive, which I don't want to replicate. 

    My build has to run two versions of the same 35Gb database. One is the production environment and the second is a copy of that database recreated once a week for review, training, access by our vendor and staging data for developers who need recent versions of production data. 

    My intention is to split the 410Gb drive on the new box up as follows:

    L:\ to hold log files. Based on a rule of 25% of the size of the database = 9Gb per DB = 18Gb).

    T:\ to hold tempdb files. Based on the following article I found a Microsoft article with a formula on setting tempDB sizes here https://social.technet.microsoft.com/wiki/contents/articles/31353.sql-server-demystifying-tempdb-and-recommendations.aspx
    4 data files (one per logical processor) at 1.5Gb each with a log file of 3Gb and a further 2Gb per DB for auto growth = 11Gb.

    That would leave me with 381Gb of free space, which I would allocate equally between a D:\ drive of 190Gb for data and an E:\ drive of 190Gb for all production backups. These would be a full once a week, differential once each evening and a 30 minute log back up, all retained for 4 weeks. There's no remit to keep backups of the other environment and they are happy for it to be refreshed from the latest version of Live once per week.

    My plan would be to trigger alerts if any of the drives reached 80% of their respective capacity.

    Again, any thoughts or comments to a fairly green DBA would be much appreciated. Thanks in advance for your time.

    • Edited by Tomsky02 Saturday, July 25, 2020 5:13 PM
    Saturday, July 25, 2020 12:46 PM

All replies

  • I would recommend leaving the system as C: + D: drives, with all SQL Server Data Temp and Log on D. The old rule of a separate physical disk (mirrored) for logs meant exactly that: SEPARATE PHYSICAL DISK! Because your infrastructure team will not give you separate physical disks, there is no point having separate logical drives that share the same group storage devices.

    Also note: the old rule applied to hard disk drives to benefit from the disparate nature of hard drive sequential versus random IO characteristics. The characteristics of NAND flash is different , but having a separate physical SSD for log is still good to avoid the NAND read-after-write bath.

    In the hard disk era, the rule of separate (physical) drives for data and tempdb was a rule from an idiot who never analyzed SQL Server temporal IO patterns. (Dell did this once in a TPC-H benchmark. Compaq did it right and spanked them.) It does not matter that the daily average is X% data, 1-X% temp. Tempdb is mostly likely active during large operations. That temporal pattern will be: Read from, Write to temp, repeat, finally read from temp.

    At any given point in time, only one of data and temp will be in use. Hence if you have separate physical drive (arrays) for data and temp, then its as if you only had one of the two for performance purposes. By having data and temp on the same physical drive array, the full power of your storage is available (except for the log drive) for whatever is going on.

    However, all of that is a moot point when you have one volume split into partitions. In this case, you may as well have everything on one logical drive for best flexibility in space allocation.


    Saturday, July 25, 2020 10:46 PM