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)

Rate

Share

Share

Rate