Database Dynamic management view DMV (1)

-- Optimize the query plan time
Select * From SYS. dm_exec_query_optimizer_info where [Counter] = 'optimizations 'or [Counter] = 'elapsed time'
-- Query status
Select * From SYS. dm_exec_query_stats
-- Wait for the task
Select * From SYS. dm_ OS _waiting_tasks

Select count (*) as cpu_waitor, t2.scheduler _ id
From SYS. dm_ OS _workers as T1
Join SYS. dm_ OS _schedulers as T2
On t1.scheduler _ address = t2.scheduler _ address
Where T1. [State] = 'runnable' and t2.scheduler _ id <255
Group by t2.scheduler _ id

-- Count the total waiting time of all wait types
Select sum (wait_time_ms) as counters, wait_type from SYS. dm_ OS _wait_stats
Group by wait_type
Order by counters DESC
-- Display memory usage
DBCC memorystatus

Select * From SYS. dm_ OS _memory_cache_counters
-- Query buffer cache usage by category
Select [type], sum (multi_pages_kb) + sum (single_pages_kb) as cache_count from SYS. dm_ OS _memory_cache_counters
Group by [type] order by cache_count DESC
-- View memory usage of Memory nodes,
-- PS: memory processing is divided into NUMA and SMP
-- 1. NUMA: Non-uniform memory access. Each group of processors has its own memory and may have its own I/O channel,
-- In addition, each CPU can access the memory associated with other groups in the same way.
-- NUMA provides separate memory for each processor,
-- Avoid trying to solve this problem when multiple memories access the same memory and cause performance loss
-- For example, the level 2 cache in inter core I5 is 4 * KB. From this we can see that the 4 Core is because the Inter processor adopts the NUMA structure,
-- Each core is allocated a kb cache.
-- Similarly, because I3 is a dual-core, the second-level cache is 2 * kb.
-- Why do I need to allocate memory for the CPU? Because the current computer CPU performance has exceeded the memory performance.
-- The CPU often needs to wait for rough data, and improve computer performance by reducing the number of accesses to the memory, that is, setting up a high cache.
-- At the same time, the NUMA structure supports multi-processor expansion, while SMP supports low scalability, and it is difficult to expand to more than 100 multi-processors.
-- 2. SMP: Symmetric Multi-processing,
-- Each processor can access different storage blocks in parallel, but access to the same block must be sequential. However, his technology is quite mature.
-- Because the Inter processor is relatively stable, the current server basically uses the Inter processor, so the SMP structure is not much described.