как узнать размер таблицы ms sql
Получение информации о размере базы данных, таблиц и индексов на диске (СУБД 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. Кажется, он занимает больше места, чем я ожидал, учитывая содержащиеся в нем данные.
есть ли простой способ определить, сколько места на диске занимает каждая таблица?
20 ответов
Если вы используете среда SQL Server Management Studio (SSMS), вместо запуска запроса (который в моем случае вернул дубликаты строк) вы можете запустить стандартный отчет.
Примечание: уровень совместимости базы данных должен быть установлен в 90 или выше, чтобы это работало правильно. Видеть http://msdn.microsoft.com/en-gb/library/bb510680.aspx
sp_spaceused может получить информацию о дисковом пространстве, используемом таблицей, индексированным представлением или всей базой данных.
об этом сообщает информацию об использовании диска для таблицы ContactInfo.
чтобы использовать это для всех таблиц сразу:
вы также можете получить использование диска из функции стандартных отчетов SQL Server, щелкнув правой кнопкой мыши. Чтобы перейти к этому отчету, перейдите из объекта server в объект Проводник, перейдите к объекту базы данных и щелкните правой кнопкой мыши любую базу данных. В появившемся меню выберите отчеты, затем Стандартные отчеты, а затем «использование диска по разделам: [DatabaseName]».
после некоторого поиска я не смог найти простой способ получить информацию обо всех таблицах. Существует удобная хранимая процедура с именем sp_spaceused, которая возвращает все пространство, используемое базой данных. Если указано имя таблицы, оно возвращает пространство, используемое этой таблицей. Однако результаты, возвращаемые хранимой процедурой, не сортируются, поскольку столбцы являются символьными значениями.
следующий скрипт генерирует информацию, которую я ищу.
вот еще один метод: использование среда SQL Server Management Studio, в Обозреватель Объектов, перейдите в свою базу данных и выберите таблицы
открыть Подробности Обозревателя Объектов (либо клавишей F7 и будет » Вид «-> «Подробности Обозревателя Объектов»). На странице сведений обозревателя объектов щелкните правой кнопкой мыши заголовок столбца и включите столбцы, которые вы хотите хотелось бы посмотреть на странице. Вы также можете сортировать данные по любому столбцу.
вышеуказанные запросы хороши для поиска объема пространства, используемого таблицей (включая индексы), но если вы хотите сравнить, сколько пространства используется индексами в таблице, используйте этот запрос:
мы используем секционирование таблиц и имели некоторые проблемы с запросами, приведенными выше, из-за повторяющихся записей.
для тех, кому это нужно, вы можете найти ниже запрос, выполняемый 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 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 Server
Есть ли встроенная функция /хранимая процедура /запрос, который полезен для получения информации о размере MyTable в базе данных SQL Server?
7 ответов
Для отдельной таблицы вы можете использовать
Для всех таблиц в базе данных вы можете использовать его с sp_msforeachtable как показано ниже
Если вы не хотите писать скрипт, вы также можете открыть слишком малоиспользуемую «Сведения об объекте обозревателя» в SSMS (клавиша быстрого доступа F7).
На верхнем уровне откройте папку «Таблицы», чтобы получить список всех таблиц в вашей базе данных.
Вам может потребоваться настроить столбцы, чтобы увидеть используемое пространство. Это можно сделать, щелкнув правой кнопкой мыши строку заголовка и выбрав столбцы, которые вы хотите отобразить.
В «Сведения обозревателя объектов» имеется больше данных, подобных этому.
В SSMS щелкните правой кнопкой мыши по базе данных, выберите «Отчеты», «Стандартные отчеты», «Использование диска по верхним таблицам».
Отчет предоставит вам количество строк и килобайт, используемых в таблице.
Вы можете изменять в соответствии с вашими потребностями, то есть фильтровать вкл /выкл некластеризованные индексы. Умножьте количество страниц на 8, чтобы получить размер в КБ, затем разделите на 2 ^ 10 (= 1024), чтобы преобразовать в MB, если это необходимо.
sp_spaceused ( http://msdn.microsoft.com/en-us/library /ms188776.aspx ) также предоставит информацию о размере таблицы.
Чтобы получить информацию о размере таблицы, мне нравится использовать следующий скрипт
вы можете использовать следующий скрипт, который вычисляет том для каждой таблицы и другой набор результатов для каждой базы данных
Размеры таблиц 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 на одной машине.