Как посмотреть размер таблиц в sql 1с
Други, выручайте! Первый раз за последние 5 лет потребовалось посмотреть на чересчур жирные таблицы, а обработку то-ли удалил, то-ли потерял((
Киньте на почту, с меня аплодисменты))
dan@tedza.com
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
RowCounts DESC
Как узнать размер таблиц базы данных MS SQL SERVER
Данная статья запланирована как первый блок информации по переносу таблиц/индексов в другие файловые группы. Перенести таблицы может понадобиться, например, для ускорения запросов(таблица на один диск, индексы на другой) или если вся база не помещается на один раздел.
Но прежде чем перераспределять таблицы в разные файловые группы необходимо узнать хотя бы какие таблицы заполнены данными, а какие нет.
Запланировано использовать два способа определения объёма таблиц, которые выявляют количество строк(записей) каждой таблицы, также размер таблиц в килобайтах. Почему именно в килобайтах, хотя бы потому, что один из способов выдаёт результат только в килобайтах, во втором способе можно более гибко настроить результат.
- Использование процедуры sp_spaceused;
- Использование системных таблиц: sys.tables, sys.schemas, sys.partitions, sys.allocation_units.
Процедура sp_spaceused: ниже приведён синтаксис взятый из официального источника:
sp_spaceused [ [ @objname = ] 'objname' ] [ , [ @updateusage = ] 'updateusage' ] [ , [ @mode = ] 'mode' ] [ , [ @oneresultset = ] oneresultset ] [ , [ @include_total_xtp_storage = ] include_total_xtp_storage ]
Для данной задачи понадобится только один параметр — @objname, более детально с данной процедурой можно ознакомиться на официальной странице
Рассмотрим базу 1с и таблицу Config, т.к. данная таблица пустой не бывает:
use db_for_1c go sp_spaceused Config; go sp_spaceused 'Config'; go exec sp_spaceused 'Config'; go sp_spaceused 'dbo.Config'; go sp_spaceused '[dbo].[Config]'; go sp_spaceused @objname='[dbo].[Config]';
выше приведён пример использования sp_spaceused процедуры и несколько вариантов синтаксиса. Если таблицы созданы по правилам, т.е. имя не начинается с цифры, не используются пробел и другие специальные символы в имени таблицы, то можно в кавычки или в квадраные скобки не заключать имя таблицы. В том случае, если используется база 1с, то схема всегда будет по умолчанию — dbo и её можно тоже не использовать в имени таблицы. Параметр @objname использовать не обязательно. Ниже на скрине результат запуска всех шести вариантов данной процедуры и все они отработали должным образом:
rows — количество строк(записей) в таблице,
reserved — количество килобайт забронированное данной таблицей,
data — количество килобайт используемое данными,
index_size — количество килобайт используемое индексами,
unused — количество килобайт забронированное таблицей, но пока данное пространство не заполнено данными.
На первый взгляд очень удобный способ, но что если база НЕ 1с и в этой базе активно используются схемы отличные от dbo? Допустим в базе присутствует несколько схем и в данных схемах есть таблица с одинаковым названием, попытаеся определить размер таблицы, в данном случае таблица [test]:
USE TSQLV4 go sp_spaceused 'test'
обратите внимание на результат выполнения процедуры и на две таблицы с одинаковыми именами, но находящихся в разных схемах: dbo и Stats:
поэтому использование схемы в имени таблицы настоятельно рекомендую:
USE TSQLV4 go sp_spaceused 'dbo.test' go sp_spaceused 'Stats.test'
результат ниже на скрине:
всё же в колонке name не отображается схема, в которой находится таблица — это очень не удобно.
Чтобы посмотреть размер всех таблицы базы, то неоходимо использовать либо CURSOR — использование данного способа нежелательно, т.к. работает довольно медленно и второй вариант — динамический SQL:
USE [db_for_1c] go IF OBJECT_ID('tempdb..#result', 'U') IS NOT NULL DROP TABLE #result; CREATE TABLE #result ( ID INT IDENTITY(1, 1) , name NVARCHAR(255) , rows BIGINT , reserved NVARCHAR(255) , data NVARCHAR(255) , index_size NVARCHAR(255) , unused NVARCHAR(255) ) DECLARE @name NVARCHAR(255), @sql NVARCHAR(MAX) DECLARE C CURSOR FAST_FORWARD READ_ONLY FORWARD_ONLY FOR SELECT s.name+'.' +t.name AS name FROM sys.tables AS t JOIN sys.schemas AS s ON t.schema_id=s.schema_id ORDER BY name OPEN C; FETCH NEXT FROM C INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'INSERT INTO #result EXEC sp_spaceused ''' + @name + ''''; EXEC (@sql); FETCH NEXT FROM C INTO @name END CLOSE C; DEALLOCATE C; SELECT * FROM #result ORDER BY rows DESC
результат работы скрипта с использованием курсора:
USE db_for_1c go IF OBJECT_ID('tempdb..#result', 'U') IS NOT NULL DROP TABLE #result; CREATE TABLE #result ( ID INT IDENTITY(1, 1) , name NVARCHAR(255) , rows BIGINT , reserved NVARCHAR(255) , data NVARCHAR(255) , index_size NVARCHAR(255) , unused NVARCHAR(255) ) DECLARE @SQL NVARCHAR(MAX) = ''; SELECT @SQL+='EXEC sp_spaceused '''+S.name+'.'+T.name+''';' FROM sys.tables AS T INNER JOIN sys.schemas AS S ON S.schema_id=T.schema_id INSERT INTO #result EXEC (@SQL); SELECT * FROM #result ORDER BY rows DESC
результат динамического SQL:
- В обоих примерах: cursor и динамический SQL присутствует динамика, но во втором примере динамического SQL больше;
- Процедуру sp_spaceused лучше использовать в базах 1с или любых других, в которых используется только одна схема, иначе можено запутаться, т.к. данная процедура в результирующем наборе не сообщает в какой схеме находится та или иная таблица даже если на вход подаётся таблица с указанием схемы.
Перейдём к использванию системных таблиц: sys.tables, sys.schemas, sys.partitions, sys.allocation_units:
USE db_for_1c go SELECT t.object_id AS ObjectID, OBJECT_NAME(t.object_id) AS ObjectName, SUM(u.total_pages) * 8 AS Total_Reserved_kb, SUM(u.used_pages) * 8 AS Used_Space_kb, u.type_desc AS TypeDesc, MAX(p.rows) AS RowsCount FROM sys.allocation_units AS u JOIN sys.partitions AS p ON u.container_id = p.hobt_id JOIN sys.tables AS t ON p.object_id = t.object_id GROUP BY t.object_id, OBJECT_NAME(t.object_id), u.type_desc ORDER BY Used_Space_kb DESC, ObjectName;
результат ниже на скрине:
немного изменю запрос: добавлю таблицу sys.schemas:
use TSQLV4 go SELECT t.object_id AS ObjectID, s.name + '.' + +OBJECT_NAME(t.object_id) AS ObjectName, SUM(u.total_pages) * 8 AS Total_Reserved_kb, SUM(u.used_pages) * 8 AS Used_Space_kb, u.type_desc AS TypeDesc, MAX(p.rows) AS RowsCount FROM sys.allocation_units AS u JOIN sys.partitions AS p ON u.container_id = p.hobt_id JOIN sys.tables AS t ON p.object_id = t.object_id JOIN sys.schemas AS s ON t.schema_id=s.schema_id GROUP BY t.object_id, OBJECT_NAME(t.object_id) , u.type_desc , s.name ORDER BY Used_Space_kb DESC, ObjectName;
в изменённом запросе ObjectName теперь состоит не только из имени но и из схемы в качестве префикса разделённого точкой, см. скрин ниже:
и ещё один результат, но уже к базе 1с:
Ну что ж, подведём итог:
В общем и целом выгоднее использовать выборку(SELECT) из системных таблиц, т.к. она выдаёт больше информации, есть возможность для масштабирования путём присоединения ещё каких-нибудь таблиц если понадобится, выполняется быстрее чем динамический SQL с использованием процедуры sp_spaceused не говоря о курсоре.
Но всё же процедура sp_spaceused очень проста в использовании при оценке количества строк и т.д. для конкретной таблицы, т.к. кода писать приходится меньше.
Все решения описанные выше были проверены на версиях: MS SQL 2008 R2 и MS SQL Server 2019.
Предполагаю что на версиях MS SQL Server 2012, MS SQL Server 2014, MS SQL Server 2016, MS SQL Server 2017 тоже буду работать.
Как узнать размеры таблиц для баз данных 1C-SQL
Как известно, для базы данных в формате DBF легко можно посмотреть размер данных и размер индексов для каждой таблицы. Для баз данных в формате SQL такой легкой возможности нет. На самом деле, конечно же, такая возможность есть, просто до нее тяжелей добраться. SQL -сервер хранит сведения обо всех параметрах таблиц в своих системных таблицах, поэтому, чтобы получить информацию о таблицах приходится либо воспользоваться хранимой процедурой sp_spaceused , которая предоставляет всю необходимую информацию, либо писать свой код. Недостаток этой процедуры в том, что она выдает характеристику только одной таблицы за один раз. Таким образом, средства, сравнимого по легкости с просмотром каталога базы данных формата DBF , для SQL -варианта базы нет.
Теперь это неудобство будет ликвидировано. Представляю компактную обработку, которая не только выдает информацию о размерах данных и индексов в каждой пользовательской таблице базы данных, но, также выдает информацию о количестве строк в таблице. Кроме этого — выдается название таблицы, указывается, к какому объекту метаданных 1С данная таблица относится.
Кроме всего прочего, данная обработка решает еще одну проблему. Иногда записи в системных таблицах о текущих объемах таблиц не соответствуют действительности. Обработка позволяет ее подправить.
Для работы необходимо наличие библиотеки rainbow.dll в папке ИБ, которую можно взять здесь. За основу был взят текст хранимой процедуры sp_spaceused , который был подправлен так, чтобы быть полностью совместимым с Rainbow .
Текст обработки представлен ниже:
Перем СЗ ;
//________________________________________________________
Процедура ПодготовитьСЗ ()
глМета =СоздатьОбъект( «MetaDataWork» );
СЗ =СоздатьОбъект( «СписокЗначений» );
СЗ . Установить ( «_1SACCS» , «План счетов» );
СЗ . Установить ( «_1SACCSEL» , «Отбор проводок по счетам» );
СЗ . Установить ( «_1SBKTTL» , «Остатки (сальдо и обороты по субконто)» );
СЗ . Установить ( «_1SBKTTLC» , «Итоги (сальдо и обороты по синтетическим счетам)» );
СЗ . Установить ( «_1SCONST» , «Периодические константы» );
СЗ . Установить ( «_1SCORENT» , «Корректные проводки» );
СЗ . Установить ( «_1SCRDOC» , «Графы отбора и ссылки документов» );
СЗ . Установить ( «_1SDBSET» , «Базы данных (УРБД)» );
СЗ . Установить ( «_1SDNLOCK» , «Блокировка номеров документов» );
СЗ . Установить ( «_1SDWNLDS» , «Пакеты обмена данными (УРБД)» );
СЗ . Установить ( «_1SENTRY» , «Проводки» );
СЗ . Установить ( «_1SJOURN» , «Журналы документов» );
СЗ . Установить ( «_1SOPER» , «Операции» );
СЗ . Установить ( «_1SSBSEL» , «Отбор проводок по субконто» );
СЗ . Установить ( «_1SSTREAM» , «Последовательности» );
СЗ . Установить ( «_1SSYSTEM» , «Системная» );
СЗ . Установить ( «_1STOPER» , «Типовые операции» );
СЗ . Установить ( «_1SUIDCTL» , «Уникальности» );
СЗ . Установить ( «_1SUPDTS» , «Обновления объектов (УРБД)» );
СЗ . Установить ( «_1SUSERS» , «Счетчики соединений» );
СЗ . Установить ( «_1SCONNECT» , «Соединение» );
СЗ . Установить ( «CJPROP» , «Параметры ЖР» );
СЗ . Установить ( «CL» , «Календари» );
//Справочники
Для к = 1 по Метаданные. Справочник () Цикл
ТекИд =Метаданные. Справочник ( к ). Идентификатор ;
ТекИмяТаблицы = глМета . ИмяТаблицыСправочника ( ТекИд );
ТекНазваниеТаблицы = «Справочник.» + ТекИд ;
СЗ . Установить ( ТекИмяТаблицы , ТекНазваниеТаблицы );
КонецЦикла;
//Документы
Для к = 1 по Метаданные. Документ () Цикл
ТекИд =Метаданные. Документ ( к ). Идентификатор ;
ТекИмяТаблицы = глМета . ИмяТаблицыШапки ( ТекИд );
ТекНазваниеТаблицы = «Документ.» + ТекИд + » (шапка)» ;
СЗ . Установить ( ТекИмяТаблицы , ТекНазваниеТаблицы );
Если Метаданные. Документ ( к ). РеквизитТабличнойЧасти ()> 0 Тогда
ТекИмяТаблицы = глМета . ИмяТаблицыТабличнойЧасти ( ТекИд );
ТекНазваниеТаблицы = «Документ.» + ТекИд + » (таблица)» ;
СЗ . Установить ( ТекИмяТаблицы , ТекНазваниеТаблицы );
КонецЕсли;
КонецЦикла;
//Регистры
Для к = 1 по Метаданные. Регистр () Цикл
ТекИд =Метаданные. Регистр ( к ). Идентификатор ;
ТекИмяТаблицы = глМета . ИмяТаблицыИтогов ( ТекИд );
ТекНазваниеТаблицы = «Регистр.» + ТекИд + » (итоги)» ;
СЗ . Установить ( ТекИмяТаблицы , ТекНазваниеТаблицы );
ТекИмяТаблицы = глМета . ИмяТаблицыДвижений ( ТекИд );
ТекНазваниеТаблицы = «Регистр.» + ТекИд + » (движения)» ;
СЗ . Установить ( ТекИмяТаблицы , ТекНазваниеТаблицы );
КонецЦикла;
//Журналы расчета
Для к = 1 по Метаданные. ЖурналРасчетов () Цикл
ТекИд =Метаданные. ЖурналРасчетов ( к ). Идентификатор ;
ТекИмяТаблицы = глМета . ИмяТаблицыЖР ( ТекИд );
ТекНазваниеТаблицы = «Журнал расчетов.» + ТекИд ;
СЗ . Установить ( ТекИмяТаблицы , ТекНазваниеТаблицы );
КонецЦикла;
КонецПроцедуры
//________________________________________________________
Процедура Сформировать ()
ПодготовитьСЗ ();
ТЗ =СоздатьОбъект( «ТаблицаЗначений» );
ТЗ . НоваяКолонка ( «Имя» , «Строка» );
ТЗ . НоваяКолонка ( «Название» , «Строка» );
ТЗ . НоваяКолонка ( «Количество» , «Число» );
ТЗ . НоваяКолонка ( «Всего» , «Число» );
ТЗ . НоваяКолонка ( «Данные» , «Число» );
ТЗ . НоваяКолонка ( «Индексы» , «Число» );
ТЗ . НоваяКолонка ( «Свободно» , «Число» );
СписокТаблиц =СоздатьОбъект( «СписокЗначений» );
//________________________________________________________
ЗапросРадуги =СоздатьОбъект( «ODBCQuery» );
Если ЗапросРадуги . Prepare ( «Select RTRIM(CONVERT(char(30),TABLE_NAME)) from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’ AND TABLE_NAME<>‘dtproperties'» , 0 , 0 )= 1 Тогда
Если ЗапросРадуги . Open ()= 1 Тогда
ЗапросРадуги . GotoNext ();
Пока ЗапросРадуги . IsOK ()= 1 Цикл
СписокТаблиц . ДобавитьЗначение ( ЗапросРадуги . GetString ( 0 ));
ЗапросРадуги . GotoNext ();
КонецЦикла;
ЗапросРадуги . Close ();
Иначе
Предупреждение( «Ошибка открытия запроса!» , 10 );
КонецЕсли;
ЗапросРадуги . Reset ();
Иначе
Предупреждение( «Ошибка выполнения запроса!» , 10 );
КонецЕсли;
//Теперь анализируем
Для к = 1 по СписокТаблиц . РазмерСписка () Цикл
ТекстЗапроса = «DECLARE @id int
|DECLARE @pages int
|DECLARE @rows int
|DECLARE @reserved dec(15)
|DECLARE @data dec(15)
|DECLARE @indexp dec(15)
|DECLARE @unused dec(15)
|SELECT @id = id FROM sysobjects WHERE >+ СписокТаблиц . ПолучитьЗначение ( к )+ «‘)
|SELECT @reserved=SUM(reserved) FROM sysindexes WHERE indid in (0, 1, 255) and >
|SELECT @pages = SUM(dpages) FROM sysindexes WHERE indid
|SELECT @pages = @pages + isnull(SUM(used), 0) FROM sysindexes WHERE indid = 255 and >
|SET @data = @pages
|SET @indexp = (select SUM(used) FROM sysindexes WHERE indid in (0, 1, 255) and — @data
|SET @unused = @reserved — (SELECT SUM(used) FROM sysindexes WHERE indid in (0, 1, 255) and >
|SELECT @rows=rows FROM sysindexes WHERE indid
|SELECT name = RTRIM(CONVERT(char(30),object_name(@id))), rows = RTRIM(CONVERT(char(11), @rows)),
|reserved = LTRIM(str(@reserved * d.low / 1024.,15,0)),data = LTRIM(str(@data * d.low / 1024.,15,0)),
|index_size = LTRIM(str(@indexp * d.low / 1024.,15,0)),unused = LTRIM(str(@unused * d.low / 1024.,15,0))
|FROM master.dbo.spt_values d WHERE d.number = 1 and d.type = ‘E'» ;
Если ЗапросРадуги . Prepare ( ТекстЗапроса , 0 , 0 )= 1 Тогда
Если ЗапросРадуги . Open ()= 1 Тогда
ЗапросРадуги . GotoNext ();
Пока ЗапросРадуги . IsOK ()= 1 Цикл
ТЗ . НоваяСтрока ();
ТЗ . Имя = ЗапросРадуги . GetString ( 0 );
ТЗ . Название = СЗ . Получить ( ТЗ . Имя );
ТЗ . Количество =Число( ЗапросРадуги . GetString ( 1 ));
ТЗ . Всего =Число( ЗапросРадуги . GetString ( 2 ));
ТЗ . Данные =Число( ЗапросРадуги . GetString ( 3 ));
ТЗ . Индексы =Число( ЗапросРадуги . GetString ( 4 ));
ТЗ . Свободно =Число( ЗапросРадуги . GetString ( 5 ));
ЗапросРадуги . GotoNext ();
КонецЦикла;
ЗапросРадуги . Close ();
Иначе
Предупреждение( «Ошибка открытия запроса!» , 10 );
Прервать;
КонецЕсли;
ЗапросРадуги . Reset ();
Иначе
Предупреждение( «Ошибка выполнения запроса!» , 10 );
Прервать;
КонецЕсли;
КонецЦикла;
ЗапросРадуги = «» ;
//________________________________________________________
Таб =СоздатьОбъект( «Таблица» );
Таб . ИсходнаяТаблица ( «Таблица» );
Таб . ВывестиСекцию ( «Заголовок» );
ТЗ . ВыбратьСтроки ();
Пока ТЗ . ПолучитьСтроку ()= 1 Цикл
Таб . ВывестиСекцию ( «Строка» );
КонецЦикла;
Таб . ВывестиСекцию ( «Итоги» );
Таб . Опции ( 0 , 0 , 0 , 0 );
Таб . ТолькоПросмотр ( 1 );
Таб . Показать ();
КонецПроцедуры
//________________________________________________________
Процедура Обновить ()
ЗапросРадуги =СоздатьОбъект( «ODBCQuery» );
ТекстЗапроса = «DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS» ;
Если ЗапросРадуги . Prepare ( ТекстЗапроса , 0 , 0 )= 1 Тогда
Если ЗапросРадуги . Open ()= 1 Тогда
Иначе
Предупреждение( «Ошибка открытия запроса!» , 10 );
КонецЕсли;
ЗапросРадуги . Reset ();
Иначе
Предупреждение( «Ошибка выполнения запроса!» , 10 );
КонецЕсли;
ЗапросРадуги = «» ;
КонецПроцедуры
//________________________________________________________
Процедура ПриОткрытии ()
ЗагрузитьВнешнююКомпоненту( «rainbow.dll» );
КонецПроцедуры
Эту обработку можно загрузить в разделе «Скачать».
Evgeniy Korshunov
Понадобилось мне тут давеча оценить размер таблиц в базе данных одного из наших клиентов. Еще со времен 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
Решение 2
http://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