Регламентные операции с индексами в MS SQL Server (Скрипты для SQL-Server - Часть 2)

08.06.18

База данных - HighLoad оптимизация

В данном вебинаре я расскажу о том, что такое индексы, зачем они нужны, какие регламентные операции необходимо выполнять с индексами, а также будут приведены соответствующие скрипты (для MS SQL-Server) для обслуживания индексов баз данных.

Скачать файлы

Наименование Файл Версия Размер
Все скрипты в одном архиве (Индексы)
.zip 5,63Kb
44
.zip 5,63Kb 44 Скачать

 

Видео в формате вебинара по данной теме:

 

 

Ну и, непосредственно, сами разобранные в вебинаре скрипты под спойлерами ниже. Все переменные собраны в соответствующем блоке в начале скриптов. Для отправки электронной почты используется предварительно настроенный профиль электронной почты компоненты DataBase Mail. О настройке компоненты можно прочитать, например, здесь. Все скрипты много раз опробованы в бою и протестированы на версиях MS SQL 2008, 2012, 2016.

 

Скрипт, показывающий фрагментированные индексы указанной базы данных:

 

 

Скрипт для обслуживания индексов указанной базы данных:

 

 

Скрипт для обслуживания индексов нескольких баз данных (по определенному условию):

 

 

Эти и другие скрипты доступны также в репозитории: https://github.com/Tavalik/SQL_TScripts

Полезные ссылки из вебинара:

  1. Про индексы на its.1c.ru: https://its.1c.ru/db/metod8dev/content/1590/hdoc
  2. Про индексы на msdn.microsoft.com: https://msdn.microsoft.com/ru-ru/library/ms189858.aspx
  3. Регламентные операции на kb.1c.ru: https://kb.1c.ru/articleView.jsp?id=13
  4. Полезные скрипты с индексами на infostart.ru: //infostart.ru/public/308762/

Все вебинары по скриптам для SQL:

  1. Автоматизируем перезаливку баз (Часть 1): //infostart.ru/public/799857/
  2. Регламентные операции с индексами в MS SQL Server (Часть 2): //infostart.ru/public/803209/
  3. Еще немного полезных SQL-скриптов (Часть 3): //infostart.ru/public/807843/

 

Индексы MS SQL Server TSQL

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    2993    spyke    27    

42

Быстродействие типовой 1С

HighLoad оптимизация Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    5117    vasilev2015    19    

37

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7651    158    ZAOSTG    68    

96

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

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5987    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8876    ivanov660    6    

76

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    5106    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

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

11.10.2023    16195    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. ADirks 186 23.03.18 11:54 Сейчас в теме
Для обслуживания индексов первым делом следует ознакомиться с https://blogs.msdn.microsoft.com/blogdoezequiel/2011/07/03/adaptive-index-defrag/

ну и так, вообще полезно:
https://blogs.msdn.microsoft.com/blogdoezequiel/tag/swiss-army-knife/
http://FirstResponderKit.org
artbear; sorb; cleaner_it; +3 Ответить
2. Silenser 592 23.03.18 12:34 Сейчас в теме
(1) Скрипты Ola Hallengren использую уже несколько лет, крайне доволен.
Tyler Durden; ADirks; sorb; +3 Ответить
6. ADirks 186 29.03.18 07:22 Сейчас в теме
10. sergey_s_ 01.12.21 08:23 Сейчас в теме
(2)
Скрипты Ola Hallengren
Поддерживаю, очень удобные, и можно настроить под себя
11. user1628996 04.12.22 17:30 Сейчас в теме
(2) (10) Парни, подскажите пожалуйста, в каком порядке Вы выполняете скрипты Ola?
14. Silenser 592 08.12.22 12:29 Сейчас в теме
(11) Ох, я этим последние 4 года не занимался, не помню уже. Вам придется инструкцию прочитать. Общий смысл такой: еженочно - быстрое обслуживание базы, реиндексация фрагментированных индексов (в параметрах нужно выбрать порог между реиндексацией и полным перестроением индекса), обновление статистики по индексам при перестроении произойдет автоматически. Так же можно будет сбросить кеш запросов.
Еженедельно - полное обновление статистики по всем индексам. Обслуживание индексов с более жестким порогом перестроение\реиндексация - на ваш выбор.
Но вообще, нужно конечно смотреть и мониторить именно ваш случай, т.к. бывают варианты, когда нужно какие-то определенные индексы перестраивать чуть ли не каждые сутки, если есть регистр, который меняется очень сильно.
5. sorb 28.03.18 17:30 Сейчас в теме
(1) имхо ezequel бедноват и не столь удобен по сравнению с ola
3. lecsysadmin 28.03.18 09:34 Сейчас в теме
MS SQL Server R2 заработало только вот так:

-------------------------------------------
-- НАСТРАИВАЕМЫЕ ПЕРЕМЕННЫЕ
-- База данных для анализа
USE WorkBase

-------------------------------------------
-- ТЕЛО СКРИПТА
DECLARE @db_id smallint;

SET @db_id = DB_ID();

-- Отбираем объекты, которые:
-- являются индексами (index_id > 0)
-- фрагментация которых более 5%
-- количество страниц в индексе более 128
SELECT
OBJECT_NAME(object_id) AS TableName,
object_id,
index_id,
partition_number,
page_count,
partition_number,
index_type_desc,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL , NULL, 'LIMITED')
WHERE index_id > 0
AND avg_fragmentation_in_percent > 5.0
AND page_count > 128
ORDER BY avg_fragmentation_in_percent DESC

