Blog

Windows Azure SQL database Performance and diagnostics and SCOM

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 1Windows Azure SQL Database SLA dashboard in SCOM

windows azure sql database 2

Windows Azure Performance Dashboard in SCOM

windows azure sql database 3

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.

windows azure sql database 4

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





Azure Managed Services