Реорганизация индексов базы данных MS SQL "Невозможно реорганизовать индекс"

1. tekit 09.11.22 11:35 Сейчас в теме
Приветствую вас заклинатели железа, направляю вам преинтереснейшую ситуёвину для разбора и помощи вашему слуге.

Проблема: После обновления платформы 8.3.20.1674->8.3.22.1603 сломалось автоматическое резервное копирование баз данных, созданное менеджером в SSMS 2012. При попытке реиндексации баз данных коих у нас не одна, ssms ругается на "Невозможно реорганизовать индекс "_Acc48_1" в таблице "_Acc48", поскольку отключена блокировка на уровне страницы." И так на 30к+ индексов. Во всех базах где произошло какое-то обновление/регламентное задание/бог его знает что ещё. В кратце, для реиндексации нужно чтобы все таблицы баз были с включенным параметром "allow_page_locks" или как описан в клиенте он "Блокировка страниц в памяти".

Что пробовал?:
· Гуглил. Не помогло.
· Доставать dt сувать обратно, индексы логично достаются и суются вместе с базой. Не помогло.
· Поднимал бэкап до обновления платформы в тест базу, смотрел что в индексах с нашим параметром. Там везде "ON". У нас после обновы в 30к "OFF".
· В небольших базах было по 1-2 индекса с флагом офф, проставлял там руками параметр в истину, запускал план бэкапа, реиндексация и сам бэкап проходили на ура. Сделал вывод, что выставив как-то параметр в 30к индексах я таки получу автобэкапирование, но не пойму причины возникновения отключения параметра. Можно утолкать кусок скрипта перед реиндексацией который будет ставить параметр в "ON". Костыль....
· Пробовал делать тестирование и исправление ошибок через конфигуратор. Не помогло.

Люди добрые:
Кто сталкивался с подобным? Подскажите как решали.
Кто сможет написать запрос который в автомате проставит флаг во всех индексах всех таблиц базы, буду очень признателен.
Конструкция
ALT ER   INDEX ALL ON <Table name>
SET (ALLOW_PAGE_LOCKS = ON)
GO
не помогает, так как во втором параметре нужна таблица, как туда указать все таблицы, не знаю, не могу, не умею.
SoDm; eklekt; ALmighty; mamonth; user986816; agentesecreto; mafnitofon; onetone; arkitekt; KirillZ44; thunder367; +11 Ответить
Найденные решения
8. splxgf 09.11.22 16:35 Сейчас в теме
(1)
как туда указать все таблицы, не знаю

USE имя базы
EXEC sp_MSforeachtable 'ALT ER   INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = ON)' 
GO

Проверьте такой вариант, имя таблицы автоматом подставляется вместо знака вопроса.
корум; The Ant; Lacoste4life; aqualife; fuser; onetone; MaZaHacKa_13; arkitekt; Sley; maikl007; yaguarrr; +11 Ответить
Остальные ответы
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
111. LineykaSBK 5 25.04.23 22:15 Сейчас в теме
(96)
DECLARE @namelike varchar(100) = 'Введите_маску_базы_символ%' -- Отбор по имени базы

Взял на вооружение,подскажите. при выполнении ошибок полно связанных с пробелом добавленым, видимо сайтом.
Как исправить вот такую ошибку?
используется SQL 2019
Код ошибки: 1018

Текст ошибки: Неправильный синтаксис около "INDEX". Если имелась в виду часть табличного указания, то для этого теперь необходимо использовать ключевое слово A WITH и круглые скобки. Правильный синтаксис см. в электронной документации по SQL Server.
Поправьте пож-та скрипт согласно этим рекомендациям - самому ни как .
99. user1863362 11.01.23 00:13 Сейчас в теме
(98) Ты просил проверить - я проверил. Не работает. А в ответ ты затеял типично 1Сные приседания - "а ставилась задача"...

