Compliant Database DevOps
Standardize team-based development
Automate database deployments
Monitor performance and availability
Protect and preserve data
Contents
Introduction
The aim of this article is to describe the most important catalog views and dynamic management views that come with Azure SQL Data Warehouse (ADSW) in order to explain and illustrate its architecture. These views are designed to help understand, manage, monitor and correct the ASDW system’s behavior.
The diagrams and SQL code examples in this article are intended to give you the means of exploring and understanding what is going on in ASDW. I hope that these examples are easily adapted, and that they lead you to compose your own system queries to understand any specific issues that you might face while you are migrating, testing or working with the system in production.
This is not an exhaustive list of all the views available to the system, although they are sufficient to get you started. They are the ones I’ve found the most useful, most interesting, or that are not identical to their SQL Server equivalent.
You will notice that in the name of most of them is prefixed by the term ‘pdw’: This refers to Parallel Data Warehouse, the name of the Massively Parallel Processing SQL Server version that is inside the on premise Analytics Platform System and also ASDW in the cloud.
For a general introduction to the ASDW service, please refer to Robert Sheldon’s ASDW articles.
The information on the system views and how they relate to each other is accurate at the date that this article was published. Things are always moving in the cloud, if you run into any errors or unexpected results, be sure to check the online documentation for ASDW/PDW related views on this link.
We will start by showing you the views and queries that allow you to explore how data is distributed, what is currently happening in the system, the Data Movement Services and any operations moving data around.
How is a table being distributed?
ASDW is a Massively Parallel Processing (MPP) system, and as such, it takes advantage of multiple storage buckets called distributions and multiple machines called nodes. When a table is created, you have to specify how it will be distributed among 60 different buckets called distributions. Currently, ASDW supports distributing a table either based on a column as a HASH key or through a simple Round-Robin distribution of all the rows.
The system’s approach is to ‘divide-and-conquer’ by splitting very large tables into distributions and attaching those distributions to multiple nodes. Many nodes can then work in parallel to resolve queries over large datasets. If different tables are distributed with the same hash key then the distributions for a particular value will end up on the same node and so data movement is avoided if that key is used in a join. Proper distribution is a critical piece to getting the best performance of ASDW.
Here are the views that can help us to understand how a given table has been distributed around the system.
Notice that sys.schemas, sys.tables, sys.indexes and sys.columns are the same as SQL Server and I’ve left them in the diagram since they are important into tying the other ASDW specific views together and writing your own queries. As far as the ASDW specific views we have:
- sys.pdw_table_distribution_properties: this view will tell you how the table was distributed. In the case of ASDW, as of the date of publication the options will be HASH or ROUND_ROBIN.
- sys.pdw_table_mappings: because tables are split into 60 distributions, ASDW actually creates 60 different internal tables. This view will let you map the ‘logical’ table record from sys.tables with the actual internal table names used by ASDW.
- sys.pdw_nodes_tables: once we have those internal table names, we can use this view to see which compute node and distribution corresponds to each internal table. The view also exposes table level properties similar to regular SQL Server tables.
- sys.pdw_distributions: this is a simple view that ties the nodes and the distributions that are attached to them together.
- sys.pdw_nodes_partitions: this view has the partition information matching those internal tables that we get from sys.pdw_nodes_tables. Remember that by default, all tables have 1 partition.
- sys.dm_pdw_nodes_db_partition_stats: this view can tie back to those internal table names, the node and the distribution to give you crucial information like the amount of rows, the space used and the space reserved for a particular table, index or partition.
- sys.pdw_column_distribution_properties: this view can tie to the sys.tables and sys.columns view to let us know the specific column that was used to distribute a table if HASH distribution was selected. If the column distribution_ordinal is equal to 1 then this is the hashing key.
In the following examples of how these views are used, we will try to understand the distribution of an example table named ‘FactProductInventory’.
This set of views enable us to answer questions and queries such as:
What type of distribution is a table using?
|
1 2 3 4 |
SELECT t.name, td.distribution_policy_desc FROM sys.tables t INNER JOIN sys.pdw_table_distribution_properties td ON t.object_id=td.object_id WHERE t.name='FactProductInventory'; |
If my table is hash distributed, which column is the hash key?
|
1 2 3 4 5 6 |
SELECT t.name, cols.name, st.name [type_name] FROM sys.tables t INNER JOIN sys.columns cols ON t.object_id=cols.object_id INNER JOIN sys.pdw_column_distribution_properties colp ON cols.object_id=colp.object_id AND cols.column_id=colp.column_id INNER JOIN sys.types st ON cols.system_type_id=st.system_type_id WHERE t.name='FactProductInventory' AND colp.distribution_ordinal=1; |
See the amount of rows and space taken by the distributions of a table ranked by the space taken
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT t.name,nps.[row_count],nps.[used_page_count]*8.0/1024 as usedSpaceMB, nt.distribution_id FROM sys.tables t INNER JOIN sys.indexes i ON t.[object_id] = i.[object_id] AND i.[index_id] <= 1 /* HEAP = 0, CLUSTERED or CLUSTERED_COLUMNSTORE =1 */ INNER JOIN sys.pdw_table_mappings tm ON t.[object_id] = tm.[object_id] INNER JOIN sys.pdw_nodes_tables nt ON tm.[physical_name] = nt.[name] INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps ON nt.[object_id] = nps.[object_id] AND nt.[pdw_node_id] = nps.[pdw_node_id] AND nt.[distribution_id] = nps.[distribution_id] WHERE t.name='FactProductInventory' ORDER BY usedSpaceMB DESC; |
If you’re also using partitioning, then you’ll have to include sys.pdw_nodes_partitions into the queries to get to that level of granularity.
For example, this query shows the same information as the one above but breaks it down even further by space per partition per distribution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT pnp.partition_number,t.name,nps.[row_count],nps.[used_page_count]*8.0/1024 as usedSpaceMB,nt.distribution_id FROM sys.tables t INNER JOIN sys.indexes i ON t.[object_id] = i.[object_id] AND i.[index_id] <= 1 /* HEAP = 0, CLUSTERED or CLUSTERED_COLUMNSTORE =1 */ INNER JOIN sys.pdw_table_mappings tm ON t.[object_id] = tm.[object_id] INNER JOIN sys.pdw_nodes_tables nt ON tm.[physical_name] = nt.[name] INNER JOIN sys.pdw_nodes_partitions pnp ON nt.[object_id]=pnp.[object_id] AND nt.[pdw_node_id]=pnp.[pdw_node_id] AND nt.[distribution_id] = pnp.[distribution_id] INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps ON nt.[object_id] = nps.[object_id] AND nt.[pdw_node_id] = nps.[pdw_node_id] AND nt.[distribution_id] = nps.[distribution_id] AND pnp.[partition_id]=nps.[partition_id] WHERE t.name='FactProductInventory' ORDER BY usedSpaceMB DESC; |
From the diagram and the examples you can adjust and customize these queries to provide other information related to table distribution.
Let’s move on to understanding what’s running in the system at a given point in time.
What is currently happening in the system?
Since ASDW is a close relative to SQL Server, the views in this section will be familiar to most administrators. ASDW follows the same concepts of sessions, connections and requests as SQL Server. However, the MPP nature of ASDW means that a given request can be queued as well as split into multiple operations. But before we start looking into the views we need to cover a feature called Query Labels.
Query Labels for tracking individual queries in the requests system view
Query Labels is a feature native to ASDW that is unfamiliar to SQL Server administrators, though its usefulness will make you want to have it on the other SQL versions! The concept is very simple: for a given query, specify a friendly text label as a hint, in order to use it to quickly identify the query in the requests system view. Here’s an example:
|
1 2 3 |
SELECT AVG(UnitsBalance) FROM FactProductSales OPTION (LABEL = 'AverageBalance'); |
By using the ‘AverageBalance’ label, we can now easily track this query on the requests system view:
|
1 2 3 |
SELECT * FROM sys.dm_pdw_exec_requests r WHERE r.[label] = 'AverageBalance'; |
Simple, right?
As we’ll see later on, the requests system view is core to understanding how the system handles any given query or data load and using a label is the easiest way to tie together a query with its assigned request identifier.
System Status Views
Here’s the diagram for system status information:
The connections, sessions and requests views all work in a similar way as their SQL Server counterparts. The sys.dm_pdw_exec_requests one in particular is crucial to get the status of any given query as it exposes the ‘label’ column and as we’ll see later on, it allows to get a lot of detail on how the request is executed in the system.
From this set of views, the only ones exclusive to ASDW are these two:
- sys.dm_pdw_errors: this view allows you tie together errors with either the session or the request that raised it. It’s very handy and surprising that there’s no equivalent on SQL Server.
- sys.dm_pdw_sys_info: this view gives an ‘at-a-glance’ snapshot of system operation. It returns the amount of requests or loads either running or queued and idle or active sessions in the system. Another handy one that I wish was included in SQL Server!
Using the System Status Views
With this set of views we can answer questions and queries such as:
How many queries are currently running and how many queued?
|
1 |
SELECT active_requests, queued_requests FROM sys.dm_pdw_sys_info; |
Which application has executed the most queries?
|
1 2 3 4 |
SELECT app_name, SUM(query_count) totalQueryCount FROM sys.dm_pdw_exec_sessions GROUP BY app_name ORDER BY totalQueryCount DESC; |
Is there a host generating more errors than all others in the last hour?
|
1 2 3 4 5 6 7 |
SELECT client_id, COUNT(error_id) errorCount FROM sys.dm_pdw_errors errors INNER JOIN sys.dm_pdw_exec_sessions sess ON errors.session_id=sess.session_id WHERE errors.create_time > DATEADD(mi,-60, GETDATE()) GROUP BY sess.client_id ORDER BY errorCount DESC; |
Are there clients connected with a non-encrypted connection and how are they authenticating?
|
1 2 3 |
SELECT client_id, encrypt_option, auth_scheme FROM sys.dm_pdw_exec_connections WHERE encrypt_option='TRUE'; |
Using the query example we had with a label of ‘AverageBalance’, we can also answer questions and queries such as:
What’s the status, command and resource class of this query?
|
1 2 3 |
SELECT [status], command, resource_class FROM sys.dm_pdw_exec_requests r WHERE r.[label]='AverageBalance'; |
Were there any errors when the request ran and which user ran it?
|
1 2 3 4 5 6 |
SELECT r.request_id, r.[status], r.[label], r.command, sess.login_name, errors.[type] errorType, errors.create_time errorTime, errors.details errorDetail FROM sys.dm_pdw_exec_requests r INNER JOIN sys.dm_pdw_exec_sessions sess ON r.session_id=sess.session_id INNER JOIN sys.dm_pdw_errors errors ON r.request_id=errors.request_id WHERE r.[label]='AverageBalance' ORDER BY r.request_id, errorTime; |
What’s the runtime of the last 10 executions of this query?
|
1 2 3 4 |
SELECT TOP 10 [status], command, total_elapsed_time FROM sys.dm_pdw_exec_requests r WHERE r.[label]='AverageBalance' ORDER BY total_elapsed_time DESC; |
Unlike the sys.dm_exec_requests view in SQL Server, the sys.dm_pdw_exec_requests view actually keeps up to 10000 records with the information of a request even after it has executed. This capability is very useful as you can track specific query executions as long as their records are still among the 10000 kept by the view. As time passes the oldest records are phased out in favor of more recent ones.
Let’s move on now to exploring and understanding the Data Movement Services and data movement operations that happen inside ASDW.
Tracking Data Movement Services
Because of the distributed nature of ASDW, the system has a component called the Data Movement Services (DMS) that takes care of moving data around the different compute nodes as part of a query’s distributed execution plan.
The DMS also has worker threads that take care of reading data from Blob Storage and distributing it to the 60 distributions of ASDW. The number of readers depends on the amount of Data Warehousing Units provisioned to the service and the amount of writers is always 60. This parallel reading from a Blob Storage file to an ASDW table is accomplished through the loading method known as PolyBase.
Data Movement Services Views
From the diagram, we can see the familiar requests view as well as three ASDW exclusive views:
- sys.dm_pdw_dms_cores: this view shows the status of the DMS service on each compute node in the system. Since ASDW is a managed service on Azure, this should always display as ‘Ready’.
- sys.dm_pdw_dms_external_work: this view shows each step that a DMS worked has taken to read a file from an external location like Blob Storage. The steps record not only the file name but also timestamps and the amount of data read.
- sys.dm_pdw_dms_workers: this view shows each DMS worker thread and information on the work they are doing or have done. This includes the node and distribution that the worker is working on, CPU, time and other measurements.
Both the external work and the DMS worker views also accumulate history at a maximum of 10000 records the same as the requests view.
Using the Data Movement Services views
With this set of views we can answer questions and queries such as:
Which files are currently being loaded into the system and by which user?
|
1 2 3 4 5 6 7 |
SELECT DISTINCT ew.input_name [file], ew.[status], s.login_name FROM sys.dm_pdw_dms_external_work ew INNER JOIN sys.dm_pdw_exec_requests r ON ew.request_id=r.request_id INNER JOIN sys.dm_pdw_exec_sessions s ON r.session_id=s.session_id WHERE ew.[status]='Processing'; |
How much data has been written by the DMS workers for each file that was loaded in the last hour?
|
1 2 3 4 5 6 7 8 9 |
SELECT files_requests.[file], SUM(dws.bytes_processed/1024/1024) MBProcessed FROM sys.dm_pdw_dms_workers dws INNER JOIN (SELECT DISTINCT request_id, input_name [file], [status] FROM sys.dm_pdw_dms_external_work ew) files_requests ON dws.request_id=files_requests.request_id WHERE files_requests.[status]='Done' AND dws.[type]='WRITER' AND dws.end_time > DATEADD(mi,-60,GETDATE()) GROUP BY files_requests.[file] ORDER BY MBProcessed DESC; |
If we perform a data load with a label of ‘Load FactServiceCalls Table’, we can also answer questions and queries such as:
How many rows have been distributed by the DMS workers for this data load?
|
1 2 3 4 |
SELECT r.[status], r.command, SUM(rows_processed) rowsProcessed FROM sys.dm_pdw_exec_requests r INNER JOIN sys.dm_pdw_dms_workers w on r.request_id = w.request_id WHERE r.[label] = 'Load FactServiceCalls Table' and w.[type] LIKE '%_CONVERTER' GROUP BY r.[status], r.command; |
What is the average Megabytes-per-sec read rate for this load?
|
1 2 3 4 |
SELECT r.[status], r.command,AVG(bytes_per_sec)/1024.0/1024.0 AverageMBSec FROM sys.dm_pdw_exec_requests r INNER JOIN sys.dm_pdw_dms_workers w on r.request_id = w.request_id WHERE r.[label] = 'Load FactServiceCalls Table ' and w.[type] LIKE '%_READER' and w.bytes_processed > 0 GROUP BY r.[status], r.command; |
As you can see, there’s enough instrumentation on these views to track load progress, monitor the status of a specific load and detect where bottlenecks might be popping up.
Drilling Down on Request Details
Once a query has been submitted, tracking it through the request identifier from sys.dm_exec_requests allows joining with other views that provide different perspectives on what the query is doing under the hood.
We already saw one of those views when we explored the Data Movement Services. Here are the rest of them:
Request Details Views
We can see here three new views:
- sys.dm_pdw_hadoop_operations: this view will populate with records that correspond to steps in map-reduce jobs when running a query to an external Hadoop. This makes perfect sense for the SQL Server 2016 and APS implementation of PolyBase that can push down to Hadoop some predicate computations so the amount of data sent from the Hadoop cluster to the SQL engine is minimized. However, at the time of this article’s publication, ASDW doesn’t have the capability of doing external Hadoop tables yet. The fact that the view is present and accessible seems to point to this feature coming to ASDW down the line.
- sys.dm_pdw_request_steps: this view allows us to see the breakdown of a request into the different steps that lead to its resolution. Because ASDW is a distributed system, any given request can require different operations such as moving data around the nodes, creating intermediate datasets on temporary tables and running different SQL on any of these structures. Using the step_index column to sort, we can see the exact order in which these steps ran. Other columns such as operation_type, distribution_type and location_type offer more details on the execution internals of ASDW.
- sys.dm_pdw_sql_requests: this view ties closely to the request_steps view. It ties back to specific steps in a request and expands this information to give us stats for a specific step inside one or more distributions. This allows much more granular instrumentation since it goes all the way down to the individual distribution level.
Using Request Details Views
For example, we could ask:
Which requests have steps that required work from the Data Movement Service?
|
1 2 3 4 5 6 7 8 9 |
SELECT r.[request_id],r.[command],steps.[step_index],steps.[operation_type],steps.[distribution_type],steps.[location_type], steps.[total_elapsed_time], steps.[command] step_command FROM sys.dm_pdw_exec_requests r INNER JOIN sys.dm_pdw_request_steps steps ON r.[request_id]=steps.[request_id] LEFT JOIN sys.dm_pdw_sql_requests sql_reqs ON steps.[request_id]=sql_reqs.[request_id] AND steps.[step_index]=sql_reqs.[step_index] WHERE steps.[location_type] = 'DMS' ORDER BY steps.[step_index]; |
If you see many operations that use the DMS and are taking the majority of your execution time then this is likely a tuning opportunity in the system. For example, tables that are frequently joined by a specific column can avoid data movement if they both use a hash key distribution on that column.
Now let’s assume we have this query:
|
1 2 3 4 |
SELECT AVG(UnitsBalance) FROM FactProductSales GROUP BY ProductKey OPTION (LABEL = 'AverageBalanceByProduct'); |
Using the query label, with these views we can answer questions such as:
What were the different steps executed by the system to resolve this query and how much time did each step take?
|
1 2 3 4 5 6 7 |
SELECT r.[command],steps.[step_index],steps.[operation_type],steps.[distribution_type],steps.[location_type], steps.[total_elapsed_time], steps.[command] step_command FROM sys.dm_pdw_exec_requests r INNER JOIN sys.dm_pdw_request_steps steps ON r.[request_id]=steps.[request_id] WHERE r.[label]='AverageBalanceByProduct' ORDER BY steps.[step_index] ASC; |
Once we can split a specific request into its individual steps, we can quickly isolate what is causing the bulk of our execution time and focus our tuning efforts on that step.
For each step of the query, what was the maximum, minimum, average and standard deviation of the execution time per distribution?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT r.[command],steps.[step_index],steps.[operation_type],steps.[distribution_type],steps.[location_type], steps.[total_elapsed_time], steps.[command] step_command, max(sql_reqs.[total_elapsed_time]) maxPerDistributionTime, min(sql_reqs.[total_elapsed_time]) minPerDistributionTime, avg(sql_reqs.[total_elapsed_time]) avgPerDistributionTime, stdev(sql_reqs.[total_elapsed_time]) stdDeviationDistributionTime FROM sys.dm_pdw_exec_requests r INNER JOIN sys.dm_pdw_request_steps steps ON r.[request_id]=steps.[request_id] LEFT JOIN sys.dm_pdw_sql_requests sql_reqs ON steps.[request_id]=sql_reqs.[request_id] AND steps.[step_index]=sql_reqs.[step_index] WHERE r.[label]='AverageBalanceByProduct' GROUP BY r.[command],steps.[step_index],steps.[operation_type],steps.[distribution_type],steps.[location_type], steps.[total_elapsed_time], steps.[command] ORDER BY steps.[step_index]; |
In this last query, I’m using a LEFT JOIN to join the request_steps view to the sql_requests view because some query steps will not break down further into operations at the distribution scope. Using a query like this you can detect if you have data distribution issues and one distribution is doing much more work than others.
In this type of skewed distribution, you have to analyze what type of distribution each table involved is using and whether there is a better way of distributing your data. The key concept to keep in mind is that in general, good performance in ASDW comes from using all the system resources in parallel: distributions and compute nodes. If the data is evenly balanced on the different distributions then more work can be done in parallel.
Checking on the ASDW nodes
Now that we’ve taken a look at the views and information we get at the detailed request level, let’s move up and check the views that operate at a node level. These views can be used as a complement on the request level analysis or individually to trend, monitor or troubleshoot issues at the node level.
Node-related Views
We already covered sys.dm_pdw_errors, and now we have three new views:
- sys.dm_pdw_nodes: this view will return a row for every node in your ASDW. As you increase and decrease the number of Data Warehousing Units in your system, the number of compute nodes will change accordingly. Regardless of the amount of DWUs, there is always just one Control node.
- sys.dm_pdw_os_threads: this view returns a row for every thread running on each node of the system.
- sys.pdw_nodes_pdw_physical_databases: this view gives us a list of the different internal databases that compose the entire Data Warehouse and which node it is attached to. You will notice that each distribution will be a separate internal database and each node has its own set of system databases.
- sys.dm_pdw_nodes_database_encryption_keys: this view lets you keep track of encryption operations on a node when you turn ON or OFF the Transparent Database Encryption (TDE) feature on ASDW. If an operation is in progress you can track it with the percent_complete column.
Using the Node-Related Views
With this set of views we can answer questions such as:
How many of each type of node do I have on my system?
|
1 2 |
SELECT [type], COUNT(*) node_count FROM sys.dm_pdw_nodes GROUP BY [type]; |
The results will always be one Control node plus a number of Compute Nodes. The exact amount of Compute Nodes will depend on the amount of Data Warehousing Units that you have provisioned and will change as you scale those DWUs up and down.
How many threads are running on each node?
|
1 2 3 4 5 |
SELECT nds.pdw_node_id,nds.[type], COUNT(*)threadCount FROM sys.dm_pdw_nodes nds INNER JOIN sys.dm_pdw_os_threads thrs ON nds.pdw_node_id=thrs.pdw_node_id GROUP BY nds.pdw_node_id,nds.[type]; |
Since ASDW is a fully managed service by Microsoft, there’s not a lot of trending or diagnostics that you can distill from the OS threads info. That said, I’m sure there are people that are just as curious as I am and enjoy looking into these internal details of the system.
What’s the status of TDE on each internal database on the system?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT enc.pdw_node_id, pds.physical_name internal_database, enc.percent_complete, CASE enc.encryption_state WHEN 1 THEN 'Unencrypted' WHEN 2 THEN 'Encryption in progress' WHEN 3 THEN 'Encrypted' WHEN 4 THEN 'Key change in progress' WHEN 5 THEN 'Decryption in progress' WHEN 6 THEN 'Protection change in progress' ELSE 'Unknown state' END encryption_state_desc FROM sys.dm_pdw_nodes_database_encryption_keys enc INNER JOIN sys.pdw_nodes_pdw_physical_databases pds ON enc.database_id=pds.database_id AND enc.pdw_node_id=pds.pdw_node_id ORDER BY percent_complete ASC; |
On a large warehouse, the encryption and decryption of the data can take a long time and this query will allow you to monitor the progress of the operation.
At this point we’ve looked at both the detailed request level and high level node information. Now, let’s look at the wait related information and views in the system.
Queuing, Blocking and other performance issues.
Similar to SQL Server, ASDW also keeps track of the different wait statistics that pop up during the operation of the system. Some of them are unavoidable and harmless, others can be signs of real bottlenecks on your Data Warehouse.
Keeping track of the top waits can help tune specific queries, monitor the system and spot trends in performance. ASDW offers wait information at the node or the request level.
Performance-Related Views
We can see four wait-related views:
- sys.dm_pdw_lock_waits: this view shows the current locks granted and being waited on for the active requests in the system.
- sys.dm_pdw_resource_waits: this view shows waits that are related to the concurrency management limits established by ASDW. As you scale your Data Warehousing Units up you can service a larger concurrent workload.
- sys.dm_pdw_waits: this view is a union of the previous two views in case you are interested in both the lock and the concurrency waits.
- sys.dm_pdw_wait_stats: this view aggregates the amount of waits and waiting time for each wait type on each node of the system.
Answering performance questions with the Performance-Related Views
The way to handle and analyze blocking and wait bottlenecks is the same as SQL Server, the only difference is in where to find the information. Also keep in mind ASDW is a distributed system and thus the wait stats are provided at the individual node level. To provide a view of the entire system, we aggregate the values over all the different nodes by grouping by wait type and ignoring internal system waits and wait types that have not occurred at all.
With this set of views we can answer questions and queries such as:
Which queries are being queued by the system due to concurrency?
|
1 2 3 4 5 6 7 |
SELECT r.request_id queuedRequest,r.[status] requestStatus, r.command command, waits.[state] wait_state,waits.[resource_class] FROM sys.dm_pdw_exec_requests r INNER JOIN sys.dm_pdw_resource_waits waits ON r.request_id=waits.request_id WHERE waits.[type]='UserConcurrencyResourceType' AND waits.[state]='Queued'; |
In the result set for this query we include the user Resource Class. A full exploration of Resource Classes is out of the scope of this article but we will say that they are part of ASDW’s workload management system and govern how much resources a query can use. Higher resource class queries get more resources but the trade-off is that they decrease the amount of concurrent queries that will be allowed to run in the system.
Which queries are currently blocked and which process is the blocker?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT blockedReqs.*,blockingReqs.blockerRequest,blockingReqs.blockerRequestStatus,blockingReqs.blockerCommand FROM (SELECT r.request_id blockedRequest,r.[status] blockedRequestStatus, r.command blockedCommand, waits.[type], waits.[object_type],waits.[object_name],waits.[state] FROM sys.dm_pdw_exec_requests r INNER JOIN sys.dm_pdw_lock_waits waits ON r.request_id=waits.request_id WHERE r.[status]='Running' AND waits.[state]='Queued') blockedReqs INNER JOIN (SELECT r2.request_id blockerRequest,r2.[status] blockerRequestStatus, r2.command blockerCommand, waits2.[type], waits2.[object_type],waits2.[object_name],waits2.[state] FROM sys.dm_pdw_exec_requests r2 INNER JOIN sys.dm_pdw_lock_waits waits2 ON r2.request_id=waits2.request_id AND waits2.[state] ='Granted') blockingReqs ON blockedReqs.[type]=blockingReqs.[type] AND blockedReqs.[object_type]=blockingReqs.[object_type] AND blockedReqs.[object_name]=blockingReqs.[object_name] ORDER BY blockedReqs.blockedRequest; |
Unlike the sys.dm_exec_requests view on the on-premises SQL Server, the sys.dm_pdw_exec_requests view does not have the blocking_session_id column. This means that monitoring and analyzing blocking requires working with the relationships and information on these views as we can see in the query above.
ASDW does offer a more direct equivalent of sys.dm_exec_requests called sys.pdw_nodes_exec_requests and this one does include the ‘blocking_session_id’ column. However, this view is not that useful as it is usually used in conjunction with functions such as sys.dm_exec_sql_text to see the command that is running, however this function is not available in ASDW.
For this reason, I left the sys.pdw_nodes_exec_requests view out of the diagram intentionally. New users of ASDW should familiarize themselves and work with sys.dm_pdw_exec_requests instead.
To analyze system wide waits, we can use the views for questions like:
What are the aggregated values for the different performance impacting waits in the system?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
SELECT wait_name, MAX(max_wait_time) max_wait_time, SUM(request_count) request_count, SUM(signal_time) signal_time, SUM(completed_count) completed_count, SUM(wait_time) wait_time FROM sys.dm_pdw_wait_stats WHERE wait_name NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE', N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS', N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS', N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST', N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT', N'SOS_SYNC_TASK_ENQUEUE_EVENT',N'CLR_MONITOR', N'CLR_TASK_START',N'CLR_CRST',N'PREEMPTIVE_XE_SESSIONCOMMIT', N'TDS_INIT',N'PREEMPTIVE_XE_TARGETINIT',N'PREEMPTIVE_XE_ENGINEINIT',N'PREEMPTIVE_XE_CALLBACKEXECUTE', N'XE_BUFFERMGR_ALLPROCESSED_EVENT',N'SNI_CONN_DUP',N'PREEMPTIVE_OS_QUERYREGISTRY',N'DAC_INIT',N'PREEMPTIVE_OS_DOMAINSERVICESOPS', N'SNI_TASK_COMPLETION') AND completed_count > 0 GROUP BY wait_name ORDER BY wait_time DESC; |
To create the list of wait types to ignore, I based it on the wait stats diagnostic query from Glen Berry found here and added some other wait types that I’ve seen on ASDW that don’t seem to impact performance and can be safely ignored.
Wrapping Up
Through the diagrams and example queries we have covered many different facets of ASDW as exposed by the system views. Take these examples as the launch point for your own analysis, monitoring and learning how the system is architected. Once you’re comfortable, roll up your sleeves and customize these or write your own from scratch as you dig deeper into the power of Azure SQL Data Warehouse.