One of our standalone Hyper-V hosts was experiencing performance issues with the Remote Desktop Session Host that provides the company’s primary user environment.
The main application running on the session host can be a bit finicky so we ran the gamut troubleshooting within the guest OS.
The performance drags were happening throughout the day with no correlation to any scheduled tasks in-guest or on the host.
The host is no slouch as far as the hardware setup goes.
- Intel Server System R1208SPLSHOR 1U with dual hot-swap power supplies
- Intel Xeon Processor E3-1270v6
- 64GB ECC
- Intel 1GB RAID with flash cache backup
- (8) 1.2TB Intel SSD DC-S3520 series SSDs in RAID 6
The workload’s memory footprint:
- DC at 2GB vRAM
- Exchange 2016 at 12GB vRAM
- LoB on Server 2016 at 4GB vRAM
- Remote Desktop Broker/Gateway/Web at 2GB vRAM
- Remote Desktop Session Host at 20GB vRAM
- SQL 2016 for LoB at 8GB vRAM
- Physical RAM Usage: 48GB
From the looks of things, there should be no issues whatsoever.
The next step was to check and see what the host was doing.
As it turns out, the SQL instance running on the host for the Veeam setup was consuming an excessive amount of memory on the host. That in turn was causing an excessive amount of swap file usage.
Recycling the service brought its memory usage right down and all of a sudden the RD session host picked-up and started responding normally.
So, what do we do to deal with this? We draw upon our Small Business Server (SBS) experiences! 😉
Note that in this case we default to using the built-in OSQL commands to manage memory so there’s no need to download and install SQL Studio Management Express for this one task.
Memory Management Steps
The first thing to do is to discover the instance name. This can be done via command line or in Services.MSC:
Veeam SQL Instance: VEEAMSQL2016
With that in hand we open an elevated command prompt (CMD) and run the following commands making sure to set the correct host name:
osql -E -S SERVERNAME\VEEAMSQL2016
sp_configure ‘show advanced options’,1
reconfigure with override
go
sp_configure ‘max server memory’,768
reconfigure with override
go
Exit
This is what the commands look like in the CMD window:
OSQL Commands to set 768MB to Veeam SQL Instance
We chose 768MB to err on the safe side as far as memory needed. Note the memory amount that was originally set!
Our experience on SBS was similar in that the SQL instance would be more than happy to take up as much memory as it could but was very reluctant to release it after it was done.
If we experience any performance related issues within Veeam we will tweak that setting upwards by 256MB chunks to get things to settle down.
Command Set in VSCode
The file name in VSCode: Veeam – SQL Instance Memory.SQL
Going forward we will be running this step on all Veeam Backup & Replication servers we deploy with the memory setting tuned to the specific instance needs over time.
Philip Elder
Microsoft High Availability MVP
MPECS Inc.
www.s2d.rocks !
Our Web Site
Our Cloud Service
One thought on “Veeam: Manage the SQL Instance Memory Limit”
COOL!