-- Code and scripts from Chapter 2: Architecture

-- The Resource Monitor has its own spid, 
--    which you can see by querying the sys.dm_exec_requests 
--    and  sys.dm_os_workers DMVs, as shown here:

SELECT session_id,  
    CONVERT (varchar(10), t1.status) AS status,  
    CONVERT (varchar(20),  t1.command) AS command, 
    CONVERT (varchar(15),  t2.state) AS worker_state
FROM sys.dm_exec_requests AS t1 JOIN sys.dm_os_workers AS t2
ON  t2.task_address = t1.task_address
WHERE command = 'RESOURCE MONITOR';
GO


-- You can check whether a DAC is in use by running 
--   the following query. If there is an active DAC, 
--   the query will return the server process id (spid) 
--   for the DAC; otherwise, it will return no rows. 
SELECT t2.session_id
FROM sys.tcp_endpoints as t1 JOIN sys.dm_exec_sessions as t2 
   ON t1.endpoint_id = t2.endpoint_id 
WHERE t1.name='Dedicated Admin Connection';  
GO


-- You can monitor Memory Broker behavior by querying 
--   the Memory Broker ring buffer:
SELECT * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER';
GO

-- The DMV called sys.dm_os_memory_clerks has a 
--   column called multi_pages_kb that shows how much 
--   space is used by a memory component outside 
--   of the buffer pool: 
SELECT type as Type_of_Clerk, sum(multi_pages_kb) AS Total_Pages
FROM sys.dm_os_memory_clerks 
WHERE multi_pages_kb != 0 
GROUP BY type;
GO