Blog

Dynamic Management Views (DMV)

Feb/ 1/ 2010SQL Server, Software

By Otoniel D.

Hi everyone, last week I had to make a quick research about Dynamic Management Views (DMV) so I could create a quick technical session to give to my colleagues; a quick overview about this particular subject.

I really have to admit that I enjoyed it a lot. Mainly because I believe that DMVs are one of the greatest features that Microsoft SQL Server have.

But now that I’m writing this post, I found out that DMV’s are actually categorized. This means basically two things. First, that the sys schema is so intuitive that it allows you to use it easily and second that I need to look at the MSDN SQL Server 2008 Books Online more closely. J

Let’s take a look about how do I find the DMVs inside the SQL Server.

Please go to your SSMS and run

USE [Master]

GO

Select*FromsysobjectsWhere name LIKE'dm_%';

As you can see the results give you a list that looks like this:

Dynamic Management Views
 

In the MSDN SQL Server 2008 Books Online divides the DMVs into the following categories.

1.       Change Data Capture Related Dynamic Management Views

a.        sys.dm_cdc_log_scan_sessions

b.       sys.dm_cdc_errors

c.        sys.dm_repl_traninfo

2.       Common Language Runtime Related Dynamic Management Views

a.        sys.dm_clr_appdomains

b.       sys.dm_clr_loaded_assemblies

c.        sys.dm_clr_properties

d.       sys.dm_clr_tasks

3.       Database Mirroring Related Dynamic Management Views

a.        sys.dm_db_mirroring_connections

b.       sys.dm_db_mirroring_auto_page_repair

4.       Database Related Dynamic Management Views

a.        sys.dm_db_file_space_usage

b.       sys.dm_db_session_space_usage

c.        sys.dm_db_partition_stats

d.       sys.dm_db_task_space_usage

e.       sys.dm_db_persisted_sku_features

5.       Execution Related Dynamic Management Views and Functions

a.        sys.dm_exec_background_job_queue

b.       sys.dm_exec_query_optimizer_info

c.        sys.dm_exec_background_job_queue

d.       sys.dm_exec_query_plan

e.       sys.dm_exec_background_job_queue_stats

f.         sys.dm_exec_query_resource_semaphores

g.        sys.dm_exec_cached_plans

h.       sys.dm_exec_query_stats

i.         sys.dm_exec_cached_plan_dependent_objects

j.         sys.dm_exec_requests

k.        sys.dm_exec_connections

l.         sys.dm_exec_sessions

m.      sys.dm_exec_cursors

n.       sys.dm_exec_sql_text

o.       sys.dm_exec_plan_attributes

p.       sys.dm_exec_text_query_plan

q.       sys.dm_exec_procedure_stats

r.        sys.dm_exec_trigger_stats

s.        sys.dm_exec_query_memory_grants

t.         sys.dm_exec_xml_handles

6.       Full-Text Search Related Dynamic Management Views

a.        sys.dm_fts_active_catalogs

b.       sys.dm_fts_fdhosts

c.        sys.dm_fts_index_keywords_by_document

d.       sys.dm_fts_index_keywords

e.       sys.dm_fts_index_population

f.         sys.dm_fts_memory_buffers

g.        sys.dm_fts_memory_pools

h.       sys.dm_fts_outstanding_batches

i.         sys.dm_fts_parser

j.         sys.dm_fts_population_ranges

7.       Index Related Dynamic Management Views and Functions

a.        sys.dm_db_index_operational_stats

b.       sys.dm_db_index_physical_stats

c.        sys.dm_db_index_usage_stats

d.       sys.dm_db_missing_index_columns

e.       sys.dm_db_missing_index_details

f.         sys.dm_db_missing_index_group_stats

g.        sys.dm_db_missing_index_groups

8.       I/O Related Dynamic Management Views and Functions

a.        sys.dm_io_backup_tapes

b.       sys.dm_io_cluster_shared_drives

c.        sys.dm_io_pending_io_requests

d.       sys.dm_io_virtual_file_stats

9.       Object Related Dynamic Management Views and Functions

a.        sys.dm_sql_referenced_entities

b.       sys.dm_sql_referencing_entities

10.    Query Notifications Related Dynamic Management Views

a.        sys.dm_qn_subscriptions

11.    Replication Related Dynamic Management Views

a.        sys.dm_repl_articles

b.       sys.dm_repl_schemas

c.        sys.dm_repl_tranhash

d.       sys.dm_repl_traninfo

12.    Resource Governor Dynamic Management Views

a.        sys.dm_resource_governor_configuration

b.       sys.dm_resource_governor_workload_groups

c.        sys.dm_resource_governor_resource_pools

13.    Service Broker Related Dynamic Management Views

a.        sys.dm_broker_activated_tasks

b.       sys.dm_broker_connections

c.        sys.dm_broker_forwarded_messages

d.       sys.dm_broker_queue_monitors

14.    SQL Server Extended Events Dynamic Management Views

a.        sys.dm_xe_map_values

b.       sys.dm_xe_session_events

c.        sys.dm_xe_object_columns

d.       sys.dm_xe_session_object_columns

e.       sys.dm_xe_objects

f.         sys.dm_xe_session_targets

g.        sys.dm_xe_packages

h.       sys.dm_xe_sessions

i.         sys.dm_xe_session_event_actions

15.    SQL Server Operating System Related Dynamic Management Views

a.        sys.dm_os_buffer_descriptors

b.       sys.dm_os_memory_pools

c.        sys.dm_os_child_instances

d.       sys.dm_os_nodes

e.       sys.dm_os_cluster_nodes

f.         sys.dm_os_performance_counters

g.        sys.dm_os_dispatcher_pools

h.       sys.dm_os_process_memory

i.         sys.dm_os_hosts

j.         sys.dm_os_schedulers

k.        sys.dm_os_latch_stats

l.         sys.dm_os_stacks

m.      sys.dm_os_loaded_modules

n.       sys.dm_os_sys_info

o.       sys.dm_os_memory_brokers

p.       sys.dm_os_sys_memory

q.       sys.dm_os_memory_cache_clock_hands

r.        sys.dm_os_tasks

s.        sys.dm_os_memory_cache_counters

t.         sys.dm_os_threads

u.       sys.dm_os_memory_cache_entries

v.        sys.dm_os_virtual_address_dump

w.      sys.dm_os_memory_cache_hash_tables

x.        sys.dm_os_wait_stats

y.        sys.dm_os_memory_clerks

z.        sys.dm_os_waiting_tasks

aa.     sys.dm_os_memory_nodes

bb.    sys.dm_os_workers

cc.     sys.dm_os_memory_objects

16.    Transaction Related Dynamic Management Views and Functions

a.        sys.dm_tran_active_snapshot_database_transactions

b.       sys.dm_tran_active_transactions

c.        sys.dm_tran_current_snapshot

d.       sys.dm_tran_current_transaction

e.       sys.dm_tran_database_transactions

f.         sys.dm_tran_locks

g.        sys.dm_tran_session_transactions

h.       sys.dm_tran_top_version_generators

i.         sys.dm_tran_transactions_snapshot

j.         sys.dm_tran_version_store

17.    Security Related Dynamic Management Views

a.        sys.dm_audit_actions

b.       sys.dm_cryptographic_provider_properties

c.        sys.dm_audit_class_type_map

d.       sys.dm_cryptographic_provider_sessions

e.       sys.dm_cryptographic_provider_algorithms

f.         sys.dm_database_encryption_keys

g.        sys.dm_cryptographic_provider_keys

h.       sys.dm_server_audit_status

 

For me,

But if you are a developer you may wonder how these so called DMVs can help me during my daily tasks.

Well, here are some small examples that might be useful to you while your headache it’s unbearable. Because you just cannot find what is going on with your data calls that are ___Place your problem here___, I hope these examples become handy to you.

-- top cpu cosumers

SelectTop 100

              cputime =sum(eqs.total_worker_time)

              , ExecutionCount =sum(eqs.execution_count)

              , StatmentsCount =count(*)

              , SqlQuery = t.text

              , PlanHandle = eqs.plan_handle

from   sys.dm_exec_query_stats eqs

cross

apply  sys.dm_exec_sql_text(sql_handle) t

group

       by     t.text,

              eqs.plan_handle

order

       by     sum(eqs.total_worker_time)desc

-- missing indexes

SELECT SchemaObject =statement

              , ColumnId = column_id

              , ColumnName = column_name

              , ColumnUsage = column_usage

              , UserSeeks = migs.user_seeks

              , UserScan = migs.user_scans

              , LastUserSeek = migs.last_user_seek

              , AverageTotalUserCost = migs.avg_total_user_cost

              , AverageUserImpact = migs.avg_user_impact

From   sys.dm_db_missing_index_details mid

cross

apply  sys.dm_db_missing_index_columns(mid.index_handle)

join   sys.dm_db_missing_index_groups mig

       on     mig.index_handle = mid.index_handle

join   sys.dm_db_missing_index_group_stats migs

       on     mig.index_group_handle=migs.group_handle

order

       by     mig.index_group_handle,

              mig.index_handle,

              column_id

--Rarely used indexes appear first 

Select ObjectName=object_name(s.object_id)

              , ObjectId = s.object_id

              , Indexname = i.name

              , IndexId = i.index_id

              , UserSeeks = user_seeks

              , UserScans = user_scans

              , UserLookups = user_lookups

              , UserUpdates = user_updates

from   sys.dm_db_index_usage_stats s

join   sys.indexes i

       on     i.object_id= s.object_id

       and    i.index_id = s.index_id

where  objectproperty(s.object_id,'IsUserTable')= 1

order

       by (user_seeks +

              user_scans +

              user_lookups +

              user_updates

       )asc

 

Well, till next time… Cheers

 

Comments

Leave a comment

 
 
 
 
CAPTCHA Image Validation