Crónicas de un informático

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.