{"id":405,"date":"2026-06-14T16:13:28","date_gmt":"2026-06-14T16:13:28","guid":{"rendered":"https:\/\/windows-vps.org\/blog\/?p=405"},"modified":"2026-09-26T22:06:45","modified_gmt":"2026-09-26T22:06:45","slug":"sql-server-on-windows-vps-installation-and-performance-tuning","status":"publish","type":"post","link":"https:\/\/windows-vps.org\/blog\/sql-server-on-windows-vps-installation-and-performance-tuning\/","title":{"rendered":"SQL Server Configuration on a Small Windows VPS: Memory, tempdb, and File Growth"},"content":{"rendered":"\n

SQL Server on a Windows VPS is frequently installed with the “click Next through the wizard” approach, and then blamed when queries are slow months later. Most of the damage is done in the first thirty minutes: defaults that assume a dedicated physical machine, memory settings that either starve the OS or let SQL eat the whole box, and tempdb placed on the same spindle as the data files. This article is a configuration-first walkthrough for small instances.<\/p>\n\n\n\n

The machine sizing context lives on windows-vps.org<\/a>; everything below assumes a Windows VPS you already have RDP access to.<\/p>\n\n\n\n

Right-size memory before you touch anything else<\/h2>\n\n\n\n

The single most consequential setting is max server memory<\/code>. Unset, SQL Server will consume virtually all available RAM and leave Windows no headroom for IIS, the file cache, or RDP itself. On a small instance this manifests as a server that swaps under load and then times out your RDP session while queries are still running.<\/p>\n\n\n\n

-- Leave the OS at least 2 GB, and 4 GB if IIS shares the box\nEXEC sys.sp_configure N'max server memory (MB)', 6144;\nRECONFIGURE;\n\nEXEC sys.sp_configure N'min server memory (MB)', 1024;\nRECONFIGURE;\n\nSELECT name, value_in_use\nFROM   sys.configurations\nWHERE  name LIKE '%server memory%';<\/code><\/pre>\n\n\n\n
Instance RAM<\/th>Reserve for OS<\/th>Suggested max server memory<\/th><\/tr><\/thead>
8 GB<\/td>3 GB<\/td>5 GB (5120 MB)<\/td><\/tr>
16 GB<\/td>4 GB<\/td>12 GB (12288 MB)<\/td><\/tr>
32 GB<\/td>6 GB<\/td>26 GB (26624 MB)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Reserve more aggressively when IIS, an app pool, or a build agent shares the same box. Memory contention between the web tier and the database tier is the most common cause of “randomly slow” small VPS deployments.<\/p>\n\n\n\n
Separate tempdb onto its own file layout<\/h2>\n\n\n\n
tempdb contention is invisible until concurrency rises. Two changes eliminate most of it: create one tempdb data file per logical core (up to eight), and size them identically so SQL Server does not have to auto-grow one file faster than the others.<\/p>\n\n\n\n
ALTER DATABASE tempdb ADD FILE (\n    NAME = 'tempdev2', FILENAME = 'T:\\tempdb\\tempdev2.ndf',\n    SIZE = 512MB, FILEGROWTH = 512MB);\n-- repeat to match logical core count, capped at 8\n\nALTER DATABASE tempdb MODIFY FILE (\n    NAME = 'tempdev', SIZE = 512MB, FILEGROWTH = 512MB);<\/code><\/pre>\n\n\n\n
Pre-grow with SIZE<\/code> rather than relying on percentage growth. Percentage growth on a 1 GB file reallocates constantly; fixed 256 MB or 512 MB increments do not. If the VPS gives you a second volume, place tempdb there and keep the OS page file off it.<\/p>\n\n\n\n
Data and log files: separate growth behaviour<\/h2>\n\n\n\n
Data files benefit from large fixed growth. Log files are different: they are written sequentially and should be pre-sized to what a normal day produces, then grown in large increments. Never shrink either on a schedule \u2014 shrinking fragments the data file and forces it to grow again under load.<\/p>\n\n\n\n