Dynamic Management Views (DMVs) are a powerful tool for administering and monitoring the performance and security of your SQL Server database. In this article, we will explore some useful queries using DMVs to administer your SQL Server database.
Getting Started
To get started, you can use the query editor in Azure, which does not require any installation. However, if you prefer to use Visual Studio or SQL Server Management Studio (SSMS), that is also fine. The examples provided in this article can be executed in both on-premises SQL Server and Azure SQL Data Warehouse (ASDW).
In on-premises SQL Server, you can use the sys.dm_exec_sessions DMV to get information about the sessions:
SELECT *
FROM sys.dm_exec_sessions;In Azure SQL Data Warehouse, the DMVs include the “pdw” prefix. For example, to get information about the ASDW sessions, you can run the following query:
SELECT *
FROM sys.dm_pdw_exec_sessions;The results will include the session status, login, number of queries per session, login time, application name, and more.
Monitoring Connections
Another popular DMV is sys.dm_pdw_exec_connections, which provides information about the connections to your ASDW:
SELECT *
FROM sys.dm_pdw_exec_connections;This query will display information about the authentication, client ID, and more.
If you want to count the number of active sessions in ASDW, you can use the following query:
SELECT COUNT(status)
FROM sys.dm_pdw_exec_sessions
WHERE status = 'active';You can also retrieve specific information about the sessions, such as the login, application, and login time in minutes, for 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';For more detailed information about the connections, the client ID is very important:
SELECT client_id
FROM sys.dm_pdw_exec_connections;You can use internet sites to get information about the IP. For example, you can use the website “whois” to get information about IPs.
Monitoring Requests
The sys.dm_pdw_exec_requests DMV is used to monitor the ASDW requests:
SELECT *
FROM sys.dm_pdw_exec_requests;This query will show the submit time, start time, end time, total elapsed time, commands sent, status, database ID, and more.
For example, you can retrieve 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 DMVs are only applicable to Parallel Data Warehouse and not to ASDW. For example, the sys.dm_pdw_query_stats_xe DMV is only applicable in Parallel Data Warehouse.
If you need to check your backups, restores, queued requests, and loads, you can use the sys.dm_pdw_sys_info view:
SELECT *
FROM sys.dm_pdw_sys_info;This view will display information about your ASDW, such as backups, restores, queued requests, and loads.
Using Labels
Labels are a good practice to use in your queries to easily identify them. You can assign a label to your query using the OPTION clause. For example:
SELECT *
FROM FactInternetSales
OPTION (LABEL = 'long query');You can then query the execution request information of the queries whose label is “long query”:
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [label] = 'long query';This query will show the results of the label “long query”.
Conclusion
Dynamic Management Views (DMVs) are a valuable tool for administering and monitoring your SQL Server database. They provide insights into sessions, requests, queries, performance, logins, and more. By utilizing DMVs, you can effectively manage and optimize your SQL Server environment. There are many more DMVs available to monitor locks, Hadoop, nodes, network credentials, and other aspects of your SQL Server database.
References: