title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | dev_langs | monikerRange | |
---|---|---|---|---|---|---|---|---|---|---|
sys.pdw_nodes_pdw_physical_databases (Transact-SQL) |
sys.pdw_nodes_pdw_physical_databases (Transact-SQL) |
WilliamDAssafMSFT |
wiassaf |
03/09/2017 |
sql |
data-warehouse |
reference |
|
>=aps-pdw-2016 |
[!INCLUDE pdw]
Contains a row for each physical database on a compute node. Aggregate physical database information to get detailed information about databases. To combine information, join the sys.pdw_nodes_pdw_physical_databases
to the sys.pdw_database_mappings
and sys.databases
tables.
Column Name | Data Type | Description |
---|---|---|
database_id | int | The object ID for the database. Note that this value is not same as a database_id in the sys.databases (Transact-SQL) view. |
physical_name | sysname | The physical name for the database on the Shell/Compute nodes. This value is same as a value in the physical_name column in the sys.pdw_database_mappings (Transact-SQL) view. |
pdw_node_id | int | Unique numeric id associated with the node. |
Examples: [!INCLUDEssPDW]
The following query returns the name and ID of each database in master, and the corresponding database name on each compute node.
SELECT D.database_id AS DBID_in_master, D.name AS UserDatabaseName,
PD.pdw_node_id AS NodeID, DM.physical_name AS PhysDBName
FROM sys.databases AS D
JOIN sys.pdw_database_mappings AS DM
ON D.database_id = DM.database_id
JOIN sys.pdw_nodes_pdw_physical_databases AS PD
ON DM.physical_name = PD.physical_name
ORDER BY D.database_id, PD.pdw_node_ID;
The following query shows information about indexes and includes useful information about the database the objects belong to objects in the database.
SELECT D.name AS UserDatabaseName, D.database_id AS DBIDinMaster,
DM.physical_name AS PhysDBName, PD.pdw_node_id AS NodeID,
IU.object_id, IU.index_id, IU.user_seeks, IU.user_scans, IU.user_lookups, IU.user_updates
FROM sys.databases AS D
JOIN sys.pdw_database_mappings AS DM
ON D.database_id = DM.database_id
JOIN sys.pdw_nodes_pdw_physical_databases AS PD
ON DM.physical_name = PD.physical_name
JOIN sys.dm_pdw_nodes_db_index_usage_stats AS IU
ON PD.database_id = IU.database_id
ORDER BY D.database_id, IU.object_id, IU.index_id, PD.pdw_node_ID;
The following query provides encryption state of the AdventureWorksPDW2012 database.
WITH dek_encryption_state AS
(
SELECT ISNULL(db_map.database_id, dek.database_id) AS database_id, encryption_state
FROM sys.dm_pdw_nodes_database_encryption_keys AS dek
INNER JOIN sys.pdw_nodes_pdw_physical_databases AS node_db_map
ON dek.database_id = node_db_map.database_id AND dek.pdw_node_id = node_db_map.pdw_node_id
LEFT JOIN sys.pdw_database_mappings AS db_map
ON node_db_map .physical_name = db_map.physical_name
INNER JOIN sys.dm_pdw_nodes AS nodes
ON nodes.pdw_node_id = dek.pdw_node_id
WHERE dek.encryptor_thumbprint <> 0x
)
SELECT TOP 1 encryption_state
FROM dek_encryption_state
WHERE dek_encryption_state.database_id = DB_ID('AdventureWorksPDW2012 ')
ORDER BY (CASE encryption_state WHEN 3 THEN -1 ELSE encryption_state END) DESC;
Azure Synapse Analytics and Parallel Data Warehouse Catalog Views
sys.databases (Transact-SQL)
sys.pdw_database_mappings (Transact-SQL)