Так что да, Хе-хе. Сообщество.
100. bardaknn 11.01.23 08:29 Сейчас в теме
(99) Да я просто мимо проходил.
Особенно смешно про 1Сные приседания))
101. srg123 05.02.23 10:51 Сейчас в теме
(96) Все норм. Все работает. Для тех кто в танке - пробелы лишние уберите в коде FR OM -> FROM и т.д.
(100) Бывает... )
102. alexis9 06.02.23 11:39 Сейчас в теме
Подскажите, пожалуйста.
Поставили платформу 8.3.22.ххх
По некоторым причинам пришлось сразу же откатиться на платформу 8.3.18
И там столкнолись с ошибкой при реиндексации. Судя по всему, реструктуризация БД не учла изменения в БД внесенные платформой 22.
Описаный здесь скрипт помог.
Но у меня вопрос.
Надо ли мне использовать после реиндексации второй скрипт для выключения блокировок?
Ведь сейчас у меня платформа 18, а не 22. А 1с внесла изменения в блокировки только на 22й платформе.
103. kiykomi 08.02.23 16:32 Сейчас в теме
Вот правильная конструкция, которая работает у меня:

До реорганизации индекса:
DECLARE @dbname nvarchar(128)
DECLARE @sql nvarchar(max)
DECLARE db_cursor CURSOR FOR
SEL ECT name FR OM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @dbname
    SET @sql = 'USE [' + @dbname + '];' +
                'EXEC sp_MSforeachtable ''ALT ER   INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = ON)'';' +
                'EXEC sp_MSforeachtable ''ALT ER   INDEX ALL ON ? SET (ALLOW_ROW_LOCKS = ON)'''
    EXEC (@sql)
    PRINT 'Using database: ' + @dbname
    FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor
Показать


И после:
DECLARE @dbname nvarchar(128)
DECLARE @sql nvarchar(max)
DECLARE db_cursor CURSOR FOR
SEL ECT name FROM sys.databases
WH ERE name NOT IN ('master','tempdb','model','msdb')

OPEN db_cursor
FETCH NEXT FR OM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @dbname
    SET @sql = 'USE [' + @dbname + '];' +
                'EXEC sp_MSforeachtable ''ALT ER   INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = OFF)'';' +
                'EXEC sp_MSforeachtable ''ALT ER   INDEX ALL ON ? SET (ALLOW_ROW_LOCKS = OFF)'''
    EXEC (@sql)
    PRINT 'Using database: ' + @dbname
    FETCH NEXT FR OM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor
Показать


Всё отрабатывает отлично.
SoDm; user1970348; mfg-super; thunder367; +4 Ответить
106. mfg-super 12.03.23 20:02 Сейчас в теме
(103)
А Вам не кажется, что в скрипте После должно быть так: "ALLOW_PAGE_LOCKS = OFF" и "ALLOW_ROW_LOCKS = ON"?

На ИТС такая информация по этому вопросу:
Важно! Начиная с версии платформы 8.3.22 необходимо выполнять дефрагментацию индексов по следующему алгоритму:
До дефрагментации индекса необходимо включить страничные блокировки. Пример команды: ALT ER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON);
Выполнить дефрагментацию.
Обратно выключить страничные блокировки. Пример команды: ALT ER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON);
SoDm; user1970348; +2 Ответить
104. kiykomi 08.02.23 16:34 Сейчас в теме
Здесь не нужно указывать базы, просто автоматически берутся все БД, кроме системных и даже обходит ошибку в именах БД со знаком "-" (тире) в имени благодаря квадратным скобкам.
Просто юзайте на здоровье.
105. kiykomi 09.02.23 10:46 Сейчас в теме
Апдейт - чтобы пропускало базы со статусом "Вне сети" добавьте AND state = 0 в конструкцию WHERE name NOT IN ('master','tempdb','model','msdb'), вот так вот:

