Определение размера данных и индексов таблиц SQL Server

Быстрый ответ

Для определения размера таблиц и индексов в SQL Server можно использовать хранимую процедуру sp_spaceused. Ее выполнение для каждой таблицы поможет понять, сколько места они занимают:

EXEC sp_spaceused 'TableName'; -- Вместо 'TableName' введите название нужной вам таблицы.

Чтобы автоматизироватьт эту процедуру для всех таблиц, результаты можно записать во временную таблицу с помощью sp_MSforeachtable:

CREATE TABLE #SpaceSizes (name NVARCHAR(128), rows CHAR(11), reserved VARCHAR(18),
data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18));

INSERT INTO #SpaceSizes EXEC sp_MSforeachtable 'EXEC sp_spaceused "?"';

SELECT * FROM #SpaceSizes;

DROP TABLE #SpaceSizes; -- После завершения не забудьте удалить временную таблицу.

Данный скрипт соберёт размеры всех таблиц и индексов, после чего корректно удалит за собой временную таблицу.

Более глубокий анализ с использованием динамических управляемых представлений

Для более детального анализа можно использовать динамические управляемые представления (DMV), которые предоставят информацию о распределении данных и эффективности использования пространства:

SELECT 
    t.NAME AS TableName,
    SUM(s.used_page_count) * 8 AS UsedSpaceKB,
    (SUM(s.used_page_count) * 8) / 1024.0 AS UsedSpaceMB
FROM 
    sys.dm_db_partition_stats s
JOIN 
    sys.tables t ON s.object_id = t.object_id
WHERE 
    s.index_id IN (0, 1)
GROUP BY 
    t.NAME
ORDER BY 
    UsedSpaceMB DESC;

Этот запрос позволит определить размеры куч и кластеризованных индексов.

Разбор некластеризованных индексов

Некластеризованные индексы также занимают место и требуют дополнительной проверки:

SELECT 
    t.NAME AS TableName,
    i.NAME AS IndexName,
    SUM(s.used_page_count) * 8 AS IndexSpaceKB,
    (SUM(s.used_page_count) * 8) / 1024.0 AS IndexSpaceMB
FROM 
    sys.dm_db_partition_stats s
JOIN 
    sys.tables t ON s.object_id = t.object_id
JOIN 
    sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE 
    s.index_id > 1
GROUP BY 
    t.NAME, i.NAME
ORDER BY 
    IndexSpaceMB DESC;

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

Визуализация

Считайте вашу базу данных как склад, а таблицы и индексы — как товары на нем. Запросы, которые мы использовали, помогают измерить их размеры:

Склад (🗄️):  База данных
Товары (📦): Таблицы и индексы
Инструмент для измерения (📏): SQL-запрос

Пример запроса для оценки размера:

SELECT
    object_name(s.object_id) AS 📦,
    SUM(s.used_page_count) * 8 / 1024.0 AS 📏
FROM
    sys.dm_db_partition_stats s
GROUP BY
    s.object_id
HAVING
    SUM(s.used_page_count) > 0;

Визуализация данных:

Склад 🗄️:
| 📦 Таблица1 | = | 📏 100 МБ |
| 📦 Индекс1  | = | 📏 30 МБ  |
| 📦 Таблица2 | = | 📏 150 МБ |
| 📦 Индекс2  | = | 📏 45 МБ  |

Это помогает визуально оценить занимаемое место 'товаров' на 'складе' (базе данных).

Подробнее об этом расскажет наш спикер на видео

Ключевые моменты при измерении пространства

1. Неиспользованное пространство: Важно учитывать пространство, которое зарезервировано, но в данный момент не используется.
2. Внутренние объекты: Контролируйте внутренние таблицы, например, те, которые используются для XML или пространственных индексов.
3. Разделение таблиц: Если вы используете разделение на секции, необходимо корректировать запросы, учитывая их.
4. Снимки баз данных: Если вы работаете со снимками, размер отображает только изменения, сделанные после их создания.

Полезные материалы

1. sp_spaceused (Transact-SQL) – SQL Server | Microsoft Learn — Официальное руководство по sp_spaceused.
2. sql server – Get size of all tables in database – Stack Overflow — Обсуждение методов определения размера всех таблиц в SQL Server.
3. SQL Server Index Basics – Simple Talk — Статья о базовых принципах работы с индексами.
4. sp_BlitzIndex® – Sanity Test индексов SQL Server — Инструмент для анализа и оптимизации индексов.