Consolidated DMV Series – Final Part – Day 80 – One DMV a Day

Hello Geeks and welcome to the Day 80 of the long series of One DMV a day. Finally the day has come to end this series. A journey started on 18th Sep, 2014 filled with awesome experiences of my life since last 80 days. You can read where I have been and what I did in last 80 days in various blogs here. The whole series is a great place to learn about most of the important DMV s. It has been a wonderful sprint to keep going for last 80 days with all travelling between states, countries and continents. The final post of the series is here. And I promise, this is just a pause for my blogging…

In this final post I will cover the last list of DMV scripts. Also list down all the series links so it would be a Non-Clustered Index at end on the heap of DMV posts. 😉

Extended Event related DMV s:

Xe packages: know more…

SELECT name,
        description,
        capabilities_desc
FROM sys.dm_xe_packages

Xe objects: know more… 

SELECT xp.name AS PkgName,
        xo.name AS ObjName,
        xo.description,
        xo.object_type,
        xo.type_name
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
WHERE xo.object_type = '<object_type>'

Xe object columns: know more…

SELECT xoe.name AS colName,
        xoe.description AS colDesc,
        xoe.object_name AS objName,
        xoe.type_name AS objType,
        xoe.column_type AS colType,
        xoe.column_value AS colValue
FROM sys.dm_xe_object_columns xoe
WHERE xoe.object_name LIKE '<object_name>’

Xe map values: know more… 

SELECT *
FROM sys.dm_xe_map_values
WHERE name LIKE '<XE Name>'

Xe sessions: know more… 

SELECT name,
        pending_buffers,
        total_regular_buffers,
        regular_buffer_size,
        total_large_buffers,
        total_buffer_size,
        buffer_policy_desc,
        flag_desc,
        dropped_event_count,
        dropped_buffer_count,
        largest_event_dropped_size
FROM sys.dm_xe_sessions

Xe session object columns: know more… 

SELECT xs.name,
        xsoc.column_name,
        xsoc.column_value,
        xsoc.object_type,
        xsoc.object_name
FROM sys.dm_xe_session_object_columns xsoc
INNER JOIN sys.dm_xe_sessions xs
ON xsoc.event_session_address = xs.address
WHERE xs.name = 'session_name'

Xe session targets: know more… 

SELECT xs.name,
        xst.target_name,
        xst.execution_count,
        xst.execution_duration_ms,
        xst.target_data
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
ON xst.event_session_address = xs.address

Xe session events: know more… 

SELECT xs.name,
        xse.event_name,
        xse.event_predicate
FROM sys.dm_xe_session_events xse
INNER JOIN sys.dm_xe_sessions xs
ON xse.event_session_address = xs.address
WHERE xs.name = 'session_name'

Xe session event actions: know more… 

SELECT xs.name,
        xsea.action_name,
        xsea.event_name
FROM sys.dm_xe_session_event_actions xsea
INNER JOIN sys.dm_xe_sessions xs
ON xsea.event_session_address = xs.address
WHERE xs.name = 'session_name'

 

In-Memory OLTP related DMV s:

Xtp memory consumers: know more…

SELECT xmc.memory_consumer_type_desc,
       xmc.memory_consumer_desc,
       OBJECT_NAME(xmc.object_id) AS objName,
       i.name AS indName,
       xmc.allocated_bytes,
       xmc.used_bytes,
       xmc.allocation_count
FROM sys.dm_db_xtp_memory_consumers xmc
INNER JOIN sys.indexes i
ON xmc.index_id = i.index_id
AND xmc.object_id = i.object_id

Xtp transactions: know more… 

SELECT xtp_transaction_id,
       transaction_id,
       session_id,
       begin_tsn,
       end_tsn,
       state_desc,
       result_desc
FROM sys.dm_db_xtp_transactions

Xtp transaction stats: know more… 

SELECT total_count,
       read_only_count,
       total_aborts,
       validation_failures,
       dependencies_failed,
       savepoint_create,
       savepoint_rollbacks,
       log_bytes_written,
       log_IO_count
FROM sys.dm_xtp_transaction_stats

Xtp checkpoint files: know more…

SELECT  file_type_desc,
       internal_storage_slot,
       file_size_in_bytes,
       file_size_used_in_bytes,
       inserted_row_count,
       deleted_row_count,
       drop_table_deleted_row_count,
       state_desc,
       lower_bound_tsn,
       upper_bound_tsn,
       delta_watermark_tsn
