SQL Server 2012 DMV’s (Dynamic Management Views)

Notes about DMV’s in General

DMV data is discarded when the SQL Server service is restarted as it is stored in memory and has virtually no impact on the server when querying it because it has been collected already. SQL Server Deep Dives Vol 1, pg 383

SQL Server DMV/DMFs that every DBA should know

SQL Server 2012 Dynamic Management Views and Functions

Execution Related
sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_exec_cached_plans
sys.dm_exec_query_plans
sys.dm_exec_sql_text
sys.dm_exec_query_stats

Index Related
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats

SQL Server Operating System
sys.dm_os_performance_counters
sys.dm_os_schedulers
sys.dm_os_nodes
sys.dm_os_waiting_tasks
sys.dm_os_wait_stats<
Here is the list of 201 different wait states for this DMV: sys.dm_os_wait_stats

I/O Related
sys.dm_io_virtual_file_stats

DMV’s to Assist in Analyzing Blocking

and

Query utilizing DMVs and CROSS APPLY statements identifying the slowest queries

Finding missing Indexes on tables

Displaying Index Fragmentation on a specific table

The Top 3 Plan Caches

* CACHESTORE_OBJCP — Compiled plans for objects such as stored procedures, functions, and triggers

* CACHESTORE_SQLCP — Cached plans for SQL statements or batches that aren’t in stored procedures. If your application doesn’t use stored procedures, then the plans are cached here. However, they are much less likely to be reused than stored procedure plans, which can lead to a bloated cache taking a lot of memory (see the “Optimize for Ad-Hoc Workloads” section later in the chapter).

* CACHESTORE_PHDR — Algebrizer trees for views, constraints, and defaults. An algebrizer tree is the parsed SQL text that resolves table and column names.

The SQL to view these caches is:

Displaying Cached Execution Plans

CachedPlans

Display the size of a plan cache per database

PlanCachePerDatabase

Views – 965

Leave a Reply