Как узнать размер таблиц базы данных MS SQL SERVER

2022-03-14 10:50:00

Данная статья запланирована как первый блок информации по переносу таблиц/индексов в другие файловые группы. Перенести таблицы может понадобиться, например, для ускорения запросов(таблица на один диск, индексы на другой) или если вся база не помещается на один раздел.

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

Запланировано использовать два способа определения объёма таблиц, которые выявляют количество строк(записей) каждой таблицы, также размер таблиц в килобайтах. Почему именно в килобайтах, хотя бы потому, что один из способов выдаёт результат только в килобайтах, во втором способе можно более гибко настроить результат.

Использование процедуры sp_spaceused;
Использование системных таблиц: sys.tables, sys.schemas, sys.partitions, sys.allocation_units.

Процедура sp_spaceused: ниже приведён синтаксис взятый из официального источника:


sp_spaceused [ [ @objname = ] 'objname' ]   
[ , [ @updateusage = ] 'updateusage' ]  
[ , [ @mode = ] 'mode' ]  
[ , [ @oneresultset = ] oneresultset ]  
[ , [ @include_total_xtp_storage = ] include_total_xtp_storage ]

Для данной задачи понадобится только один параметр - @objname, более детально с данной процедурой можно ознакомиться на официальной странице

Рассмотрим базу 1с и таблицу Config, т.к. данная таблица пустой не бывает:


use db_for_1c
go
sp_spaceused Config;
go
sp_spaceused 'Config';
go
exec sp_spaceused 'Config';
go
sp_spaceused 'dbo.Config';
go
sp_spaceused '[dbo].[Config]';
go
sp_spaceused @objname='[dbo].[Config]';

выше приведён пример использования sp_spaceused процедуры и несколько вариантов синтаксиса. Если таблицы созданы по правилам, т.е. имя не начинается с цифры, не используются пробел и другие специальные символы в имени таблицы, то можно в кавычки или в квадраные скобки не заключать имя таблицы. В том случае, если используется база 1с, то схема всегда будет по умолчанию - dbo и её можно тоже не использовать в имени таблицы. Параметр @objname использовать не обязательно. Ниже на скрине результат запуска всех шести вариантов данной процедуры и все они отработали должным образом:

Простое использование sp_spaceused

где

rows - количество строк(записей) в таблице,

reserved - количество килобайт забронированное данной таблицей,

data - количество килобайт используемое данными,

index_size - количество килобайт используемое индексами,

unused - количество килобайт забронированное таблицей, но пока данное пространство не заполнено данными.

На первый взгляд очень удобный способ, но что если база НЕ 1с и в этой базе активно используются схемы отличные от dbo? Допустим в базе присутствует несколько схем и в данных схемах есть таблица с одинаковым названием, попытаеся определить размер таблицы, в данном случае таблица [test]:


USE TSQLV4
go
sp_spaceused 'test'

обратите внимание на результат выполнения процедуры и на две таблицы с одинаковыми именами, но находящихся в разных схемах: dbo и Stats:

Путаница с таблицами при использовании sp_spaceused

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


USE TSQLV4
go
sp_spaceused 'dbo.test'
go
sp_spaceused 'Stats.test'

результат ниже на скрине:

Использование схем в именах таблиц

всё же в колонке name не отображается схема, в которой находится таблица - это очень не удобно!!!

Чтобы посмотреть размер всех таблицы базы, то неоходимо использовать либо CURSOR - использование данного способа нежелательно, т.к. работает довольно медленно и второй вариант - динамический SQL:

CURSOR


USE [db_for_1c]
go

IF OBJECT_ID('tempdb..#result', 'U') IS NOT NULL
DROP TABLE #result;

CREATE TABLE #result
(
 ID INT IDENTITY(1, 1)
 , name NVARCHAR(255)
 , rows BIGINT
 , reserved NVARCHAR(255)
 , data NVARCHAR(255)
 , index_size NVARCHAR(255)
 , unused NVARCHAR(255)
)

DECLARE
	@name  NVARCHAR(255),
	@sql NVARCHAR(MAX)

