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

08.06.18

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

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

Скачать исходный код

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

 

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

 

 

Ну и, непосредственно, сами разобранные в вебинаре скрипты под спойлерами ниже. Все переменные собраны в соответствующем блоке в начале скриптов. Для отправки электронной почты используется предварительно настроенный профиль электронной почты компоненты 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    3845    spyke    28    

47

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

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

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

13.03.2024    5871    vasilev2015    19    

39

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

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

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

1 стартмани

15.02.2024    8938    175    ZAOSTG    74    

105

Переход на Clickhouse для анализа метрик

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

Принимать, хранить и анализировать показания счетчиков (метрики) в базе 1С? Почему бы нет? Но это решение быстро привело к проблемам с производительностью при попытках построить какую-то более-менее сложную аналитику. Переход на PostgresSQL только временно решил проблему, т.к. количество записей уже исчислялось десятками миллионов и что-то сложное вычислить на таких объемах за разумное время становилось все сложнее. Кое-что уже практически невозможно. А что будет с производительностью через пару лет - представить страшно. Надо что-то предпринимать! В этой статье поделюсь своим первым опытом применения СУБД Clickhouse от Яндекс. Как работает, что может, как на нее планирую (если планирую) переходить, сравнение скорости работы, оценка производительности через пару лет, пример работы из 1С. Все это приправлено текстами запросов, кодом, алгоритмами выполненных действий и преподнесено вам для ознакомления в этой статье.

1 стартмани

24.01.2024    3804    glassman    17    

38

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

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

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

09.01.2024    7454    doom2good    49    

65

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

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

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

20.11.2023    10057    ivanov660    6    

76

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

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

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

15.11.2023    5627    a.doroshkevich    20    

72

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

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

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

11.10.2023    17032    skovpin_sa    14    

102
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
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 597 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 597 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 1121 16.11.20 10:51 Сейчас в теме
Немного непонятно количество страниц в индексе, если делать через стандартные средства МС Студио там указано 1000 страниц, здесь 128. Подробно бы описать от чего зависят страницы и их количество? Именно для перестроения индекса. Ведь 128 и 1000 это существенная разница
9. Tavalik 3368 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 3368 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% и ошибок никаких нет. От чего это зависит не могу понять. Но скрипт классный конечно.
Оставьте свое сообщение