• Personally, I have moved to the idea of putting the SQL programs and system databases (apart from tempdb and maybe msdb) on to the system drive.

    In any SQL server install for SQL 2005 or above, most of the SQL binaries will always end up on the system drive because they live in the GAC. Typically a SQL install will add 2.5 to 3 GB to the system drive and under 0.5 GB to the 'program' drive if you try to separate the components. In terms of IO load, you gain almost nothing from separating the SQL components.

    The master, model and resource databases have next to no IO load, even while SQL Server is starting, so there is little point in moving these from the default location. Some Sysadmins always want to put the system Page file on a separate drive, others leave it on the system drive. Regardless of what is done at system build time, you need to monitor IO to the system drive and start to get worried if you see any consistant IO queueing.

    Tempdb should always be placed away from the system drive, unless you expect your SQL instance to do almost no work (some SQL Express instances come into this category). Msdb can be left in the default location or moved, depending on what use you make of the SQL components that store their data in msdb - the more you hit msdb the greater is the case for moving it.

    User databases should go on their own drives, with data and log files on separate drives. Often, tempdb log (and msdb log if you move it) can share the same drive as user db logs.

    Backups should go to a drive not used by any of the above.

    This gives a minimum drive letter footprint for a SQL Server system of 5 drive letters, but it will not be the right footprint for heavy workloads. You need to estimate the IO load you will get on your DBs before you build your server, work out how many IO channels are needed to service the IO, then monitor the result. In simplistic terms (as simple as a desktop PC), an IO channel can be a single physical drive. For servers, an IO channel can be a LUN that is isolated from other LUNs. For the Cloud, an IO channel can be a LUN with a specified IO service level (eg Provisioned IOPS in the AWS environment).

    Do not be surprised if your acutal IO load is different from your estimate. You should plan to review your disk layout on a regular basis and add additional IO channels as required to meet your performance SLA.

    At my place, we are going down the path of allocating a pool of drive letters for DB data files. Small databases will be allocated to one of the disks in the pool, while larger DBs will have multiple files in a filegroup, whith a given file allocated to a given disk in the pool. We used to isolate IO for a given DB to a given drive(s), but after many years of this we reckon that just spreading the IO load over a pool of disks will achieve the same results but be easier to manage.

    Finally, your standard server build will depend on how many SQL instances a single DBA has to manage. Some people manage 100s of instances, and at this scale you have to establish standard builds for small, medium and large workloads, then assign your database to the appropriate server build. If you undersize your server in this situation, the solution is more likely to be to move the workload to the right size build than tinkering with the server you have.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara