January 30, 2017 at 5:05 am
I have executed below query and my tempdb came on top of the list with 3674MB usage out of total 6GB assigned to SQL Server.
can anyone help me understand what exactly tempdb does with this 3+GB RAM.
IF OBJECT_ID('TempDB..#BufferSummary') IS NOT NULL BEGIN
DROP TABLE #BufferSummary
END
IF OBJECT_ID('TempDB..#BufferPool') IS NOT NULL BEGIN
DROP TABLE #BufferPool
END
CREATE TABLE #BufferPool
(
Cached_MB Int
, Database_Name SysName
, Schema_Name SysName NULL
, Object_Name SysName NULL
, Index_ID Int NULL
, Index_Name SysName NULL
, Used_MB Int NULL
, Used_InRow_MB Int NULL
, Row_Count BigInt NULL
)
SELECT Pages = COUNT(1)
, allocation_unit_id
, database_id
INTO #BufferSummary
FROM sys.dm_os_buffer_descriptors
GROUP BY allocation_unit_id, database_id
DECLARE @DateAdded SmallDateTime
SELECT @DateAdded = GETDATE()
DECLARE @sql NVarChar(4000)
SELECT @sql = ' USE [?]
INSERT INTO #BufferPool (
Cached_MB
, Database_Name
, Schema_Name
, Object_Name
, Index_ID
, Index_Name
, Used_MB
, Used_InRow_MB
, Row_Count
)
SELECT sum(bd.Pages)/128
, DB_Name(bd.database_id)
, Schema_Name(o.schema_id)
, o.name
, p.index_id
, ix.Name
, i.Used_MB
, i.Used_InRow_MB
, i.Row_Count
FROM #BufferSummary AS bd
LEFT JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
LEFT JOIN sys.partitions p ON (au.container_id = p.hobt_id AND au.type in (1,3)) OR (au.container_id = p.partition_id and au.type = 2)
LEFT JOIN (
SELECT PS.object_id
, PS.index_id
, Used_MB = SUM(PS.used_page_count) / 128
, Used_InRow_MB = SUM(PS.in_row_used_page_count) / 128
, Used_LOB_MB = SUM(PS.lob_used_page_count) / 128
, Reserved_MB = SUM(PS.reserved_page_count) / 128
, Row_Count = SUM(row_count)
FROM sys.dm_db_partition_stats PS
GROUP BY PS.object_id
, PS.index_id
) i ON p.object_id = i.object_id AND p.index_id = i.index_id
LEFT JOIN sys.indexes ix ON i.object_id = ix.object_id AND i.index_id = ix.index_id
LEFT JOIN sys.objects o ON p.object_id = o.object_id
WHERE database_id = db_id()
GROUP BY bd.database_id
, o.schema_id
, o.name
, p.index_id
, ix.Name
, i.Used_MB
, i.Used_InRow_MB
, i.Row_Count
HAVING SUM(bd.pages) > 128
ORDER BY 1 DESC;'
EXEC sp_MSforeachdb @sql
SELECT Cached_MB
, Pct_of_Cache = CAST(Cached_MB * 100.0 / SUM(Cached_MB) OVER () as Dec(20,3))
, Pct_Index_in_Cache = CAST(Cached_MB * 100.0 / CASE Used_MB WHEN 0 THEN 0.001 ELSE Used_MB END as DEC(20,3))
, Database_Name
, Schema_Name
, Object_Name
, Index_ID
, Index_Name
, Used_MB
, Used_InRow_MB
, Row_Count
FROM #BufferPool
ORDER BY Cached_MB DESC
January 30, 2017 at 9:32 am
at a high level, this query will show you how TEMPDB is being used:
-- space usage summary
SELECT SUM(unallocated_extent_page_count) AS FreePages,
CAST(SUM(unallocated_extent_page_count)/128.0 AS decimal(9,2)) AS FreeSpaceMB,
SUM(version_store_reserved_page_count) AS VersionStorePages,
CAST(SUM(version_store_reserved_page_count)/128.0 AS decimal(9,2)) AS VersionStoreMB,
SUM(internal_object_reserved_page_count) AS InternalObjectPages,
CAST(SUM(internal_object_reserved_page_count)/128.0 AS decimal(9,2)) AS InternalObjectsMB,
SUM(user_object_reserved_page_count) AS UserObjectPages,
CAST(SUM(user_object_reserved_page_count)/128.0 AS decimal(9,2)) AS UserObjectsMB
FROM sys.dm_db_file_space_usage;
to get more detailed, you can see by session:
-- space by session
SELECT top 10 s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.status AS session_status, t1.session_id,
(t1.internal_objects_alloc_page_count + t2.task_alloc) AS alloc_pages,
(t1.internal_objects_alloc_page_count + t2.task_alloc) / 128 AS alloc_mb,
(t1.internal_objects_dealloc_page_count + t2.task_dealloc) AS dealloc_pages,
(t1.internal_objects_dealloc_page_count + t2.task_dealloc) / 128 AS dealloc_mb
FROM sys.dm_db_session_space_usage as t1
INNER JOIN
(SELECT session_id, SUM(internal_objects_alloc_page_count) AS task_alloc, SUM(internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage GROUP BY session_id) AS t2 ON t1.session_id = t2.session_id
INNER JOIN sys.dm_exec_sessions s ON t1.session_id = s.session_id
WHERE s.is_user_process = 1
ORDER BY alloc_pages DESC, dealloc_pages DESC
A very good article about TEMPDB and temporary tables is at:
https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply