SQL Server is designed for machines with hundreds of gigabytes of memory and dozens of cores, and its defaults assume it is the only thing that matters on the box. Installed on a 4 or 8 GB virtual server, those defaults make it take every byte of RAM, grow tempdb in one file on the system drive, and parallelise queries that would finish faster on one core. None of this shows up as an error; it shows up as a server that pages, an application that stalls on tempdb contention and a Windows Update that fails for lack of memory. This guide gives the handful of settings that make SQL Server behave on a small server, with the figures for 4, 8, 16 and 32 GB, and ends with a sizing table that maps database size and user count to a Windows VPS, a Windows Dedicated VPS or a managed SQL Server host at MassiveGRID.

Why the defaults hurt on a small server

SQL Server allocates memory for the buffer pool (cached data pages), the plan cache, and per-query work, and it releases memory only when Windows signals pressure. The max server memory setting defaults to 2,147,483,647 MB, which is to say unlimited. On a 64 GB host that is a reasonable "take what you need" policy. On a 4 GB VPS, the engine grows into the space Windows needs for itself, the operating system starts paging, and SQL Server's own memory grants then wait on a disk that is busy paging. The server is not slow because the database is big; it is slow because two processes are fighting for the same 4 GB.

The same pattern repeats with tempdb (one file, on C:, growing 64 MB at a time), with parallelism (every query costing more than 5 units may use every core), and with file growth (each growth zero-fills the new space unless instant file initialisation is on). Each is a one-line fix, and together they are the difference between a server that feels undersized and one that is fine.

Max server memory: the table

The rule of thumb: leave Windows Server about 2 GB on a small machine, 4 GB from 16 GB upward, and leave room for anything else running on the same server (IIS, a monitoring agent, SSMS over RDP). Then set max server memory to what remains. These are starting points, not measurements; watch the Memory\Available MBytes counter after a week and adjust.

Server RAMKeep for Windows and other servicesmax server memory (MB)Notes
4 GB2 GB2048SQL Server Express only (its buffer pool is capped at 1,410 MB anyway); fine for a development copy or a very small site
8 GB2.5 to 3 GB5120 to 5632One application database of a few gigabytes; the smallest sensible production size
16 GB4 GB12288Comfortable for databases up to the tens of gigabytes, or several small ones
32 GB4 to 6 GB26624 to 28672Large working sets; also the point where the Web edition's 64 GB buffer pool limit stops being theoretical

If IIS and the application run on the same server, subtract their working set (a .NET application pool often sits at 500 MB to 2 GB) before setting the cap. Set it with:

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 5120; RECONFIGURE;
EXEC sp_configure 'min server memory (MB)', 1024; RECONFIGURE;

The change applies immediately, no restart. min server memory stops SQL Server from being squeezed too far by a memory-hungry neighbour and can stay low. Keep in mind the edition caps: Express uses at most 1,410 MB of buffer pool, Web 64 GB and Standard 128 GB, regardless of what you set; buying 128 GB of RAM for a Web edition instance only helps the operating system cache.

tempdb: files, size and growth

Every sort, hash join, temporary table, version store row and online index rebuild goes through tempdb, and a single tempdb data file becomes a contention point on allocation pages as soon as several sessions work at once. The guidance Microsoft built into the installer is the right one: one data file per logical core, up to eight, all the same initial size and the same growth increment, so the round-robin allocation stays even. A 2 vCPU server gets two files, a 4 vCPU server four, an 8 vCPU server eight; beyond eight cores add files in groups of four only if you still see PAGELATCH waits on tempdb.

Size the files so they do not have to grow during normal work. One gigabyte each is a sensible start on a small server, more if the application is known to use temporary tables heavily; set growth to a fixed 256 MB or 512 MB rather than the default percentage. To check and fix an existing instance:

-- what is there now
SELECT name, physical_name, size * 8 / 1024 AS size_mb, growth * 8 / 1024 AS growth_mb, is_percent_growth
FROM tempdb.sys.database_files;

-- resize the first file and add a second (2 vCPU server)
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 1024MB, FILEGROWTH = 256MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:\SQLTempDB\tempdev2.ndf', SIZE = 1024MB, FILEGROWTH = 256MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 512MB, FILEGROWTH = 128MB);

Moving tempdb to another volume is a MODIFY FILE with a new FILENAME followed by a service restart; the files are recreated at start-up, so nothing has to be copied. On a VPS with a single NVMe volume, keeping tempdb in its own folder is still worth it so its growth is visible in Explorer.

Data, log and the disk underneath

Separate volumes for data and log files were essential on spinning disks, because the log is written sequentially and the data files randomly, and the two patterns fought over the same heads. On NVMe the case is weaker, but separate volumes still give you separate growth limits (a runaway log cannot fill the data volume) and separate performance counters. On a hosted VM, add a second disk for the data and log if the provider lets you; otherwise separate folders on one volume are acceptable for a small instance.

Allocation unit size. Format the volumes that hold data, log and tempdb files with a 64 KB allocation unit. SQL Server reads and writes in 8 KB pages grouped into 64 KB extents, and a 64 KB cluster aligns with that; the Windows default of 4 KB works but costs some I/O efficiency. The system drive stays at 4 KB.

Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel "SQLData" -Confirm:$false
# check an existing volume
fsutil fsinfo ntfsinfo D: | findstr /i "Bytes Per Cluster"

Instant file initialisation. Without it, every data file growth and every restore zero-fills the new space before SQL Server can use it; with it, the space is claimed instantly. Grant the SQL Server service account the Perform volume maintenance tasks right (the installer has a checkbox for it; afterwards it is Local Security Policy > User Rights Assignment) and restart the service. Log files cannot use it and are zero-filled regardless, which is one more reason to size the log sensibly up front:

SELECT servicename, instant_file_initialization_enabled FROM sys.dm_server_services;

File growth. Pre-size the user database to the size you expect in a year and set fixed growth (256 MB to 1 GB for data, 128 to 512 MB for the log) instead of the 10 percent default. The log grows in virtual log files, and hundreds of tiny growths produce a fragmented log that slows recovery.

Antivirus. If Defender or another scanner runs on the server, exclude the data, log, tempdb and backup directories and the sqlservr.exe process. Scanning .mdf files on every write is a classic cause of unexplained I/O stalls on small Windows servers.

Parallelism on two to eight cores

Two settings decide when SQL Server splits a query across cores. Cost threshold for parallelism defaults to 5, a figure from the 1990s; on modern hardware queries costing 5 units finish in milliseconds on one core and only lose time coordinating threads. Raising it to 50 is the widely used starting point. Max degree of parallelism (MAXDOP) caps how many cores one query may use; Microsoft's guidance for servers with up to eight logical cores is to set it to the core count, and the 2019 and later installer proposes exactly that. On a 2 vCPU server, MAXDOP 2 with a cost threshold of 50 means that only genuinely expensive queries parallelise, and a parallel query can never starve the second core for everyone else.

EXEC sp_configure 'cost threshold for parallelism', 50; RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 2; RECONFIGURE;

On a shared-core VPS, remember that the vCPUs are a share of a physical core's time. CPU-bound queries get consistent time only on reserved cores, which is why the sizing table below moves databases with steady load to the Dedicated VPS or the managed dedicated server.

Which server size for which database

The table maps a workload to the smallest MassiveGRID server that fits it, with monthly prices in New York, London and Frankfurt on monthly billing. Database size matters less than working set and concurrency: a 50 GB archive that is rarely queried needs less memory than a 5 GB order table hit by a hundred users. These are starting points; the counters above tell you whether to move up.

WorkloadEditionServerSizeMonthly
Development copy, small internal tool, databases under 10 GB, a handful of usersExpressWindows VPS with SQL Server Express, shared cores2 vCPU, 4 GB, 64 GB$9.58 on the 180-day evaluation; $22.20 with the Windows licence
One public web application and its database, tens of gigabytes, steady trafficExpress or your own licenceWindows Dedicated VPS, reserved cores2 vCPU, 8 GB, 128 GB$28.12 on the evaluation; $40.74 with the Windows licence
Public web application with the SQL Server Web licence and the operating system managed for youWebManaged SQL Server host2 vCores, 8 GB, 128 GB$86.34, both licences included
Busy web application or API, several databases, 100 GB of dataWebManaged SQL Server host4 vCores, 16 GB, 256 GB$172.68
E-commerce or SaaS back end, heavy reporting, working set in the tens of gigabytesWebManaged SQL Server host8 vCores, 32 GB, 512 GB$345.36
Consolidated instance for several applications, 1 TB of dataWebManaged SQL Server host16 vCores, 64 GB, 1 TB$690.72
Internal line-of-business or ERP database, where Web edition is not permittedStandardSQL Server Standard hostingSized with youQuoted

The VPS figures are the configurator rates: shared cores $2.87 per vCPU, $0.80 per GB RAM and $0.01 per GB NVMe; reserved cores $5.74, $1.60 and $0.03; the Windows Server licence $6.31 per vCPU. The managed host is $33.25 per vCore with Windows Server Standard and SQL Server Web included, $2.00 per GB of RAM and $0.03 per GB of NVMe, and MassiveGRID applies the operating-system side of this guide for you: the memory cap, tempdb layout and antivirus exclusions are part of the build, and the instance settings remain yours to tune.

Frequently Asked Questions

What should max server memory be on an 8 GB SQL Server?

Around 5 to 5.5 GB (5120 to 5632 MB) if SQL Server is the only service on the machine, leaving Windows Server 2.5 to 3 GB. If IIS or another application runs on the same server, subtract its working set as well. Watch Available MBytes for a week and lower the cap if it falls under 500 MB.

How many tempdb files does a small server need?

One data file per logical core, up to eight, all the same size with the same fixed growth increment. A 2 vCPU server gets two files, a 4 vCPU server four. Start at 1 GB each on a small server and grow in fixed 256 MB steps rather than by percentage.

Does the 64 KB allocation unit size still matter on NVMe?

Less than it did on spinning disks, but it still aligns the file system cluster with SQL Server's 64 KB extents and avoids split I/O. It costs nothing when the volume is created, so format data, log and tempdb volumes with 64 KB clusters and keep the system drive at the default.

Should I set MAXDOP to 1 on a 2 vCPU server?

Usually not. Set MAXDOP to the core count (2) and raise the cost threshold for parallelism to about 50, so that only expensive queries use both cores. MAXDOP 1 is appropriate for applications whose vendor requires it, such as some ERP systems and SharePoint.

Does SQL Server perform well on shared vCPUs?

For light and bursty workloads, yes, and a shared-core Windows VPS is the cheapest place to run SQL Server Express. A database with steady CPU load or latency-sensitive queries belongs on reserved cores: the Windows Dedicated VPS if you manage the server, or the managed SQL Server host if MassiveGRID does.

Reserved cores and the licences, sized for the database

MassiveGRID SQL Server hosting puts Windows Server Standard and SQL Server Web on a Managed Cloud Dedicated Server with reserved vCores, ECC RAM and NVMe, from $86.34 a month for 2 vCores, 8 GB and 128 GB; 4 vCores and 16 GB are $172.68. The operating system build, patching, daily backups and monitoring are done for you in New York, London, Frankfurt or Singapore.

Configure a SQL Server host

Further Reading