ГлавнаяРазноеРазмеры таблиц ms sql

Размер таблиц в БД MS SQL SERVER отсортированный по убыванию. Размеры таблиц ms sql


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

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

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

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

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

Падение к этому коду - это поле данных, которое не сохраняется как int (в этом поле хранится "KB" символов), и это было бы полезно (но не обязательно) для сортировки.

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

CREATE PROCEDURE [dbo].[usp_getAllDBTableSizes] AS BEGIN SET NOCOUNT OFF CREATE TABLE #DatabaseTables([dbname] sysname,TableName sysname) CREATE TABLE #AllDatabaseTableSizes(Name sysname,[rows] VARCHAR(18), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18)) DECLARE @SQL nvarchar(4000) SET @SQL='select ''?'' AS [Database], Table_Name from [?].information_schema.tables WHERE TABLE_TYPE = ''BASE TABLE'' ' INSERT INTO #DatabaseTables(DbName, TableName) EXECUTE sp_msforeachdb @Command1=@SQL DECLARE AllDatabaseTables CURSOR LOCAL READ_ONLY FOR SELECT TableName FROM #DatabaseTables DECLARE AllDatabaseNames CURSOR LOCAL READ_ONLY FOR SELECT DBName FROM #DatabaseTables DECLARE @DBName sysname OPEN AllDatabaseNames DECLARE @TName sysname OPEN AllDatabaseTables WHILE 1=1 BEGIN FETCH NEXT FROM AllDatabaseNames INTO @DBName FETCH NEXT FROM AllDatabaseTables INTO @TName IF @@FETCH_STATUS<>0 BREAK INSERT INTO #AllDatabaseTableSizes EXEC ( 'EXEC ' + @DBName + '.dbo.sp_spaceused ' + @TName) END --http://msdn.microsoft.com/en-us/library/aa175920(v=sql.80).aspx INSERT INTO rsp_DatabaseTableSizes (DatabaseName, name, [rows], data) SELECT [dbname], name, [rows], data FROM #DatabaseTables INNER JOIN #AllDatabaseTableSizes ON #DatabaseTables.TableName = #AllDatabaseTableSizes.Name GROUP BY [dbname] , name, [rows], data ORDER BY [dbname] --To be honest, I have no idea what exact duplicates we are dropping -- but in my case a near enough approach has been good enough. DELETE FROM [rsp_DatabaseTableSizes] WHERE name IN ( SELECT name FROM [rsp_DatabaseTableSizes] GROUP BY name HAVING COUNT(*) > 1 ) DROP TABLE #DatabaseTables DROP TABLE #AllDatabaseTableSizes CLOSE AllDatabaseTables DEALLOCATE AllDatabaseTables CLOSE AllDatabaseNames DEALLOCATE AllDatabaseNames END --EXEC [dbo].[usp_getAllDBTableSizes]

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

CREATE TABLE [dbo].[rsp_DatabaseSizes]( [DatabaseName] [varchar](1000) NULL, [dbSize] [decimal](15, 2) NULL, [DateUpdated] [smalldatetime] NULL ) ON [PRIMARY] GO

qaru.site