DECLARE C CURSOR FAST_FORWARD READ_ONLY FORWARD_ONLY FOR
	SELECT 
	 s.name+'.' +t.name AS name
	FROM sys.tables AS t
		JOIN sys.schemas AS s ON t.schema_id=s.schema_id
	ORDER BY name

	OPEN C;

	FETCH NEXT FROM C INTO @name

	WHILE @@FETCH_STATUS = 0
	BEGIN
	
		SET @sql = 'INSERT INTO #result EXEC sp_spaceused ''' + @name + '''';

		EXEC (@sql);

		FETCH NEXT FROM C INTO @name

	END

CLOSE C;
DEALLOCATE C;

SELECT * FROM #result
ORDER BY rows DESC

результат работы скрипта с использованием курсора:

Использование CURSOR к базе 1с

динамический SQL:


USE db_for_1c
go

IF OBJECT_ID('tempdb..#result', 'U') IS NOT NULL
DROP TABLE #result;

CREATE TABLE #result
(
 ID INT IDENTITY(1, 1)
 , name NVARCHAR(255)
 , rows BIGINT
 , reserved NVARCHAR(255)
 , data NVARCHAR(255)
 , index_size NVARCHAR(255)
 , unused NVARCHAR(255)
)

DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL+='EXEC sp_spaceused '''+S.name+'.'+T.name+''';' 
FROM sys.tables AS T
	INNER JOIN sys.schemas AS S ON S.schema_id=T.schema_id

INSERT INTO #result
EXEC (@SQL);

SELECT * FROM #result
ORDER BY rows DESC

результат динамического SQL:

Динамический SQL

Выводы:

В обоих примерах: cursor и динамический SQL присутствует динамика, но во втором примере динамического SQL больше;
Процедуру sp_spaceused лучше использовать в базах 1с или любых других, в которых используется только одна схема, иначе можено запутаться, т.к. данная процедура в результирующем наборе не сообщает в какой схеме находится та или иная таблица даже если на вход подаётся таблица с указанием схемы.

Перейдём к использванию системных таблиц: sys.tables, sys.schemas, sys.partitions, sys.allocation_units:

Данное решение взято с официальной документации


USE db_for_1c
go
SELECT t.object_id AS ObjectID,
       OBJECT_NAME(t.object_id) AS ObjectName,
       SUM(u.total_pages) * 8 AS Total_Reserved_kb,
       SUM(u.used_pages) * 8 AS Used_Space_kb,
       u.type_desc AS TypeDesc,
       MAX(p.rows) AS RowsCount
FROM sys.allocation_units AS u
JOIN sys.partitions AS p ON u.container_id = p.hobt_id
JOIN sys.tables AS t ON p.object_id = t.object_id
GROUP BY t.object_id,
         OBJECT_NAME(t.object_id),
         u.type_desc
ORDER BY Used_Space_kb DESC,
         ObjectName;

результат ниже на скрине:

Результат использования системных таблица

немного изменю запрос: добавлю таблицу sys.schemas:


use TSQLV4
go
SELECT t.object_id AS ObjectID,
		s.name + '.' + 
       +OBJECT_NAME(t.object_id) AS ObjectName,
       SUM(u.total_pages) * 8 AS Total_Reserved_kb,
       SUM(u.used_pages) * 8 AS Used_Space_kb,
       u.type_desc AS TypeDesc,
       MAX(p.rows) AS RowsCount
FROM sys.allocation_units AS u
JOIN sys.partitions AS p ON u.container_id = p.hobt_id
JOIN sys.tables AS t ON p.object_id = t.object_id
JOIN sys.schemas AS s ON t.schema_id=s.schema_id
GROUP BY t.object_id,
         OBJECT_NAME(t.object_id)
		 , u.type_desc
		 , s.name
ORDER BY Used_Space_kb DESC,
         ObjectName;

в изменённом запросе ObjectName теперь состоит не только из имени но и из схемы в качестве префикса разделённого точкой, см. скрин ниже:

Доработанный запрос из системных таблиц

и ещё один результат, но уже к базе 1с:

Информация из системных таблиц к базе 1с

Ну что ж, подведём итог:

В общем и целом выгоднее использовать выборку(SELECT) из системных таблиц, т.к. она выдаёт больше информации, есть возможность для масштабирования путём присоединения ещё каких-нибудь таблиц если понадобится, выполняется быстрее чем динамический SQL с использованием процедуры sp_spaceused не говоря о курсоре.

Но всё же процедура sp_spaceused очень проста в использовании при оценке количества строк и т.д. для конкретной таблицы, т.к. кода писать приходится меньше.

Все решения описанные выше были проверены на версиях: MS SQL 2008 R2 и MS SQL Server 2019.

Предполагаю что на версиях MS SQL Server 2012, MS SQL Server 2014, MS SQL Server 2016, MS SQL Server 2017 тоже буду работать.

Скачать скрипты в архиве.