Sql вывести размеры таблица

Получение информации о размере базы данных, таблиц и индексов на диске (СУБД MS SQL Server)

Рост размера информационной базы является закономерным явлением ее эксплуатации, но, в некоторых случаях, данный процесс свидетельствует об ошибках в архитектуре системы. Среда SQL Server Management Studio предоставляет возможность легко получить информацию о занимаемом БД месте на диске, в том числе: сводную информацию; в разрезе таблиц базы данных; индексов таблиц. Анализ необычных (для системы в целом) данных может выявить ошибки архитектуры и/или ошибки выполнения регламентных операций. Способы получить такую информацию о размере данных на диске будут рассмотрены в данной статье.

«Стандартные отчеты» в пользовательском интерфейсе Management Studio

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

Доступ к этим отчетам может быть выполнен через «Обозреватель объектов» (Object explorer) → Правый клик мыши по базе данных → «Отчеты» (Reports) → «Стандартный отчет» (Standard reports)
Стандартные отчеты по использованию дискового пространства

Отчет «Занято места на диске» (Disk Usage)

Отчет содержит общие сведения об использовании места на диске базой данных.

В отчете представлена информация следующего рода:

• Общий объем, занятый на диске (Total space reserved)
• Место, занятое файлами данных (Data files space reserved)
• Место, занятое журналом транзакций (Transaction log space reserved)
• Отражает графически процент пространств в составе файлов данных: индексов (index), данных (data), не выделенного (unallocated) и не используемого (unused)
• Отражает графически процент примененного (used) и неиспользуемого (unused) пространства в составе журнала транзакций
• Выводит записи событий автоматического увеличения (autogrow) и/или сжатия (autoshrink) для базы данных
• Выводит информацию о месте на диске, используемом файлами данных

Отчет «Занято места на диске» (Disk Usage)

Отчеты «Использование дисковой памяти таблицей» (Disk Usage by Table), «Использование дисковой памяти верхними таблицами» (Disk Usage by Top Tables)

Отчет содержит подробные данные об использовании места на диске таблицами, расположенными в базе данных. Отличие этих двух отчетов заключается лишь в том что в отчете «By Top Tables» вывод происходит только для «верхних» (первых) 1000 таблиц.

В отчете представлена информация:

• Количество записей в таблице базы данных (Records)
• Размер зарезервированного пространства на диске (Reserved)
• Размер данных на диске (Data)
• Общий размер индексов таблицы на диске (Indexes)
• Размер не используемого пространства (Unused)

Отчет «Использование дисковой памяти таблицей» (Disk Usage by Table)

Отчет «Использование дисковой памяти секцией» (Disk Usage by Partition)

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

Хотел бы обратить Ваше внимание что в данном отчете неверно рассчитывается дисковое пространство по кластерному индексу. Для получения реально используемого дискового пространства кластерным индексом можно: из «объема, используемого всеми индексами таблицы» (указанном в отчете «Использование дисковой памяти таблицей») вычесть «объем всех не кластерных индексов» (по отчету «Использование дисковой памяти секцией»)

В отчете представлена информация:

• Число записей в индексе/секции (Records)
• Зарезервированное пространство на диске (Reserved)
• Используемое пространство на диске (Used)

Отчет «Использование дисковой памяти секцией» (Disk Usage by Partition)

Хранимые процедуры

Данные о размере базы данных и таблиц также можно получить с помощью хранимой процедуры sp_spaceused Management Studio.

Синтаксис:
sp_spaceused [[ @objname = ] ‘objname’ ]
[,[ @updateusage = ] ‘updateusage’ ]

В процедуре могут быть использованы 2 не обязательных параметра:

• @objname — Полное или неполное имя таблицы, индексированного представления или очереди. Если параметр не указан — результаты возвращаются для всей базы данных.
• @updateusage — Указывает на необходимость запустить процедуру обновления сведений

Примеры запросов по всей базе данных и по конкретной таблице приведены ниже:

Источник

Как в SQL Server 2014 получить размеры записей в таблице?

Как в SQL Server 2014 получить размеры записей в таблице? Какой запрос нужно написать?

1 ответ 1

Можно, например, запросить статистику в sys.dm_db_index_physical_stats (ссылка).

Запрос для случая, когда на таблице не используется partitioning (секционирование):

Если секционирование используется, то данные по разным секциям нужно агрегировать (среднее лучше взять взвешенное по количеству записей в секции):

Обратите, однако, внимание. Если соответствующий запрос кроме строки с alloc_unit_type_desc равным IN_ROW_DATA возвращает также LOB_DATA или ROW_OVERFLOW_DATA с ненулевыми значениями (т.е. если в таблице есть LOB или variable-length столбцы с данными, из-за которых размер записи может превышать 8060 байт), то по данным возвращаемым sys.dm_db_index_physical_stats достоверно определить размеры записей таблицы не представляется возможным.

Дело в том, что одна запись таблицы физически может состоять из нескольких кусков (одного in-row и нескольких lob и row-overflow), но sys.dm_db_index_physical_stats не складывает данные по ним, а считает их отдельными «записями». Это можно видеть на следующем примере.

Если в таблицу с одним LOB столбцом вставить одну запись:

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

т.е. единственная вставленная нами запись состоит из 1+4=5 кусков. В LOB_DATA не один кусок с max_record_size

32 тыс. байт, а четыре куска по

8 тыс. байт, что не позволяет правильно оценить размер записи.

Поэтому, в случаях, когда записи таблицы имеют данные в LOB или row-overflow страницах, целесообразнее может быть попытаться оценить размеры записей с помощью функции datalength :

С datalength , однако, есть подводные камни.

Источник

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