WHERE name NOT IN ('master','tempdb','model','msdb') AND state = 0
107. TormDV 13.03.23 08:17 Сейчас в теме
Кто-нибудь тестил отключение-включение страничных блокировок сколько времени занимает? Если допустим терабайтная база на сервере крутится или 30-40 баз поменьше... Какую нагрузку включение-отключение создает на сервер?
З.Ы. Сам не проверял, возможно там доли секунды и никаких подводных камней, но вдруг..
108. ESPakhomov 06.04.23 14:33 Сейчас в теме
На сколько вообще корректно использовать:
ALT ER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = ON)
ALT ER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = OFF)
Если посмотреть индексы до этих манипуляций, то есть индексы у которых изначально ALLOW_PAGE_LOCKS = ON и этими действиями по итогу изменяем их на ALLOW_PAGE_LOCKS = OFF. например:
Прикрепленные файлы:
andrew.ab; NaKa; user1970348; +3 Ответить
109. LuMp1ck 08.04.23 06:27 Сейчас в теме
Дай Вам Бог всем здоровья, и долгих лет жизни!

Тоже столкнулся, платформа v8.3.22.1750 + MS SQL 2012

Всё было настроено и всё работало ... но потом ... но потом перестало ))
В результате добавив в план обслуживания шаги на нужных этапах, всё заработало снова!

Единственной, там выше уточняли, что запросе изначально была ошибка ALTER было написано через пробел, поэтому запрос не отрабатывал

USE ERP
EXEC sp_MSforeachtable 'ALT ER   INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = OFF)'
EXEC sp_MSforeachtable 'ALT ER   INDEX ALL ON ? SET (ALLOW_ROW_LOCKS= ON)'
GO
Прикрепленные файлы:
Asus0; SoDm; STEPPRK; user1970348; yaguarrr; Anvarich; user1300601; +7 Ответить
110. MDR_Tiger 11.04.23 11:20 Сейчас в теме
Использую такой код для обслуживания индексов в MS SQL 2016:
DECLARE @Base_Name nvarchar(100);
DECLARE @Base_id int;
-- Обрабатываются все пользовательские базы данных в оперативном режиме
DECLARE Bases CURSOR FOR SEL ECT database_id FR OM sys.databases where database_id > 4 and state_desc = 'ONLINE';
-- Обрабатываются только нужные пользовательские базы данных в оперативном режиме
-- DECLARE Bases CURSOR FOR SEL ECT database_id FR OM sys.databases where database_id in (5,6,7,8) and state_desc = 'ONLINE';

OPEN Bases;
FETCH NEXT FR OM Bases INTO @Base_id;
WHILE @@FETCH_STATUS = 0
BEGIN  
-- Отбор таблиц и индексов с помощью системного представления sys.dm_db_index_physical_stats
-- Отбор только тех объектов которые являются индексами (index_id > 0), 
-- фрагментация которых более 10% и количество страниц в индексе более 128
Sel ect @Base_Name = DB_NAME(@Base_id)

PRINT @base_Name;

Exec ('Use ' + @base_Name + '
SET NOCOUNT ON; -- отключаем вывод количества возвращаемых строк, это несколько ускорит обработку
DECLARE @objectid int; -- ID объекта
DECLARE @indexid int; -- ID индекса
DECLARE @partitioncount bigint; -- количество секций если индекс секционирован
DECLARE @schemaname nvarchar(130); -- имя схемы в которой находится таблица
DECLARE @objectname nvarchar(130); -- имя таблицы 
DECLARE @indexname nvarchar(130); -- имя индекса
DECLARE @partitionnum bigint; -- номер секции
DECLARE @frag float; -- процент фрагментации индекса
DECLARE @allow_page_locks int; -- признак включенности блокировки на уровне страниц
DECLARE @command nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации
	SEL ECT
		IndexStats.object_id AS objectid,
		IndexStats.index_id AS indexid,
		IndexStats.partition_number AS partitionnum,
		IndexStats.avg_fragmentation_in_percent AS frag,
		AllIndexes.allow_page_locks as allow_page_locks
	INTO #work_to_do
	FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') AS IndexStats
	INNER JOIN sys.indexes AS AllIndexes
		ON AllIndexes.index_id = IndexStats.index_id
		AND AllIndexes.object_id = IndexStats.object_id
		AND IndexStats.avg_fragmentation_in_percent > 10.0
		AND IndexStats.index_id > 0
		AND IndexStats.page_count > 128;

	-- Объявление курсора для чтения секций
	DECLARE partitions CURSOR FOR SEL ECT * FR OM #work_to_do;

	-- Открытие курсора
	OPEN partitions;
	-- Цикл по секциям
	WHILE (1=1)
		BEGIN;
			FETCH NEXT
			   FROM partitions
			   INTO @objectid, @indexid, @partitionnum, @frag, @allow_page_locks;

			IF @@FETCH_STATUS < 0 BREAK;
		
	-- Собираем имена объектов по ID		
			SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
			FR OM sys.objects AS o
			JOIN sys.schemas as s ON s.schema_id = o.schema_id
			WH ERE o.object_id = @objectid;
			SELECT @indexname = QUOTENAME(name)
			FR OM sys.indexes
			WH ERE  object_id = @objectid AND index_id = @indexid;
			SELECT @partitioncount = count (*)
			FR OM sys.partitions
			WH ERE object_id = @objectid AND index_id = @indexid;

	-- Если фрагментация менее или равна 30% тогда дефрагментация, иначе реиндексация
			IF @frag <= 30.0
				SET @command = N''ALT ER   INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
			IF @frag > 30.0
				SET @command = N''ALT ER   INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD'';
			IF @partitioncount > 1
				SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));
			
	-- Если реиндексация, то для ускорения добавляем параметры использования TEMPDB(имеет смысл только если TempDB на отдельном физ. диске) и многопроцессорной обработки 
			IF @frag > 30.0
				SET @command = @command + N'' WITH (SORT_IN_TEMPDB = ON, MAXDOP = 0)'';
			
	-- Принудительное включение блокировки индекса на уровне страницы и её отключение, если блокировка была изначально выключена (для платформы 1С 8.3.22.х)
			IF @allow_page_locks = 0
				BEGIN
					SET @command = N''ALT ER   INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' SET (ALLOW_PAGE_LOCKS = ON)'' + char(10) + @command;
					SET @command = @command + char(10) + N''ALT ER   INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' SET (ALLOW_PAGE_LOCKS = OFF)'';
				END

			EXEC (@command);
			PRINT N''Executed: '' + @command;
		END;

	-- Закрытие курсора
	CLOSE partitions;
	DEALLOCATE partitions;

	-- Удаление временной таблицы
	DR OP   TABLE #work_to_do;')
	FETCH NEXT FR OM Bases INTO @Base_id;
END;
CLOSE Bases;
DEALLOCATE Bases;
GO
Показать
SoDm; mamonth; andrew.ab; STEPPRK; ruddanil; sys1c; yaguarrr; fatman78; LineykaSBK; +9 Ответить
112. LineykaSBK 5 25.04.23 22:33 Сейчас в теме
(110)
Спасибо, вроде работает на sql 2019
113. fatman78 17 23.05.23 20:39 Сейчас в теме
(110)
INTO


