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

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

03 апрель 2019, Среда

47 165

3

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

USE {database_name};
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 {database_name};  
GO  
EXEC sp_spaceused N'{dbo}.{table_name}';  
GO  

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

USE {database_name};  
GO  
sp_msforeachtable N'EXEC sp_spaceused [?]';  
GO

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

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

USE {database_name};  
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 отсортированный по убыванию: speshuric — LiveJournal

Размер таблиц в БД MS SQL SERVER отсортированный по убыванию: speshuric — LiveJournal

?

Когда-то я это написал, но один раз я этот скрипт терял. Недавно нашёл его. В силу лени больше не хочу терять.

select
case when grouping (so. name) = 1 then 'Итого:' else so.name end name,
8*sum(si.data_pages) data, 
8*sum(si.index_pages) indexes, 
8*(sum(si.data_pages)+sum(si.index_pages)) q, 
case when grouping (so.name) = 0 then max (si.rows) else 0 end rows, 
count (is_index) index_count, 
case when grouping (so.name) = 0 then min (isnull(date_recount,getdate())) else null end date_recount_stat,
case when grouping (so.name) = 0 then max (reclen) else 0 end record_len
from
(select 
        case 
                when si.indid between 2 and 251 then isnull(si.used,0)
                else 0
        end index_pages,
        case 
                when si.indid<2 then si.dpages 
                when si.indid = 255 then isnull(si.used,0)
                else 0
        end data_pages,
        case    when si.indid < 2 then si.rows 
                when si.indid between 2 and 251 then 0
                else 0
        end rows,
        case    
                when si.indid between 1 and 251 then 1
                else 0
        end is_index,
        si. id, si.indid, si.name, 
        STATS_DATE(si.id, si.indid) date_recount
from sysindexes si (nolock) 
where si.name not like '_wa_sys%' and si.name not like 'Statistic_%'
) si inner join sysobjects as so (nolock) on so.id=si.id and so.name not like 'sys%'
inner join (select sum(length) reclen, id from syscolumns (nolock) group by id) sc on sc.id = so.id
group by so.name with rollup
order by grouping (so.name), q desc, so.name

1. Скрипт запускать из контекста анализируемой БД
2. Размеры приведены к килобайтам.
3. rows может быть неточно (см. документацию)
4. Колонки:
name — имя таблицы
data — килобайт данных
indexes — килобайт индексов
q — сумма данных и индексов
rows — записей
index_count — индексов
date_recount_stat — статистика
record_len — длина записи
5. На 2005 не проверял.

Tags: 1С

Subscribe

• Мои первые тесты внешних компонент в новой версии

  Решил посмотреть новую технологию. Заодно решить мелокобытовые задачи. В новой версии механизм внешних компонент стал гораздо более ограниченным по…

• Паранойя

  Прямо сейчас. Сижу, ковыряю 1С 8.1, в фоне, пока одна обработка тестируется ковыряю 8.2… И вот такой вопрос возник: а это нормально, что внешние…

• Планирование серверной подсистемы для 1С8 в клиент-серверном варианте (MS SQL)

  Применимость документа Данные рекомендации имеет смысл применять только для оценки типовых конфигураций в типовом варианте использования со…

Photo

Hint http://pics.livejournal.com/igrick/pic/000r1edq

• 3 comments

sql server — Как измерить размер таблицы в ГБ в таблице в SQL

спросил
11 лет, 6 месяцев назад

Изменено
11 месяцев назад

Просмотрено
34к раз

В предыдущем вопросе @Morawski говорил, что «таблица с 1000 столбцов и 44 000 строк занимает около 330 МБ; именно столько браузер использует всего для нескольких открытых вкладок».

Сколько столбцов и строк должно быть в таблице, чтобы ее размер был равен > 10 ГБ
(предположим, что таблица имеет только двойные значения).

Как @Morawski пришел к выводу, что 1000 столбцов и 44 000 — это 330 МБ ?

Есть ли какой-нибудь сценарий, который мог бы сказать это в SQL?

• sql-сервер
• размер
• двойной

1

Имеется вызов sproc sp_spaceused. Не знаю, использовал ли это @Morawski, но в качестве примера на dev db мне было удобно:

 exec sp_spaceused 'aspnet_users'
 

дает

 строк имен зарезервированных данных index_size не используется
------------- ------- ------------ -------- ---------- -- ----------
aspnet_Users 3 48 КБ 8 КБ 40 КБ 0 КБ
 

4

 -- Измеряет размер таблиц (в килобайтах)
-- Протестировано в MS SQL Server 2008 R2. 
объявить таблицу @t (
имя nvarchar(100), [строки] int, [зарезервировано] nvarchar(100), [данные] nvarchar(100), [index_size] nvarchar(100), [не используется] nvarchar(100)
)
объявить @name nvarchar(100)
объявить курсор tt для
Выберите имя из sys.tables
открыть тт
выбрать следующий из tt в @name
в то время как @@FETCH_STATUS = 0
начинать
  вставить в @t
  exec sp_spaceused @имя
  выбрать следующий из tt в @name
конец
близко тт
освободить тт
выберите имя как table_name, [rows] как rows_count, данные + [index] как total_size, данные как data_size, [index] как index_size
от (выберите имя,
[ряды],
привести (LEFT(данные, LEN(данные)-3) как int) данные,
приведение (LEFT(index_size, LEN(index_size)-3) как int) [index]
 от @t
) Икс
порядок по 3 уб, 1
 

1

Не уверен насчет TSQL-скрипта (уверен, что он существует), но его можно найти через UI (SSMS) следующим образом:

1) R-щелкнуть таблицу
2) …Свойства
3) . .. Вкладка «Хранилище»

Оттуда он сообщит вам как «пространство данных», так и «индексное пространство», поэтому, если вам нужен общий размер, просто добавьте их.

РЕДАКТИРОВАТЬ
Учитывайте также пространство журнала, если вы ищете общий размер таблицы.

Вот информация о хранимой процедуре, указанной в ответе @jon. Кроме того, он ссылается на системные представления, где вы можете напрямую запрашивать данные об использовании пространства. http://msdn.microsoft.com/en-us/library/ms188776.aspx

Существуют точные формулы для планирования емкости для SQL Server:

• Оценка размера кластеризованного индекса
• Оценка размера некластеризованного индекса
• Оценка размера кучи

С 1000 столбцов фиксированной длины удваивается (это будет float(53) Тип SQL, 8 байт памяти) ваша строка приближается к максимальному пределу размера строки, но на самом деле она умещается на странице. Для строк 44 КБ требуется 44 КБ страниц (из-за огромного размера строки поместится только одна строка на страницу), то есть при 8 КБ страница 44000 * 8 КБ = ~ 344 МБ. Если у вас размер кластеризованного индекса будет увеличиваться в зависимости от размера ключа, смотрите первую ссылку выше.

Но дизайн таблицы из 1000 столбцов — это огромный запах кода. Ваш вопрос очень расплывчат о части базы данных, в вашем предыдущем вопросе никогда не упоминается база данных и речь идет о массивах памяти, когда эти два вопроса суммируются, они просто не имеют большого смысла.

Возможно, вам интересно почитать о разреженных столбцах, о моделировании EAV или о типе данных XML.

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

Из SQL Shack:

В SQL Server всегда были некоторые недокументированные объекты, которые используются Microsoft внутри компании, но их может использовать любой, у кого есть к ним доступ. Одним из таких объектов является хранимая процедура с именем 9.0075 sp_MSforeachtable .

sp_MSforeachtable — это хранимая процедура, которая в основном используется для итеративного применения команды T-SQL к каждой таблице, существующей в текущей базе данных.

Вот как это можно использовать:

 sp_MSforeachtable 'exec sp_spaceused [?]'
 