FROM sys.dm_db_xtp_checkpoint_files

Xtp checkpoint stats: know more…

SELECT log_to_process_in_bytes AS pendingLogB,
       total_log_blocks_processed AS logBlocksProcessed,
       total_log_records_processed AS logRecsProcessed,
       xtp_log_records_processed AS xtpRecsProcessed,
       total_wait_time_in_ms/1000 AS waitTime_ms,
       waits_for_io AS ioWaits,
       io_wait_time_in_ms AS ioWait_ms,
       waits_for_new_log AS newLogWaits,
       new_log_wait_time_in_ms AS lewLogWait_ms,
       log_generated_since_last_checkpoint_in_bytes AS logsFromLastChkpntB,
       time_since_last_checkpoint_in_ms AS lastChkpnt_ms
FROM sys.dm_db_xtp_checkpoint_stats

Xtp object stats: know more…

SELECT o.name,
       row_insert_attempts,
       row_update_attempts,
       row_delete_attempts,
       write_conflicts,
       unique_constraint_violations
FROM sys.dm_db_xtp_object_stats xos
INNER JOIN sys.objects o
ON xos.object_id = o.object_id
WHERE o.name = 'table_name'

Xtp hash index stats: know more…

SELECT OBJECT_NAME(object_id) AS name,
       index_id,
       total_bucket_count,
       empty_bucket_count,
       avg_chain_length,
       max_chain_length
FROM sys.dm_db_xtp_hash_index_stats

Xtp merge requests: know more…

SELECT request_state_desc,
       destination_file_id,
       lower_bound_tsn,
       upper_bound_tsn,
       collection_tsn,
       checkpoint_tsn,
       source0_file_id,
       source1_file_id
FROM sys.dm_db_xtp_merge_requests

Xtp garbage collection stats: know more…

SELECT * FROM sys.dm_xtp_gc_stats

Xtp garbage collection queue stats: know more…

SELECT *
FROM sys.dm_xtp_gc_queue_stats

Xtp garbage collection cycle stats: know more…

SELECT cycle_id,
       ticks_at_cycle_start,
       ticks_at_cycle_end,
       base_generation,
       xacts_copied_to_local,
       xacts_in_gen_0,
       xacts_in_gen_1,
       xacts_in_gen_15
FROM sys.dm_db_xtp_gc_cycle_stats

Xtp table memory stats: know more…

SELECT OBJECT_NAME(object_id) AS tblName,
       memory_allocated_for_table_kb,
       memory_used_by_table_kb,
       memory_allocated_for_indexes_kb,
       memory_used_by_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats

Xtp nonclustered index stats: know more…

SELECT OBJECT_NAME(xnis.object_id) AS tableName,
       i.name AS indName,
       delta_pages AS deltaP,
       internal_pages AS internalP,
       leaf_pages AS leafP,
       outstanding_retired_nodes,
       page_update_count AS pageUp,
       page_update_retry_count AS pageUpRe,
       page_consolidation_count AS pageCon,
       page_consolidation_retry_count AS pageConRe,
       page_split_count AS pageSpl,
       page_split_retry_count AS pageSplRe,
       key_split_count AS keySpl,
       key_split_retry_count AS keySplRe,
       page_merge_count AS pageMrg,
       page_merge_retry_count AS pageMrgRe,
       key_merge_count AS keyMrg,
       key_merge_retry_count AS keyMrgRe
FROM sys.dm_db_xtp_nonclustered_index_stats xnis
INNER JOIN sys.indexes i
ON xnis.object_id = i.object_id
       AND xnis.index_id = i.index_id

Xtp index stats: know more…

SELECT OBJECT_NAME(xis.object_id) AS tableName,
       i.name AS indName,
       scans_started,
       rows_returned
FROM sys.dm_db_xtp_index_stats xis
INNER JOIN sys.indexes i
ON i.object_id = xis.object_id
       AND i.index_id = xis.index_id

 

Here is the list of all the blog posts in this series.

