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

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

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

40 632

2

Для определения размера таблиц в базе данных, размещенной на сервере 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

• Странная штука powershell…

  Странная штука powershell. .. Простой скрипт: $start = get-date for($i=1; $i -le 1000000; $i++){} $end = get-date ($end-$start).TotalMilliseconds #…

• про Linux

  С IE на оперу я перелез, когда мне понадобилось сильно экономить трафик. Других достоинств у оперы нет и не было. Как появилась возможность — перелез…

• Об эффективности отечественной государственной системы

  Только Факты: 1. Сегодня курс доллара 1$ = 28.223 (по данным rbc.ru) 2. По данным BBC: Соединенные Штаты уже потратили на участие в военной и…

Photo

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

• 3 comments

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

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

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

Просмотрено
171k раз

99

Новинка! Сохраняйте вопросы или ответы и организуйте свой любимый контент.
Узнать больше.

Можем ли мы получить 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. total_pages) DESC
;
 

Существует расширенная хранимая процедура sp_spaceused , которая выводит эту информацию. Это довольно сложно сделать из словаря данных, но эта ссылка ведет к сценарию, который это делает. Этот вопрос stackoverflow имеет некоторое разветвление на информацию о базовых структурах данных, которые вы можете использовать для построения оценок размеров таблиц и индексов для планирования пропускной способности.

Для просмотра данных хранилища одной таблицы (и ее индексов):

 exec sp_spaceused MyTable
 

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

Зарегистрируйтесь с помощью Google

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

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

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

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

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

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

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

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

SQL Server Получить размер таблицы

MS SQL Server

10 месяцев назад

от John Otieno

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

В этом руководстве вы узнаете о различных способах получения размера таблиц, хранящихся в базе данных SQL Server.

ПРИМЕЧАНИЕ . В этом руководстве мы протестировали руководства и фрагменты кода на Microsoft SQL Server 2019 и SQL Server Management Studio 18. Мы рекомендуем реплицировать аналогичную среду для максимальной совместимости и вывода.

Метод 1 — хранимая процедура

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

Возьмем приведенный ниже пример кода:

USE salesdb;
EXEC sp_spaceused N'Products';

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

https://www.dropbox.com/s/zqg8lv20ivf0lzj/salesdb%28linuxhint%29.zip?dl=0

Затем используйте sp_spaceused , сохраненный Процедура получения размера таблицы Products из базы данных.

Пример вывода выглядит следующим образом:

Предположим, вы хотите получить информацию о размерах и хранении всех таблиц в определенной базе данных?

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

Ознакомьтесь с нашим руководством по этой теме, чтобы узнать больше.

Рассмотрим приведенный ниже пример кода:

USE salesdb;
EXEC sp_msforeachtable 'exec sp_spaceused [?]';

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

Ниже показан пример вывода:

Метод 2 — стандартный отчет в SQL Server Management Studio

Более простой и интуитивно понятный способ получить размер таблицы в SQL Server — использовать функцию стандартного отчета, доступную в ССМС.

Запустите утилиту SSMS и подключитесь к своему экземпляру SQL Server.

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

Выберите отчеты -> Стандартные отчеты -> Использование диска по таблице .

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

Пример вывода показан ниже:

Закрытие

В этом руководстве мы рассмотрели два основных метода получения размера таблицы или нескольких таблиц в базе данных SQL Server.