Прикрепил этот скрипт в виде файла. на MS SQL 2017 с 1С 8.3.22 отлично работает.
Прикрепленные файлы:
SQLQuery_Rebuild_Reindex.sql
user1970348; ruddanil; +2 Ответить
126. ruddanil 06.07.23 16:20 Сейчас в теме
(110) Спасибо. Работает на MS SQL 2012 с 1С 8.3.22
114. Vstur 26.05.23 14:29 Сейчас в теме
При откате с 22 платформы на более ранние, достаточно выгрузить в dt и загрузить обратно.
Тогда проблема проблема уйдет
115. oleg-x 26 01.06.23 14:07 Сейчас в теме
В июне ЗУП и в августе БУХ планируют поднять режим совместимости и минимальную версию платформы до 8.3.22
И я так понимаю на текущий момент проблема с индексами так же и осталась?
116. TormDV 01.06.23 14:36 Сейчас в теме
(115) Это не проблема, а фича. Считается, что так лучше производительность. Не завидую тем, кто на 3.1.25 ветке ЗУПа сидит. Про 8.3.23 версию платформы сегодня тема была, что в последнем релизе ерп проблемы в связи с наименование переменных и объектов платформы. Зато, возможно не будет сложного выбора, на какой платформе сидеть 8.3.17, 19 или 23...
119. user1960153 17.06.23 02:13 Сейчас в теме
(116)можете ссылку дать на эту тему, не могу найти
(116)
120. elenasulimova 19.06.23 08:50 Сейчас в теме
(116) Дайте, пожалуйста, ссылку на эту тему. Никак не могу найти. У нас как раз такая проблема.
117. oleg-x 26 01.06.23 14:38 Сейчас в теме
(116) Возможно то и лучше, только вот что делать тем, кто не в теме, как корректно работать с этой новой фичей :-(
118. TormDV 01.06.23 14:49 Сейчас в теме
(117) Методом научного тыка) В идеале на тестовом сервере платформу развернуть, и пару баз попробовать обслужить. Где завалится - использовать скрипты, что 1с сами предлагают или хоть из этой темы. Или, на крайняк, на боевом сервере, если вариантов нет. После обновления платформы тестовую базу небольшую развернуть, и для нее план обслуживания сделать на основании имеющегося, но с учетом индексов. Без реиндексации, конечно, плохо, за денек-другой, неделю глобально с базами ничего плохого не должно случиться.
З.Ы. Мы сами еще не переходили на новую платформу, только предстоит, вот и советы такие.
З.Ы.Ы. Да и сейчас много похожих тем будут создавать, сообщество поможет))
122. Djo82 6 01.07.23 18:36 Сейчас в теме
(121) мне помогло это Убрать пробел в "ALT ER"?
123. sys1c 05.07.23 11:37 Сейчас в теме
(110) подскажите как в этом скрипте указать конкретную базу?
этот скрипт получается выполняет три действия?
1) выключает блокировку
2) делает дефрагментацию
3) включает блокировку?


этот скрипт надо выполнять в окне "Выполнение инструкции T-SQL" ?
124. MDR_Tiger 05.07.23 15:00 Сейчас в теме
(123)
Чтобы указать конкретную базу, нужно знать её ID в SQL Server.
Код
 SEL ECT * FR OM sys.databases
покажет, у какой базы какой ID.

Закомментированная строка
DECLARE Bases CURSOR FOR SELECT database_id FR OM sys.databases wh ere database_id in (5,6,7,8) and state_desc = 'ONLINE';
- это и есть организация выборки именно тех баз, ID которых указаны в скобках, и находящиеся в состоянии ONLINE.

Скрипт проверяет уровень фрагментации индексов и в зависимости от того, насколько всё грустно в таблице, выполняет либо реиндексацию, либо дефрагментацию индекса. Да, перед всем этим "действом-злодейством" блокируется только "виновник торжества", а не все индексы всех таблиц.

Скрипт можно выполнять как в окне выполнения инструкции T-SQL, так и использовать внутри плана обслуживания. У меня используется именно в плане обслуживания. Там тоже можно выполнять произвольный код T-SQL.
mamonth; user1970348; sys1c; +3 Ответить
127. user1715153 11.07.23 16:57 Сейчас в теме
(124)
Подскажите пожалуйста, при выполнении выдает две ошибки.

Скрипт изменил только ту строку где базы по выбору ну и пробелы убрал которые тут выставились
Прикрепленные файлы:
128. MDR_Tiger 11.07.23 21:40 Сейчас в теме
(127)

Проверяйте правильное написание всех SEL ECT'ов и FR OM'ов.
В сообщении об ошибке идёт указание на 29 и 51 строку Вашего скрипта.

Прикладываю скрипт в виде файла, чтобы сайт его не повредил.
Прикрепленные файлы:
Обслуживание индексов.sql
9313496; tanat74; user1970348; user1715153; +4 Ответить
129. user1715153 12.07.23 09:54 Сейчас в теме
(128)
Спасибо, так взлетело, да где-то видимо не заметил пробелов все же.

