Monitoring Azure SQL Database using dynamic management views
Microsoft Azure SQL Database enables a subset of dynamic management views to diagnose performance problems, which might be caused by blocked or long-running queries, resource bottlenecks, poor query plans, and so on. This topic provides information on how to detect common performance problems by using dynamic management views.
Azure SQL Database partially supports three categories of dynamic management views:
- Database-related dynamic management views.
- Execution-related dynamic management views.
- Transaction-related dynamic management views.
Permissions
In SQL Database, querying a dynamic management view requires VIEW DATABASE STATE permissions. The VIEW DATABASE STATE permission returns information about all objects within the current database.
To grant the VIEW DATABASE STATE permission to a specific database user, run the following query:
GRANT VIEW DATABASE STATE TO database_user;
In an instance of on-premises SQL Server, dynamic management views return server state information. In SQL Database, they return information regarding your current logical database only.
Calculating database size
The following query returns the size of your database (in megabytes):
-- Calculates the size of the database.
SELECT SUM(reserved_page_count)*8.0/1024
FROM sys.dm_db_partition_stats;
GO
The following query returns the size of individual objects (in megabytes) in your database:
-- Calculates the size of individual database objects.
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name;
GO
Monitoring connections
You can use the sys.dm_exec_connections view to retrieve information about the connections established to a specific Azure SQL Database server and the details of each connection. In addition, the sys.dm_exec_sessions view is helpful when retrieving information about all active user connections and internal tasks. The following query retrieves information on the current connection:
SELECT
c.session_id, c.net_transport, c.encrypt_option,
c.auth_scheme, s.host_name, s.program_name,
s.client_interface_name, s.login_name, s.nt_domain,
s.nt_user_name, s.original_login_name, c.connect_time,
s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE c.session_id = @@SPID;
Note:
When executing the sys.dm_exec_requests and sys.dm_exec_sessions views, if you have VIEW DATABASE STATE permission on the database, you see all executing sessions on the database; otherwise, you see only the current session.
Monitoring query performance
Slow or long running queries can consume significant system resources. This section demonstrates how to use dynamic management views to detect a few common query performance problems. An older but still helpful reference for troubleshooting, is the Troubleshooting Performance Problems in SQL Server 2008 article on Microsoft TechNet.
Finding top N queries
The following example returns information about the top five queries ranked by average CPU time. This example aggregates the queries according to their query hash, so that logically equivalent queries are grouped by their cumulative resource consumption.
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
Monitoring blocked queries
Slow or long-running queries can contribute to excessive resource consumption and be the consequence of blocked queries. The cause of the blocking can be poor application design, bad query plans, the lack of useful indexes, and so on. You can use the sys.dm_tran_locks view to get information about the current locking activity in your Azure SQL Database.
Monitoring query plans
An inefficient query plan also may increase CPU consumption. The following example uses the sys.dm_exec_query_stats view to determine which query uses the most cumulative CPU.
SELECT
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
FROM
(SELECT TOP 50
qs.plan_handle,
qs.total_worker_time
FROM
sys.dm_exec_query_stats qs
ORDER BY qs.total_worker_time desc) AS highest_cpu_queries
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC;
Questions :-
Q1. In Azure SQL Database, querying a dynamic management view requires
1. VIEW DATABASE STATE permissions.
2. View Server state permission.
3.All
4.None
Ans : 1
Q2. In local SQl Server instance, querying a dynamic management view requires
1. VIEW DATABASE STATE permissions.
2. View Server state permission.
3.All
4.None
Ans :2