getting the memory settings for all instances in a server

Question

I have a query that shows me all the sql server instances in a server:

DECLARE @GetInstances TABLE
( Value nvarchar(100),
 InstanceNames nvarchar(100),
 Data nvarchar(100))
Insert into @GetInstances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'
Select [ServerName] = @@servername,InstanceNames from @GetInstances

and I also have a query that shows me the memory settings and usage of a particular instance:


SELECT [instance] = @@servername, p.* 
  FROM (SELECT name, [value_in_use] 
          FROM sys.configurations) t 
          PIVOT (MAX([value_in_use]) FOR name IN ([min server memory (MB)], 
                                                  [min memory per query (KB)], 
                                                  [max server memory (MB)], 
                                                  [optimize for ad hoc workloads])) p

is there a way I can have the best of both these worlds - have all info for all my sql server instances, in one query?

I would like to avoid having to create a linked server.

I would not mind using powershell or any other tool, as long as I don't need to run it as admin.

score 6 · Accepted Answer · answered Jun 24 '24 at 21:53

You can use DbaTools' excellent Powershell script library for this.

$instances = Find-DbaInstance -ScanType All -ComputerName localhost;
$instances |
  Get-DbaSpConfigure -Name "min server memory (MB)","min memory per query (KB)","max server memory (MB)","optimize for ad hoc workloads" |
  select Name,RunningValue

You can also do this using SMO directly if you have an instance name or other connection string.

$server = [Microsoft.SqlServer.Management.Smo.Server]::new("(LocalDb)\MSSQLLocalDB2019");
@(
  $server.Configuration.MinServerMemory;
  $server.Configuration.MinMemoryPerQuery;
  $server.Configuration.MaxServerMemory;
  $server.Configuration.OptimizeAdhocWorkloads
) | select DisplayName,RunValue;

Marcello Miorelli · Answer 2 · 2024-06-24T11:47:47.180

one way I have found is to add a group in the registered servers for example SERVER_T007, then inside that group add both instances - in my case:

server_t007
server_t007\sql2016

then run a query against the registered group - the 3 in the picture below.

the result is:

in case the link get lost, this is the name of the article and author, good reference as it has pictures, we like that.

T-SQL Tuesday #101. CMS – Effortlessly run queries against multiple SQL Servers at once APRIL 10, 2018 ~ MATTHEW MCGIFFEN

there are other ways as described (without neither pictures nor examples here)

getting the memory settings for all instances in a server

2 Answers2

Linked