| title | SQL Data Warehouse system views | Microsoft Docs |
|---|---|
| description | Links to system views content for SQL Data Warehouse. |
| services | sql-data-warehouse |
| documentationcenter | NA |
| author | kevinvngo |
| manager | jhubbard |
| editor | |
| ms.assetid | 21ec594b-d270-4202-a8cd-bb150e5ae12c |
| ms.service | sql-data-warehouse |
| ms.devlang | NA |
| ms.topic | article |
| ms.tgt_pltfrm | NA |
| ms.workload | data-services |
| ms.custom | reference |
| ms.date | 06/01/2017 |
| ms.author | kevin;barbkess |
- sys.pdw_column_distribution_properties
- sys.pdw_database_mappings
- sys.pdw_distributions
- sys.pdw_index_mappings
- sys.pdw_loader_backup_run_details
- sys.pdw_loader_backup_runs
- sys.pdw_nodes_column_store_dictionaries
- sys.pdw_nodes_column_store_row_groups
- sys.pdw_nodes_column_store_segments
- sys.pdw_nodes_columns
- sys.pdw_nodes_indexes
- sys.pdw_nodes_partitions
- sys.pdw_nodes_pdw_physical_databases
- sys.pdw_nodes_tables
- sys.pdw_table_distribution_properties
- sys.pdw_table_mappings
- sys.dm_pdw_dms_cores
- sys.dm_pdw_dms_external_work
- sys.dm_pdw_dms_workers
- sys.dm_pdw_errors
- sys.dm_pdw_exec_connections
- sys.dm_pdw_exec_requests
- sys.dm_pdw_exec_sessions
- sys.dm_pdw_hadoop_operations
- sys.dm_pdw_lock_waits
- sys.dm_pdw_nodes
- sys.dm_pdw_nodes_database_encryption_keys
- sys.dm_pdw_os_threads
- sys.dm_pdw_request_steps
- sys.dm_pdw_resource_waits
- sys.dm_pdw_sql_requests
- sys.dm_pdw_sys_info
- sys.dm_pdw_wait_stats
- sys.dm_pdw_waits
The following DMVs are applicable to SQL Data Warehouse, but must be executed by connecting to the master database.
- sys.all_columns
- sys.all_objects
- sys.all_parameters
- sys.all_sql_modules
- sys.all_views
- sys.assemblies
- sys.assembly_modules
- sys.assembly_types
- sys.certificates
- sys.check_constraints
- sys.columns
- sys.computed_columns
- sys.credentials
- sys.data_spaces
- sys.database_credentials
- sys.database_files
- sys.database_permissions
- sys.database_principals
- sys.database_role_members
- sys.databases
- sys.default_constraints
- sys.external_data_sources
- sys.external_file_formats
- sys.external_tables
- sys.filegroups
- sys.foreign_key_columns
- sys.foreign_keys
- sys.identity_columns
- sys.index_columns
- sys.indexes
- sys.key_constraints
- sys.numbered_procedures
- sys.objects
- sys.parameters
- sys.partition_functions
- sys.partition_parameters
- sys.partition_range_values
- sys.partition_schemes
- sys.partitions
- sys.procedures
- sys.schemas
- sys.securable_classes
- sys.sql_expression_dependencies
- sys.sql_modules
- sys.stats
- sys.stats_columns
- sys.symmetric_keys
- sys.synonyms
- sys.syscharsets
- sys.syscolumns
- sys.sysdatabases
- sys.syslanguages
- sys.sysobjects
- sys.sysreferences
- sys.system_columns
- sys.system_objects
- sys.system_parameters
- sys.system_sql_modules
- sys.system_views
- sys.systypes
- sys.sysusers
- sys.tables
- sys.types
- sys.views
SQL Data Warehouse exposes many of the SQL Server dynamic management views (DMVs). These views, when queried in SQL Data Warehouse, are reporting the state of SQL Databases running on the distributions.
Since SQL Data Warehouse is built on Microsoft's MPP technology, both SQL Data Warehouse and Analytics Platform System's Parallel Data Warehouse (PDW) use the same system views.
This is why each of these DMV's has a specific column called pdw_node_id. This is the the identifier for the Compute node. In PDW the Compute node is a stronger concept for the architecture. In SQL Data Warehouse, the architecture relies more heavily on the distributions.
Note
To use these view, insert ‘pdw_nodes_’ into the name, as shown in the following table.
| DMV name in SQL Data Warehouse | Link to SQL Server Transact-SQL topic on MSDN |
|---|---|
| sys.dm_pdw_nodes_db_column_store_row_group_physical_stats | sys.dm_db_column_store_row_group_physical_stats |
| sys.dm_pdw_nodes_db_column_store_row_group_operational_stats | sys.dm_db_column_store_row_group_operational_stats |
| sys.dm_pdw_nodes_db_file_space_usage | sys.dm_db_file_space_usage |
| sys.dm_pdw_nodes_db_index_usage_stats | sys.dm_db_index_usage_stats |
| sys.dm_pdw_nodes_db_partition_stats | sys.dm_db_partition_stats |
| sys.dm_pdw_nodes_db_session_space_usage | sys.dm_db_session_space_usage |
| sys.dm_pdw_nodes_db_task_space_usage | sys.dm_db_task_space_usage |
| sys.dm_pdw_nodes_exec_background_job_queue | sys.dm_exec_background_job_queue |
| sys.dm_pdw_nodes_exec_background_job_queue_stats | sys.dm_exec_background_job_queue_stats |
| sys.dm_pdw_nodes_exec_cached_plans | sys.dm_exec_cached_plans |
| sys.dm_pdw_nodes_exec_connections | sys.dm_exec_connections |
| sys.dm_pdw_nodes_exec_procedure_stats | sys.dm_exec_procedure_stats |
| sys.dm_pdw_nodes_exec_query_memory_grants | sys.dm_exec_query_memory_grants |
| sys.dm_pdw_nodes_exec_query_optimizer_info | sys.dm_exec_query_optimizer_info |
| sys.dm_pdw_nodes_exec_query_resource_semaphores | sys.dm_exec_query_resource_semaphores |
| sys.dm_pdw_nodes_exec_query_stats | sys.dm_exec_query_stats |
| sys.dm_pdw_nodes_exec_requests | sys.dm_exec_requests |
| sys.dm_pdw_nodes_exec_sessions | sys.dm_exec_sessions |
| sys.dm_pdw_nodes_io_pending_io_requests | sys.dm_io_pending_io_requests |
| sys.dm_pdw_nodes_io_virtual_file_stats | sys.dm_io_virtual_file_stats |
| sys.dm_pdw_nodes_os_buffer_descriptors | sys.dm_os_buffer_descriptors |
| sys.dm_pdw_nodes_os_child_instances | sys.dm_os_child_instances |
| sys.dm_pdw_nodes_os_cluster_nodes | sys.dm_os_cluster_nodes |
| sys.dm_pdw_nodes_os_dispatcher_pools | sys.dm_os_dispatcher_pools |
| sys.dm_pdw_nodes_os_dispatchers | Transact-SQL Documentation is not available. |
| sys.dm_pdw_nodes_os_hosts | sys.dm_os_hosts |
| sys.dm_pdw_nodes_os_latch_stats | sys.dm_os_latch_stats |
| sys.dm_pdw_nodes_os_memory_brokers | sys.dm_os_memory_brokers |
| sys.dm_pdw_nodes_os_memory_cache_clock_hands | sys.dm_os_memory_cache_clock_hands |
| sys.dm_pdw_nodes_os_memory_cache_counters | sys.dm_os_memory_cache_counters |
| sys.dm_pdw_nodes_os_memory_cache_entries | sys.dm_os_memory_cache_entries |
| sys.dm_pdw_nodes_os_memory_cache_hash_tables | sys.dm_os_memory_cache_hash_tables |
| sys.dm_pdw_nodes_os_memory_clerks | sys.dm_os_memory_clerks |
| sys.dm_pdw_nodes_os_memory_node_access_stats | Transact-SQL Documentation is not available. |
| sys.dm_pdw_nodes_os_memory_nodes | sys.dm_os_memory_nodes |
| sys.dm_pdw_nodes_os_memory_objects | sys.dm_os_memory_objects |
| sys.dm_pdw_nodes_os_memory_pools | sys.dm_os_memory_pools |
| sys.dm_pdw_nodes_os_nodes | sys.dm_os_nodes |
| sys.dm_pdw_nodes_os_performance_counters | sys.dm_os_performance_counters |
| sys.dm_pdw_nodes_os_process_memory | sys.dm_os_process_memory |
| sys.dm_pdw_nodes_os_schedulers | sys.dm_os_schedulers |
| sys.dm_pdw_nodes_os_spinlock_stats | Transact-SQL Documentation is not available. |
| sys.dm_pdw_nodes_os_sys_info | sys.dm_os_sys_info |
| sys.dm_pdw_nodes_os_sys_memory | sys.dm_os_memory_nodes |
| sys.dm_pdw_nodes_os_tasks | sys.dm_os_tasks |
| sys.dm_pdw_nodes_os_threads | sys.dm_os_threads |
| sys.dm_pdw_nodes_os_virtual_address_dump | sys.dm_os_virtual_address_dump |
| sys.dm_pdw_nodes_os_wait_stats | sys.dm_os_wait_stats |
| sys.dm_pdw_nodes_os_waiting_tasks | sys.dm_os_waiting_tasks |
| sys.dm_pdw_nodes_os_workers | sys.dm_os_workers |
| sys.dm_pdw_nodes_resource_governor_resource_pools | sys.dm_resource_governor_resource_pools |
| sys.dm_pdw_nodes_resource_governor_workload_groups | sys.dm_resource_governor_workload_groups |
| sys.dm_pdw_nodes_tran_active_snapshot_database_transactions | sys.dm_tran_active_snapshot_database_transactions |
| sys.dm_pdw_nodes_tran_active_transactions | sys.dm_tran_active_transactions |
| sys.dm_pdw_nodes_tran_commit_table | sys.dm_tran_commit_table |
| sys.dm_pdw_nodes_tran_current_snapshot | sys.dm_tran_current_snapshot |
| sys.dm_pdw_nodes_tran_current_transaction | sys.dm_tran_current_transaction |
| sys.dm_pdw_nodes_tran_database_transactions | sys.dm_tran_database_transactions |
| sys.dm_pdw_nodes_tran_locks | sys.dm_tran_locks |
| sys.dm_pdw_nodes_tran_session_transactions | sys.dm_tran_session_transactions |
| sys.dm_pdw_nodes_tran_top_version_generators | sys.dm_tran_top_version_generators |
- sys.dm_exec_compute_node_errors
- sys.dm_exec_compute_node_status
- sys.dm_exec_compute_nodes
- sys.dm_exec_distributed_request_steps
- sys.dm_exec_distributed_requests
- sys.dm_exec_distributed_sql_requests
- sys.dm_exec_dms_services
- sys.dm_exec_dms_workers
- sys.dm_exec_external_operations
- sys.dm_exec_external_work
- CHECK_CONSTRAINTS
- COLUMNS
- PARAMETERS
- ROUTINES
- SCHEMATA
- TABLES
- VIEW_COLUMN_USAGE
- VIEW_TABLE_USAGE
- VIEWS
For more reference information, see SQL Data Warehouse reference overview.