As of this writing, link connections are in preview. They are currently supported only with Azure SQL database and SQL server. When either of those datastores is linked with a dedicated SQL pool, the operational data from the source database is automatically replicated to the SQL pool. Once the operational data is in the Azure Synapse Analytics workspace, you can perform analytics on it and gather business insights from it.
System Dynamic Management Views
Chapter 2, “CREATE DATABASE dbName; GO,” introduced Database Console Commands (DBCC), which have database‐scoped dynamic management views (DMVs). Platform system dynamic management views (PDW) have a server scope, which, as the name implies, is focused on the health of the server the database is running on. Although PDWs and DMVs have different names and scope, their purpose is to provide useful information for tuning performance and diagnosing problems on dedicated SQL pools. There are many PDW and DMV functions, which are broken into categories such as Change Tracking, Database, Execution, I/O, and Transactions. The category most relevant in this context is Azure Synapse Analytics & Parallel Warehouse. Table 9.2 provides many of the PDW and DMV functions in this category, plus a few noteworthy functions for monitoring Azure Synapse Analytics workspace features.
TABLE 9.2 Synapse platform system dynamic management views
| Name | Description |
| sys.dm_pdw_component_health_alert | Stores a historical list of issued alerts |
| sys.dm_pdw_component_health_status | Retains the current status of component health |
| sys.dm_pdw_diag_processing_stats | Presents internal diagnostic events information |
| sys.dm_pdw_lock_waits | Has information about requests waiting for locks |
| sys.dm_pdw_network_credentials | Provides a list of networking credentials |
| sys.dm_pdw_node_status | Displays node status and performance metrics |
| sys.dm_pdw_nodes | Renders a list of nodes in the workspace |
| sys.dm_pdw_os_event_logs | Contains Windows Event logs per node |
| sys.dm_pdw_os_performance:counters | Has Windows performance counters per node |
| sys.dm_pdw_os_threads | Shows a list of and details about threads per node |
| sys.dm_pdw_resource:waits | Lists wait information for workspace resources |
| sys.dm_pdw_sys_info | Presents application‐level activity counters |
| sys.dm_pdw_wait_stats | Shows node wait states for queries and requests |
| sys.dm_pdw_waits | Lists wait states for queries, requests, and locks |
| sys.dm_tran_active_transactions | Displays SQL Server‐level transaction details |
| sys.dm_tran_current_transaction | Returns transaction‐specific details |
| sys.dm_tran_database_transactions | Displays database‐level transaction details |
| sys.dm_tran_locks | Presents the active lock manager status |
| sys.dm_pdw_errors | Provides query or request execution error details |
| sys.dm_pdw_exec_requests | Provides current and recent request information |
| sys.dm_pdw_exec_sessions | Lists current and recently open sessions |
| sys.dm_pdw_request_steps | Lists steps that make up a request or query |
| sys.dm_pdw_sql_requests | Stores query distributions as part of SQL steps |
Because they are based on internal and implementation specific data, these DMVs and functions change often. Therefore, you should not create any application dependency on them. Additionally, you must recognize that they depend directly on the software versions the product is running on, where “software versions” implies the entire stack, from the operating system to the source code library performing the operations. This means that you can expect a great variety of function availability and rendered output. To be successful in this area, you must be flexible and skilled at operational data interpretation and the configuration of DBMS management components. The following are a few examples of DMV output, beginning with sys.dm_pdw_nodes. These functions can be executed within a SQL script that targets a dedicated SQL pool within your Azure Synapse Analytics workspace. The output followed by the TSQL that generates it contains the node ID, type, name, and passive state: SELECT TOP 10 * FROM sys.dm_pdw_nodes
