Как администратор баз данных SQL Server, управление пространством на жестком диске является обычной задачей. Важно определить таблицы и схемы, которые используют больше всего пространства, чтобы оптимизировать хранение и улучшить производительность. В этой статье мы рассмотрим, как выполнить запрос к системным каталогам SQL Server для получения информации об использовании пространства.

Шаг 1: Войдите в экземпляр SQL Server

Сначала войдите в экземпляр SQL Server, который вы хотите проанализировать. Это можно сделать с помощью SQL Server Management Studio или любого другого клиентского инструмента SQL Server.

Шаг 2: Перейдите к базе данных

После входа перейдите к конкретной базе данных, для которой вы хотите получить информацию о пространстве. Это можно сделать, выбрав базу данных из списка доступных баз данных.

Шаг 3: Выполните запрос

Скопируйте и вставьте следующий код в новое окно запроса и выполните его:

begin try
SELECT 
    (row_number() over(order by a3.name, a2.name))%2 as l1, 
    a3.name AS [schemaname], 
    a2.name AS [tablename], 
    a1.rows as row_count, 
    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
    a1.data * 8 AS data, 
    (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) – a1.data ELSE 0 END) * 8 AS index_size, 
    (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) – a1.used ELSE 0 END) * 8 AS unused 
FROM 
    (SELECT 
        ps.object_id, 
        SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], 
        SUM (ps.reserved_page_count) AS reserved, 
        SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, 
        SUM (ps.used_page_count) AS used 
    FROM 
        sys.dm_db_partition_stats ps 
    GROUP BY 
        ps.object_id) AS a1 
LEFT OUTER JOIN 
    (SELECT 
        it.parent_id, 
        SUM(ps.reserved_page_count) AS reserved, 
        SUM(ps.used_page_count) AS used 
    FROM 
        sys.dm_db_partition_stats ps 
    INNER JOIN 
        sys.internal_tables it ON (it.object_id = ps.object_id) 
    WHERE 
        it.internal_type IN (202,204) 
    GROUP BY 
        it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) 
INNER JOIN 
    sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN 
    sys.schemas a3 ON (a2.schema_id = a3.schema_id) 
WHERE 
    a2.type <> 'S' and a2.type <> 'IT' 
ORDER BY 
    a3.name, a2.name 
end try 
begin catch 
select 
    -100 as l1 , 
    1 as schemaname , 
    ERROR_NUMBER() as tablename , 
    ERROR_SEVERITY() as row_count , 
    ERROR_STATE() as reserved , 
    ERROR_MESSAGE() as data , 
    1 as index_size , 
    1 as unused 
end catch

Этот запрос извлечет информацию об использовании пространства для каждой таблицы в выбранной базе данных. Результат будет включать имя схемы, имя таблицы, количество строк, зарезервированное пространство, пространство данных, размер индекса и неиспользуемое пространство.

Изменение запроса для использования пространства схемы

Если вы хотите получить информацию об использовании пространства на уровне схемы, вы можете изменить запрос, используя функции SUM, COUNT и оператор GROUP BY. Скопируйте и вставьте следующий код в новое окно запроса и выполните его:

begin try
SELECT 
    –(row_number() over(order by a3.name, a2.name))%2 as l1, 
    a3.name AS [schemaname], 
    count(a2.name ) as NumberOftables, 
    sum(a1.rows) as row_count, 
    sum((a1.reserved + ISNULL(a4.reserved,0))* 8) AS reserved, 
    sum(a1.data * 8) AS data, 
    sum((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) – a1.data ELSE 0 END) * 8 )AS index_size, 
    sum((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) – a1.used ELSE 0 END) * 8) AS unused 
FROM 
    (SELECT 
        ps.object_id, 
        SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], 
        SUM (ps.reserved_page_count) AS reserved, 
        SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, 
        SUM (ps.used_page_count) AS used 
    FROM 
        sys.dm_db_partition_stats ps 
    GROUP BY 
        ps.object_id) AS a1 
LEFT OUTER JOIN 
    (SELECT 
        it.parent_id, 
        SUM(ps.reserved_page_count) AS reserved, 
        SUM(ps.used_page_count) AS used 
    FROM 
        sys.dm_db_partition_stats ps 
    INNER JOIN 
        sys.internal_tables it ON (it.object_id = ps.object_id) 
    WHERE 
        it.internal_type IN (202,204) 
    GROUP BY 
        it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) 
INNER JOIN 
    sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN 
    sys.schemas a3 ON (a2.schema_id = a3.schema_id) 
WHERE 
    a2.type <> 'S' and a2.type <> 'IT' 
GROUP BY 
    a3.name 
ORDER BY 
    a3.name 
end try 
begin catch 
select 
    -100 as l1 , 
    1 as schemaname , 
    ERROR_NUMBER() as tablename , 
    ERROR_SEVERITY() as row_count , 
    ERROR_STATE() as reserved , 
    ERROR_MESSAGE() as data , 
    1 as index_size , 
    1 as unused 
end catch

Этот измененный запрос извлечет информацию об использовании пространства на уровне схемы. Результат будет включать имя схемы, количество таблиц, общее количество строк, общее зарезервированное пространство, общее пространство данных, общий размер индекса и общее неиспользуемое пространство для каждой схемы.

Заключение

Путем выполнения запросов к системным каталогам SQL Server вы можете легко получить