На сколько оно быстро отработало )

То есть ваш скрипт получается заменяет две стандартные задачи перестроение и реорганизация индексов?
Или перестроение все равно надо запускать до этого?

А можете глянуть, после отработки получил такой вывод, все тут нормально?
Прикрепленные файлы:
exit.txt
130. MDR_Tiger 12.07.23 13:36 Сейчас в теме
(129)
Повторюсь:
"Скрипт проверяет уровень фрагментации индексов и в зависимости от того, насколько всё грустно в таблице, выполняет либо реиндексацию, либо дефрагментацию индекса."

В большинстве случаев дополнительно ребилд делать не надо. Зачем напрягать систему лишний раз?

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

В логе всё нормально.
Строки с комментарием "REBUILD" - это перестроение индекса (реиндексация). А-ля HEAVY METAL / HARD ROCK
Строки с комментарием "REORGANIZE" - это дефрагментация индекса. А-ля ROMANCE

:-)
131. user1715153 12.07.23 14:00 Сейчас в теме
(130)
Понятно. Если бы я еще на столько хорошо в этом разбирался, чтобы еще и условия там осмысленно менять :)
Ну, а так в целом этот скрипт универсален для обычных 1с баз?
Я пока только на копии рабочей попробовал )
132. MDR_Tiger 16.07.23 07:21 Сейчас в теме
(131)
Да, скрипт универсален для всех баз 1С. По крайней мере, пока. Только разработчикам 1С известны их планы по изменению взаимодействия платформы 1С:Предприятие с MS SQL Server'ом.
134. user606760_masaloff 31.07.23 16:02 Сейчас в теме
(124) Подскажите пожалуйста. У меня в имени базы есть "-". И задание заканчивается с ошибкой "Incorrect syntax near '-'." Как можно обойти, не меняя имени базы? Спасибо!
136. MDR_Tiger 31.07.23 20:24 Сейчас в теме
(134)

Как вариант, заменить текст

Exec ('Use ' + @base_Name + '

на

Exec ('Use [' + @base_Name + ']

То есть, нужно заключить имя базы в квадратные скобки.
А вообще, всем известна нелюбовь MS к символу '-', поэтому использовать его в названиях чего-либо крайне не рекомендую.
138. user606760_masaloff 01.08.23 12:36 Сейчас в теме
(136)
Как вариант, заменить текст

Exec ('Use ' + @base_Name + '

на

Exec ('Use [' + @base_Name + ']

(136)

Спасибо, попробую.
139. user606760_masaloff 01.08.23 13:46 Сейчас в теме
(136)
Большое спасибо, помогло!
Про нелюбовь к '-' не знал ,стыдно(. А к '_' у MS притензий нет?
140. MDR_Tiger 01.08.23 17:20 Сейчас в теме
(139)
К символу подчёркивания MS нормально относится. Пробелы тоже не желательно использовать, хоть и позволяют операционки в общий доступ с таким именем принтер выставлять. Все программы же разные. Кто знает, как программист напишет обращение к принтеру. Да и администрировать (скрипты писать, например) проще, когда нет всей этой ненужности.
125. sys1c 06.07.23 07:29 Сейчас в теме
(124) огромное спасибо что разжували, все заработало
MS SQL 2019
137. user1544784 01.08.23 12:24 Сейчас в теме
Подскажите пожалуйста где ошибка? при выполнении скрипта выдало такое, MS SQL 2012

Сообщение 102, уровень 15, состояние 1, строка 1
Неправильный синтаксис около конструкции "2014".
Сообщение 102, уровень 15, состояние 1, строка 46
Неправильный синтаксис около конструкции "WH".
Сообщение 102, уровень 15, состояние 1, строка 49
Неправильный синтаксис около конструкции "ERE".
Сообщение 102, уровень 15, состояние 1, строка 52
Неправильный синтаксис около конструкции "ERE".
Сообщение 102, уровень 15, состояние 1, строка 82
Неправильный синтаксис около конструкции "DR".
141. MDR_Tiger 01.08.23 17:24 Сейчас в теме
(137)
Текст скрипта из файла возьмите, а не со страницы этого сайта. Сайт портит скрипты.
Прикрепленные файлы:
Обслуживание индексов.sql
142. user1544784 01.08.23 19:56 Сейчас в теме
(141)
Вы были правы, осталась всего одна ошибка?

Сообщение 102, уровень 15, состояние 1, строка 1
Неправильный синтаксис около конструкции "2014"

Но в первой строке нет такого, может он ругается на то что у меня не 2014 SQL?

Осознал свой косяк, нельзя базе начинаться с чисел, имя базы начинается на 2014_)))
143. emcya 4 17.08.23 12:02 Сейчас в теме
(141) Спасибо за скрипт. После выполнения скрипта запрос SEL ECT * FR OM sys.dm_db_index_physical_stats
(DB_ID(N'МояБаза'), NULL, NULL, NULL , 'DETAILED') показывает индексы с avg_fragmentation_in_percent 100% с количеством страниц page_count больше указанного в скрипте (у себя для теста поставил 10). Это норм?
Прикрепленные файлы:
145. MDR_Tiger 19.08.23 06:58 Сейчас в теме
(143)
Вы можете накладывать условия, как Вам удобно. В этом и состоит суть администрирования, чтобы мониторить состояние баз, таблиц, индексов и других объектов на сервере. Каждая ситуация может быть уникальной. У кого-то на сервере много мелких баз, у кого-то одна, но огромная.
У меня в скрипте изначально условие "IndexStats.page_count > 128". Если маленькое количество страниц, то это маленькая табличка. Копошиться с маленькими табличками, возможно, нет особого смысла.
Решение всё равно за Вами.
147. emcya 4 20.08.23 16:22 Сейчас в теме
(145) Вопрос был не о количестве страниц, а том, что после выполнения скрипта все равно остаются индексы с avg_fragmentation_in_percent =100% (при этом page_count в этих индексах больше, чем указанный в скрипте лимит)
148. MDR_Tiger 20.08.23 20:10 Сейчас в теме
(147)
Ну, не знаю, что Вам ответить. У себя проверил. До обработки было 5 индексов, после обработки ничего не осталось. Возможно разные базы анализируете или "собачка в дороге могла подрасти". Пока готовили второй скрипт, после обработки могло сработать какое-то регламентное задание, которое переколбасило всё в базе (обмены, например). Попробуйте тормознуть все регламенты и выполните проверку ещё раз.
149. redfred 21.08.23 07:09 Сейчас в теме
(143)
После выполнения скрипта запрос SEL ECT * FR OM sys.dm_db_index_physical_stats
(DB_ID(N'МояБаза'), NULL, NULL, NULL , 'DETAILED') показывает индексы с avg_fragmentation_in_percent 100% с количеством страниц page_count больше указанного в скрипте (у себя для теста поставил 10). Это норм?


В скрипте не DETALIED, а LIMITED, что даёт только приблизительную оценку фрагментации (но сокращает время оценки). Полагаю, что причина в этом.
В любом случае, таблица в 10 страниц - это всего 80 килобайт данных. Нет никакого смысла дефрагментировать такое.
144. user1715153 18.08.23 10:10 Сейчас в теме
(141) Да, огромное спасибо за скрипт. Можно еще обратиться за советом ) Ваш скрипт лучше выполнять после обновления статистики со сбросом кэша или до, как вы посоветуете? Или тут это вообще не важно?
146. MDR_Tiger 19.08.23 07:08 Сейчас в теме
(144)
У меня обновление статистики выполняется после обслуживания индексов.
Возможно, супергуру по MS SQL меня поправят, но я думаю, что статистику нужно готовить с готовыми индексами. Ведь процесс обновления статистики в том числе обновляет статистику индексов, если режим обновления "Вся собранная статистика".
150. _IvaN_ 26.11.23 11:46 Сейчас в теме
(146) я тоже где-то на просторах инета читал, что обновление статистики должно по логике выполняется после обслуживания индексов.

Профи, поправьте если что-то сделал не так?!
У меня в планах обслуживания следующий порядок:
1. Бэкап пользовательских баз. Проверка целостности.
2. Оптимизация пользовательских баз а именно:
а. выполнения скрипта SQLQuery_Rebuild_Reindex.sql форумчанина fatman78 из поста 113
б. обновление статистики
в. выполнение T-SQL: DBCC FREEPROCCACHE
г. еженедельный ребилд (реиндексация) пользовательских БД.

буду благодарен если кто-то что добавит.
151. MDR_Tiger 28.11.23 11:16 Сейчас в теме
(150)
На вкус и цвет все фломастеры разные.

Использую базы данных с полной моделью восстановления (не с простой), поэтому плана обслуживания три:
1) Еженедельно:
- Очистка старых резервных копий (полных)
- Обслуживание индексов
- Обновление статистики
- Резервное копирование баз данных (полное)
- Очистка журнала
2) Ежедневно:
- Очистка старых резервных копий (разностных)
- Обновление статистики
- Резервное копирование баз данных (разностное)
3) Ежечасно:
- Резервное копирование журнала транзакций

