Sample Header Ad - 728x90

getting the memory settings for all instances in a server

2 votes
2 answers
155 views
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.
Asked by Marcello Miorelli (17274 rep)
Jun 24, 2024, 11:14 AM
Last activity: Jun 25, 2024, 10:58 AM