In my previous blog, I covered in-depth on installation and configuration System Center Operation Manager 2012 R2 and monitoring Windows Azure Environment.
I want to extend the conversation on Windows Azure SQL Database Performance Monitoring and Diagnostics. Current available Windows Azure SQL database Management pack version 1.5.4.0 published on 07-05-2013 for SCOM. Provides capabilities to discover, monitor, and manage your Windows Azure SQL Databases. After configuration, the Microsoft Windows Azure SQL Database Monitoring Management Pack offers the following functionalities:
• User-friendly wizard to discover Windows Azure SQL Database servers
• Provides availability status of Windows Azure SQL Database server. SLA Dashboard.
• Collects and monitors health of Windows Azure SQL Database databases.
• Space monitoring, Used space, Free space, Total allocated quota
• Track the total number of databases per server, Number of database sessions
• Collects and monitors performance information, Average memory per session
• Total memory per session, Total CPU time per session, Total I/O per session
• Maximum Transaction execution time, Max. Transaction lock count, Max. Transaction log space used
• Network Egress/Ingress bandwidth
• Ability to define Custom thresholds for each monitor to configure the warning and critical alerts.
• Run-as profile to securely connect to Windows Azure SQL Database.
• Detailed knowledge to guide the IT operator with troubleshooting the problem
• Custom tasks to redirect the user to the Windows Azure SQL Database online portal
• Custom query support to enable application-specific availability and performance monitoring
Below Screen shots captured System Center Operation Manager 2012 R2 monitoring Windows Azure SQL Database
Windows Azure SQL Database SLA dashboard in SCOM
Windows Azure Performance Dashboard in SCOM
Windows Azure Performance Counters in SCOM
In addition to standard health and performance monitoring of the Windows Azure SQL Database cloud service, you can define custom SQL queries to be executed. This allows you to monitor your application‑specific health.
Custom queries can be defined in Authoring console with option to create Two state or Three State monitoring Windows Azure SQL database.
Beyond SCOM standard and custom Monitoring, we can leverage SCOM Reporting Server for lengthy Performance and Diagnostics Azure SQL custom queries.
Performance and Diagnostics Queries for SQL Azure Databases
Top 10 Most CPU intensive queries
SELECT TOP 10 query_stats.query_hash AS \”QueryHash\”, SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS \”AvgCPUTime\”, MIN(query_stats.statement_text) AS \”StatementText\” 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 |
Top 10 Most Resource Intensive Queries
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 10 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”; |
Current Connection Info
SELECT e.connection_id, s.session_id, s.login_name, s.last_request_end_time, s.cpu_time FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_connections e ON s.session_id = e.session_id |
Top 10 Running Queries having the Longest Total Elapsed Time & are Blocking Other Queries
SELECT TOP 10 r.session_id, r.plan_handle, r.sql_handle, r.request_id, r.start_time, r.status, r.command, r.database_id, r.user_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level, r.row_count, st.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st WHERE r.blocking_session_id = 0 and r.session_id in (SELECT distinct(blocking_session_id) FROM sys.dm_exec_requests) GROUP BY r.session_id, r.plan_handle, r.sql_handle, r.request_id, r.start_time, r.status, r.command, r.database_id, r.user_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level,r.row_count, st.text ORDER BY r.total_elapsed_time desc |
Row Count Aggregate Information (total rows, min rows, max rows and last rows) for Queries
SELECT qs.execution_count, SUBSTRING(qt.text,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 ELSE qs.statement_end_offset end – qs.statement_start_offset )/2 ) AS query_text, qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid, qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.text like ‘%SELECT%’ ORDER BY qs.execution_count DESC; |
Top 10 Most Expensive Queries by Logical Reads
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)+1) SQLStatement, qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.total_logical_reads DESC |
Top 10 Most Expensive Queries by Logical Writes
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)+1) SQLStatement, qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.total_logical_writes DESC |
Top 10 Most Expensive Queries by Worker Time
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)+1) SQLStatement, qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.total_worker_time DESC |
Queries Taking Longest Elapsed Time
SELECT TOP 10 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds, qs.total_elapsed_time / 1000000.0 AS total_seconds, qs.execution_count, SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS individual_query, o.name AS object_name, DB_NAME(qt.dbid) AS database_name FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id where qt.dbid = DB_ID() ORDER BY average_seconds DESC; |
Queries doing the Most I/O
SELECT TOP 10 (total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO, (total_logical_reads + total_logical_writes) AS total_IO, qs.execution_count AS execution_count, SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS indivudual_query, o.name AS object_name, DB_NAME(qt.dbid) AS database_name FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id where qt.dbid = DB_ID() ORDER BY average_IO DESC; |
Calculate the Database Size (MB)
SELECT SUM(reserved_page_count) * 8192 / 1024 / 1024 as DatabaseSize FROM sys.dm_db_partition_stats |
The above will provide extensive performance and diagnostic metrics for Windows Azure SQL database.
Happy Reading!
Puneet