Резервные копии каждый час дублируются на другой сервер. Таким образом, если сервер выйдет из строя без возможности достать базу с самыми последними изменениями, то максимум потеряется час работы пользователей в базе. Мне достаточно.
152. sys1c 30.11.23 08:20 Сейчас в теме
(151) а может, раз пошло, дабы не плодить темы, поделитесь настройками самого MS SQL?
можно узнать как у вас настроен Параллизм?
значения Блокировки, Максимальная степерь параллизма, Ожидание запросов, Стоимостной порог.
В свойствах самих баз - Автоматическое обновление статистики, Автоматическое обновление статистики, Максимальный DOP
153. MDR_Tiger 30.11.23 12:33 Сейчас в теме
(152)
Ну, у меня так...

MS SQL 2016
Максимальный размер памяти = {Половина всей памяти сервера, так как тут же и сервер 1С крутится}
Блокировки = 0
Максимальная степень параллелизма = 0
Ожидание запроса = -1
Стоимостный порог для параллелизма = 30

База model
Автоматическое обновление статистики = True
Асинхронное автоматическое обновление статистики = True
Статистика автоматического создания = True
Автоматическое создание статистики с добавлением = False
Автоматическое сжатие = False
Максимальная DOP = 0

Баз несколько, но они небольшие. Для крупных баз, возможно, надо будет поменять параметры.
154. sys1c 18.12.23 11:51 Сейчас в теме
(153) скажите а базу TempDB надо настраивать так же как остальные?

Автоматическое обновление статистики = True
Асинхронное автоматическое обновление статистики = True
Статистика автоматического создания = True
Автоматическое создание статистики с добавлением = False
Автоматическое сжатие = False
Максимальная DOP = 0
155. TormDV 18.12.23 12:20 Сейчас в теме
(154) В MS SQL 2019 Асинхронное автоматическое обновление статистики = False по умолчанию, возможно, и не влияет. Про особую настройку параметров базы темпдб не слышал. На отдельный быстрый ссд желательно вынести ее, и на 8 файлов разбить.
156. user593103_igorbardin 21.01.24 03:10 Сейчас в теме
По всей этой теме решение простое вместо реиндексации в регламентном задании используйте «Перестроение индекса» это тоже самое даже лучшеи работает в новых версиях 1С без всяких там скриптов
Kalius; WWWolfy; +2 Ответить
Оставьте свое сообщение
Вакансии
1С аналитик
Москва
зарплата от 210 000 руб.
Полный день

Руководитель направления 1С
Москва
зарплата от 350 000 руб.
Полный день

1С Программист
Москва
зарплата от 180 000 руб.
Полный день

Программист 1С
Москва
зарплата от 180 000 руб. до 220 000 руб.
Полный день

Аналитик 1С / Бизнес-аналитик
Нижний Новгород
зарплата от 100 000 руб. до 250 000 руб.
Временный (на проект)