OS related SQL Server Dynamic Management Views
from http://www.sqlserverspecialists.com/2013/03/os-related-to-sql-server-dynamic.html
The following SQL Server Operating System related dynamic management views are for internal use only: sys.dm_os_function_symbolic_name, sys.dm_os_ring_buffers, sys.dm_os_memory_allocations, sys.dm_os_sublatches, and sys.dm_os_worker_local_storage. The remaining DMVs are user-accessible and contain a panoply of information to help you understand and manage the SQL Server Operating System (SQLOS) responsible for governing resources within the system.
Returns information about all data pages that are in the SQL Server buffer cache, excluding free, stolen, or erroneously read pages. SQL Server 2005 tracks anything put into the buffer cache using buffer descriptors. You can easily scope the results to show one or all database and one or all of the objects within the database.
WARNING: Be careful using dm_os_buffer_descriptors as it can return 200,000+ rows for just a 1.6GB address space.
Returns a row showing the state and pipe name of each user instance spawned from the parent server instance.
IMP:This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Returns a row for each node in the failover cluster instance configuration or an empty rowset if the server is not configured as in a failover cluster
Note: This view replaces the fn_virtualservernodes function, which will be deprecated in a future release.
Returns a row for each host (such as an OLE DB provider) currently registered in an instance of SQL Server and each resource used by these hosts.
The following example determines the total amount of memory committed by a hosted component.
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;
Returns information about all latch waits, organized by latch class. Latches are holds placed on very lightweight and transient system resources, such as an address in memory. This DMV is useful for troubleshooting latch contention. It does not track latch usage where the latch was granted immediately or failed immediately.
Note: sys.dm_os_latch_stats does not track latch requests that were granted immediately, or that failed without waiting.
Returns a row for each module loaded into the server address space.
Returns a row for each memory broker that is currently active. Memory brokers are used by SQL Server 2008 to track memory allocations between various SQL Server internal and external components, based on current and projected usage.
Returns the status (suspended or running) of each hand for a specific cache clock. The process used to age items out of cache is called the cache clock and each clock can have one or more processes (called a hand) to sweep the cache clean.
Remarks: The process that removes old information from the memory cache is called a memory sweep. The memory sweep is a frequent activity, but is not continuous. A clock algorithm controls the sweep of the memory cache. Each clock can control several memory sweeps, which are called hands. The memory-cache clock hand is the current location of one of the hands of a memory sweep.
Returns an overview of the cache health, including run-time imformation about cache entries allocated, the source of memory for the cache entries, and how they are used.
Returns information, such as statistics, for all entries in the various caches. This DMV is useful for linking cache entries back to their associated database objects.
Returns information about each active cache in the instance of SQL Server, such as the type of cache, the number and type of hash buckets, the length of time the hash buckets have been in use, etc.
Returns all currently active memory clerks within SQL Server. A memory clerk is the primary means for allocating memory to the various users of SQL Server.
Returns all memory objects that are currently allocated by SQL Server. Memory objects are more granular than memory clerks and are used by internal SQL Server processes and components, but not users like memory clerks. This DMV is ideal for analyzing memory use and identifying memory leaks.
To calculate the amount of memory allocated by each memory object type:
SELECT SUM (pages_in_bytes) as 'Bytes Used', type
FROM sys.dm_os_memory_objects
GROUP BY type
ORDER BY 'Bytes Used' DESC;
GO
Returns a row for each memory pool object store in the SQL Server instance. Memory pool objects are certain homogeneous, equally important, stateless types of data. This information is sometimes useful for identifying bad caching behavior.
Returns information about the currently active nodes on the instance. Nodes are created by SQL OS to mimic hardware processor locality and can be altered by SQL OS using soft-NUMA techniques.
Returns information providing a complete picture of the process memory address space in kilobytes, including things like the page_fault_count, amount of virtual address space available and committed, process physical and virtual memory, and so forth.
Returns a row per performance counter, the same counters as in Windows PerfMon, maintained by the server. To calculate a discreet value for the various per-second counters, you must sample two discrete values and then subtract the earlier cumulative per-second value from the later.
Returns one row per scheduler for schedulers mapped to an individual processor. Active_worker scheduler threads are those devoted to the regular user-controlled work of the server, such as queries and SSIS jobs, while a variety of system and hidden schedulers may be active on the system at any time. This DMV is very useful for monitoring the thread scheduler and to find runaway tasks.
Monitoring hidden and nonhidden schedulers
The following query outputs the state of workers and tasks in SQL Server across all schedulers. This query was executed on a computer system that has the following:
- Two processors (CPUs)
- Two (NUMA) nodes
- One CPU per NUMA node
- Affinity mask set to 0x03.
SELECT
scheduler_id,
cpu_id,
parent_node_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count
FROM sys.dm_os_schedulers;
Used internally by SQL Server track debug data, such as outstanding allocations, and to validate the logic in a component that assumes that a certain call was made.
Returns a miscellaneous set of useful information about the computer, such as the hyperthread ratio, the max worker count, and other resources used by and available to SQL Server.
Returns a complete picture of memory at the operating system level, including information about total and available physical memory, total and available page memory, system cache, kernel space and so forth.
Returns one row for each OS task that is active in the instance of SQL Server.
Returns a row for each SQLOS threads running under the SQL Server process.
Returns information about the region, i.e. the range of pages in the virtual address space, used by a calling process.
Returns information about waits encountered by currently executing threads. There are a limited number of reasons why a thread might be forced to wait before it can complete execution. Refer to the SQL Server Books On-Line for more information about all possible wait states. This is an excellent DMV to use to diagnose performance issues with SQL Server and also with specific queries and batches because it records any time anything within SQL Server has to wait.
Returns information about the wait queue of SQLOS tasks that are waiting on some resource, such as blocking and latch contention.
Returns a row detailing information for every worker in the system, what it is doing and what it is waiting to do.
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
Requires VIEW SERVER STATE permission on the server.
小小菜鸟一枚
浙公网安备 33010602011771号