| |
This is a view in the mssqlsystemresource database. To find out the number of data pages in the buffer cache for each database, from largest to smallest consumers of the cache:
SELECT count(*)AS cached_pages,
CASE database_id
WHEN 32767 THEN ‘ResourceDb’
ELSE db_name(database_id)
END AS database
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC |
To see all of the memory consumed by hosted components:
SELECT h.type, SUM(single_pages_kb + multi_pages_kb) AS commited_memory
FROM sys.dm_os_memory_clerks AS mc
INNER JOIN sys.dm_os_hosts AS h
ON mc.memory_clerk_address = h.default_memory_clerk_address
GROUP BY h.type; |
To associate a SQL Server session ID value with a Windows thread ID that you could then track with Windows PerfMon, use a query like this:
SELECT STasks.session_id, SThreads.os_thread_id
FROM sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads
ON STasks.worker_address = SThreads.worker_address
WHERE STasks.session_id IS NOT NULL
ORDER BY STasks.session_id; |
To find if you have more currently running tasks than the maximum number of runnable tasks for the server and thus a likely CPU bottleneck:
SELECT scheduler_id,
current_tasks_count,
runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255; |
To find out if any active queries are running parallel for a given instance requires a more sophisticated query:
SELECT r.session_id,
r.request_id,
MAX(ISNULL(exec_context_id, 0)) as nbr_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
FROM sys.dm_exec_requests r
JOIN sys.dm_os_tasks t ON r.session_id = t.session_id
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE s.is_user_process = 0x1
GROUP BY r.session_id, r.request_id, r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
HAVING MAX(ISNULL(exec_context_id, 0)) > 0 |
|
|
