Udostępnij za pośrednictwem

APS / PDW DMVs Explained

  • Artykuł

In this post we are going to discuss different types of DMV's in PDW and where the data comes from.

 

The concept of DMV's is not new and is taken straight out of SMP SQL Server.  In the appliance though, you don't need information from just a single instance.  You need it from multiple instances and servers aggregated in one place as well as an all-up appliance view.  PDW provides both by either aggregating the DMVs from the CMP nodes a single pass-through DMV, or the appliance level view with new PDW specific DMV's.

 

There are two types of DMV's in PDW.  Passthrough DMV's and PDW specific DMV's.  The difference is where the data for the DMV is coming form.  For a passthrough DMV, PDW simply collects the output of the DMV from each CMP node instance and makes the data available through a single PDW DMV.  These DMV's are identified by having the prefix 'sys.dm_pdw_nodes_<DMV_Name>' or 'sys.pdw_nodes_<DMV_Name>'.  Any DMV which includes this in the name is simpy a collection of the SMP exquilivant on all of the CMP nodes.  An additional column is added to the end of the DMV titled 'pdw_node_id' to indicate which node that specific entry came from. 

The second type of DMV's are specific to PDW.  These may or may not have SQL server equivalents in SMP.  These DMV's will have the prefix 'sys.pdw_,<DMV Name>' or sys.dm_pdw_<DMV name>'.  The data in these DMV's will always be at the appliance level and will not surface data form the individual instances. 

 

Examples:

sys.dm_pdw_nodes_tables vs sys.tables

sys.dm_pdw_nodes_tables : This DMV will return the results of sys.tables from each of the CMP nodes.  You will notice the table names will be the internal GUIDS that are assigned by PDW and there will be a table for each distribution on each node for a distributed or round robin table.

Compare this to sys.tables which show the tables that exist at the appliance level and will the user friendly table name.

sys.dm_pdw_exec_requests vs sys.dm_pdw_nodes_exec_requests

sys.dm_pdw_exec_requests is a PDW specific DMV.  It shows the last 10K statements executed against the PDW appliance.  While it is similar to sys.dm_exec_requests in SMP - it displays different information and keeps a history of 10K statements

Compare this to sys.dm_pdw_nodes_exec_requests which will show the SMP dmv sys.dm_exec_requests from each node with the pdw_node_id column appended to each entry to indicate which node that entry came from

 

Not every SMP DMV will be available as a pass-through DMV and not every pass-through DMV has a PDW equivalent.  To get the most out of these DMV's you will need to join a combination of pass-through and PDW level to get a good view of what is happening across the appliance.  Examples of these types of queries can be found in the help file distributed through the Microsoft download center.  Check out the section "Common Metadata Query Examples"

Comments

  • Anonymous
    November 11, 2015
    How can I increase the limit of storing last 10K statements ??

  • Anonymous
    December 18, 2015
    This limit is currently not configurable.  The Microsoft Premier Field Engineering team has developed a process that can archive the DMV data.  You can discuss this with your TAM or assigned PFE for APS/PDW.