System views for Analytics Platform System Parallel Data Warehouse
System views for Analytic Platform System (APS) SQL Server Parallel Data Warehouse (PDW).
Parallel Data Warehouse catalog views
- 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
Parallel Data Warehouse 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
SQL Server DMVs applicable to Parallel Data Warehouse
The following DMVs are applicable to Parallel Data Warehouse, 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_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 Server DMVs available in Parallel Data Warehouse
Parallel Data Warehouse exposes many of the SQL Server dynamic management views (DMVs). These views, when queried in Parallel Data Warehouse, are reporting the state of SQL Server databases running on the distributions.
Each of these DMV's has a specific column called pdw_node_id. This is the identifier for the Compute node.
Note
To use these view, insert 'pdw_nodes_' into the name, as shown in the following table.
DMV name in Parallel Data Warehouse | Link to SQL Server T-SQL topic |
---|---|
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_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 |
SQL Server 2016 PolyBase DMVs available in Parallel Data Warehouse
- 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 language elements and T-SQL statements.
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for