Определение размера данных и индексов таблиц 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; -- После завершения не забудьте удалить временную таблицу.
Данный скрипт соберёт размеры всех таблиц и индексов, после чего корректно удалит за собой временную таблицу.
[AsideBanner]
Более глубокий анализ с использованием динамических управляемых представлений
Для более детального анализа можно использовать динамические управляемые представления (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 МБ |
Это помогает визуально оценить занимаемое место 'товаров' на 'складе' (базе данных).
Ключевые моменты при измерении пространства
- Неиспользованное пространство: Важно учитывать пространство, которое зарезервировано, но в данный момент не используется.
- Внутренние объекты: Контролируйте внутренние таблицы, например, те, которые используются для XML или пространственных индексов.
- Разделение таблиц: Если вы используете разделение на секции, необходимо корректировать запросы, учитывая их.
- Снимки баз данных: Если вы работаете со снимками, размер отображает только изменения, сделанные после их создания.
Полезные материалы
- sp_spaceused (Transact-SQL) – SQL Server | Microsoft Learn — Официальное руководство по sp_spaceused.
- sql server – Get size of all tables in database – Stack Overflow — Обсуждение методов определения размера всех таблиц в SQL Server.
- SQL Server Index Basics – Simple Talk — Статья о базовых принципах работы с индексами.
- sp_BlitzIndex® – Sanity Test индексов SQL Server — Инструмент для анализа и оптимизации индексов.