Получение информации о размере базы данных, таблиц и индексов на диске (СУБД 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 — Указывает на необходимость запустить процедуру обновления сведений

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

-- Указываем использование конкретной базы данных USE [DevBase2] -- Информация по всей базе exec sp_spaceused -- Информация по конкретной таблице exec sp_spaceused N'_AccumRg17539'

-- Указываем использование конкретной базы данных

USE [DevBase2]

 

-- Информация по всей базе

exec sp_spaceused

 

-- Информация по конкретной таблице

exec sp_spaceused N'_AccumRg17539'
Результат работы хранимой процедуры sp_spaceused

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

Динамические административные представления

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

Примеры диагностируемых ошибок

Данный перечень не является полным, но одним из общих сигналов приведенных выше ошибок является чрезмерный рост тех или иных таблиц базы данных. Естественно, большой размер таблиц/индексов не является прямым указанием на наличие ошибок, в то же время следует «приглядеться» к такого рода сигналам и провести их анализ.

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

Для ошибки «Отсутствует перерасчет итогов по регистрам» также характерен рост таблицы итоговых записей, но при этом (особенно для регистра остатков) дополнительным условием является наличие «нулевых» записей в регистре итогов. Подробнее об этом можно прочитать в статье «Что такое итоги регистров накопления».

Для ошибки «Использование избыточных индексов» первоочередным, конечно же, является анализ необходимости такого индекса. Но никто не станет анализировать все индексы базы данных без причин, а вот наличие «тяжелых» индексов таблицы может стать одним из таких сигналов к действию.

Касательно «Хранения массивных двоичных данных в таблицах БД», я считаю это неприемлемым, данные такого рода должны храниться на диске независимо от базы данных с обеспечением доступа к ним из системы, а также с обеспечением отдельной процедуры резервного копирования.

ausevich.ru

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

Когда-то я это написал, но один раз я этот скрипт терял. Недавно нашёл его. В силу лени больше не хочу терять. 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.name1. Скрипт запускать из контекста анализируемой БД2. Размеры приведены к килобайтам.3. rows может быть неточно (см. документацию)4. Колонки:name - имя таблицыdata - килобайт данныхindexes - килобайт индексовq - сумма данных и индексовrows - записейindex_count - индексовdate_recount_stat - статистикаrecord_len - длина записи5. На 2005 не проверял.

speshuric.livejournal.com

Узнать размеры таблиц 1С в базе MS SQL

Дано: База 1С SQL, надо разобраться, отчего она растет, как на дрожжах. Поскольку это 1С - имеются сотни таблиц, перебирать которые вручную никакого желания нет.  Решение 1.http://blog.imageofyou.ru/2011/11/09/all-tables-size/ Понадобилось мне тут давеча оценить размер таблиц в базе данных одного из наших клиентов. Еще со времен SQL Server 2000 я знаю про процедуру sp_spaceused, но она хороша только для случая, когда нужен размер лишь одного объекта – конкретной таблицы или же всей БД целиком. А для того, чтобы посмотреть размер каждого объекта (например, для того, чтобы найти аномально большие таблицы) приходилось извращаться с курсором и сохранением результата выполнения процедуры во временной таблице. Не самый изящный способ. С появлением в SQL Server 2005 data management views эту задачу стало возможно решить с помощью всего одного запроса (без курсоров, временных таблиц и прочей “экзотики”).   select t.name as TableName, Min(t.create_date) as CreateDate, ds.name as FileGroupName, SUM(u.total_pages) * 8 / 1024 as SizeMB from sys.tables as t inner join sys.partitions as p on t.object_id = p.object_id inner join sys.allocation_units as u on p.partition_id = u.container_id inner join sys.data_spaces as ds on u.data_space_id = ds.data_space_id group by t.name, ds.name order by SizeMB desc   Решение 2http://mainview.ru/raznoe/sql-mysql/kak-najti-samye-bolshie-tablicy-v-baze-dannyx-ms-sql USE [DatabaseName] GO CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate database_default GROUP BY a.table_name, a.row_count, a.data_size ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC DROP TABLE #temp   Альтернативные варианты: искать обработки с инфостарта, писать самому, получить демонстрационный доступ на сервисе gilev.ru

ekorshunov.blogspot.com

Определение размера таблиц базы MS SQL

USE base;

SET nocount on

    Declare @table varchar(50)

        Declare spaceused cursor local

             for

                select

                    name

                from sysobjects

                where xtype='U'

                order by name

        if  EXISTS(select * from tempdb..sysobjects where id =object_id('tempdb.dbo.#tspaceused'))

        DROP TABLE #tspaceused

        Create Table #tspaceused(

            _Name nvarchar(50),

            _Rows int,

            reserved varchar(18),

            Data varchar(18),

            _index varchar(18),

            Unused varchar(18) )

        open spaceused

            Fetch next from spaceused into @table

            while @@fetch_status=0

            Begin

                insert #tspaceused  

                    execute sp_spaceused @table

                fetch next from spaceused into @table

            end

        Close spaceused

        Deallocate spaceused

If (Select Convert(int,CONVERT(Char(1),SERVERPROPERTY('ProductVersion'))))>8

    select top 100

        _name,

        _rows,

        Convert(int,REPLACE(reserved,' KB','')) as reserved,

        Convert(int,REPLACE(Data,' KB','')) as Data1,

        Convert(int,REPLACE(_index,' KB','')) as _index,

        Convert(int,REPLACE(Unused,' KB','')) as Unused/*,

        OBJECT_ID(_name) as id */

    from #tspaceused

Else

    select top 100

        _name,

        _rows,

        Convert(int,REPLACE(reserved,' KB','')) as reserved,

        Convert(int,REPLACE(Data,' KB','')) as Data1,

        Convert(int,REPLACE(_index,' KB','')) as _index,

        Convert(int,REPLACE(Unused,' KB','')) as Unused,

        /*OBJECT_ID(_name) as id,*/

        OBJECTPROPERTY(OBJECT_ID(_name),'TableIsPinned')as Pinned

    from #tspaceused  order by reserved desc

 

    DROP TABLE #tspaceused;