я унаследовал довольно большую базу данных SQL Server. Кажется, он занимает больше места, чем я ожидал, учитывая содержащиеся в нем данные.

есть ли простой способ определить, сколько места на диске занимает каждая таблица?

20 ответов

Если вы используете среда SQL Server Management Studio (SSMS), вместо запуска запроса (который в моем случае вернул дубликаты строк) вы можете запустить стандартный отчет.

1. щелкните правой кнопкой мыши по базе данных
2. перейти к Отчеты > Стандартные Отчеты > Использование Диска По Таблице

Примечание: уровень совместимости базы данных должен быть установлен в 90 или выше, чтобы это работало правильно. Видеть http://msdn.microsoft.com/en-gb/library/bb510680.aspx

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

об этом сообщает информацию об использовании диска для таблицы ContactInfo.

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

вы также можете получить использование диска из функции стандартных отчетов SQL Server, щелкнув правой кнопкой мыши. Чтобы перейти к этому отчету, перейдите из объекта server в объект Проводник, перейдите к объекту базы данных и щелкните правой кнопкой мыши любую базу данных. В появившемся меню выберите отчеты, затем Стандартные отчеты, а затем «использование диска по разделам: [DatabaseName]».

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

следующий скрипт генерирует информацию, которую я ищу.

для всех таблиц ,использовать..(добавляя из комментариев Павла)

вот еще один метод: использование среда SQL Server Management Studio, в Обозреватель Объектов, перейдите в свою базу данных и выберите таблицы

открыть Подробности Обозревателя Объектов (либо клавишей F7 и будет » Вид «-> «Подробности Обозревателя Объектов»). На странице сведений обозревателя объектов щелкните правой кнопкой мыши заголовок столбца и включите столбцы, которые вы хотите хотелось бы посмотреть на странице. Вы также можете сортировать данные по любому столбцу.

вышеуказанные запросы хороши для поиска объема пространства, используемого таблицей (включая индексы), но если вы хотите сравнить, сколько пространства используется индексами в таблице, используйте этот запрос:

Если вам нужно вычислить точно такие же числа, которые находятся на странице «свойства таблицы — хранилище» в SSMS, вам нужно подсчитать их тем же методом, что и в SSMS (работает для sql server 2005 и выше . а также правильно работает для таблиц с полями LOB-потому что просто подсчета «used_pages» недостаточно, чтобы показать точный размер индекса):

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

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

это работает как шарм.

небольшое изменение в ответе Mar_c, так как я так часто возвращался к этой странице, упорядоченной первым большинством строк:

Это даст вам размеры и количество записей для каждой таблицы.

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

и вы можете изменить его, чтобы вставить весь результат в таблицу temp и после этого выбрать из таблицы temp.

Я добавил еще несколько столбцов поверх ответа marc_s:

вот способ быстро получить размеры всех таблиц со следующими шагами:

написать данную команды T-SQL чтобы перечислить все таблицы базы данных:

теперь скопируйте список таблиц базы данных и скопируйте его в новое окно анализатора запросов

в SQL анализатор запросов, выберите из верхней панели инструментов опцию результаты в файл ( Ctrl + Shift + F ).

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

Базы Данных размер всех таблиц теперь хранится в файле на вашем компьютере.

расширение до @xav ответ который обрабатывал разделы таблицы, чтобы получить размер в МБ и ГБ. Протестировано на SQL Server 2008/2012 (прокомментировал строку где is_memory_optimized = 1 )

мой пост имеет отношение только к SQL Server 2000 и был протестирован для работы в моей среде.

этот код обращается к все возможные базы данных одного экземпляра, а не только одна база данных.

Я использую две временные таблицы, чтобы помочь собрать соответствующие данные, а затем сбросить результаты в одну «живую» таблицу.

возвращаемые данные: DatabaseName, DatabaseTableName, строки (в таблице), данные (размер таблицы в КБ казалось бы), запись данные (я нахожу это полезным, чтобы знать, когда я последний раз запускал скрипт).

падение этого кода-поле «данные» не хранится как int (символы » KB » хранятся в этом поле), и это было бы полезно (но не совсем необходимо) для сортировки.

надеюсь этот код поможет кому-то и экономит их время!

в случае, если вам нужно знать,rsp_DatabaseTableSizes таблица была создана с помощью:

из командной строки с помощью OSQL:

как простое расширение ответа marc_s (тот, который был принят), это настраивается для возврата количества столбцов и позволяет фильтровать:

Источник

Размеры таблиц SQL

• 
• 
• 

Иногда хочется понять, какие таблицы занимают больше всего места? Может быть что-то можно удалить? Формируем структуру хранения, смотрим отчет в SQL по размеру таблиц. Сопоставляем по именам. Отчет делает именно это. Но написан на СКД. А значит можно рисовать графики, группировки и т.п.

Отчет показывает размер таблиц, количество записей, используемое и неиспользуемое место. Соответственно, вы можете сориентироваться сколько места вам сможет выиграть shrink. А может быть и обнаружите, что кучу места у вас съедает неиспользуемая таблица.

Особенности работы

Подготавливается два набора данных:

2. Запрос через SQLCMD с выводом результата во временный файл и парсингом этого файла.

Имя сервера SQL и Имя базы SQL — обязательные параметры. Имя пользователя SQL и Пароль пользователя SQL нужно указывать, если по каким-либо причинам нужно выполнить запрос размеров таблиц под другим пользователем.

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

Поскольку формируется и запускается cmd-файл, возможно сообщение системы безопасности.

Протестировано на версии платформы 1С 8.3.12.1714, MS SQL Server 14, Microsoft (R) SQL Server Command Line Tool Version 12.0.2000.8 NT. Сервер 1С и Сервер SQL на одной машине.

Источник