System views supported in Synapse SQL
Links to the documentation for T-SQL statements supported in Synapse SQL.
Note
Synapse serverless SQL pool supports SQL Server catalog views only.
Dedicated SQL pool and serverless SQL pool 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.resource_governor_workload_groups
- sys.workload_management_workload_classifier_details (Preview)
- sys.workload_management_workload_classifiers (Preview)
Dedicated SQL pool 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_pdw_waits
SQL Server DMVs applicable to dedicated SQL pool
The following DMVs are applicable to dedicated SQL pool, 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
SQL pool exposes many of the SQL Server dynamic management views (DMVs). These views, when queried in dedicated SQL pool, are reporting the state of SQL Databases running on the distributions.
SQL pool and Analytics Platform System's 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 | 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
The following DMVs are applicable to dedicated SQL pool, 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 Synapse SQL, and T-SQL language elements in Synapse SQL.