Memory Consumption by Object
SQL Server caches object data in memory in the buffer pool. Understanding memory consumption by object can be crucial for performance. For instance, you may have a large logging table consuming 90% of the buffer pool. Moving older rows out of the logging table might allow you to reduce memory consumption if you really only care about the last 2 weeks of log records. The script below takes a snapshot of sys.dm_os_buffer_descriptors, then links it to the objects in each database on the instance. The output shows which objects are in memory, along with how much memory is being consumed by each object.
Edouard Cortès – Rue du Temple et la Place de la Republique – I bet Parisians at the time weren’t concerned with memory consumption by object!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
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 DatabaseName = d.name , obd.page_type , MB_in_memory = (COUNT_BIG(1) * 8192 / 1048576.0) 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''; SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N' UNION ALL ' END + N'SELECT au.allocation_unit_id , ObjectName = o.name , database_id = ' + CONVERT(nvarchar(10), d.database_id) + N' 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'; INSERT INTO #allocunits (allocation_unit_id, ObjectName, database_id) EXEC sys.sp_executesql @cmd; SELECT DatabaseName = d.name , ObjectName = au.ObjectName , obd.page_type , MB_in_memory = (COUNT_BIG(1) * 8192 / 1048576.0) 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 (COUNT_BIG(1)) DESC , d.name , au.ObjectName , obd.page_type; |
The script returns two result sets showing memory consumption by object. The first set contains a summary of total megabytes consumed by database and page-type. The second set shows the same details down to the object level.
Let me know if you have a question about this script, or if you notice something wrong.
Check out the rest of our posts on SQL Server Performance.
[…] Hannah Vernon has a script to help you figure out which objects are consuming the most buffer pool spac…: […]
Thanks for the script Hannah. If you have databases with different collations, the UNION ALL will fail on the ObjectName column. To fix this you can use the COLLATE statement to use the server default collation: