Как узнать размер таблицы ms sql 1с
я унаследовал довольно большую базу данных SQL Server. Кажется, он занимает больше места, чем я ожидал, учитывая содержащиеся в нем данные.
есть ли простой способ определить, сколько места на диске занимает каждая таблица?
Если вы используете среда SQL Server Management Studio (SSMS), вместо запуска запроса (который в моем случае вернул дубликаты строк) вы можете запустить стандартный отчет.
- щелкните правой кнопкой мыши по базе данных
- перейти к Отчеты > Стандартные Отчеты > Использование Диска По Таблице
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 (тот, который был принят), это настраивается для возврата количества столбцов и позволяет фильтровать:
Это приложение с графическим интерфейсом для администрирования и настройки MS SQL Server 2000, а также управления серверами и базами данных MS SQL. Запускается или через меню Пуск:: Программы - Microsoft SQL Server - Enterprise Manager (в случае стандартной установки группы прогамм MS SQL Server 2000). Или же через стандартную консоль Управление компьютером (Computer Management).
2. Что такое QA?
QA - Query Analyzer - приложения для работы с базами данных SQL на языке Transact SQL. Запускается или с пункта меню "Программы - Microsoft SQL Server - Query Analyzer" (в случае стандартной установки группы прогамм MS SQL Server 2000). Другим способом является запуск из SQL Server Enterorise Manager через меню Tools. Также можно запустить через командную строку, выполнив "Пуск - Выполнить - isqlw.exe".
3. Что такое BOL?
BOL - SQL Server Books Online - Справочное руководство по языку Transact-SQL (и не только). Вызвать можно или с Query Analyzer нажав Shift+F1 (или через меню Help - Transact-SQL Help). Или же через меню Пуск: Программы - Microsoft SQL Server - Books Online (в случае стандартной установки группы прогамм MS SQL Server 2000).
4. Какие особенности 1С при доступе к MS SQL Server 2000?
5. Как установить или конвертировать конфигурацию с dbf версии в SQL версию базы данных?
1. Надо иметь приложение 1С, поддерживающую работу с SQL Server. Рекомендуется использовать релизы 15 или 25 (15 более медленный, и не поддерживает конвертацию данных в 1С 8.0). Основные отлияия SQL версии приложения 1С, это название запускаемого файла 1cv7s.exe и характерное окно выбора формата хранения данных, где можно выбрать форматы: Файлы *.DBF;*CDX или MS SQL Server.
2. Еслу уже имеется база (с какими то заполненными данными), то предварительно надо выгрузить эти данные. Это делается из меню Конфигуратора: Администрирование - Выгрузить данные.
3. Создать базу в SQL Server Enterorise Manager. Закрыть SQL Server Enterorise Manager.
4. Создать базу в 1С в режиме Конфигуратора, выбрать тип хранения данных - MS SQL Server.
5. Создать в Конфигураторе пользователя.
6. Определить параметры доступа к MS SQL Server с Конфигуратора: Меню - Администрирование - Параметры базы данных SQL. .
7. Выполнить загрузку данных, елси есть выгрузка: Меню - Администрирование - Загрузить данные.
8. Если данных нет - создать метаданные конфигурации или загрузить измененую конфигурацию.
Необходимо учитывать, что при стечении некоторых обстоятельств, данные могут не загружаться в MS SQL Sever (например длинные строки в отборах или дубли данных в индексных полях), для таких случаев описание будет отдельное, или же можно поискать в интернете описание подобных ошибок.
6. Как изменить тип авторизации доступа к SQL Server?
Вся проблема в том, что 1С подключается к MS SQL Server только под логином SQL и авторизацией MS SQL Server а Windows. Если при установке был выбран режим авторизации только Windows (Windows only) - то подключиться 1С к базе в таком случае не сможет. Для того чтобы изменить тип авторизации надо открыть SQL Server Enterorise Manager, развернуть в левом углу группу "Concole Root - Microsoft SQL Servers", развернуть SQL Server Group, выбрать необходимый сервер (соответствует или имени компьютера или (local) - для локального сервера). Если серверов нет - надо зарегистрировать новый сервер, выбрав из меню Action - New SQL Server Registration. . После выбора сервера надо через меню Action выбрать пункт меню Properties (или через контекстное меню). В появишемся окне "SQL Server Properties (Configure)" надо перейти на закладку Security, в группе Security изменить Authentication на SQL Server and Windows. Перезапустить сервис (Управление комьютером - сервисы и приложения - сервисы - MSSQLSERVER - перезапустить), или перегрузить компьютер с установленным MS SQL Server.
7. Как создать базу для 1С на SQL Server
Есть как минимум 2 способа создания базы. 1 - посредством графического интерфейса SQL Server Enterorise Manager. Для создания базы, необходимо выбрать сервер, на котором будет создана база. Установить курсор на Databases, выбрать через контекстное меню или через меню Action пункт "New database. ". На закладке General ввести имя базы (например base_1c), на закладке Data Files скорректировать путь на файл данных (поле location), точно также на закладке Transaction Log можно скорректировать путь для файла транзакций. Опыт показывает, что если эти 2 файла (данных и транзакций) хранятся на разных физических дисках - то скрость системы повышается (при условии что скорость дисков также сопоставима).
Второй метод - выполнив инструкцию на языке Transact-SQL (например с QA)
8. Как поменять владельца базы SQL
Доступ к базе с 1С возможен только под владельцем базы. для того чтобы поменять влядальце, надо в QA выполнить следующий код:
9. Как переиндексировать базу на MS SQL Server
Если хранимой процедуры _1sp_DBReindex нет в базе данных (база не 1С), то можно выполнить такой код (это и есть код хранимой процедуры _1sp_DBReindex):
10. Как проверить (восстановить) базу на MS SQL Server средствами сервера
Переиндексация была рассмотрена в 9 пункте.
11. Как упаковать (зашринковать) базу на MS SQL Server
Упаковать базу данных можно или с помощью графического интерфейса приложения SQL Server Enterorise Manager, или с помощью выполнения кода (например с QA).
Первый случай выполняется посредством вызова контекстного меню на необходимой базе и выбора All Tasks - Shrink Database. .
Второй случай опишем несколько подробнее. В приведенном коде выполняется 4 строки кода. 1 строка - усечение всей базы. 2 строка - усечение только DATA файла до размера 101 мб. 3 строка - усечение только файла транзакций до размера 0 мб (несколько килобайт). 4 - строка - очистка журнала транзакций (иногда файл транзакций усечь невозможно, по причине заисших открітіх транзакций, выполнение этой команды должно привести до полной очистки журнала транзакций).
Если приведенный више код не привел к требуемым результатам, можно попробовать выполнить следующий код:
12. Как упаковать (дефрагментировать) индексы базы на MS SQL Server
13. Как уменьшить рост журнала транзакций базы на MS SQL Server
Данная информация находилась по адресу: http://www.perlscript.ru , сейчас находится по адресу: http://www.softpoint.ru .
Для того чтобы файл транзакций не рос очень стремительно, надо в свойствах базы данных установить режим "Simple" поля Recovery закладки Options окна свойств базы данных.
14. Как скопировать базу 1С, работающую с базой БД в формате MS SQL Server?
- Сделать выгрузку/загрузку в режиме Конфигуратора. Этот способ наиболее длительный, и в тоже время делать эту процедуру можно делать лишь тогда, когда другие пользователи не работают в 1С с требуемой базой. Методика того как это делается подробно описана в литературе, поставляемой с коробоынми вариантами 1С. Отмечу лишь что технология загрузки почти ничем не отличается от описанной в пункте 5 "Как установить или конвертировать конфигурацию с dbf версии в SQL версию базы данных" методики.
- Скопировать каталог базы данных 1С и скопировать файлы базы данных SQL. Этот способ является наименее временизатратным (тратится время только на дисковые операции копирования). Но выполнять его также можно лишь тогда, когда с базой 1С никто не работает. Описание процесса:
- в появившемся окне в поле "MDF file of database to atach:" надо внести полный путь к файлу MDF базы данных (или выбрать этот файл нажав на кнопку выбора файла слева от этого поля).
- сделать Atach Database для новой базы. Заходим в Enterprise Manager, выбираем необходимый сервер и становимся на ветку дерева Databases. Вызываем контекстное меню (или меню Action главного меню) - Tools - All Tasks - Atach Database .
- в появившемся окне:
На второй закладке "Options" необходимо выбрать "Force restore over existing database" (1). Также необходимо изменить полный путь к фацлу данных (mdf) и к файлу транзакций (ldf), на пути к базе в которую происходит восстановление:
15. Как изменить место размещения файлов базы tempdb MS SQL Server
16. Как узнать логин и пароль подключения 1С к MS SQL Server
// индексы значений с списке
// server = 2, db = 4, uid = 6, pwd = 8, checksum = 10
Function ConnectionString() export
Var SQLKeyCode[36], ConnectCode[200];
ConnectFile = IBDir() + "1Cv7.DBA";
If FS.ExistFile(ConnectFile) = 0 Then
Message("Это не SQL - база!", "!");
Return (0);
EndIF;
FSO = CreateObject("Scripting.FileSystemObject");
F = FSO.OpenTextFile(ConnectFile, 1);
ConnectLen = 0;
While F.AtEndOfStream = 0 Do
ConnectLen = ConnectLen + 1;
ConnectCode[ConnectLen] = Asc(F.Read(1));
EndDo;
F.Close();
FSO = 0;
SQLKey = "19465912879oiuxc ensdfaiuo3i73798kjl";
For i = 1 To 36 Do
SQLKeyCode[i] = Asc(Сред(SQLKey, i, 1));
EndDo;
Connect = "";
For i = 1 To ConnectLen Do
Connect = Connect + Chr(XOR(ConnectCode[i], SQLKeyCode[(i - 1) % 36 + 1]));
EndDo;
vl=createobject("ValueList");
Connect=StrReplace(Connect," <","");
Connect=StrReplace(Connect,">","");
vl.fromSeparatedString(Connect);
return (vl);
EndFunction
17. Как узнать размер таблиц в базе данных MS SQL Server
19. При попытке монопольно войти в 1С или при записи конфигурации выдается сообщние "База данных не может быть открыта в однопользовательском режиме"
Причиной такого поведения 1С есть невозможность захватить базу данных в монопольном режиме (так называемый режим Single User). Это может произойти по нескольким причинам. 1 - есть другое приложение, которое в данный момент подключено к базе, которая является базой SQL для 1С. в таком случае это приложение надо или закрыть или же открыть в нем другую базу (например в Query Analiser-е использовать команду USE Master). Если таким приложений нет (которые явно могли присоединиться к базе), можно попробовать остановить и снова запустить SQL Server (или перегрузить сервер, на котором находиться SQL база и SQL сервер). 2 - произошел сбой во время работы с базой данных в монопольном редиме с аварийным отключением соединения. При таком стечении обстоятельств, перезапуск SQL сервера не поможет. Необходимо удалять ссылки на зависшые процессы. Чтобы этого не делать, можно отключить базу (сделать Detach) и обратно подключить под другим именем (поле Attach as), после чего надо изменить настройки подключения и в Конфигураторе для текущей базы 1С (в качестве базы SQL указать базу с новым именем).
20. Как узнать какой сервис-пак установлен на SQL Server 2000 или SQL Server 2005
SELECT
@version = @@microsoftversion / 0x01000000,
@build = cast ( @@microsoftversion as binary (2))
21. Как решить проблемы с памьятью. 1 - Windows Server не видит больше 4 GB ОЗУ, 2 SQL Server не может использовать больше 2 GB.
22. Медленно работает 1С по сети с базой на SQL Server.
Для такой настройки ODBC должен быть версии не ниже 3.5
С помощью утилиты Client Network Utility (CLICONFG.EXE) (Program - Microsoft SQL Server - Client Network Utility) необходимо настроить протокол общения с SQL сервером. По умолчанию "Default network library" установлен в "Named Pipes", надо установить в "TCP/IP" и добавить строку в Server alias configuration. Где, соответственно, указать псевдоним, имя сервера, протокол TCP/IP, порт (можно оставить без изменений, если на сервере порт не был изменен). Вот пример настройки:
1 - пример настройки протоколов, 2 - ставить необходимо когда клиент и SQL Server находятся физически на одном компьютере, в єтом случае обмен идет не по сетвевым ресурсам, а через память.
23. После установки на SQL Server 2000 Service Pack 4 SQL стал медленне работать и меньше загружать память.
24. После установки на SQL Server 2000 проведение документов замедлилось
25. Не могу подключиться к SQL серверу
26. По каким то причина SQL Server очень медленно работает
- патчим bkend.dll
- в свойствах базы данных на SQL Server ставим Compatibility Level SQL Server 2000(80)
28. Как подключиться к SQL2005 по IP адресу а не по имени сервера
Проблема в том, что при установке по умалочанию во всех редакциях кроме Enterprise Edition включается только протокол SharedMemory, необходимо включить еще и TCP/IP для доступа к серверу по сети. Для этого необходимо запустить приложение Programm - Microsoft SQL Server 2005 - Configuration Tools - SQL Server Configuration Manager и включить протококол TCP/IP:
29. Как получить имя пользователя, который занимает много процессорного времени SQL-Server.
Очень часто приходиться сталкиваться с такой ситуацией, когда SQL-Server полностью или почти полностью занимает (загружает) процессор или дисковую подсистему во время работы. Для того чтобы выяснить кто из пользователей "загрузил" SQL-Server надо войти в SQL-Profiller и посмотреть какой из запросов (после его віполнения, т.е. в момент когда идет выполнение запроса, запрос в Profiler не попадает, а это знгачит что Profiler надо запустить до момента возникновения ситуации, которую надо отследить). Когда будет выявлен запрос, который занимет много процессорного времени (по колонке CPU, а также время его віполнения в милисекундах - колонка Duration), можно узнать его SPID в колонке SPID того же Profiler-а:
Реузльтат этого запроса может дать нам овтет в первом из двух случаев. Есть 2 варианта работы 1С версии 7.7 с SQL-сервером (точнее штатный вариант один, разница лишь в том, где вполняется приложение 1С): 1 - работа в локальной сети, пользователи работают с приложением 1С на своих локальных компьютерах; 2 - работа на терминальном сервере, когда пользователи для работы с приложением 1С сначал подключаются к серверу терминалов, а уже с него работают с 1С.
Так если пользователь с указанным SPID работает по сети (не посредством терминального сервера), то в поле hostname - можно увидеть с какого компьютера был послан запрос, ну и зная компьютер можно легко узнать а кто же там работал в требуемое время.
Если же пользователи работают с терминального сервера - ситуация усложняется, так как в поле hostname - будет имя сервера терминадов. В этом случае может помочь значение поля hostprocess. Именно под этим номером на клиентском компьютере (сервере терминалов) стартовал процесс 1cv7s.exe, и посмотрев в Task Manager-е список процессов на термианльном сервере можно найти от имени какого пользователя стартовал процесс и таким образом выясгить имя пользователя:
30. Как сделать чтобы 1С подключался к SQL не под одним логином (SQL аутентификация), а под логинами Windows (Windows аутотентификация).
declare @textZP VarChar (1000)
select @textZP = 'select spid,
ecid,
status=rtrim(status),
loginame=rtrim(loginame),
hostname=rtrim(hostname),
program_name=rtrim(program_name),
blk=convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,cpu
,physical_io
,memusage
,login_time
from master.dbo.sysprocesses
where (spid >= 0 and spid
if (@dbname is NOT NULL )
if (@progname is NOT NULL )
select @textZP = @textZP + '
and ((db_name(dbid) = ''' + isnull (@dbname,'')+ ''') and (dbid <> 0))
and upper(rtrim(program_name)) =''' + upper (@progname)+ ''''
else
select @textZP = @textZP + '
and ((db_name(dbid) = ''' + isnull (@dbname, '' )+ ''') and (dbid <> 0))'
else
if (@progname is NOT NULL )
select @textZP = @textZP + '
and upper(rtrim(program_name)) =''' + upper (@progname)+ ''''
select @textZP = @textZP + '
order by ' +@orderBy
31. Работа 1С v77 под Vista и MS SQL 2005
- 1 - Работа в файловом варианте базы.
- 2 - Работа с базой в формате MS SQL.
- sqlsrv32.dll
- sqlsrv32.rll
- odbcbcp.dll
32. При подключении 1С возникате ошибка "SQL State 42000. Native 7202. . SQL Server Could not find serve 'ServerName' . "
33. Как создать дополнительные индексы для таблиц
34. Резервная копия базы SQL и MD-файла
Для выполнения резервного копирования базы SQL и сохранением файла конфигурации (1Cv7.md) можно создать задание (job) на SQL сервере с примерно такой инструкцией на языке Transact - SQL:
35. Как разрешить в SQL Server 2005 использование xp_cmdshell
Для включения возможности работы некоторых хранимых процедур и функций доступа в SQL 2005 предусмотрено приложение SQL Server Surface Area Configuration. Его можно открыть с меню программ Microsoft SQL Server 2005 - Configuration Tools - SQL Server Surface Area Configuration. В появившемся окне надо выбрать "Surface Arrey Configuration for Feature", затем включить необходимое значение и нажать кнопку Применить (Apply).
Как известно, для базы данных в формате 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" );
КонецПроцедуры
Рост размера информационной базы является закономерным явлением ее эксплуатации, но, в некоторых случаях, данный процесс свидетельствует об ошибках в архитектуре системы. Среда 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 by Table), «Использование дисковой памяти верхними таблицами» (Disk Usage by Top Tables)
Отчет содержит подробные данные об использовании места на диске таблицами, расположенными в базе данных. Отличие этих двух отчетов заключается лишь в том что в отчете «By Top Tables» вывод происходит только для «верхних» (первых) 1000 таблиц.
В отчете представлена информация:
- Количество записей в таблице базы данных (Records)
- Размер зарезервированного пространства на диске (Reserved)
- Размер данных на диске (Data)
- Общий размер индексов таблицы на диске (Indexes)
- Размер не используемого пространства (Unused)
Отчет «Использование дисковой памяти секцией» (Disk Usage by Partition)
Отчет содержит подробные данные об использовании места на диске индексом и секциями, расположенными в базе данных.
Хотел бы обратить Ваше внимание что в данном отчете неверно рассчитывается дисковое пространство по кластерному индексу. Для получения реально используемого дискового пространства кластерным индексом можно: из «объема, используемого всеми индексами таблицы» (указанном в отчете «Использование дисковой памяти таблицей») вычесть «объем всех не кластерных индексов» (по отчету «Использование дисковой памяти секцией»)
В отчете представлена информация:
Хранимые процедуры
Данные о размере базы данных и таблиц также можно получить с помощью хранимой процедуры sp_spaceused Management Studio.
Синтаксис:
sp_spaceused [[ @objname = ] ‘objname’ ]
[,[ @updateusage = ] ‘updateusage’ ]
В процедуре могут быть использованы 2 не обязательных параметра:
- @objname — Полное или неполное имя таблицы, индексированного представления или очереди. Если параметр не указан — результаты возвращаются для всей базы данных.
- @updateusage — Указывает на необходимость запустить процедуру обновления сведений
Примеры запросов по всей базе данных и по конкретной таблице приведены ниже:
Результат работы хранимой процедуры sp_spaceused
В первом блоке отражен результат запроса по всей базе данных, во втором — по конкретной таблице.
Динамические административные представления
Помимо вышеприведенных способов, для получения необходимой информации можно обратиться к динамическим административным представлениям. На самом деле, как отчеты, так и хранимая процедура sp_spaceused сама их и использует. Способы получения информации с помощью динамических административных представлений будут рассмотрены в следующих статьях.
Примеры диагностируемых ошибок
Ошибка | Следствие |
---|---|
Использование остаточного регистра накопления для оборотных данных | Итоговая таблица регистра накопления не будет закрываться, что приведет к ее чрезмерному разрастанию |
Ошибки в алгоритме закрытия остаточного регистра | Аналогично предыдущей ошибке |
Отсутствует перерасчет итогов по регистрам | Рост таблицы итоговых записей за счет «нулевых» записей |
Использование избыточных индексов | Рост размера индексов таблицы |
Хранение массивных двоичных данных в таблицах БД | Рост размера таблицы |
Данный перечень не является полным, но одним из общих сигналов приведенных выше ошибок является чрезмерный рост тех или иных таблиц базы данных. Естественно, большой размер таблиц/индексов не является прямым указанием на наличие ошибок, в то же время следует «приглядеться» к такого рода сигналам и провести их анализ.
Для ошибок «Использование остаточного регистра накопления для оборотных данных» и «Ошибки в алгоритме закрытия остаточного регистра» рост таблиц итоговых записей является буквально прямым следствием, но, возможна ситуация, что таков набор данных (приход был, а расхода еще не было).
Для ошибки «Отсутствует перерасчет итогов по регистрам» также характерен рост таблицы итоговых записей, но при этом (особенно для регистра остатков) дополнительным условием является наличие «нулевых» записей в регистре итогов. Подробнее об этом можно прочитать в статье «Что такое итоги регистров накопления».
Для ошибки «Использование избыточных индексов» первоочередным, конечно же, является анализ необходимости такого индекса. Но никто не станет анализировать все индексы базы данных без причин, а вот наличие «тяжелых» индексов таблицы может стать одним из таких сигналов к действию.
Касательно «Хранения массивных двоичных данных в таблицах БД», я считаю это неприемлемым, данные такого рода должны храниться на диске независимо от базы данных с обеспечением доступа к ним из системы, а также с обеспечением отдельной процедуры резервного копирования.
Часто возникает необходимость определить в какой таблице СУБД хранится тот или иной объект метаданных. Или наоборот, какой объект метаданных соответствует определенной таблице СУБД. Здесь стоит упомянуть, что имена таблиц и полей СУБД, в которых хранятся объекты метаданных 1С:Предприятия, не соответствуют именам объектов метаданных, их реквизитам, измерениям, ресурсам. Получение такой информации не представляет особой сложности, тем не менее, если Вы никогда этого не делали, данная статья будет Вам полезна.
Получение структуры хранения базы данных
В целом, эту статью можно было бы изложить в одном коротком предложении: для получения информации о структуре хранения базы данных, а также соответствие имен объектов в терминах 1С:Предприятие и СУБД необходимо воспользоваться методом ПолучитьСтруктуруХраненияБазыДанных () . Но мы пойдем дальше и попробуем разобраться с областью применимости этого метода, а также организуем удобную работу с возвращаемыми методом данными.
Давайте, для начала, посмотрим что же возвращает данный метод. Результатом вычисления данной функции будет таблица значений, в которой каждая строка таблицы определяет одну таблицу СУБД. В первых двух колонках указано имя таблицы в терминах СУБД и в терминах 1С:Предприятия. Далее идут колонки описывающие к какому метаданному относится таблица и назначение этой таблицы СУБД. Последние две колонки содержат вложенные таблицы значений полей и индексов таблицы СУБД. В таблице полей содержится соответствие имен полей в терминах СУБД и терминах 1С:Предприятие, а так же связь поля с объектом метаданного (какой реквизит/ресурс/измерение). Таблица индексов содержит набор имен индексов, а так же вложенную таблицу значений, содержащую поля таблицы СУБД, включенные в состав индекса, и соответствие их имен в терминах СУБД и 1С:Предприятие.
Структура хранения базы данных
Как мы видим, структура, возвращенная методом, включает несколько уровней вложенности и требует создания инструмента для удобного использования. Мы не будем рассматривать как его сделать, поскольку, эта задача с одной стороны простая, с другой каждый может привнести в нее свои «фишки». Пример такого инструмента (обработки) вы можете найти во вложении к статье. Ниже приведен скриншот обработки.
Обработка «Структура хранения базы данных»
Область применимости
Давайте попробуем проанализировать ту информацию, которую нам предоставляет платформа с помощью метода ПолучитьСтруктуруХраненияБазыДанных () и сопоставим ее с реальной структурой СУБД (рассмотрим на примере MS SQL Server). Сразу оговорюсь что метод имеет 2 параметра: первый устанавливает отбор на метаданные по которым необходимо получить информацию; второй устанавливает режим вывода информации: «В терминах СУБД» или «В терминах 1С:Предприятие» (данная опция так же присутствует в обработке). Представленный ниже текст относится к режиму вывода «В терминах 1С:Предприятие» если не указано иного.
Подготовка базы
Давайте создадим пустую информационную базу в режиме совместимости с 8.2.16, основной режим запуска установим «обычное приложение». В конфигурацию добавим 2 документа (со значениями по умолчанию) и регистр сведений «АнализСтруктурыХранения» (периодичность в пределах дня). У регистра добавим реквизиты:
- «Число»: тип число
- «Строка»: тип строка
- «ДокументСсылка»: тип ДокументСсылка1
- «СоставнойЧислоСтрока»: составной тип Число и Строка
- «СоставнойДокументСсылка»: составной тип ДокументСсылка1 и ДокументСсылка2
- «СоставнойЧислоСтрокаДокументСсылки»: составной тип Число, Строка, ДокументСсылка1, ДокументСсылка2
Далее добавим общий реквизит «Разделитель» (тип Число), в состав включим наши документы и регистр сведений. Установим «Разделение данных» в «разделять» и разрешим создать параметры сеансов, «Использование разделяемых данных» установим в значение «независимо и совместно». Обновим конфигурацию.
Набор таблиц базы данных в СУБД и методе платформы
Откроем обработку в 1С:Предприятие, а также откроем список таблиц базы данных в СУБД и сравним их. Как видно, метод ПолучитьСтруктуруХраненияБазыДанных () вернул не полный список таблиц базы, который мы можем увидеть на уровне СУБД, а набор таблиц за исключением системных таблиц, таких как Config, ConfigSave, v8users и другие.
Структура базы данных на уровне СУБД и платформы
Набор полей (колонок) в таблице
Перейдем теперь к нашему регистру сведений. Получим набор полей таблицы регистра с помощью обработки, а так же получим набор колонок в СУБД и сравним их. Как видно, для не составных типов (вне зависимости от типа) в СУБД используется 1 колонка таблицы, структура хранения 1С:Предприятия отражает аналогичную информацию. Если же мы перейдем к полям составного типа, то в обработке все так же выводится информация только об 1 поле, как мы его и задавали в конфигурации, а в СУБД это поле хранится в нескольких колонках и их количество может быть различно в зависимости от состава типа поля, определенного в конфигурации. Это связано со способом хранения информации платформой и более подробно можно прочесть на сайте ИТС. Замечу, что при анализе запроса в СУБД или анализе плана запроса необходимо учитывать этот факт и правильно интерпретировать имена колонок.
Структура полей (колонок) в таблице базы данных
Состав индексов
Проведем аналогичное сравнение индексов и их состава, полученных обработкой и реально существующих в СУБД. Первое что бросается в глаза, это 12 индексов в таблице СУБД, в то время как обработка выводит только 2. По своей сути все верно, если индексы разделить по «назначению» («ByDims» — по измерениям, и «ByPeriod» — по периоду). Размножение произошло по причине того что в составных типах включено более 1 примитивного типа или примитивный тип включен вместе с ссылочным — в этом случае платформа создает для каждой комбинации свой индекс. Так, в нашем примере реквизит «СоставнойЧислоСтрока» включает 2 примитивных типа, а «СоставнойЧислоСтрокаДокументСсылки» включает 2 примитивных типа и ссылочные (их количество не важно, важно наличие хотя бы одного). Таким образом, платформа создала комбинации индексов: ЧислоЧисло, ЧислоСтрока, ЧислоСсылка, СтрокаЧисло, СтрокаСтрока, СтрокаСсылка.
Откроем состав одного из индексов и сопоставим поля. Как видно, в СУБД первым полем индекса стоит «Fld12», (по таблице полей можно определить что это «Разделитель«) и оно отсутствует составе индекса, выведенного в обработке.
Структура и состав индексов на уровне СУБД и платформы
Продолжаем эксперименты
Давайте продолжим наши эксперименты и (в исследовательских целях) нарушим лицензионное соглашение. Для того чтобы быть уверенными что не сломаем нашу базу данных, сделаем ее резервное копирование средствами СУБД.
Сделаем следующие вещи:
- Откроем в СУБД индекс «ByDocNum» объекта Документ1 и поменяем порядок следования полей Number и IDRRef
- Удалим в СУБД индекс «ByDocDate» объекта Документ1
- Добавим в СУБД колонку, например, «MyColumn» с числовым типом в таблицу регистра сведений
- Добавим в СУБД в нашу базу данных новую таблицу, например, «MyTable»
Откроем нашу обработку и проверим что получилось:
- Платформа не знает о том что порядок полей в индексе был изменен
- Платформа не знает о том что индекс удален
- Платформа не видит добавленную колонку
- Платформа не видит добавленную таблицу
Задание для самостоятельной работы
Как ранее было сказано, вышеприведенное исследование выполнено для режима «В терминах 1С:Предприятие». Предлагаю проделать всю вышеприведенную работу для режима «В терминах СУБД» самостоятельно и сделать соответствующие выводы. Оговорюсь лишь о том, что большая часть расхождений между реальной структурой в СУБД и структурой возвращенной методом платформы — исчезают.
Выводы
Метод ПолучитьСтруктуруХраненияБазыДанных () предоставляет достаточно полную и корректную информацию о структуре хранения базы данных, а так же выводит соответствия имен таблиц, их колонок, и индексов в терминах СУБД и 1С:Предприятие. Но, при этом есть некоторые ограничения при работе с этим методом:
- Нет информации о системных таблицах (но она особо и не нужна)
- Состав полей отражается с точки зрения 1С, а не с точки зрения хранения в СУБД (только в терминах 1С:Предприятие)
- Набор индексов так же отражается с точки хранения 1С, а не с точки зрения СУБД (только в терминах 1С:Предприятие)
- Некоторые существующие поля индекса могут быть не отражены в составе индекса на уровне платформы (только в терминах 1С:Предприятие)
- Информация, видимо, строится не по структуре базы данных, а по некому представлению платформы о структуре базы на основании метаданных конфигурации и их свойств
- Необходимо иметь ввиду что механизм работы метода может быть изменен в другой версии платформы
Таким образом, если необходимо получить точную информацию о структуре базы данных, необходимо получать эту информацию с помощью СУБД, а при получении данных методом ПолучитьСтруктуруХраненияБазыДанных () лучше использовать режим «В терминах СУБД»
Читайте также: