System views for dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics
Links to the documentation for T-SQL statements supported in dedicated SQL pool (formerly SQL DW).
Dedicated SQL pool (formerly SQL DW) catalog views
- sys.pdw_column_distribution_properties
- sys.pdw_distributions
- sys.pdw_index_mappings
- sys.pdw_loader_backup_run_details
- sys.pdw_loader_backup_runs
- sys.pdw_materialized_view_column_distribution_properties (Preview)
- sys.pdw_materialized_view_distribution_properties (Preview)
- sys.pdw_materialized_view_mappings (Preview)
- 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_permanent_table_mappings
- sys.pdw_replicated_table_cache_state
- sys.pdw_table_distribution_properties
- sys.pdw_table_mappings
- sys.workload_management_workload_classifier_details
- sys.workload_management_workload_classifiers
- sys.workload_management_workload_groups
Dedicated SQL pool (formerly SQL DW) dynamic management views (DMVs)
- 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
- sys.dm_workload_management_workload_groups_stats
SQL Server DMVs applicable to dedicated SQL pool (formerly SQL DW)
The following DMVs are applicable to dedicated SQL pool (formerly SQL DW), but must be executed by connecting to the master database.
SQL Server catalog views
- 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_query_store_options
- 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.query_context_settings
- sys.query_store_plan
- sys.query_store_query
- sys.query_store_query_text
- sys.query_store_runtime_stats
- sys.query_store_runtime_stats_interval
- 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 Server DMVs available in dedicated SQL pool (formerly SQL DW)
Dedicated SQL pool (formerly SQL DW) exposes many of the SQL Server dynamic management views (DMVs). These views, when queried in dedicated SQL pool (formerly SQL DW), are reporting the state of SQL Databases running on the distributions.
Dedicated SQL pool (formerly SQL DW) and Parallel Data Warehouse (PDW) use the same system views. Each DMV has a column called pdw_node_id, which is the identifier for the Compute node.
Note
To use these views, insert 'pdw_nodes_' into the name, as shown in the following table:
| DMV name in dedicated SQL pool (formerly SQL DW) | SQL Server Transact-SQL article |
|---|---|
| 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_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 |
SQL Server 2016 PolyBase DMVs available in dedicated SQL pool (formerly SQL DW)
The following DMVs are applicable to dedicated SQL pool (formerly SQL DW), but must be executed by connecting to the master database.
- 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
SQL Server INFORMATION_SCHEMA views
- CHECK_CONSTRAINTS
- COLUMNS
- PARAMETERS
- ROUTINES
- SCHEMATA
- TABLES
- VIEW_COLUMN_USAGE
- VIEW_TABLE_USAGE
- VIEWS
Next steps
For more reference information, see T-SQL statements in dedicated SQL pool (formerly SQL DW), and T-SQL language elements in dedicated SQL pool (formerly SQL DW).