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