xn--80aabia5arlapblnctq.xn--p1ai

sql - Физический размер таблицы MS SQL

Как узнать, что физический размер таблицы в MS Sql 2005? Можно ли использовать SQL-запрос или нет? Благодарю.

задан jitm 12 мая '10 в 11:34 источник поделиться

Попробуйте хранимую процедуру:

exec sp_spaceused TableName

Для всех таблиц, которые вы можете использовать:

exec sp_Msforeachtable 'exec sp_spaceused ''?''' ответ дан Yoda 12 мая '10 в 11:39 источник поделиться

Вы также можете использовать встроенный отчет (по крайней мере, в 2008 году).

Щелкните правой кнопкой мыши базу данных в MS-SMS, затем в контекстном меню выберите "Отчеты" > "Стандартные отчеты" > "Использование диска по таблице".

Результаты экспортируются в Excel.

ответ дан twleblanc 09 янв. '13 в 2:45 источник поделиться

SELECT table_schema, table_name, ROUND (data_length/1024/1024,2) total_size_mb FROM information_schema.tables WHERE table_name = 'emp_master' AND table_schema = 'emp_database';

ответ дан RPL 12 мая '10 в 12:35 источник поделиться

qaru.site

Как узнать размеры таблицы через SQL

Вы здесь: Главная - MySQL - SQL - Как узнать размеры таблицы через SQL

Не так давно я заинтересовался максимальным размером таблицы у своего сайта, где продаются мои курсы. Там ведётся постоянная статистика посещений. В итоге в одной таблице уже больше миллиона записей. К счастью, поиск по этой таблице нужен только мне, а я могу и подождать, а добавление новой записи происходит всё равно моментально. Но я подумал, что бывают случаи, когда таблица должна быть максимум некоторого размера. А вот дальше она должна либо удаляться, либо создаваться новая. Как это делается? Узнаётся размер таблицы через SQL, а дальше она либо частично/полностью очищается, либо переносится в какой-нибудь бэкап, где просто хранилище старых данных. И в этой статье я покажу, как узнать размер таблицы через SQL.

Вот такой запрос выведет информацию о статусе таблицы:

SHOW TABLE STATUS LIKE 'table'

Рекомендую выполнить этот запрос на какой-нибудь таблице в PHPMyAdmin, чтобы сразу увидеть, что он делает. Там много различных параметров, но нас с Вами интересует "Data_length". Именно он отвечает за размер таблицы. Проверяя его, Вы можете решать: проводить очистку данных или нет.

Разумеется, проверку надо проводить в автоматическом режиме, например, раз в сутки, задав это через cron.

Также учтите, что это необходимо делать только если Вам требуется быстродействие по поиску в этой таблице. В другом случае, это вообще не нужно. Если Вы беспокоитесь о максимальном размере таблицы, то едва ли Вас это коснётся. Максимальный размер таблицы также можно найти в этой выборке. За максимальный размер отвечает параметр "Max_data_length", и он действительно огромен, 256 ТБ.

Если моя статистика будет работать в том же темпе, то этого размера хватит примерно на миллион лет. Поэтому тем, кто беспокоится о том, что записи перестанут помещаться, зря это делает. А вот те, кому нужен максимально быстрый поиск и выборка по таблице, вот тем действительно стоит задуматься, а нельзя ли удалить всё лишнее или отправить хотя бы в историю старые записи?

  • Создано 28.01.2013 21:04:57
  • Михаил Русаков
Предыдущая статья Следующая статья

Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!

Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.

Если Вы не хотите пропустить новые материалы на сайте,то Вы можете подписаться на обновления: Подписаться на обновления

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

Порекомендуйте эту статью друзьям:

Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):

  1. Кнопка: <a href="https://myrusakov.ru" target="_blank"><img src="https://myrusakov.ru//images/button.gif" alt="Как создать свой сайт" /></a>

    Она выглядит вот так:

  2. Текстовая ссылка:<a href="https://myrusakov.ru" target="_blank">Как создать свой сайт</a>

    Она выглядит вот так: Как создать свой сайт

  3. BB-код ссылки для форумов (например, можете поставить её в подписи): [URL="https://myrusakov.ru"]Как создать свой сайт[/URL]

myrusakov.ru