Sp spaceused: примеры (SQL)

Анализ пространства баз данных с помощью sp_spaceused
Раздел: Системные административные функции, Метаданные
sp_spaceused([@objname =] 'objname'): N/A
типовые примерыsp spaceused на mySQL, Oracle, PostgreSQL, SQLitesp spaceused изменения sqlпримеры расширенные

Описание функции sp_spaceused

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

Процедура может быть выполнена без параметров для получения информации о текущей базе данных, либо с указанием конкретного объекта. Она возвращает два результирующих набора: первый содержит общие сведения, второй (при указании флага @updateusage) — детали по разным типам страниц.

Аргументы

  • @objname (nvarchar(776), по умолчанию NULL): Имя таблицы, индексированного представления или базы данных (если указано имя базы данных, требуется быть в контексте master). Если NULL, возвращаются данные для текущей базы данных.
  • @updateusage (varchar(5), по умолчанию NULL): Если указано 'TRUE' или 'YES', SQL Server обновляет устаревшие данные в системных представлениях (выполняет DBCC UPDATEUSAGE). Это может занять время для больших объектов.
  • @mode (varchar(20), по умолчанию NULL): Введен в SQL Server 2016. Определяет тип выходных данных. NULL или 'LIMITED' — возвращает базовый набор столбцов. 'DETAILED' — возвращает дополнительные столбцы: index_id, index_type, allocated, used, data.
  • @oneresultset (bit, по умолчанию 0): Введен в SQL Server 2016. Если установлен в 1, возвращает один результирующий набор, объединяя информацию из двух стандартных наборов.

Возвращаемые значения (стандартный вывод)

Первый набор (базовый): database_name, database_size, unallocated space.
Второй набор (резервный): reserved, data, index_size, unused. При использовании @mode='DETAILED' структура меняется.

Примеры использования

Пример 1: Пространство для текущей базы данных.

EXEC sp_spaceused;
database_name    database_size  unallocated space
--------------   -------------  -----------------
MyDatabase       10240.00 MB    512.00 MB

reserved          data           index_size      unused
-------------     -------------  -------------   ------------
8500 KB           5200 KB        3000 KB         300 KB

Пример 2: Пространство для конкретной таблицы с обновлением статистики.

EXEC sp_spaceused @objname = N'Sales.Orders', @updateusage = 'TRUE';
name        rows        reserved    data        index_size  unused
----------  ----------  ----------  ----------  ----------  ----------
Orders      15245       10240 KB    6144 KB     4096 KB     0 KB

Пример 3: Использование режима DETAILED для таблицы.

EXEC sp_spaceused @objname = N'Products', @mode = 'DETAILED';
index_id  index_type                 allocated    used        data
--------  -------------------------  -----------  ----------  ----------
0         HEAP                       1024 KB      768 KB      768 KB
1         CLUSTERED INDEX            2048 KB      1536 KB     1536 KB
2         NONCLUSTERED INDEX         512 KB       384 KB      0 KB

Пример 4: Использование параметра @oneresultset.

EXEC sp_spaceused @objname = NULL, @oneresultset = 1;
database_name  database_size  unallocated  reserved  data  index_size  unused
-------------  -------------  -----------  --------  ----  ----------  ------
MyDatabase     10240.00 MB    512.00 MB    8500 KB  5200  3000 KB     300 KB

Похожие функции в MS SQL

Для анализа пространства в SQL Server также применяют другие инструменты:

  • sys.dm_db_partition_stats: Динамическое административное представление (DMV) предоставляет наиболее детальную и актуальную страничную статистику по всем разделам. Позволяет строить гибкие запросы, но требует более сложного синтаксиса.
  • sys.allocation_units и sys.partitions: Системные представления, на основе которых строятся многие отчеты о пространстве. Дают низкоуровневую информацию о типах страниц (IN_ROW_DATA, ROW_OVERFLOW_DATA, LOB_DATA).
  • DBCC SQLPERF('logspace'): Команда для получения информации о размере и использовании журнала транзакций для всех баз данных на экземпляре.

sp_spaceused удобна для быстрого получения сводного отчета. DMV sys.dm_db_partition_stats предпочтительнее для сложных, автоматизированных сценариев мониторинга или детального анализа.

Аналоги в других СУБД

MySQL / MariaDB: Аналогом по смыслу является запрос к информационной схеме INFORMATION_SCHEMA.TABLES для получения размера таблиц.

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'my_database';
TABLE_SCHEMA  TABLE_NAME  Size (MB)
------------  ----------  ----------
my_database   users       5.33
my_database   orders      12.75

PostgreSQL: Используют функции pg_database_size() для базы и pg_total_relation_size() для таблиц, часто вместе с pg_size_pretty().

SELECT
  pg_size_pretty(pg_total_relation_size('public.orders')) AS total_size,
  pg_size_pretty(pg_relation_size('public.orders')) AS data_size,
  pg_size_pretty(pg_indexes_size('public.orders')) AS index_size;
