Script to return memory information for the operating system

SELECT
  total_physical_memory_kb / 1024 "PhysicalMem(MB)",
  available_physical_memory_kb / 1024 "AvailablePhysicalMem(MB)",
  system_cache_kb /1024 "SystemCache(MB)",
  (kernel_paged_pool_kb + kernel_nonpaged_pool_kb) / 1024 "KernelPool(MB)",
  total_page_file_kb / 1024 "TotalPageFile(MB)",
  available_page_file_kb / 1024 "AvailablePageFile(MB)",
  system_memory_state_desc "MemoryState",
  system_high_memory_signal_state "SystemHighMemState",
  system_low_memory_signal_state "SystemLowMemState"
FROM
  sys.dm_os_sys_memory;

Query to Find index FileGroup location

select distinct
        idx.name as IndexName
        ,object_name(idx.id) as TableName
        ,fil.name as FileGroup
        ,dbf.physical_name as PhysicalFileName
    from sys.tables tb
         inner join sys.sysindexes idx
         on idx.id = tb.object_id
         and tb.type = 'U' and idx.name is not null
         inner join sys.filegroups fil
         on fil.data_space_id = idx.groupid
         inner join sys.database_files dbf
         on fil.data_space_id = dbf.data_space_id
         inner join sys.data_spaces dsp
         on fil.data_space_id = dsp.data_space_id