Как узнать размер таблицы ms sql

Оценка размера таблицы

Оценить пространство, необходимое для хранения данных в таблице, можно с помощью следующих шагов:

1. Рассчитайте пространство, необходимое для кучи или кластеризованного индекса, с помощью инструкций в статьях Оценка размера кучи и Оценка размера кластеризованного индекса.
2. Вычислите необходимое пространство для каждого некластеризованного индекса с помощью инструкций в статье Оценка размера некластеризованного индекса.
3. Сложите значения, рассчитанные на шаге 1 и 2.

См. также

Дополнительные ресурсы

Значок отказа согласно Закону Калифорнии о защите конфиденциальности потребителей (CCPA)

• Светлая
• Темная
• Высокая контрастность

• Предыдущие версии
• Блог
• Участие в доработке
• Конфиденциальность
• Условия использования
• Товарные знаки
• © Microsoft 2023

Дополнительные ресурсы

Значок отказа согласно Закону Калифорнии о защите конфиденциальности потребителей (CCPA)

• Светлая
• Темная
• Высокая контрастность

• Предыдущие версии
• Блог
• Участие в доработке
• Конфиденциальность
• Условия использования
• Товарные знаки
• © Microsoft 2023

SQL: Как определить размер таблиц в БД MS SQL

Для определения размера таблиц в базе данных, размещенной на сервере Microsoft SQL Server, требуется выполнить следующие действия:
1. Подключиться к серверу баз данных, при помощи SQL Server Management Studio (SSMS)
2. Выбрать базу данных, размер таблиц которой необходимо определить
3. Выполнить SQL запрос:

USE ; GO SELECT t.Name AS TableName, s.Name AS SchemaName, p.Rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.Name NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.object_id > 255 GROUP BY t.Name, s.Name, p.Rows ORDER BY t.Name; GO

где, «database_name» — имя базы данных, для которой необходимо получить список таблиц с размерами.
Размер таблиц базы данных будет указан в Килобайтах

Если необходимо получить ограниченных список таблиц, например, содержащих определенные слова в названии, то можно сократить вывод добавив условие (t.Name Like ‘%Filter%’) в конструкцию WHERE

WHERE t.Name NOT LIKE 'dt%' AND t.Name Like '%Filter%' AND t.is_ms_shipped = 0 AND i.object_id > 255

где, Filter — это подстрока в названии таблицы

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

Хранимая процедура (stored procedure) sp_spaceused выводит количество строк, зарезервированное место на диске и место на диске, которое используется таблицей, индексированным представлением или очередью компонента Service Broker в текущей базе данных, либо выводит место на диске, зарезервированное и используемое всей базой данных.

Ниже показан пример, в котором предоставляются сведения о занимаемом месте на диске для таблицы table_name и ее индексах в базе данных database_name, используя хранимую процедуру sp_spaceused:

USE ; GO EXEC sp_spaceused N'.'; GO 

Ниже показан пример, в котором предоставляются сведения о занимаемом месте на диске для всех таблиц и ее индексах в базе данных database_name.

USE ; GO sp_msforeachtable N'EXEC sp_spaceused [?]'; GO

Определить дисковое пространство используемое индексами

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

USE ; GO SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, i.index_id AS IndexID, 8 * SUM(a.used_pages) AS 'Indexsize(KB)' FROM sys.indexes AS i JOIN sys.partitions AS p ON p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.allocation_units AS a ON a.container_id = p.partition_id GROUP BY i.OBJECT_ID, i.index_id, i.name ORDER BY OBJECT_NAME(i.object_id), i.index_id

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

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

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

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

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

1. Использование процедуры sp_spaceused;
2. Использование системных таблиц: 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 использовать не обязательно. Ниже на скрине результат запуска всех шести вариантов данной процедуры и все они отработали должным образом:

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

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

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

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

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

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

 USE TSQLV4 go sp_spaceused 'test' 

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

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

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

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

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

Чтобы посмотреть размер всех таблицы базы, то неоходимо использовать либо CURSOR — использование данного способа нежелательно, т.к. работает довольно медленно и второй вариант — динамический 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 @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 

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

 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:

1. В обоих примерах: cursor и динамический SQL присутствует динамика, но во втором примере динамического SQL больше;
2. Процедуру 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с:

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

В общем и целом выгоднее использовать выборку(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 тоже буду работать.

Как вычислить размер таблицы в базе данных?

В приложение необходимо реализовать удаление данных из базы данных за определенные промежуток времени. При этом необходимо показывать сколько данных удалиться, например, если пользователь удалить данные за предыдущий месяц то освободиться Х Кб места.
Нашел множество Sql запросов считающих объем всей базы и конкретной таблицы. Но если посчитать размер все базы, и каждой таблицы в отдельности они не совпадают.

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

• Вопрос задан более трёх лет назад
• 11552 просмотра

Комментировать

Решения вопроса 0

Ответы на вопрос 1

MS SQL Server Developer/Administrator

Ответ написан более трёх лет назад

Комментировать

Нравится Комментировать

Ваш ответ на вопрос

Войдите, чтобы написать ответ

• SQL Server

Не устанавливается Express, как исправить?

• 1 подписчик
• 7 часов назад
• 37 просмотров

• C#
• +2 ещё

Как исправить ошибку подключения к sql в visial studio?

• 1 подписчик
• 19 дек. 2023
• 93 просмотра