Зарегистрируйтесь или войдите в систему

Зарегистрироваться с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Размер таблицы и индекса в SQL Server

спросил
14 лет, 3 месяца назад

Изменено
2 года, 1 месяц назад

Просмотрено
175 тысяч раз

Можем ли мы получить SQL-запрос, который в основном поможет в просмотре размеров таблиц и индексов в SQl Server.

Как сервер SQL поддерживает использование памяти для таблиц/индексов?

• sql-сервер

3

sp_spaceused дает вам размер всех объединенных индексов.

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

 SELECT
    i.name AS IndexName,
    SUM(s.used_page_count) * 8 AS IndexSizeKB
ОТ sys.dm_db_partition_stats AS s
ПРИСОЕДИНЯЙТЕСЬ к sys.indexes КАК я
ON s.[object_id] = i.[object_id] И s.index_id = i.index_id
ГДЕ s.[object_id] = object_id('dbo.TableName')
СГРУППИРОВАТЬ ПО i.name
ЗАКАЗАТЬ ПО i.name
ВЫБИРАТЬ
    i.name AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
ИЗ sys.dm_db_index_physical_stats(
    db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
ПРИСОЕДИНЯЙТЕСЬ к sys.indexes КАК я
ON s.[object_id] = i.[object_id] И s.index_id = i.index_id
СГРУППИРОВАТЬ ПО i.name
ЗАКАЗАТЬ ПО i.name
 

Результаты обычно немного отличаются, но в пределах 1%.

3

exec sp_spaceused без параметра показывает сводку для всей базы данных. Решение foreachtable создает один результирующий набор для каждой таблицы, с чем SSMS может не справиться, если у вас слишком много таблиц.

Я создал сценарий, который собирает информацию о таблице через sp_spaceused и отображает сводку в одном наборе записей, отсортированном по размеру.

 создать таблицу #t
(
  имя nvarchar(128),
  строки varchar(50),
  зарезервированный varchar (50),
  данные varchar(50),
  index_size varchar (50),
  неиспользованный varchar(50)
)
объявить @id nvarchar (128)
объявить c курсор для
выберите '[' + sc.name + '].[' + s.name + ']' FROM sysobjects s ​​INNER JOIN sys.schemas sc ON s.uid = sc.schema_id, где s.xtype='U'
открыть с
получить c в @id
в то время как @@fetch_status = 0 начать
  вставить в #t
  exec sp_spaceused @id
  получить c в @id
конец
близко с
освободить c
выберите * из #t
упорядочить по конвертации (int, substring (data, 1, len (data)-3)) desc
удалить стол #t
 

4

В SQL 2012 получение этой информации на уровне таблицы стало восхитительно простым:

SQL Management Studio -> Щелкните правой кнопкой мыши на базе данных -> Отчеты -> Стандартные отчеты -> Использование диска по таблице!

Наслаждайтесь

0

 EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
 

1

Прошло много времени с момента создания этого поста, но я хотел поделиться своим скриптом:

 С CteIndex
КАК
(
ВЫБИРАТЬ
     зарезервированные страницы = (reserved_page_count)
     ,используемые страницы = (used_page_count)
     , страницы = (
            СЛУЧАЙ
                КОГДА (s. index_id < 2) ТО (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                ИНАЧЕ lob_used_page_count + row_overflow_used_page_count
            КОНЕЦ
            )
     ,s.object_id
     ,i.index_id
     ,i.type_desc AS IndexType
     ,i.name AS indexname
    ИЗ sys.dm_db_partition_stats s
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.indexes i ON s.[object_id] = i.[object_id] И s.index_id = i.index_id
)
ВЫБЕРИТЕ ОТЛИЧНЫЙ
DB_NAME(DB_ID()) AS Имя_базы_данных
,o.name КАК ИмяТаблицы
,o.object_id
,ct.indexname
,ct.IndexType
,ct.index_id
, IndexSpace = LTRIM (STR ((CASE WHEN usedpages > pages THEN CASE WHEN ct.index_id < 2 THEN pages ELSE (usedpages - pages) END ELSE 0 END) * 8, 15, 0) + 'КБ')
ОТ CteIndex ct
ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.objects o ON o.object_id = ct.object_id
ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , NULL) ps ON ps.object_id = o.object_id
И ps.index_id = ct.index_id
ЗАКАЗАТЬ ПО названию ASC
 

Это работает для:

• SQL Server (начиная с 2008)
• Включает информацию для всех таблиц текущей базы данных

1

 -- Получает размер каждого индекса для указанной таблицы. 
DECLARE @TableName sysname = N'SomeTable';
ВЫБЕРИТЕ i.name КАК IndexName
      ,8 * СУММ(s.used_page_count) AS IndexSizeKB
ИЗ sys.indexes КАК я
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.dm_db_partition_stats AS s
        ON i.[object_id] = s.[object_id] И i.index_id = s.index_id
ГДЕ s.[object_id] = OBJECT_ID(@TableName, N'U')
СГРУППИРОВАТЬ ПО i.name
ЗАКАЗАТЬ ПО i.name;
ВЫБЕРИТЕ i.name КАК IndexName
      ,8 * СУММ(a.used_pages) AS IndexSizeKB
ИЗ sys.indexes КАК я
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.partitions AS p
        ON i.[object_id] = p.[object_id] И i.index_id = p.index_id
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ sys.allocation_units КАК
        ВКЛ p.partition_id = a.container_id
ГДЕ i.[object_id] = OBJECT_ID(@TableName, N'U')
СГРУППИРОВАТЬ ПО i.name
ЗАКАЗАТЬ ПО i.name;
 

Вот более компактная версия самого удачного ответа:

 создать таблицу #tbl(
  имя nvarchar(128),
  строки varchar(50),
  зарезервированный varchar (50),
  данные varchar(50),
  index_size varchar (50),
  неиспользованный varchar(50)
)
exec sp_msforeachtable 'вставить в #tbl exec sp_spaceused [?]'
выберите * из #tbl
    упорядочить по конвертации (int, substring (data, 1, len (data)-3)) desc
