Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Useful information and case studies covering Data Warehousing, Data Modeling, and Business Intelligence

I started my career in IT nearly 10 years ago and have remained influenced and driven by two particular technology initiatives - Business Intelligence and Data Warehousing. Opportunities for partnership, learning, and innovation will continue to present themselves as we strive to meld people, business, and technology. I look forward to these opportunities as I am fulfilled by my membership to a scientific community that is driven by the development and execution of technology solutions that enhance the way we live and conduct business.

Parallel Data Warehouse (PDW) Tip: Useful Appliance Queries

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%’
and pe.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)


Comments

Leave a comment on the original post [saldeloera.wordpress.com, opens in a new window]

Loading comments...