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:
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.
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 
