/*
<documentation>
  <summary>Get memory for all objects in all databases</summary>
  <returns>Temp table #obd with memory consumption by objects</returns>
  <created>2019-08-02 by Max Vernon</created>
  <modified>2020-02-05 by Konstantin Taranov</modified>
  <version>1.2</version>
  <sourceLink>https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Memory_Consumption_By_Object.sql</sourceLink>
  <originalLink>https://www.sqlserverscience.com/performance/memory-consumption-by-object/</originalLink>
</documentation>
*/

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

IF OBJECT_ID(N'tempdb..#obd', N'U') IS NOT NULL
BEGIN
    DROP TABLE #obd;
END
CREATE TABLE #obd
(
      database_id         int          NULL
    , file_id             int          NULL
    , page_id             int          NULL
    , page_level          int          NULL
    , allocation_unit_id  bigint       NULL
    , page_type           nvarchar(60) NULL
    , row_count           int          NULL
    , free_space_in_bytes int          NULL
    , is_modified         bit          NULL
    , numa_node           int          NULL
    , read_microsec       bigint       NULL
);


INSERT INTO #obd
(
      database_id
    , file_id
    , page_id
    , page_level
    , allocation_unit_id
    , page_type
    , row_count
    , free_space_in_bytes
    , is_modified
    , numa_node
    , read_microsec
)
SELECT
      obd.database_id
    , obd.file_id
    , obd.page_id
    , obd.page_level
    , obd.allocation_unit_id
    , obd.page_type
    , obd.row_count
    , obd.free_space_in_bytes
    , obd.is_modified
    , obd.numa_node
    , obd.read_microsec
FROM sys.dm_os_buffer_descriptors obd;

SELECT d.name AS DatabaseName
    , obd.page_type
    , (COUNT(1) * 8192 / 1048576.0) AS MB_in_memory
FROM #obd obd
    INNER JOIN sys.databases d ON obd.database_id = d.database_id
WHERE d.state_desc = N'ONLINE'
GROUP BY d.name
    , obd.page_type
ORDER BY d.name
    , obd.page_type;

IF OBJECT_ID(N'tempdb..#allocunits', N'U') IS NOT NULL
BEGIN
    DROP TABLE #allocunits;
END
CREATE TABLE #allocunits
(
      allocation_unit_id bigint        NOT NULL
    , ObjectName         nvarchar(128) NOT NULL
    , database_id        smallint      NULL
);

DECLARE @cmd nvarchar(max);
SET @cmd = N'';
DECLARE @serverCollation nvarchar(200) = CAST(SERVERPROPERTY('Collation') AS nvarchar(100));

SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
UNION ALL
' END + N'SELECT au.allocation_unit_id
    , o.name COLLATE ' + @serverCollation + N' AS ObjectName' +
  N', ' + CONVERT(nvarchar(10), d.database_id) + N' AS database_id
FROM ' + QUOTENAME(d.name) + N'.sys.allocation_units au
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.partitions p ON ((au.type = 1 OR au.type = 3) AND (au.container_id = p.hobt_id))
        OR (au.type = 2 AND au.container_id = p.partition_id)
    INNER JOIN ' + QUOTENAME(d.name) + N'.sys.objects o ON p.object_id = o.object_id
WHERE o.is_ms_shipped = 0'
FROM sys.databases d
WHERE d.state_desc = N'ONLINE';

PRINT(@cmd);

INSERT INTO #allocunits (allocation_unit_id, ObjectName, database_id)
EXEC sys.sp_executesql @cmd;

SELECT d.name AS DatabaseName
    , au.ObjectName
    , obd.page_type
    , (COUNT(1) * 8192 / 1048576.0) AS MB_in_memory
FROM #obd obd
    INNER JOIN sys.databases d ON obd.database_id = d.database_id
    INNER JOIN #allocunits au  ON obd.database_id = au.database_id
                              AND obd.allocation_unit_id = au.allocation_unit_id
GROUP BY d.name
    , au.ObjectName
    , obd.page_type
ORDER BY d.name
    , MB_in_memory DESC
    , au.ObjectName
    , obd.page_type;