Day 1 – sys.dm_db_index_physical_stats
Day 2 – sys.dm_db_index_usage_stats
Day 3 – sys.dm_db_missing_index_details
Day 4 – sys.dm_io_cluster_shared_drives
Day 5 – sys.dm_db_index_operational_stats
Day 6 – sys.dm_io_pending_io_requests
Day 7 – sys.dm_io_virtual_file_stats
Day 8 – sys.dm_db_file_space_usage
Day 9 – sys.dm_db_session_space_usage
Day 10 – sys.dm_db_task_space_usage
Day 11 – sys.dm_os_performance_counters
Day 12 – sys.dm_os_cluster_nodes
Day 13 – sys.dm_os_tasks
Day 14 – sys.dm_os_workers
Day 15 – sys.dm_os_threads
Day 16 – sys.dm_os_schedulers
Day 17 – sys.dm_os_wait_stats
Day 18 – sys.dm_os_waiting_tasks
Day 19 – sys.dm_os_process_memory
Day 20 – sys.dm_os_memory_clerks
Day 21 – sys.dm_os_memory_objects
Day 22 – sys.dm_os_memory_pools
Day 23 – sys.dm_os_memory_cache_counters
Day 24 – sys.dm_os_memory_cache_entries
Day 25 – sys.dm_os_memory_cache_hash_tables
Day 26 – sys.dm_os_memory_brokers
Day 27 – sys.dm_os_memory_cache_clock_hands
Day 28 – sys.dm_os_buffer_descriptors
Day 29 – sys.dm_exec_connections
Day 30 – sys.dm_exec_sessions
Day 31 – sys.dm_exec_requests
Day 32 – sys.dm_exec_sql_text
Day 33 – sys.dm_exec_query_plan
Day 34 – sys.dm_exec_text_query_plan
Day 35 – sys.dm_exec_procedure_stats
Day 36 – sys.dm_exec_query_stats
Day 37 – sys.dm_exec_trigger_stats
Day 38 – sys.dm_exec_cursors
Day 39 – sys.dm_exec_background_job_queue
Day 40 – sys.dm_exec_background_job_queue_stats
Day 41 – sys.dm_exec_cached_plans
Day 42 – sys.dm_exec_plan_attributes
Day 43 – sys.dm_exec_cached_plan_dependent_objects
Day 44 – sys.dm_exec_query_memory_grants
Day 45 – sys.dm_exec_resource_semaphores
Day 46 – sys.dm_exec_describe_first_result_set
Day 47 – sys.dm_tran_locks
Day 48 – sys.dm_tran_database_transactions
Day 49 – sys.dm_tran_session_transactions
Day 50 – sys.dm_tran_active_transactions
Day 51 – sys.dm_tran_current_transactions
Day 52 – sys.dm_tran_version_store
Day 53 – sys.dm_tran_top_version_generators
Day 54 – sys.dm_repl_articles
Day 55 – sys.dm_xe_packages
Day 56 – sys.dm_xe_objects
Day 57 – sys.dm_xe_object_columns
Day 58 – sys.dm_xe_map_values
Day 59 – sys.dm_xe_sessions
Day 60 – sys.dm_xe_session_object_columns
Day 61 – sys.dm_xe_session_tragets
Day 62 – sys.dm_xe_session_events
Day 63 – sys.dm_xe_session_event_actions
Day 64 – sys.dm_db_xtp_memory_consumers
Day 65 – sys.dm_db_xtp_transactions
Day 66 – sys.dm_xtp_transaction_stats
Day 67 – sys.dm_db_xtp_checkpoint_files
Day 68 – sys.dm_db_xtp_checkpoint_stats
Day 69 – sys.dm_db_xtp_object_stats
Day 70 – sys.dm_db_xtp_hash_index_stats
Day 71 – sys.dm_db_xtp_merge_requests
Day 72 – sys.dm_xtp_gc_stats
Day 73 – sys.dm_xtp_gc_queue_stats
Day 74 – sys.dm_db_xtp_gc_cycle_stats
Day 75 – sys.dm_db_xtp_table_memory_stats
Day 76 – sys.dm_db_xtp_nonclustered_index_stats
Day 77 – sys.dm_db_xtp_index_stats
Day 78 – Consolidated DMV Scripts – Part 1
Day 79 – Consolidated DMV Scripts – Part 2
Day 80 – Consolidated DMV series – Final Part

A BIG THANK YOU to the SQL Community for following the series and keep me going.

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *