Дневник megavtogal.com

МЕНЮ
  • Контакты
  • Статьи

Реиндексация 1с mssql как сделать

Обновлено: 06.01.2025

sp_MSforeachtable "DBCC DBREINDEX ('?') WITH NO_INFOMSGS"

use "karat_mebel"
DECLARE @MyTable varchar(32)
DECLARE @MyIndex varchar(32)
DECLARE MyCursor CURSOR FOR
SELECT o.name, i.name
FROM sysobjects o INNER JOIN sysindexes i ON o.id = i.id
WHERE (o.xtype = 'U') AND (INDEXPROPERTY(i.id, i.name, 'isStatistics') = 0) AND (i.dpages > 0)
ORDER BY o.name, i.indid
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @MyTable, @MyIndex
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'Дефрагментация индекса '+@MyIndex+' из таблицы '+@MyTable
DBCC INDEXDEFRAG (0,@MyTable,@MyIndex)
DBCC UPDATEUSAGE (0,@MyTable,@MyIndex)
DBCC DROPCLEANBUFFERS
FETCH NEXT FROM MyCursor INTO @MyTable, @MyIndex
END
CLOSE MyCursor
DEALLOCATE MyCursor

(4) скуль, реиндексирует обновляет дефрагментирует индексы, ну и дропает буфер на всякий случай.
можно удаленно.
(но уже VBS)
Const adOpenStatic = 3
Const adLockOptimistic = 3
User="Пользователь" 'Это администратор домена или того компа где SQL (если нет домена)
Pass="Пароль" 'это его пароль
DB="karat_mebel" 'Это база
SQLServer="proxysrv" 'это скуль сервер
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Command="use """ & DB """ & vbCrLf & _
"DECLARE @MyTable varchar(32)" & vbCrLf & _
"DECLARE @MyIndex varchar(32)" & vbCrLf & _
"DECLARE MyCursor CURSOR FOR" & vbCrLf & _
"SELECT o.name, i.name" & vbCrLf & _
"FROM sysobjects o INNER JOIN sysindexes i ON o.id = i.id" & vbCrLf & _
"WHERE (o.xtype = 'U') AND (INDEXPROPERTY(i.id, i.name, 'isStatistics') = 0) AND (i.dpages > 0)" & vbCrLf & _
"ORDER BY o.name, i.indid" & vbCrLf & _
"OPEN MyCursor" & vbCrLf & _
"FETCH NEXT FROM MyCursor INTO @MyTable, @MyIndex" & vbCrLf & _
"WHILE @@FETCH_STATUS=0" & vbCrLf & _
"BEGIN" & vbCrLf & _
"DBCC INDEXDEFRAG (0,@MyTable,@MyIndex)" & vbCrLf & _
"DBCC UPDATEUSAGE (0,@MyTable,@MyIndex)" & vbCrLf & _
"DBCC DROPCLEANBUFFERS" & vbCrLf & _
"FETCH NEXT FROM MyCursor INTO @MyTable, @MyIndex" & vbCrLf & _
"END" & vbCrLf & _
"CLOSE MyCursor" & vbCrLf & _
"DEALLOCATE MyCursor" & vbCrLf
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQLServer & ";Trusted_Connection=Yes;Initial Catalog=" & DB & ";User ;Password=" & Pass & ";"
objRecordSet.Open Command, objConnection, adOpenStatic, adLockOptimistic
objConnection.Close

(4,5) Прошу прощения,Коллега,но реиндексации (ре-воссоздания)
индексов здесь,как раз и не происходит,дефрагмент.,да,согласен,но
реиндексация-ни божеж мой!

млин, у меня база нифига не работает.
в пользовательском режиме заходит, а при выгрузке архива в конфигураторе, или Тестирования базы выдаёт ошибку
native 21
Fatal Error 7987.
Невосстановимая ошибка БД.
Что делать?

Одной из распространенных причин не оптимальной работы СУБД MS SQL являет неправильная настройка выполнения регламентных операций в системе управления базы данных. Обычно правильная настройка сразу же оказывает положительный эффект на производительность системы в целом. Эту информацию должен знать каждый программист 1С.

Выполнение регламентных операций в SQL

Виды регламентных операций СУБД MS SQL

  1. обновление статистик;
  2. очистка процедурного кеша;
  3. дефрагментация индексов;
  4. реиндексация таблиц БД.

Обновление статистик

MS SQL подбирает оптимальный план запросов на основании статистических данных, полученных за период. Однако эта статистическая информация может быть не всегда актуальной. Обновление статистики позволяет системе подобрать оптимальный план запроса.

По умолчанию SQL Server настроен на частичное автоматическое обновление статистики. Однако желательно периодически запускать полное обновление статистики (full scan).

Если вы только начинаете программировать в 1С или просто хотите систематизировать свои знания - попробуйте Школу программирования 1С нашего друга Владимира Милькина. Пошаговые и понятные уроки даже для новичка с поддержкой учителя.
Попробуйте бесплатно по ссылке >>

Рекомендуется выполнять не реже одного раза в день.

Очистка процедурного кеша

Оптимизатор MS SQL обработки запроса кеширует данные плана запроса. Это сделано для лучшей производительности системы. Но однако так же, как в примере со статистикой, это может и помешать оптимальному выполнению запроса.

Для очистки кэша 1С не нужно выгонять пользователей, она работает в фоновом режиме.

Дефрагментация индексов

Когда выполняется вставка данных в таблицу, эти данные сохраняются на указанной странице среди страниц уровня листовых вершин кластеризованного индекса. Ключи некластеризованного индекса также должны быть вставлены на правильную страницу среди страниц уровня листовых вершин некластеризованного индекса. Если на этой странице нет места, то SQL Server должен выполнить разбор страниц, это означает выделение и привязку новой к соответствующему индексу. Подобные ситуации приводят к фрагментации индекса. Данный эффект возникает при большой нагрузке на СУБД MS SQL и снижает общую производительность системы.

Рекомендуется выполнять данную регламентную операцию не реже раза в неделю.

Реиндексация таблиц БД

Данную регламентную операцию рекомендует производить не реже одного раза в неделю.

SQL я знаю достаточно мало. Особенно тонкости. Поэтому просьба к знающим людям - дополните меня и ответьте на вопросы, которые есть в статье.

Как советуют знающие люди в своих статьях (в частности Гилев в статье по созданию базы 1с в SQL 2005) нужно регулярно дефрагментировать индексы и обновлять статистику. Для этого есть штатные средства и простой скрипт

USE db1cut
GO
DECLARE @MyTable varchar(252)
DECLARE @MyIndex varchar(252)
DECLARE @DSQL varchar(8000)
DECLARE MyCursor CURSOR FOR
SELECT o.name, i.name
FROM sysobjects o INNER JOIN sysindexes i ON o.id = i.id
WHERE (o.xtype = 'U') AND (INDEXPROPERTY(i.id, i.name, 'isStatistics') = 0) AND (i.dpages > 0)
ORDER BY o.name, i.indid

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @MyTable, @MyIndex
WHILE @@FETCH_STATUS=0

BEGIN
PRINT 'Перестройка индекса '+@MyIndex+' из таблицы '+@MyTable
SET @DSQL = ' BEGIN TRY
ALTER INDEX '+@MyIndex+' ON '+@MyTable+
' REBUILD WITH (ONLINE = ON,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
END TRY

BEGIN CATCH ALTER INDEX '+@MyIndex+' ON '+@MyTable+
' REBUILD WITH (ONLINE = OFF,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON) END CATCH;'
EXEC(@DSQL)

FETCH NEXT FROM MyCursor INTO @MyTable, @MyIndex

END
CLOSE MyCursor
DEALLOCATE MyCursor

Тут вроде всё понятно - пробегает по индексам и перестраивает их. Обычно перестройка (Rebuild) сопровождается обновлением статистики, но параметр STATISTICS_NORECOMPUTE = ON отключает пересчет статистики. В связи с этим вопрос: из каких соображение в данном скрипте отключен пересчет статистики? Ведь в любом случае после реорганизации/перестройки требуется пересчитать статистику. Или всё-таки нет?

Но данный скрип имеет недостаток - на больших базах долго выполняется. При круглосуточной работе неприменим. Для этого есть скрипты с избирательной перестройкой, дефрагментацией

Скрипт производит выборочную дефрагментацию регистров. НО! Скрип предназначен для SQL 2000 и для дефрагментации использует конструкцию

Реакция на эту публикацию оказалась очень неоднозначной, в практике встречал многое, но того что высказано в критике не видел. Что конечно не говорит что этого не может быть. Как говорил оин из моих учителей наставников - "Видишь суслика? Нет? А он есть! Так и с ошибками в коде любой программы." Впрочем это все лирика.

Не буду утверждать что преложенный регламент обслуживания подойдет всем. Также отмечу что SQL server развивается и регламент обслуживания также может претерпеть изменения. Текущий пример регламента прелагаю использовать всем малым и средним компаниям. Навредить предложенный регламент не может. После описания регламента приведены запросы по более детальному анализу скульной БД, ремонт БД (2 типовые ошибки), запуск отладки на сервере, режимы запуска 1С (+ в файле есть описание для тех кто потерял ключи от БД, только для баз на сервере).

Базы данных нынче очень тяжелые, раньше делал регламент по бэкапам средствами SQL каждые 4 часа на базе размером более 20 Gb. Сейчас на более сильном железе это дает ошибки транзакции, что бесит пользователей. Поэтому теперь все делаем ночью. Встречается, что бэкап средствами SQL при разворачивании оказывается нерабочим, поэтому предпочитаю бэкапы средствами 1С (ВНИМАНИЕ: 1С рекомендует бэкапы средствами скуля делать). Скрипт по запуску на сервере прилагаю ниже.

В приложенном файле собрано много чего: работа с файлами (скрипты cmd) и их можно в регламент SQL смело вставлять при необходимости, перезапуск ragent (сервера), собранные материалы из статьи ниже (на всякий случай все проверил на живом\рабочем сервере), решение некоторых ошибок связанных с обновление (БД разрушена, недоступна или БД не выгружается через конфигуратор), кратко памятка основных и часто используемых команд по входу в БД (блокировка, лог, отладка серверных процедур и т.д), регламентные работы на сервере и возврат доступа к БД на сервере. Приложены ссылки на похожие публикации.

Самая упрощенная схема реламента эта бэкап средствами 1С раз в неделю. А обслуживание БД состоит из 4 заданий: Шринкование (вручную или раз в неделю автоматом), реиндексация, обновление статистики, очистка процедураного кэша.

В качестве примера приведена БД "ara2014"

Запуск конфигуратора для создания бэкапа БД 1С (вход в заблокированную БД, файл с пропиской тек. даты, запись лога работы сеанса):

Пользователь Б -"exchange", пароль БД - "exc878787", имя файла с датой - "1Cv8_ARA_%date:

0,2%.dt", код блокировки (как и пароль как вы указали) - "55513", и имя лога работы сеанса 1С.

"C:\Program Files (x86)\1cv82\common\1cestart.exe" CONFIG /S"winsrv02\ara2014" /N"exchange" /P"exc878787" /DumpIB"C:\1C\Arxiv\1Cv8_ARA_%date:

0,2%.dt" /UC55513 /Out"C:\1C\Arxiv\LOGBackupARA.txt" -NoTruncate

Шринкование БД (вручную, если ставите в атоматическое выполнение, то раза в неделю остаточно):

USE [ara2014]
GO
DBCC SHRINKDATABASE(N'ara2014' )
GO

Реиндексация базы данных (раз в сутки, в не рабочее время) (рекомендовано от 1С):

DECLARE reindex_cursor CURSOR

SELECT name FROM sysobjects WHERE type = 'U'

DECLARE @tablename sysname

FETCH NEXT FROM reindex_cursor INTO @tablename

WHILE (@@FETCH_STATUS <> -1)

EXECUTE ('DBCC DBREINDEX (''' + @tablename + ''')')

FETCH NEXT FROM reindex_cursor INTO @tablename

Обновление статистики базы данных (один или несколько раз в день) (рекомендовано от 1С):

exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'
DBCC UPDATEUSAGE (ara2014)

Очистка процедураного кэша СУБД (после обновления статистики) (рекомендовано от 1С):


ДОПОЛНИТЕЛЬНЫЕ СКРИПТЫ ОБСЛУЖИВАНИЯ SQL сервера:

Временами, при восстановлении базы из архива может сбиться настройка смещения дат, что чревато серьезными проблемами.
Проверить настройку смещения дат можно следующим скриптом:

SELECT TOP 1 Offset FROM _YearOffset

Для оценки самых тяжелых запросов я использую немного другие скрипты:

1. Топ 10 самых тяжелых для процессора:

SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

2. Топ 10 самых тяжелых по вводу/выводу:

SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;

Статистика ввода/вывода по файлам баз данных:

USE master
GO
SELECT TOP 10 DB_NAME(saf.dbid) AS [База данных]
, saf.name AS [Логическое имя]
, vfs.BytesRead/1048576 AS [Прочитано (Мб)]
, vfs.BytesWritten/1048576 AS [Записано (Мб)]
, saf.filename AS [Путь к файлу]
FROM sysaltfiles AS saf
JOIN :: fn_virtualfilestats(NULL,NULL) AS vfs
ON vfs.dbid = saf.dbid
AND vfs.fileid = saf.fileid
AND saf.dbid NOT IN (1,3,4)
ORDER BY vfs.BytesRead/1048576 + BytesWritten/1048576 DESC
GO

Статистика ввода/вывода по дискам:

SELECT SUBSTRING(saf.physical_name, 1, 1) AS [Диск]
, SUM(vfs.num_of_bytes_read/1048576) AS [Прочитано (Мб)]
, SUM(vfs.num_of_bytes_written/1048576) AS [Записано (Мб)]
FROM sys.master_files AS saf
JOIN sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs
ON vfs.database_id = saf.database_id
AND vfs.file_id = saf.file_id
AND saf.database_id NOT IN (1,3,4)
AND saf.type < 2
GROUP BY SUBSTRING(saf.physical_name, 1, 1)
ORDER BY [Диск]
GO

Производительность журнала транзакций:

SELECT (wait_time_ms - signal_wait_time_ms) / waiting_tasks_count AS [Время отклика долговременного носителя журнала (ms)],
max_wait_time_ms AS [Максимальное время ожидания (ms)]
FROM sys.dm_os_wait_stats
WHERE wait_type = 'WRITELOG' AND waiting_tasks_count > 0;

Многострадальный sys.dm_exec_query_stats можно еще использовать так, топ 30 запросов по длительности блокировки:

SELECT TOP 30
(total_elapsed_time - total_worker_time) / qs.execution_count AS [Average Time Blocked],
total_elapsed_time - total_worker_time AS [Total Time Blocked],
qs.execution_count AS [Execution count],
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS [Individual Query],
qt.text [Parent Query],
DB_NAME(qt.dbid) AS [Database name]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
/* этот кусок отберет по базе, если надо по всем - удалить */
/*WHERE DB_NAME(qt.dbid)='ИмяБазы'*/
ORDER BY [Average Time Blocked] DESC;

Наблюдаем за памятью: если Free Pages меньше 300, объем ОЗУ узкое место в производительности системы:

SELECT * FROM sys.sysperfinfo where counter_name like 'Page Writes%' or counter_name like 'Page reads%'
or counter_name like 'lazy%' or counter_name like 'Page Life%' or counter_name like 'Memory Grants Pending%'
or (counter_name = 'Free pages' and [object_name] LIKE '%BUFFER MANAGER%')

WITH autostats ( object_id, stats_id, name, column_id )
AS ( SELECT sys.stats.object_id ,
sys.stats.stats_id ,
sys.stats.name ,
sys.stats_columns.column_id
FROM sys.stats
INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id
AND sys.stats.stats_id = sys.stats_columns.stats_id
WHERE sys.stats.auto_created = 1
AND sys.stats_columns.stats_column_id = 1
)
SELECT OBJECT_NAME(sys.stats.object_id) AS [Table] ,
sys.columns.name AS [Column] ,
sys.stats.name AS [Overlapped] ,
autostats.name AS [Overlapping] ,
'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id)
+ '].[' + OBJECT_NAME(sys.stats.object_id) + '].['
+ autostats.name + ']'
FROM sys.stats
INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id
AND sys.stats.stats_id = sys.stats_columns.stats_id
INNER JOIN autostats ON sys.stats_columns.object_id = autostats.object_id
AND sys.stats_columns.column_id = autostats.column_id
INNER JOIN sys.columns ON sys.stats.object_id = sys.columns.object_id
AND sys.stats_columns.column_id = sys.columns.column_id
WHERE sys.stats.auto_created = 0
AND sys.stats_columns.stats_column_id = 1
AND sys.stats_columns.stats_id != autostats.stats_id
AND OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0

Статистика ожиданий (более информативно чем перекрывающаяся статистика):

Восстановление баз данных:

Восстановление SQL базы 1С 8.2. рухнувшей во время сохранения конфигурации:
("Внимание. При обновлении данных, после последней реструктуризации, произошла критическая ошибка. Повторить обновление?")

----------------------------------------------------
Ошибка SDBL (Возникает при обновлении конфигурации ИБ): Для полей, начиная с FileName, не хватило значений (pos=19)

Лечится путем запуска в консоли SQLсервера команд:

TRUNCATE TABLE _ConfigChangeRec
TRUNCATE TABLE _ConfigChangeRec_ExtProps

Отладка на сервере

Иногда возникает необходимость отладки серверных процедур 1С при работе с программой в клиент-серверном варианте. По умолчанию отладка на сервере
выключена. Существует, по крайней мере, два способа:

Можно сделать выгрузку конфигурации и развернуть ее в файловом варианте.
Можно включить отладку серверных процедур, запустив агента сервера с параметром debug.
Остановимся на последнем варианте более подробно.Алгоритм примерно следующий:

Останавливаем службу 1C:Enterprise 8.2 Server Agent, например, вот так: "C:\Program Files (x86)\1cv82\8.2.19.130\bin\ragent.exe" –stop
В системном реестре находим ветку «HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\services\1C:Enterprise 8.2 Server Agent» и для параметра «ImagePath» добавляем «-debug».
Было: "C:\Program Files (x86)\1cv82\8.2.19.130\bin\ragent.exe" -srvc -agent -regport 1541 -port 1540 -range 1560:1591 -d "C:\Program Files (x86)\1cv82\srvinfo"
Стало:"C:\Program Files (x86)\1cv82\8.2.19.130\bin\ragent.exe" -srvc -agent -regport 1541 -port 1540 -range 1560:1591 -d "C:\Program Files (x86)\1cv82\srvinfo" -debug

Запускаем службу.
Запускаем 1С:Предприятие в обычном режиме, заходим в меню «Сервис - Параметры», закладка «Системные». Проверяем, чтобы было отмечено: «Отладка разрешена».
Запускаем 1С:Предприятие в режиме Конфигуратора, заходим в меню «Отладка - Подключение», нажимаем кнопку «Автоматическое подключение», выбираем необходимые типы подключения.

Варианты запуска

"C:\Program Files (x86)\1cv8\common\1cestart.exe" ENTERPRISE /F"C:\LocalBase8\Demo\DemoTrd" /UC555

"C:\Program Files (x86)\1cv8\common\1cestart.exe" ENTERPRISE /S"sky-1\trd" /UC555

"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /F"C:\LocalBase8\Demo\DemoTrd" /UC555

"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /S"sky-1\trd" /UC555

"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /S[my_server]\[my_base] /N[привелигерованая_учетка] /P[пароль] /DumpIB"[путь_приема_файла]\backup.dt"

move "[путь_приема_файла]\backup.dt" "[путь_приема_файла]\%date%.dt"


"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /F"C:\LocalBase8\Demo\DemoTrd" /N"exchange" /P"exc878787" /DumpIB"C:\NS\Arxiv_1С8\Accounting_%date:

"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /S"sky-1\trd" /N"exchange" /P"exc878787" /DumpIB"C:\NS\Arxiv_1С8\Trd\1Cv8_Trd_%date:

// Бэкап заблокированной базы
"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /S"sky-1\trd" /UC555 /N"exchange" /P"exc878787" /DumpIB"C:\NS\Arxiv_1С8\Trd\1Cv8_Trd_%date:

"C:\Program Files (x86)\1cv8\common\1cestart.exe" CONFIG /S"sky-1\ara" /UC55513 /Out"E:\Distr_1C8\Update\Error.txt" -NoTruncate

"C:\Program Files (x86)\1cv8\common\1cestart.exe" /UC55513 /Out"E:\Distr_1C8\LOG\Error_%date:

В приложенном архиве все это разложено по файлам, собирал долго, как обычно все валялось как попало, но тут возник заказ на выдачу инструкции для подопечной компании, прилось все прилизать. Ну а раз есть все собранное компактно, возникла мысль поделиться таким сборником срочной помощи.

Читайте также:

      
  • Установленный и максимально поддерживаемый размер памяти bios
    •   
  • Как удалить adobe bridge
    •   
  • Как убрать фон с печати в фотошопе
    •   
  • Как в аксессе сделать таблицу excel
    •   
  • Как использовать socks5 прокси в браузере
  • Контакты
  • Политика конфиденциальности