¿Cuánto ocupan mis tablas y mis índices en Sql Azure (y no Azure)?

El tamaño de la base de datos, siempre es importante, en Sql Azure más si cabe, pues afecta directamente a nuestro bolsillo.

Hace bastante tiempo publiqué un script, que se volvió bastante popular, que permite responder la pregunta ¿Cuanto ocupan mis tablas y mis vistas indexadas en Sql Server?, este script hace uso del procedimiento almacenado del sistema sp_spaceused que por desgracia no está disponible en SQL Azure, haciendo que el script anterior no funcione. Así que me he puesto manos a la obra y he hecho una versión similar que si que funciona en SQL Azure (por su puesto en un SQL Server ‘on premise’).

SELECT   

      sys.objects.name AS Name, SUM(reserved_page_count) * 8.0 / 1024 AS [Reserved in MB], SUM(used_page_count) * 8.0 / 1024 AS [Used in MB], MAX(row_count) AS [Number of rows]

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

UNION ALL

SELECT 

    sys.indexes.name AS Name, SUM(reserved_page_count) * 8.0 /1024 AS [Reserved in MB], SUM(used_page_count) * 8.0 / 1024 AS [Used in MB], MAX(row_count) AS [Number of rows]

FROM

    sys.dm_db_partition_stats, sys.indexes

WHERE

    sys.dm_db_partition_stats.object_id = sys.indexes.object_id

    AND sys.dm_db_partition_stats.index_id = sys.indexes.index_id

    AND sys.dm_db_partition_stats.index_id > 0

GROUP BY sys.indexes.name

ORDER BY 2 DESC

Espero que os sea útil, un saludo.

Consultas para mejorar el rendimiento de tu SQL Azure (y no Azure)

Cuando trabajamos contra un SQL Server ‘on premise’ contamos con un motón de herramientas para averiguar que consultas están siendo más costosas y que índices podrían ayudarnos a mejorar el rendimiento de nuestra base de datos. Cuando trabajamos con SQL Azure, contamos un arsenal mucho más reducido de armas. Hoy quiero compartir dos consultas que me han sido de suma utilidad a la hora de mejorar el rendimiento de un importante proyecto que estamos realizando en Plain Concepts con SQL Azure.

La primera es una consulta que nos permite saber, a partir de la información que SQL Server almacena sobre los planes de ejecución qué consultas están siendo más costosas. Esta consulta extrae información de la vista dm_exec_query_stats que almacena estadísticas sobre la ejecución de consultas. En este caso la consulta muestra, por este orden, el texto de la consulta, el tiempo total consumido por todas las ejecuciones de cada consulta, el máximo tiempo que ha tardado en ejecutarse esa consultas, el tiempo mínimo que ha tardado alguna ejecución, el tiempo que ha tardado la última ejecución, el número de veces que se ha ejecutado la consulta, y la hora de la última ejecución. Todo esto ordenado por el tiempo total consumido por la consulta. Esta consulta nos permite ver de manera rápida que consultas están siendo más costosas para nuestro SQL Azure.

SELECT TOP (20)

    q.text, s.total_elapsed_time, s.max_elapsed_time, s.min_elapsed_time,

    s.last_elapsed_time, s.execution_count, last_execution_time, *

FROM sys.dm_exec_query_stats as s

      cross apply sys.dm_exec_sql_text(plan_handle) AS q

WHERE s.last_execution_time > DateAdd(mi , 1500 , GetDate()) — solo las que se han ejecutado recientemente

AND text not like ‘%sys.%’ — eliminar esta propia consulta

ORDER BY s.total_elapsed_time DESC

La vista en la que se basa esta consulta, dm_exec_query_stats , contiene un motón de campos interesantes con información que os invito a explorar.

La segunda consulta que me ha sido de gran utilidad permite saber qué índices podrían ser beneficiosos en nuestra base de datos y cual sería en impacto (entendido como la mejora esperable si se crea el índice). Se basa en una capacidad de SQL Server llamada ‘missing indexes’.

SELECT TOP (20)

    CAST(REPLACE(CAST(qp.query_plan AS NVARCHAR(MAX)),

    ‘xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"’,») AS XML),

    qp.query_plan.value(

        ‘declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]’ , ‘decimal(18,4)’) * execution_count AS TotalImpact

FROM

    sys.dm_exec_query_stats qs

    cross apply sys.dm_exec_sql_text(sql_handle) st

    cross apply sys.dm_exec_query_plan(plan_handle) qp

WHERE

    qp.query_plan.exist(

        ‘declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex[@Database!="m"]’) = 1

ORDER BY TotalImpact DESC

Estas consultas son perfectamente validas para un SQL Server ‘on premise’, pero resultan mucho más útiles cuando estamos trabajando contra un SQL Azure, pues carecemos de otras herramientas.