This post contains the following queries that support development and operational tasks within a Microsoft Parallel Data Warehousing (PDW) Appliance environment.
-Create Statistics
-Query Execution
-Query Runtimes
-Database Metadata
-Appliance Health Status
-DMS Errors
-Volume Size
These queries utilize system views found in the sys database and expose information on SQL Server PDW values, objects, and settings. There are two types of views within system views: dynamic management views (DMVs) and catalog views. DMVs expose information on dynamic processes, such as the queries in progress and memory usage on each appliance node. Catalog views expose information on static elements of SQL Server PDW, such as table and column names, principals, and disk capacities.
Additionally, all SQL Server PDW views include “pdw” in the view name. DMVs also include “dm_” as the first three letters of the view name.
Create Statistics (All objects and columns)
select ‘create statistics ‘ + b.name + ‘ on dbo.’ + a.name + ‘ (‘ + b.name + ‘)’
from sys.tables a, sys.columns b
where a.object_id = b.object_id
and not exists (select null
from sys.stats_columns
where object_id in (select object_id from sys.stats_columns group by object_id having count(*)=1)
and object_id = b.object_id and column_id = b.column_id)
order by a.name, b.column_id;
Query Execution
select distinct * from
sys.dm_pdw_exec_requests per
inner join sys.dm_pdw_sql_requests psr
on per.request_id = psr.request_id
inner join sys.dm_pdw_nodes_exec_requests ser
on psr.spid = ser.session_id
and ser.pdw_node_id = psr.pdw_node_id
where per.status=’Running’ and per.session_id session_id()
Query Runtimes
select
er.request_id,
er.Submit_Time,
SUM(sr.total_elapsed_time)/60000 TotalTimeForQID_minutes , –milliseconds
Max(sr.total_elapsed_time)/60000 LongestStep_Minutes,
Operation_Type LongestStep_Type,
Max(row_count) MaxRowsinANyStep,
left(er.command,50) Cmd_Preview
from dwsys.sys.dm_pdw_exec_requests er
join dwsys.sys.dm_pdw_request_steps sr on er.request_id = sr.request_id
where
er.submit_time > ’2012-04-22′
group by er.request_id,left(er.command,50),er.Submit_Time ,Operation_Type
order by TotalTimeForQID_minutes desc
Database Metadata
SELECT
d.name,
dm.physical_name
FROM
sys.pdw_database_mappings dm
JOIN sys.databases d
ON dm.database_id = d.database_id
SELECT
distinct
name,
physical_name
FROM
sys.tables t
INNER JOIN
sys.pdw_table_mappings ptm
ON t.object_id = ptm.object_id
Appliance Health Status
select
haa.alert_instance_id,
pn.name,
hcg.group_name + ‘/’+hc.component_name +’: ‘+ha.state as component,
haa.current_value,
hcp.physical_name + ‘: ‘ + isnull(chs.property_value,”) as property,
create_time,
ha.severity as severity_description,
ha.description,
(CASE ha.severity
WHEN ‘Error’ then 2
WHEN ‘Warning’ then 1
ELSE 0
END)
AS severity
from
sys.dm_pdw_component_health_active_alerts haa
inner join sys.dm_pdw_nodes pn
on haa.pdw_node_id = pn.pdw_node_id
inner join sys.pdw_health_alerts ha
on haa.alert_id = ha.alert_id
inner join sys.pdw_health_components hc
on haa.component_id = hc.component_id
inner join sys.pdw_health_component_groups hcg
on hc.group_id = hcg.group_id
inner join sys.dm_pdw_component_health_status chs
on haa.pdw_node_id = chs.pdw_node_id
and haa.component_id = chs.component_id
and haa.component_instance_id = chs.component_instance_id
inner join sys.pdw_health_component_properties hcp
on chs.property_id = hcp.property_id
order by name, alert_instance_id, property
DMS Errors
select
pn.name,
pe.request_id,
pe.create_time,
pe.details,
per.command
from sys.dm_pdw_errors pe
left outer join sys.dm_pdw_exec_requests per
on pe.request_id = per.request_id
inner join sys.dm_pdw_nodes pn
on pe.pdw_node_id = pn.pdw_node_id
where pe.source ‘Agent’
and pe.source = ‘Dms’
and pe.type not like ‘%ProtocolFunctionStatementGetRowsErrorEvent%’
andpe.type not like ‘%LoginFailedEvent%’
and pe.type not like ‘%QueryErrorEvent%’
and pe.details not like ‘%Invalid object%’
and pe.details not like ‘%Invalid username%’
and pe.details not like ‘%Incorrect syntax%’
and pe.details not like ‘%Invalid column name%’
and pe.details not like ‘%unexpected token%’
and pe.details not like ‘TRACE FROM DMS%’
and pe.details not like ‘%Command Type:SHUFFLE%’
and pe.details not like ‘%The value violates the MaxLength limit of this column%’
and pe.details not like ‘%batch contains more than one statement%’
and pe.details not like ‘%Subquery returned more than 1 value%’
and pe.details not like ‘%Command Type:DIRECT_BULK_COPY_MOVE%’
and pe.details not like ‘%ActionCancelledException%’
and pe.details not like ‘%RightParenthesis%’
and pe.details not like ‘%Query%Plan%Step%’
and pe.details not like ‘%GROUP BY%’
and pe.details not like ‘%No rows processed in the last%’
and pe.details not like ‘%An existing connection was forcibly closed by the remote host%’
order by create_time desc
Volume Size
select name,
max (case property_name
WHEN ‘volume_name’ then property_value
else null
end) as [Volume],
sum(
CASE property_name
WHEN ‘volume_size’
THEN convert(bigint,property_value)/1024/1024
ELSE 0
END
) AS [Volume MB],
sum(
CASE property_name
WHEN ‘volume_free_space’
THEN convert(bigint,property_value)/1024/1024
ELSE 0
END
) AS [Volume Free Space MB],
(sum(
CASE property_name
WHEN ‘volume_free_space’
THEN convert(float, property_value)
ELSE 0
END
)
/
sum(
CASE property_name
WHEN ‘volume_size’
THEN convert(float, property_value)
ELSE 0
END
))*100 AS [% Free]
from sys.dm_pdw_component_health_status chs
inner join sys.pdw_health_components hc
on chs.component_id = hc.component_id
inner join sys.pdw_health_component_properties hcp
on chs.property_id = hcp.property_id
inner join sys.dm_pdw_nodes pn
on chs.pdw_node_id = pn.pdw_node_id
where
component_name = ‘Volume’
and
(
property_name like ‘volume_free_space’
or
property_name = ‘volume_size’
or
property_name = ‘volume_name’
)
group by
chs.pdw_node_id,
name,
address,
component_instance_id
order by
chs.pdw_node_id,
name,
address,
component_instance_id,
max (case property_name
WHEN ‘volume_name’ then property_value
else null
end)
