Before you can start executing the DMVs make sure the VIEW DATABASE PERMISSION is set for the Database user.
GRANT VIEW DATABASE STATE TO database_user;
Top 10 Queries consuming High CPU
SELECT TOP 10 Getdate() runtime, * FROM (SELECT query_stats.query_hash, Sum(query_stats.cpu_time) 'Total_Request_Cpu_Time_Ms', Sum(logical_reads) 'Total_Request_Logical_Reads', Min(start_time) 'Earliest_Request_start_Time', Count(*) 'Number_Of_Requests', Substring(Replace(Replace(Min(query_stats.statement_text), Char( 10), ' ' ), Char( 13), ' '), 1, 256) AS "Statement_Text" FROM (SELECT req.*, Substring(ST.text, ( req.statement_start_offset / 2 ) + 1 , ( ( CASE statement_end_offset WHEN -1 THEN Datalength(ST.text) ELSE req.statement_end_offset END - req.statement_start_offset ) / 2 ) + 1) AS statement_text FROM sys.dm_exec_requests AS req CROSS apply sys.Dm_exec_sql_text(req.sql_handle) AS ST) AS query_stats GROUP BY query_hash) AS t ORDER BY total_request_cpu_time_ms DESC;
Top 10 long running queries
SELECT TOP 10 req.session_id, req.start_time, cpu_time 'cpu_time_ms', Object_name(ST.objectid, ST.dbid) 'ObjectName', Substring(Replace(Replace(Substring(ST.text, ( req.statement_start_offset / 2 ) + 1, ( ( CASE statement_end_offset WHEN -1 THEN Datalength(ST.text) ELSE req.statement_end_offset END - req.statement_start_offset ) / 2 ) + 1), Char(10 ), ' '), Char(13), ' '), 1, 512) AS statement_text FROM sys.dm_exec_requests AS req CROSS apply sys.Dm_exec_sql_text(req.sql_handle) AS ST ORDER BY cpu_time DESC;
Monitoring Connections
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;
Retrieve Database Size
SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 AS DatabaseSizeInMB FROM sys.database_files WHERE type_desc = 'ROWS';
Individual Object Sizes
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;
Few more other queries you can use to monitor the performance of your Azure SQL Database. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs
No comments:
Post a Comment