Consultas de monitorización de Azure SQL


Mediante el siguiente artículo se pretende describir un conjunto de consultas que permiten obtener información que puede ser de utilidad para monitorizar las bases de datos de Azure. 

Monitorización básica

La monitorización mas básica que se puede realizar para conocer la disponibilidad de las bases de datos SQL de Azure consiste en controlar el puerto 1433 mediante herramientas como Nagios. Destacar que para ello se deberá de modificar el firewall dentro de Azure de tal forma que permita las conexiones entrantes para comprobar la disponibilidad a través del servicio de monitorización. Es decir, se pemitirá el acceso desde las direcciones IP asociadas a los servicios de monitorización. 

Consulta para obtener el tamaño de la base de datos 

La tabla sus.dm_db_partition_stats al ser consultada permite conocer el tamaño de la base de datos. La siguiente consulta permitirá obtener información acerca del tamaño de la base de datos a consultar.


SELECT (SUM(reserved_page_count) * 8192) / 1024 / 1024 AS TamañoBBDDenMB
FROM    sys.dm_db_partition_stats

Consulta para obtener el tamaño de las tablas que componen la base de datos 

A continuación, se muestra la consulta que sería necesaria ejecutar para conocer el tamaño de las distintas tablas que componen la base de datos en MB ordenadas por su tamaño.


select sys.objects.name as 'NombreTabla', sum(reserved_page_count) * 8.0 / 1024 as 'Tamaño en MB'
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
Order By 'Tamaño en MB' DESC


Consulta para obtener el tamaño de las filas que componen las tablas así como el número de filas por tabla

Mediante la siguiente consulta se puede obtener el tamaño de cada fila de cada tabla así como el número de filas que contiene cada tabla.



select  sys.objects.name as 'NombreTabla', row_count as 'Número de filas', sum(reserved_page_count) * 8.0 / 1024 as 'Tamaño en MB', (sum(reserved_page_count) * 8.0 / 1024)/row_count as 'Tamaño por cada fila'
from  sys.dm_db_partition_stats, sys.objects
where sys.dm_db_partition_stats.object_id = sys.objects.object_id and row_count<>0
group by row_count,sys.objects.name


Consulta para obtener el tamaño de la base de datos así como el coste de la base de datos


Azure tarifica  las bases de datos en función del tamaño de las mismas. Para ello será muy importante poder conocer cual es el tamaño de las bases de datos. Esta información se puede obtener a través de la tabla sus.dm_db_partition_stats. 

Mediante el siguiente código de transact-SQL se puede obtener la información del tamaño de la base de datos así como su precio:

-- Declaración Variables
Declare @DBSize float;
Declare @Price float;
-- Calculo del total
SELECT @DBSize=(SUM(reserved_page_count) * 8192) / 1048576 FROM sys.dm_db_partition_stats;
-- Calculo del precio
SELECT @Price = (CASE
WHEN @DBSize/1024 < 1 THEN 7.44
WHEN @DBSize/1024 < 5 THEN 19.35
WHEN @DBSize/1024 < 10 THEN 34.23
WHEN @DBSize/1024 < 20 THEN 49.11
WHEN @DBSize/1024 < 30 THEN 63.99 
WHEN @DBSize/1024 < 40 THEN 78.86 
WHEN @DBSize/1024 < 50 THEN 93.74
WHEN @DBSize/1024 < 100 THEN 130.94
WHEN @DBSize/1024 < 150 THEN 168.14 
END)
-- Se devuelve el tamaño total de la base de datos así como el precio aproximado
Select @DBSize as 'Tamaño BBDD MB', @Price as ' Precio Total al Mes'

Destacar que será necesario adaptar los precios en función de la tarificación que efectúe Azure en el momento de ejecución del código. Para mas información acerca de los precios vease el siguiente enlace de Azure.

Consulta para obtener el tamaño de la base de datos así como el coste de las distintas tablas que la componen

Mediante el siguiente código Transac-SQL se puede obtener la información acerca del tamaño de la base de datos así como el precio de cada tabla que compone la base de datos.


-- Declaración Variables
Declare @DBSize float;
Declare @Costeporbit float;
-- Calculo del total
SELECT @DBSize=(SUM(reserved_page_count) * 8192)  FROM sys.dm_db_partition_stats;
-- Calculo del precio
SELECT    @Costeporbit = (CASE
    WHEN @DBSize/1073741824.0  < 1 THEN 7.44/1073741824.0
    WHEN @DBSize/1073741824.0  < 5 THEN 19.35/1073741824.0
    WHEN @DBSize/1073741824.0  < 10 THEN 34.23/1073741824.0
    WHEN @DBSize/1073741824.0  < 20 THEN 49.11/1073741824.0
    WHEN @DBSize/1073741824.0  < 30 THEN 63.99/1073741824.0          
    WHEN @DBSize/1073741824.0  < 40 THEN 78.86/1073741824.0            
    WHEN @DBSize/1073741824.0  < 50 THEN 93.74/1073741824.0
    WHEN @DBSize/1073741824.0  < 100 THEN 130.94/1073741824.0
    WHEN @DBSize/1073741824.0  < 150 THEN 168.14/1073741824.0                  
         END)
-- Se muestra consulta por cada tabla el numero total de bytes, el número total de filas, los bytes por filas así como coste mensual por tabla
SELECT  sys.objects.name as 'Tabla', sum(reserved_page_count) * 8192 'Bytes', row_count 'Número de Filas', (CASE row_count WHEN 0 THEN 0 ELSE (sum(reserved_page_count) * 8192)/ row_count END) 'Bytes por fila',
      (CASE row_count WHEN 0 THEN 0 ELSE ((sum(reserved_page_count))* 8192) * @Costeporbit END) 'Coste mensual por fila'
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, row_count
Order By 'Coste mensual por fila' DESC

Consulta para monitorizar las conexiones a la base de datos 

La tabla sus.dm_exec_sessions permite mostrar información acerca de la sesiones que se realizan a la base de datos. En el siguiente caso se obtendrá información de la conexión así como el nombre del usuario que realizó dicha conexión, el consumo de CPU y cuando se ejecuto la última consulta. 

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

Monitorización de consultas que mas CPU han consumido 

Mediante la siguiente consulta se podrá obtener información acerca de las consultas que mas CPU han consumido de la base de datos


SELECT TOP 5 query_stats.query_hash AS "Hash de la consulta", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Media de tiempo CPU", MIN(query_stats.statement_text) AS "Textp de la sentencia"
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;

Monitorización de consultas que mas CPU han consumido por acumulación

A través de la siguiente consulta se podrá obtener información acerca de aquellas consultas que mas CPU han consumido de la base de datos por acumulación.

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


Últimas consultas ejecutadas

A través de la siguiente consulta se podrá obtener información acerca de las últimas consultas ejecutadas.



SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC




Últimas 5 consultas ejecutadas con información adicional

A través de la siguiente consulta se podrá obtener información acerca de las últimas consultas ejecutadas así como las lecturas y escritura lógicas realizadas por las mismas.



SELECT TOP 5 query_plan,q2.[text],
  (total_logical_reads/execution_count) AS avg_logical_reads, 
  (total_logical_writes/execution_count) AS avg_logical_writes, 
  (total_physical_reads/execution_count) AS avg_phys_reads, 
  execution_count,
  (total_elapsed_time/execution_count) AS avg_Duration,
  last_execution_time
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS APPLY sys.dm_exec_sql_text(Sql_handle) AS q2 
ORDER BY last_execution_time DESC



Referencias

La información de este artículo ha sido obtenida y adaptada de Microsoft Technet