LLI Wiki — My Digital Knowledge Base

Azure SQL : taille des bases et des pools élastiques (T-SQL)

Requêtes T-SQL pour lister les bases par pool élastique et mesurer le stockage utilisé, alloué et libre.

Requêtes pour lister les bases d'un serveur logique Azure SQL Database par pool élastique et mesurer le stockage des bases et des pools. À exécuter dans la base master du serveur logique, où sys.resource_stats est disponible.

Bases de données par pool élastique

SELECT
       @@SERVERNAME as [ServerName],
       dso.elastic_pool_name,
       d.name as DatabaseName,
       dso.edition
FROM
       sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master'
ORDER BY
       d.name, dso.elastic_pool_name

Pool d'une seule base :

SELECT
       @@SERVERNAME as [ServerName],
       dso.elastic_pool_name,
       d.name as DatabaseName
FROM
       sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master' and d.name = 'AppDb'
ORDER BY
       dso.elastic_pool_name

Statistiques actuelles d'un pool élastique

SELECT
    elastic_pool_name,
    *
FROM
    sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'pool01'

Espace de données utilisé par un pool, en Mo (dernier échantillon) :

SELECT TOP 1 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'pool01'
ORDER BY end_time DESC

Même calcul avec le pourcentage de stockage alloué :

SELECT TOP 1 avg_allocated_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'pool01'
ORDER BY end_time DESC

Dernière taille de chaque pool :

WITH RankedElasticPoolStats AS (
    SELECT
        elastic_pool_name,
        avg_allocated_storage_percent,
        elastic_pool_storage_limit_mb,
        ROW_NUMBER() OVER (PARTITION BY elastic_pool_name ORDER BY end_time DESC) AS rn
    FROM
        sys.elastic_pool_resource_stats
)
SELECT
    elastic_pool_name,
    avg_allocated_storage_percent,
    elastic_pool_storage_limit_mb,
    avg_allocated_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM
    RankedElasticPoolStats
WHERE
    rn = 1
ORDER BY
    elastic_pool_name;

Taille des bases

Historique d'une base :

USE master;
GO

SELECT
    database_name,
    storage_in_megabytes AS DatabaseDataSpaceUsedInMB, *
FROM sys.resource_stats
WHERE database_name = 'AppDb'
ORDER BY end_time DESC

Dernière taille utilisée de toutes les bases :

with RankedDatabaseSize as (
    SELECT
        database_name,
        storage_in_megabytes AS DatabaseDataSpaceUsedInMB,
        ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY end_time DESC) AS rn
    FROM sys.resource_stats
)
select *
FROM RankedDatabaseSize
where rn = 1
order by DatabaseDataSpaceUsedInMB desc

Dernière taille allouée de toutes les bases :

with RankedDatabaseSize as (
    SELECT
        database_name,
        allocated_storage_in_megabytes AS DatabaseDataSpaceAllocatedInMB,
        ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY end_time DESC) AS rn
    FROM sys.resource_stats
)
select *
FROM RankedDatabaseSize
where rn = 1
order by DatabaseDataSpaceAllocatedInMB desc

Vue combinée : espace libre du pool et plus grosses bases

Pour chaque pool, cette requête additionne la taille allouée de ses deux plus grosses bases (en Go, min_need_space) et affiche l'espace libre restant dans le pool (en Go). Elle permet de vérifier qu'un pool a assez de place, par exemple avant de déplacer ou d'agrandir des bases.

with linkdbep as
(
    SELECT
       @@SERVERNAME as [ServerName],
       dso.elastic_pool_name,
       d.name as DatabaseName
    FROM
       sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
    WHERE d.Name <> 'master'
),
RankedDatabaseSize as (
    SELECT
        database_name,
        allocated_storage_in_megabytes AS DatabaseDataSpaceAllocatedInMB,
        ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY end_time DESC) AS rn
    FROM sys.resource_stats
),
RankedElasticPoolStats AS (
    SELECT
        elastic_pool_name,
        avg_allocated_storage_percent,
        elastic_pool_storage_limit_mb,
        ROW_NUMBER() OVER (PARTITION BY elastic_pool_name ORDER BY end_time DESC) AS rn
    FROM
        sys.elastic_pool_resource_stats
),
linkdata as (
    select kde.ServerName
        , rds.DatabaseDataSpaceAllocatedInMB
        , kde.elastic_pool_name
        , DatabaseName, reps.avg_allocated_storage_percent / 100.0 * reps.elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
        , elastic_pool_storage_limit_mb
        , elastic_pool_storage_limit_mb - ( reps.avg_allocated_storage_percent / 100.0 * reps.elastic_pool_storage_limit_mb) as elastic_pool_storage_free_mb
    from linkdbep kde
        inner join RankedDatabaseSize rds on kde.DatabaseName = rds.database_name
        inner join RankedElasticPoolStats reps on reps.elastic_pool_name = kde.elastic_pool_name
    where rds.rn = 1
        and reps.rn = 1
), computecte as (
    select
     ROW_NUMBER() OVER (PARTITION BY elastic_pool_name ORDER BY DatabaseDataSpaceAllocatedInMB DESC) AS rn,
     ServerName,
     elastic_pool_name,
     DatabaseDataSpaceAllocatedInMB,
     elastic_pool_storage_free_mb
    from
        linkdata
)
select elastic_pool_name, sum(DatabaseDataSpaceAllocatedInMB) / 1024 as min_need_space, max(elastic_pool_storage_free_mb) / 1024 as elastic_pool_storage_free_mb
from computecte
where rn <= 2
group by elastic_pool_name

Remarques

Mastodon LLIMastodon SocialFosstodon