SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Test Your SQL Memory Setting with dbatools

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

One of the settings that has caused me problems at times is the max memory setting for a SQL Server. Overall, I like to have dedicated hosts for SQL Server and may not care about limiting SQL Server in way way. However, there are places where I may have multiple instances, or other applications, and I should be setting memory to some level.

Jonathan Kehayias wrote a post about calculating max memory, and that was the inspiration for Test-DbaMaxMemory. This is a great little resource that you may use rarely, but it saves some time and makes the memory check quick and easy.

It’s a simple command, with a parameter for the instance. The Format-Table is a great place to pipe this output. For one of my instances, I see:

2017-09-28 13_29_48-powershell

Not great, since I have other instances running. I wouldn’t want this to take over my workstation. My other instance is a little better configured.

2017-09-28 13_30_38-powershell

There are all sorts of filters here to use with this. The doc pages shows filtering to find instances where the setting is greater than total  memory. Maybe a better one might be looking for instances greater than (total memory –2GB), or 4GB. Or maybe you want some deviance from the recommended value.

You can run this with any list of servers as well, and get a nice report. Maybe even one that you can use to convince your boss that you need a bit more RAM for that busy instance.

As with the other dbatools, this fulfills a simple function. It’s quick, easy, and helpful. Give it a try today.

Filed under: Blog Tagged: dbatools, powershell, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...