Introduction

The Dynamic Management Views (DMVs) are used to administer the security and performance of the database. In this new article, we will check some useful queries using DMVs to administer our ASDW.

Requirements

1. An Azure Data Warehouse installed.

Getting started

We will use the query editor in Azure. I like it, because you do not need to install anything. However, if you prefer to use Visual Studio or SSMS, it is OK:

SQL Server on-premises used the sys.dm_exec_sessions. Let's run it:

select * from sys.dm_exec_sessions

You will get the following message:

In Azure Data Warehouse and in Parallel Data Warehouse, the DMVs include the pdw (Parallel Data Warehouse) prefix. For example, to get information about the Azure Data Warehouse sessions, you can run the following query:

select * from sys.dm_pdw_exec_sessions

The results displayed are the following:

The sessions will include the status, the session login, the number of queries per session, login time, application name and more.

Another popular DMV is the sys.dm_pdw_exec_connections:

select * from sys.dm_pdw_exec_connections

The results displayed are the following:

You can verify the authentication, the client id and more.

The following query will count the number of active sessions in ASDW:

select count(status)
 from sys.dm_pdw_exec_sessions
 where status='active'

The following example will show the login, application and login time in minutes of all the sessions longer than 10 minutes:

SELECT login_name,
       app_name,
       DATEDIFF (minute,login_time ,getdate() ) AS total_time
 FROM sys.dm_pdw_exec_sessions
 WHERE DATEDIFF (minute, login_time , getdate() )>10
 AND status='active'

If you want to get more information about the connections, the client id is very important:

SELECT client_id
 FROM sys.dm_pdw_exec_connections

The result displayed by the query will include the IP:

You can use internet sites to get information about the IP. I will use in this example the web site whois. This is a web site to get information about IPs:

As you can see, the IP is from USA and the ASN is Microsoft Corporation.

Another important DMV is the sys.dm_pdw_exec_requests. This DMV is used to monitor the ASDW requests:

SELECT *
 FROM sys.dm_pdw_exec_requests

The query will show the submit time, start time, end time, total elapsed time, commands sent, status, database id and more:

The following example will show the execution requests including the command sent, total elapsed time and database name of the requests longer than 30 seconds:

SELECT command,
       total_elapsed_time,
      db_name(database_id)
 FROM sys.dm_pdw_exec_requests
 WHERE total_elapsed_time>30000

Note that some of the DMVs are only applicable to Parallel Data Warehouse and not to ASDW. For example, the sys.dm_pdw_query_stats_xe is only applicable in Parallel Data Warehouse:

SELECT *
 FROM sys.dm_pdw_query_stats_xe

When we run that query, the message displayed is the following:

If we check the documentation, we will find the following information:

As you can see, the DMV only applies to Parallel Data Warehouse and the DMV is deprecated.

If you need to check your backups, restores, queued request and loads, the sys.dm_pdw_sys_info is the view to use:

SELECT *
 FROM sys.dm_pdw_sys_info

The results displayed will be the following:

Another interesting option is the LABEL. LABEL is a good practice to use in your queries to identify your queries easily. In a query, use the OPTION and assign a label to your query. In this example the label is "long query":

SELECT *
 FROM FactInternetSales 
 OPTION ( LABEL = 'long query' );

You can for example query the execution request information of the queries whose label is "long query":

SELECT *
 FROM sys.dm_pdw_exec_requests
 WHERE [label] = 'long query';

The query will show the results of the label "long query":

Finally, we will show the top 5 requests including status, total elapsed time and commands with more total elapsed time:

SELECT TOP 5
  request_id,
  [status],
  total_elapsed_time,
  command
 FROM sys.dm_pdw_sql_requests 
 ORDER BY total_elapsed_time DESC;

Conclusion

As you can see, the DVMs are very useful to verify, sessions, requests, queries, monitor performance, logins, activity and much more in ASDW. There are more DMVs to monitor locks, Hadoop, nodes, network credentials and more.

References

If you need more information about DMVs, refer to these links:

• SQL and Parallel Data Warehouse Dynamic Management Views
• Monitor your workload using DMVs