как узнать размер таблицы mssql
Получение информации о размере базы данных, таблиц и индексов на диске (СУБД MS SQL Server)
Рост размера информационной базы является закономерным явлением ее эксплуатации, но, в некоторых случаях, данный процесс свидетельствует об ошибках в архитектуре системы. Среда SQL Server Management Studio предоставляет возможность легко получить информацию о занимаемом БД месте на диске, в том числе: сводную информацию; в разрезе таблиц базы данных; индексов таблиц. Анализ необычных (для системы в целом) данных может выявить ошибки архитектуры и/или ошибки выполнения регламентных операций. Способы получить такую информацию о размере данных на диске будут рассмотрены в данной статье.
«Стандартные отчеты» в пользовательском интерфейсе Management Studio
SQL Server Management Studio предоставляет минимальный необходимый набор стандартных отчетов для получения информации о размере базы данных/ее файлов/таблиц/индексов в режиме пользовательского интерфейса.
Доступ к этим отчетам может быть выполнен через «Обозреватель объектов» (Object explorer) → Правый клик мыши по базе данных → «Отчеты» (Reports) → «Стандартный отчет» (Standard reports)
Стандартные отчеты по использованию дискового пространства
Отчет «Занято места на диске» (Disk Usage)
Отчет содержит общие сведения об использовании места на диске базой данных.
В отчете представлена информация следующего рода:
Отчеты «Использование дисковой памяти таблицей» (Disk Usage by Table), «Использование дисковой памяти верхними таблицами» (Disk Usage by Top Tables)
Отчет содержит подробные данные об использовании места на диске таблицами, расположенными в базе данных. Отличие этих двух отчетов заключается лишь в том что в отчете «By Top Tables» вывод происходит только для «верхних» (первых) 1000 таблиц.
В отчете представлена информация:
Отчет «Использование дисковой памяти секцией» (Disk Usage by Partition)
Отчет содержит подробные данные об использовании места на диске индексом и секциями, расположенными в базе данных.
Хотел бы обратить Ваше внимание что в данном отчете неверно рассчитывается дисковое пространство по кластерному индексу. Для получения реально используемого дискового пространства кластерным индексом можно: из «объема, используемого всеми индексами таблицы» (указанном в отчете «Использование дисковой памяти таблицей») вычесть «объем всех не кластерных индексов» (по отчету «Использование дисковой памяти секцией»)
В отчете представлена информация:
Хранимые процедуры
Данные о размере базы данных и таблиц также можно получить с помощью хранимой процедуры sp_spaceused Management Studio.
Синтаксис:
sp_spaceused [[ @objname = ] ‘objname’ ]
[,[ @updateusage = ] ‘updateusage’ ]
В процедуре могут быть использованы 2 не обязательных параметра:
Примеры запросов по всей базе данных и по конкретной таблице приведены ниже:
Размер строк и таблицы для таблиц, оптимизированных для памяти
До выхода SQL Server 2016 (13.x); размер данных «в строке» для таблицы, оптимизированной для памяти, не мог превышать 8060 байт. Но в SQL Server 2016 (13.x); и более поздних версиях, а также в базе данных SQL Azure появилась возможность создать таблицу, оптимизированную для памяти, с несколькими большими столбцами (например, несколькими столбцами varbinary(8000)) и столбцами LOB (т. е. varbinary(max), varchar(max) и nvarchar(max)), и выполнять с ними операции, используя типы таблиц и модули T-SQL, скомпилированные в собственном коде.
Столбцы, превышающие максимальный размер строки в 8060 байт, размещаются вне строки в специальной внутренней таблице. У каждого такого столбца имеется соответствующая внутренняя таблица, которая, в свою очередь, имеет один некластеризованный индекс. Дополнительные сведения о внутренних таблицах, используемых для столбцов «вне строки», см. в статье sys.memory_optimized_tables_internal_attributes (Transact-SQL).
Существуют определенные сценарии, в которых удобно вычислять размер строки и таблицы.
Какой объем памяти используется таблицей?
Объем используемой таблицей памяти невозможно подсчитать точно. На объем используемой памяти влияет множество факторов. Это такие факторы, как постраничное выделение места в памяти, размещение, кэширование и заполнение. Кроме того, несколько версий строк, которые имеют активные связанные транзакции либо ожидают сборку мусора.
Минимальный размер, необходимый для данных и индексов в таблице, определяется вычислением для [размера таблицы], о котором рассказывается ниже.
Вычисление используемой памяти в самом лучшем случае может быть выполнено лишь приближенно, поэтому рекомендуется включить планирование вместимости в планы разработки.
Каков размер строки данных, и укладывается ли он в ограничение размера строки 8060 байт? Чтобы получить ответ на эти вопросы, используйте вычисление для [размера текста строки], о котором рассказывается ниже.
Таблица, оптимизированная для памяти, представляет собой набор строк, а также индексов, которые содержат указатели на строки. На следующей схеме показана таблица с индексами и строками, которые в свою очередь содержат заголовки и текст:
Таблица, оптимизированная для памяти, состоящая из индексов и строк.
Вычисление размера таблицы
Размер, занимаемый таблицей в памяти (в байтах) вычисляется следующим образом.
Размер строки вычисляется путем сложения значений для заголовка и текста:
Вычисление размера текста строки
Структура строк. Строки в таблице, оптимизированной для памяти, включают следующие компоненты.
Заголовок строки содержит метку времени, необходимую для управления версиями строки. Заголовок строки также содержит указатель индекса, который позволяет реализовать цепочку строк в хэш-контейнере (описано выше).
Текст строки содержит фактические данные столбцов, которые включают некоторые вспомогательные сведения, такие как массив значений NULL для столбцов, допускающих значение NULL, и массив смещений для типов данных с переменной длиной.
На следующем рисунке показана структура строк для таблицы с двумя индексами.
Метки времени начала и конца показывают период, в котором определенная версия строки является допустимой. Транзакции, запускаемые в данном интервале, могут видеть эту версию строки. Дополнительные сведения см. в разделе Транзакции с таблицами, оптимизированными для памяти.
Указатели индекса указывают на следующую строку в цепочке, принадлежащей хэш-контейнеру. На следующем рисунке показана структура таблицы с двумя столбцами (имя, город) и двумя индексами, один для столбца name и второй для столбца city.
На этом рисунке имена Джон и Джейн хэшированы на первый контейнер. Сьюзан хэширована на втором контейнере. Города Пекин и Богота хэшированы на первом контейнере. Париж и Прага хэшированы на втором контейнере.
Таким образом, цепочки для хэш-индекса по именам выглядят следующим образом.
Первый контейнер: (Джон, Пекин); (Джон, Париж); (Джейн, Прага)
Второй контейнер: (Сьюзан, Богота)
Цепочки для индекса по городам выглядят следующим образом:
Первый контейнер: (Джон Пекин), (Сьюзан, Богота)
Второй контейнер: (Джон, Париж), (Джейн, Прага)
Конечная метка времени ∞ (бесконечность) указывает, что это действительная на данный момент версия строки. Строка была обновлена или удалена с того момента, как была записана эта версия.
Для времени больше 200 таблица содержит следующие строки.
| Имя | Город |
|---|---|
| Джон | Пекин |
| Джейн | Прага |
Однако любая активная транзакция с начальным временем 100 увидит следующую версию таблицы.
| Имя | Город |
|---|---|
| Джон | Париж |
| Джейн | Прага |
| Сьюзан | Богота |
Вычисление [размера текста строки] демонстрируется в следующей таблице.
Размер текста строки вычисляется двумя способами: вычисляемый размер и фактический размер.
Вычисляемый размер (далее — вычисляемый размер строки) используется для того, чтобы определить, не превышает ли размер строки ограничение в 8060 байт.
Фактический размер (далее — фактический размер строки) представляет собой фактический размер строки в памяти и в файлах контрольных точек.
В следующей таблице описано вычисление размера текста строки как фактический размер текста строки = SUM(размер мелких типов) + 2 + 2 * число столбцов глубокого типа.
| Section | Размер | Комментарии |
|---|---|---|
| Столбцы поверхностных типов | SUM [размер поверхностных типов] Размер отдельных типов в байтах: Bit: 1 Tinyint: 1 Smallint: 2 Int: 4 Real: 4 Smalldatetime: 4 Smallmoney: 4 Bigint: 8 Datetime: 8 Datetime2: 8 Float: 8 Money: 8 Numeric (точность 18): 16 Uniqueidentifier: 16 | |
| Заполнение столбца поверхностного типа | Возможны следующие значения: 1, если в таблице присутствуют столбцы глубоких типов, а общий размер данных в столбцах поверхностного типа является нечетным числом. 0 в остальных случаях | Глубокие типы — это типы (var)binary и (n)(var)char. |
| Массив смещений для столбцов глубоких типов | Возможны следующие значения: 0, если в таблице нет столбцов глубоких типов 2 + 2 * [количество столбцов глубоких типов] во всех остальных случаях | Глубокие типы — это типы (var)binary и (n)(var)char. |
| Массив значений NULL | [количество столбцов, в которых допустимы значения NULL] / 8, с округлением в сторону увеличения до целого числа байт. | Массив содержит один бит для каждого столбца, допускающего значения NULL. Эта величина округляется в сторону увеличения до целого числа байт. |
| Заполнение массива значений NULL | Возможны следующие значения: 1, если в таблице имеются столбцы глубоких типов данных и размер массива значений NULL равен нечетному числу байтов. 0 в остальных случаях | Глубокие типы — это типы (var)binary и (n)(var)char. |
| Заполнение | Если в таблице нет столбцов глубоких типов: 0 Если есть столбцы глубоких типов данных, добавляется 0–7 байт заполнения, исходя из наибольшего выравнивания, требующегося для столбцов поверхностных данных. Каждый столбец поверхностных типов требует выравнивания, равного его размеру (как показано в документе выше), за исключением столбцов GUID, которые требуют выравнивания на 1 байт (а не на 16), и числовых столбцов, которые всегда требуют выравнивания 8 байт (ни в коем случае не 16). Используется наибольшее требование выравнивания среди всех столбцов поверхностных типов, и заполнение 0–7 байт добавляется таким образом, чтобы общий размер на этот момент (без столбцов глубоких типов) был кратным числу необходимых выравниваний. | Глубокие типы — это типы (var)binary и (n)(var)char. |
| Столбцы глубоких типов фиксированной длины | SUM (размер столбцов глубоких типов фиксированной длины) Размер каждого столбца составляет: i для типов char(i) и binary(i). 2 * i для типа nchar(i) | Столбцы глубоких типов данных фиксированной длины — это столбцы типов char(i), nchar(i) или binary(i). |
| Столбцы глубоких типов данных переменной длины вычисляемый размер | SUM (вычисляемый размер столбцов глубоких типов данных переменной длины) вычисляемый размер каждого столбца составляет: i для типов varchar(i) и varbinary(i) 2 * i для типа nvarchar(i) | Эта строка применяется только к вычисляемому размеру строки. Столбцы глубоких типов переменной длины — это столбцы типов varchar(i), nvarchar(i) или varbinary(i). вычисляемый размер определяется максимальной длиной (i) столбца. |
| Столбцы глубоких типов данных переменной длины фактический размер | SUM (фактический размер столбцов глубоких типов данных переменной длины) Фактический размер каждого столбца составляет: n, где n — количество символов, хранящихся в столбце, для типа varchar(i). 2 * n, где n — количество символов, хранящихся в столбце, для типа nvarchar(i). n, где n — число байтов, хранящихся в столбце, для типа varbinary(i). | Эта строка применяется только к фактическому размеру строки. Фактический размер определяется данными, которые хранятся в столбцах в данной строке. |
Пример. Вычисление размера строки и таблицы
Для хэш-индекса фактическое число контейнеров округляется в сторону увеличения до ближайшей степени числа 2. Например, если заданное число bucket_count равно 100 000, то фактическое число контейнеров для индекса составляет 131 072.
Рассмотрим таблицу Orders со следующим определением:
Обратите внимание, что эта таблица содержит один хэш-индекс и некластеризованный индекс (первичный ключ). Кроме того, она содержит три столбца фиксированной длины и один столбец переменной длины, при этом один из столбцов допускает значения NULL ( OrderDescription ). Допустим, таблица Orders содержит 8379 строк, а средняя длина значений в столбце OrderDescription составляет 78 символов.
Чтобы определить размер таблицы, сначала необходимо определить размер индексов. Для обоих индексов указан показатель bucket_count, равный 10 000. Эта величина округляется в большую сторону до ближайшей степени числа 2: 16384. Поэтому общий размер индексов для таблицы Orders составляет:
Остается найти размер данных таблицы, который равен
(Пример таблицы содержит 8379 строк.) Теперь у нас есть:
Теперь давайте рассчитаем [фактический размер текста строки].
Столбцы поверхностных типов:
Заполнение для столбцов поверхностных типов равно 0, поскольку общий размер столбцов поверхностного типа является четным числом.
Массив смещений для столбцов глубоких типов:
Массив значений NULL = 1
Заполнение массива значений NULL = 1, так как размер массива значений NULL является нечетным числом, а в таблице есть столбцы глубоких типов.
8 — наибольшее требования выравнивания.
Размер на данный момент равен 16 + 0 + 4 + 1 + 1 = 22.
Ближайшее число, кратное 8, — это 24.
В итоге заполнение составляет 24 – 22 = 2 байта.
В таблице нет столбцов глубоких типов фиксированной длины (столбцов глубоких типов фиксированной длины: 0).
Для завершения вычисления:
Таким образом, общий размер, занимаемый таблицей в памяти, составляет около 2 мегабайт. Это значение не учитывает потенциальные издержки при выделении памяти, а также управление версиями строк, необходимое для доступа транзакций к этой таблице.
Фактический размер памяти, выделяемый для данной таблицы и используемый ею и ее индексами, можно получить при помощи следующего запроса:
Ограничения столбца «вне строки»
Ниже перечислены некоторые ограничения и пояснения, касающиеся использования столбцов «вне строки» в таблице, оптимизированной для памяти.
Как узнать размеры таблицы через SQL
Не так давно я заинтересовался максимальным размером таблицы у своего сайта, где продаются мои курсы. Там ведётся постоянная статистика посещений. В итоге в одной таблице уже больше миллиона записей. К счастью, поиск по этой таблице нужен только мне, а я могу и подождать, а добавление новой записи происходит всё равно моментально. Но я подумал, что бывают случаи, когда таблица должна быть максимум некоторого размера. А вот дальше она должна либо удаляться, либо создаваться новая. Как это делается? Узнаётся размер таблицы через SQL, а дальше она либо частично/полностью очищается, либо переносится в какой-нибудь бэкап, где просто хранилище старых данных. И в этой статье я покажу, как узнать размер таблицы через SQL.
Вот такой запрос выведет информацию о статусе таблицы:
SHOW TABLE STATUS LIKE ‘table’
Рекомендую выполнить этот запрос на какой-нибудь таблице в PHPMyAdmin, чтобы сразу увидеть, что он делает. Там много различных параметров, но нас с Вами интересует «Data_length«. Именно он отвечает за размер таблицы. Проверяя его, Вы можете решать: проводить очистку данных или нет.
Разумеется, проверку надо проводить в автоматическом режиме, например, раз в сутки, задав это через cron.
Также учтите, что это необходимо делать только если Вам требуется быстродействие по поиску в этой таблице. В другом случае, это вообще не нужно. Если Вы беспокоитесь о максимальном размере таблицы, то едва ли Вас это коснётся. Максимальный размер таблицы также можно найти в этой выборке. За максимальный размер отвечает параметр «Max_data_length«, и он действительно огромен, 256 ТБ.
Если моя статистика будет работать в том же темпе, то этого размера хватит примерно на миллион лет. Поэтому тем, кто беспокоится о том, что записи перестанут помещаться, зря это делает. А вот те, кому нужен максимально быстрый поиск и выборка по таблице, вот тем действительно стоит задуматься, а нельзя ли удалить всё лишнее или отправить хотя бы в историю старые записи?
Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!
Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.
Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления
Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.
Порекомендуйте эту статью друзьям:
Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):
Комментарии ( 2 ):
Очень полезная статья! Как всегда радуете! Спасибо большое!
Согласен с предидущим оратором.
Для добавления комментариев надо войти в систему.
Если Вы ещё не зарегистрированы на сайте, то сначала зарегистрируйтесь.
Copyright © 2010-2021 Русаков Михаил Юрьевич. Все права защищены.
Размеры таблиц SQL
Иногда хочется понять, какие таблицы занимают больше всего места? Может быть что-то можно удалить? Формируем структуру хранения, смотрим отчет в SQL по размеру таблиц. Сопоставляем по именам. Отчет делает именно это. Но написан на СКД. А значит можно рисовать графики, группировки и т.п.
Отчет показывает размер таблиц, количество записей, используемое и неиспользуемое место. Соответственно, вы можете сориентироваться сколько места вам сможет выиграть shrink. А может быть и обнаружите, что кучу места у вас съедает неиспользуемая таблица.
Особенности работы
Подготавливается два набора данных:
2. Запрос через SQLCMD с выводом результата во временный файл и парсингом этого файла.
При компоновке результата формируется 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 на одной машине.
Отчет по размерам таблиц в базе SQL
Возникла проблема. База MSSQL (*.mdf) «пухнет» довольно быстро. Как найти главного виноватого. Первым делом следует посмотреть на размеры физических таблиц базы. Отчет выводит следующую информацию:
Предусмотрена настраиваемая сортировка по всем полям отчета.
Скачать файлы
Специальные предложения
Обновление 19.02.15 10:15
Код открыт Не указано
См. также
Универсальная обработка для статистики базы данных (документы, справочники, регистры, отчеты) с отбором по подсистемам и с анализом наличия основных реквизитов (организации, контрагенты, договора, номенклатура, сотрудники, физлица, валюта). Возможность просмотра списка документов или справочников или регистров при активизации в колонке «Документы, справочники, регистры, отчеты» в текущей строке. Полезная обработка для консультации пользователей, где искать метаданные в каком интерфейсе, т.к. подсистема указывает в каком интерфейсе находятся метаданные (документы, справочники, регистры, отчеты).
20.02.2017 21349 56 strelec13 12
Отчет с информацией о количестве созданных пользователем документов в базе 1С (проведенных и нет)
Кто из пользователей активнее всех создает документы, бухгалтер Иванова или Петрова? У кого их максимальное количество за прошлый год и каких видов? Нужны ли Кривцову права на добавление «реализации товаров» или он никогда их не создает? Данный отчет поможет Вам ответить на эти вопросы.
30.08.2021 473 1 nissti 0
Подсчет количества документов в информационной базе
25.03.2021 1837 13 Cohap 6
Простое логирование действий системы
Маленькое расширение для быстрой организации записи и просмота логов действий системы
21.03.2021 3022 2 Yashazz 3
Статистика работы пользователя Промо
17.07.2011 50786 430 imozg 63
Анализ количества справочников и документов за период
Универсальная обработка для анализа количества справочников и документов в базе за период. Подходит для любых конфигураций.
05.03.2021 1379 5 mangushov 1
Отчет формирует список основных метаданных (документы, справочники, регистры сведений, планы видов характеристик). Выводит количество элементов для справочников и планов видов характеристик, общее количество и количество проведенных для документов, количество записей для регистров сведений.
16.12.2020 2560 9 Svb84 4
Получение данных по количеству документов и справочников в базе данных
Обработка показывает количество документов в базе, а также количество элементов справочников, писалась как затычка при написании правил обмена, нужно было понять, какие документы и справочники переносит, обработка показывает количество элементов справочка и также количество документов в базе данных
19.11.2020 1863 4 costy_admin 0
Отчет по конфигурации: «Справочники, документы, регистры. Подсчет количества записей и переход в форму списка объекта» Промо
Отчет даёт моментальное представление о качественном и количественном заполнении базы данных по видам справочников, документов, регистров (накопления, бухгалтерии, сведений). Отбор документов по периоду, по организации (при наличии такого реквизита). Авто установка фильтра по периоду, по организации при переходе в форму списка. Отбор регистров по периоду (кроме непериодических регистров сведений), по организации (при наличии такого измерения). Авто установка фильтра по периоду, по организации при переходе в форму списка. Дополнительный подсчёт количества справочников, документов по статусу «Помечен на удаление» в отчёте. Авто установка фильтра по статусу «Помечен» при переходе в форму списка. Дополнительный подсчёт количества документов по статусу «Проведён / Не проведён». Авто установка фильтра по статусу «Проведён» при переходе в форму списка. Двойной клик по ячейке отчёта выполняет переход в форму списка выбранного справочника, документа, регистра (накопления, бухгалтерии, сведений).
19.03.2012 33575 241 Designer1C 10