удалить таблицу #tbl
 

Этот запрос исходит из двух других ответов:

Получить размер всех таблиц в базе данных

Как найти самые большие объекты в базе данных SQL Server?

, но я сделал его универсальным. Он использует словарь sys.objects :

 SELECT
 s.NAME как SCHEMA_NAME,
 t.NAME КАК OBJ_NAME,
 t.type_desc как OBJ_TYPE,
 i.name как indexName,
 сумма (p.rows) как RowCounts,
 сумма (a.total_pages) как TotalPages,
 сумма (a.used_pages) как UsedPages,
 сумма (a.data_pages) как DataPages,
 (сумма (a.total_pages) * 8) / 1024 как TotalSpaceMB,
 (сумма (a.used_pages) * 8) / 1024 как UsedSpaceMB,
 (сумма (a.data_pages) * 8) / 1024 как DataSpaceMB
 ОТ
 sys.objects t
 ВНУТРЕННЕЕ СОЕДИНЕНИЕ
 sys.schemas s ON t.SCHEMA_ID = s.SCHEMA_ID
 ВНУТРЕННЕЕ СОЕДИНЕНИЕ
 sys.indexes i ON t.OBJECT_ID = i.object_id
 ВНУТРЕННЕЕ СОЕДИНЕНИЕ
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
 ВНУТРЕННЕЕ СОЕДИНЕНИЕ
 sys.allocation_units ON p.partition_id = a.container_id
 ГДЕ
 t.NAME НЕ КАК 'dt%' И
 i.OBJECT_ID > 255 И
 i.index_id <= 1
ГРУППА ПО
 s.NAME, t.NAME, t.type_desc, i.object_id, i.index_id, i.name
СОРТИРОВАТЬ ПО
 сумма(a.