Veeam: Manage the SQL Instance Memory Limit

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:

image

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:

imageOSQL 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

image

The file name in VSCode: Veeam – SQL Instance Memory.SQL

image

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

Leave a comment

Your email address will not be published. Required fields are marked *