GO
4. IvSchekin 28.03.18 09:54 Сейчас в теме
(0)
(3)
(0)Еще надо определится в какой последовательности выполнять процедуры обслуживания БД, в частности сжатие и обслуживание индексов, да и создания резервной копии.
Резервная копия, сжатие, перестройка индексов.
7. Milanick 30.03.18 08:36 Сейчас в теме
Коллеги, можете подскажите советом , куда капнуть?

Задача "Проверка целостности базы данных" (W2008R2) Проверить целостность базы данных Соединение с локальным сервером Базы данных TEMP_FG Включить индексы Начало задачи: 2018-03-25T19:00:01.
Конец задачи: 2018-03-25T19:07:52.
Ошибка:(-1073548784) Сбой выполнения запроса "DBCC CHECKDB(N'TEMP_FG') WITH NO_INFOMSGS " со следующей ошибкой: "Экстент (1:8488) в базе данных с идентификатором 7 размещен несколькими объектами размещения.
Экстент (1:8528) в базе данных с идентификатором 7 размещен несколькими объектами размещения.
Экстент (1:8488) размещен в "Unknown" и по крайней мере еще одном объекте.
Экстент (1:8528) размещен в "Unknown" и по крайней мере еще одном объекте.
На страницу карты распределения индекса (IAM) (1:64493) указывает предыдущий указатель IAM-страницы (1:6378) в объекте с идентификатором 0, идентификатор индекса -1, идентификатор секции 0, идентификатор единицы размещения 72060067471556608 (тип Unknown), но это не было обнаружено в ходе просмотра.
Экстент (1:8488) размещен в "dbo.Config, PK__ConfigNG__589E6EED4499BB57" и по крайней мере еще одном объекте.
Экстент (1:8528) размещен в "dbo.Config, PK__ConfigNG__589E6EED4499BB57" и по крайней мере еще одном объекте.
CHECKDB обнаружил 5 ошибок размещения и 0 ошибок согласованности, не связанных ни с одним объектом.
CHECKDB обнаружил 2 ошибок размещения и 0 ошибок согласованности в таблице "Config" (идентификатор объекта 1118925541).
CHECKDB обнаружил 7 ошибок размещения и 0 ошибок согласованности в базе данных
repair_allow_data_loss - это минимальный уровень исправления для ошибок, найденных DBCC CHECKDB (TEMP_FG).". Возможные причины сбоя: проблемы с этим запросом, свойство "ResultSet" установлено неправильно, параметры установлены неправильно или соединение было установлено неправильно.
8. logarifm 1118 16.11.20 10:51 Сейчас в теме
Немного непонятно количество страниц в индексе, если делать через стандартные средства МС Студио там указано 1000 страниц, здесь 128. Подробно бы описать от чего зависят страницы и их количество? Именно для перестроения индекса. Ведь 128 и 1000 это существенная разница
9. Tavalik 3352 21.11.20 08:37 Сейчас в теме
(8)

Вот официальная документация от Microsoft: https://docs.microsoft.com/ru-ru/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15

Если найдете более понятное объяснение, поделитесь, пожалуйста.
12. user1628996 04.12.22 20:21 Сейчас в теме
Виталий, спасибо за Ваш труд! Очень подробно все рассказываете и показываете, использую Ваши скрипты, а видео помогло ответить на многие вопросы. Лайк и подписка.
13. Tavalik 3352 08.12.22 09:03 Сейчас в теме
(12)
Вам спасибо за комментарий.
15. user1876524 18.12.22 10:30 Сейчас в теме
(13)
Виталий прошу Вас обновить скрипты в связи с выходом платформы 8.3.22
Необходимо ввести проверку allow_page_locks: https://forum.infostart.ru/forum86/topic289736/
Я не программист, сам доработать не могу, скрипт отличнейший, очень жду новой версии.
Lacoste4life; gozhdik; +2 Ответить
16. user592237_mambabos 30.12.22 09:51 Сейчас в теме
Добрый день!

При выполнении скрипта "Скрипт для обслуживания индексов указанной базы данных" ошибка:
Сообщение 102, уровень 15, состояние 1, строка 11
Неправильный синтаксис около конструкции "(".

Исходные данные: 1С 7.7 + MS SQL 2008 R2

Подозреваю из за того, что есть таблицы с названием где первая цифра, а потом буквы.
Проверял на другой БД, все норм.
Кто знает как победить эту проблему?
17. evgenyd 16.03.23 14:25 Сейчас в теме
(16)
Попробуйте

USE TestBase

/////

DECLARE @db_id SMALLINT; -- id базы

SET @db_id = DB_ID(N'TestBase');

////

FROM sys.dm_db_index_physical_stats (@db_id, NULL, NULL , NULL, 'LIMITED')
18. Wolfkind 11.10.23 06:53 Сейчас в теме
Указал в скрипте по обслуживанию нескольких баз нужные, отрабатывает почему то "через раз" по всему списку, то все базы попадут, (~30шт.) а то 2-3шт а остальные не трогает, хотя там есть что делать 100% и ошибок никаких нет. От чего это зависит не могу понять. Но скрипт классный конечно.
Оставьте свое сообщение