total_size  data_size  index_size
----------  ---------  ----------
328 kB      184 kB     144 kB

Oracle: Запросы к представлениям DBA_SEGMENTS, USER_SEGMENTS или DBA_DATA_FILES. Сводную информацию можно получить через DBMS_SPACE.

SELECT SEGMENT_NAME, BYTES/1024/1024 AS SIZE_MB
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE';
SEGMENT_NAME  SIZE_MB
------------  -------
EMPLOYEES     10
DEPARTMENTS   2

SQLite: Прямого аналога нет. Размер файла базы данных можно получить средствами ОС. Для анализа страниц используется pragma page_count и page_size.

PRAGMA page_size;
PRAGMA page_count;
SELECT page_size * page_count / 1024 AS size_kb FROM pragma_page_count, pragma_page_size;
page_size
---------
4096

page_count
----------
125

size_kb
-------
500

В отличие от sp_spaceused, большинство аналогов в других СУБД — это запросы к системным представлениям, а не отдельные процедуры.

Типичные ошибки

Ошибка 1: Указание имени базы данных без контекста master.

USE MyDatabase;
EXEC sp_spaceused @objname = 'AnotherDatabase'; -- Ошибка
Msg 15007, Level 16, State 1, Procedure sp_spaceused, Line 91
'AnotherDatabase' is not a valid object in this database.

Для анализа другой базы данных нужно выполнить процедуру из master.

USE master;
EXEC sp_spaceused @objname = 'AnotherDatabase'; -- Корректно

Ошибка 2: Неверное имя объекта или отсутствие разрешений.

EXEC sp_spaceused @objname = 'NonExistentTable';
Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 94
The object 'NonExistentTable' does not exist in database 'MyDatabase' or is invalid for this operation.

Ошибка 3: Использование устаревшего синтаксиса без именованных параметров с новыми флагами.

EXEC sp_spaceused @objname, 'TRUE', 'DETAILED'; -- Неоднозначно

Рекомендуется использовать явные имена параметров при работе с @mode и @oneresultset.

История изменений

  • SQL Server 2016 (13.x): Добавлены параметры @mode и @oneresultset. Режим DETAILED предоставляет разбивку по индексам. Параметр @oneresultset объединяет вывод в один набор для удобства программирования.
  • SQL Server 2005 (9.x): Процедура была значительно обновлена для поддержки секционирования и новых типов данных. Поведение и точность расчета были улучшены.

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

Расширенные примеры

Пример 1: Сбор статистики по всем таблицам в базе данных с помощью курсора.

Пример sql
CREATE TABLE #TableSizes (TableName sysname, Rows bigint, Reserved varchar(50), Data varchar(50), IndexSize varchar(50), Unused varchar(50));
DECLARE @TableName sysname;
DECLARE cur CURSOR FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
OPEN cur;
FETCH NEXT FROM cur INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #TableSizes
    EXEC sp_spaceused @objname = @TableName;
    FETCH NEXT FROM cur INTO @TableName;
END
CLOSE cur;
DEALLOCATE cur;
SELECT * FROM #TableSizes ORDER BY TableName;
DROP TABLE #TableSizes;

Пример 2: Использование @oneresultset для вставки результата в таблицу.

Пример sql
CREATE TABLE #DbStats (database_name sysname, database_size varchar(50), unallocated varchar(50), reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50));
INSERT INTO #DbStats
EXEC sp_spaceused @oneresultset = 1;
SELECT * FROM #DbStats;
DROP TABLE #DbStats;

Пример 3: Анализ пространства для индексированного представления.

Пример sql
EXEC sp_spaceused @objname = 'dbo.vw_IndexedView';
name            rows    reserved    data        index_size  unused
--------------  ------  ----------  ----------  ----------  ----------
vw_IndexedView  5000    2048 KB     1024 KB     1024 KB     0 KB

Пример 4: Сравнение DETAILED режима для таблицы с кластеризованным и некластеризованными индексами.

Пример sql
EXEC sp_spaceused @objname = 'BigTable', @mode = 'DETAILED', @oneresultset = 1;
database_name  index_id  index_type          allocated  used    data
-------------  --------  ------------------  ---------  ------  ------
NULL           0         HEAP                0 KB       0 KB    0 KB
NULL           1         CLUSTERED INDEX     10240 MB   8192 MB 8192 MB
NULL           2         NONCLUSTERED INDEX  2048 MB    1536 MB 0 KB
NULL           3         NONCLUSTERED INDEX  512 MB     384 MB  0 KB

Пример 5: Комбинирование с DBCC UPDATEUSAGE для исправления несоответствий перед анализом.

Пример sql
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS;
EXEC sp_spaceused;

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

MS SQL sp_spaceused function comments

En
Sp spaceused Displays the number of rows, disk space reserved, and disk space used by a table or database