Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Обслуживание индексов  [new]
Saniacot
Member

Откуда:
Сообщений: 45
Имеется бизнес система, к данным обращается посредством СУБД ms sql. Последнее время начали поступать жалобы от пользователей на медленную работу системы. Были теоретически изучены возможные причины сбоев, производительность сервера в порядке, место есть. Блокировок (во всяком случае таких, чтобы приводили к серьёзным тормозам в системе), нет. Возникли подозрения (подкрепленные словами вендора) о том, что проблема может быть в индексах и статистике. Не являюсь администратором, поэтому представления о индексах по большей части теоретические. Поэтому хотел бы посоветоваться с более опытными коллегами:
1. Каким образом лучше всего комплексно проверить индексы и статистику?
2. Если проблема в индексах, есть ли универсальный скрипт для реорганизации (или перестройки?) индексов?
3. Могут ли возникнуть проблемы во время процесса и нужно ли переводить БД в оффлайн режим?
4. С какого количества строк индексы вообще становятся эффективны (во многих статьях указывается, что на небольших таблицах они могут даже замедлить выборки, небольшие это сотни, тысячи, десятки тысяч строк?)
Спасибо!
6 дек 19, 13:57    [22034188]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
покажите статистику ожиданий
SQL Server Wait Statistics
6 дек 19, 14:21    [22034239]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
komrad
Member

Откуда:
Сообщений: 5245
Saniacot

1. Каким образом лучше всего комплексно проверить индексы и статистику?

основные проблемы у перечисленного : устаревание (статистики), нехватка (статистики, индексы), избыток (индексы), фрагментация (индексы)
вся информация доступна в системных представлениях
- sys.dm_db_index_physical_stats
- sys.dm_db_index_usage_stats
- sys.dm_db_index_operational_stats
- sys.dm_db_missing_index_details
- sys.dm_db_stats_properties


Saniacot

2. Если проблема в индексах, есть ли универсальный скрипт для реорганизации (или перестройки?) индексов?

можно использовать де-факто стандарт на подобные скрипты: Ola Hallengren SQL Server Index and Statistics Maintenance
ссылка: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Saniacot

3. Могут ли возникнуть проблемы во время процесса и нужно ли переводить БД в оффлайн режим?

база в режиме offline недоступна ни для кого/чего
реиндекс лучше проводить в периоды минимальной нагрузки, не пересекаясь с другими процессами типа бекапа
обновление статистики не грузит особо систему и, обычно, проходит быстро, если запущено с настройками по умолчанию

Saniacot

4. С какого количества строк индексы вообще становятся эффективны (во многих статьях указывается, что на небольших таблицах они могут даже замедлить выборки, небольшие это сотни, тысячи, десятки тысяч строк?)

Видел такое (Dave Pinal демонстрировал), когда без индекса оптимизатор строил быстрый план, а с вроде бы нужным индексом, план выходил по факту очень медленным.
Нет железного правила с какого кол-ва записей стоит строить индексы.
Как и во многих других вопросах ответ начинается с фразы "это зависит от многих вещей" - размера таблицы и вида данных в ней, кол-ва ресурсов на сервере, характера работы с таблицей и т.п.
Оптимизатор сам принимает решение использовать индекс или просто просканировать таблицу исходя из самого запроса, его стоимости запроса и многих других параметров.
6 дек 19, 14:44    [22034276]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Saniacot
Member

Откуда:
Сообщений: 45
Если речь идёт о запросе в статье, то вот:

К сообщению приложен файл. Размер - 18Kb
6 дек 19, 14:44    [22034277]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
вообще да, начитывает много, может и индексов нехват.
а может и просто памяти нет, у вас сколько RAM?
и какова полная версия сервера?
( select @@version )
6 дек 19, 15:11    [22034312]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Saniacot
Member

Откуда:
Сообщений: 45
komrad
Saniacot

1. Каким образом лучше всего комплексно проверить индексы и статистику?

основные проблемы у перечисленного : устаревание (статистики), нехватка (статистики, индексы), избыток (индексы), фрагментация (индексы)
вся информация доступна в системных представлениях
- sys.dm_db_index_physical_stats
- sys.dm_db_index_usage_stats
- sys.dm_db_index_operational_stats
- sys.dm_db_missing_index_details
- sys.dm_db_stats_properties


Saniacot

2. Если проблема в индексах, есть ли универсальный скрипт для реорганизации (или перестройки?) индексов?

можно использовать де-факто стандарт на подобные скрипты: Ola Hallengren SQL Server Index and Statistics Maintenance
ссылка: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Saniacot

3. Могут ли возникнуть проблемы во время процесса и нужно ли переводить БД в оффлайн режим?

база в режиме offline недоступна ни для кого/чего
реиндекс лучше проводить в периоды минимальной нагрузки, не пересекаясь с другими процессами типа бекапа
обновление статистики не грузит особо систему и, обычно, проходит быстро, если запущено с настройками по умолчанию

Saniacot

4. С какого количества строк индексы вообще становятся эффективны (во многих статьях указывается, что на небольших таблицах они могут даже замедлить выборки, небольшие это сотни, тысячи, десятки тысяч строк?)

Видел такое (Dave Pinal демонстрировал), когда без индекса оптимизатор строил быстрый план, а с вроде бы нужным индексом, план выходил по факту очень медленным.
Нет железного правила с какого кол-ва записей стоит строить индексы.
Как и во многих других вопросах ответ начинается с фразы "это зависит от многих вещей" - размера таблицы и вида данных в ней, кол-ва ресурсов на сервере, характера работы с таблицей и т.п.
Оптимизатор сам принимает решение использовать индекс или просто просканировать таблицу исходя из самого запроса, его стоимости запроса и многих других параметров.

Спасибо за советы! Попробовал вызывать первое системное представление по одной из основных таблиц. Как я понимаю по значению поля avg_fragmentation_in_percent всё плохо?

К сообщению приложен файл. Размер - 42Kb
6 дек 19, 15:14    [22034313]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
komrad
Member

Откуда:
Сообщений: 5245
Saniacot

Спасибо за советы! Попробовал вызывать первое системное представление по одной из основных таблиц. Как я понимаю по значению поля avg_fragmentation_in_percent всё плохо?

ориентируйтесь на столбец page_count
одна страница - 8кб
так что, грубо, можно игнорировать всё меньше 10000
6 дек 19, 15:21    [22034327]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Saniacot
Попробовал вызывать первое системное представление по одной из основных таблиц. Как я понимаю по значению поля avg_fragmentation_in_percent всё плохо?

все плохо только с кластерным.
остальные индексы слишком малы, чтобы как-то влияло.
про память и редакцию сервера ответьте,
а то если это Express с гигим памяти,
то действительно все плохо
6 дек 19, 15:23    [22034329]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Saniacot
Member

Откуда:
Сообщений: 45
komrad
Saniacot

Спасибо за советы! Попробовал вызывать первое системное представление по одной из основных таблиц. Как я понимаю по значению поля avg_fragmentation_in_percent всё плохо?

ориентируйтесь на столбец page_count
одна страница - 8кб
так что, грубо, можно игнорировать всё меньше 10000


Просто в документации к view указано следующее:
"Для обеспечения наибольшей производительности значение аргумента avg_fragmentation_in_percent должно быть как можно более близким к нулю. Но могут быть приемлемыми значения от 0 до 10 процентов. Для снижения этих значений могут использоваться любые методы снижения фрагментации, такие как перестройка, реорганизация или повторное создание. Дополнительные сведения о том, как анализировать степень фрагментации в индексе, см. в разделе реорганизация и перестроение индексов."

А у меня там все значения близки к 100% :)
6 дек 19, 15:24    [22034330]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Saniacot
Member

Откуда:
Сообщений: 45
Yasha123
Saniacot
Попробовал вызывать первое системное представление по одной из основных таблиц. Как я понимаю по значению поля avg_fragmentation_in_percent всё плохо?

все плохо только с кластерным.
остальные индексы слишком малы, чтобы как-то влияло.
про память и редакцию сервера ответьте,
а то если это Express с гигим памяти,
то действительно все плохо

C памятью всё вроде бы нормально, версия не экспресс:

К сообщению приложен файл. Размер - 15Kb
6 дек 19, 15:33    [22034340]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
komrad
Member

Откуда:
Сообщений: 5245
Saniacot,

если беспокоит только одна эта таблица, то она мала - всего 33МБ
перестройте кластерный индекс и проверьте фрагментацию
скорее всего изменений по производительности не увидите, хотя может новые планы и будут быстрее - индексная статистика-то обновится
6 дек 19, 15:40    [22034353]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7771
Saniacot,

4к страниц не существенно, это всего 32 мегабайта данных. Тормозит из-за неоптимальных планов запроса. Такие планы могут строиться из-за особенностей используемых запросов или устаревания статистик.
6 дек 19, 15:42    [22034356]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Saniacot
Member

Откуда:
Сообщений: 45
Владислав Колосов
Saniacot,

4к страниц не существенно, это всего 32 мегабайта данных. Тормозит из-за неоптимальных планов запроса. Такие планы могут строиться из-за особенностей используемых запросов или устаревания статистик.

Есть другие инстансы данного приложения (с другой БД, но аналогичной структурой, которая не тормозит). Значит скорее всего дело в статистике. Не подскажите, как правильно увидеть, что с ней что-то не так?
6 дек 19, 16:09    [22034389]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
и толку от обрезанной версии сервера?
там же не видно редакцию.
и память у вас в каких единицах, простите?
committed это в байтах что ли?
6 дек 19, 16:26    [22034416]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Saniacot
Member

Откуда:
Сообщений: 45
Yasha123
и толку от обрезанной версии сервера?
там же не видно редакцию.
и память у вас в каких единицах, простите?
committed это в байтах что ли?

Сервер вообще не загружен, версия сервера Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)
6 дек 19, 16:50    [22034441]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
у вас 18% PAGEIOLATCH_SH, он же постоянно читает с диска.
при мизерных таблицах и Standard Edition(ограничение на RAM 64Гб, не 1 как в Экспрессе) как это можно объяснить?
6 дек 19, 16:57    [22034447]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
а размер базы какой?
6 дек 19, 16:58    [22034448]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
а вообще о чем разговор, у вас сервер перегружен совсем недавно...
6 дек 19, 17:00    [22034450]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
komrad
Member

Откуда:
Сообщений: 5245
Yasha123
а вообще о чем разговор, у вас сервер перегружен совсем недавно...

либо
dbcc sqlperf('sys.dm_os_wait_stats', clear)


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

Сообщение было отредактировано: 6 дек 19, 17:10
6 дек 19, 17:07    [22034456]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Saniacot
Member

Откуда:
Сообщений: 45
Yasha123
а вообще о чем разговор, у вас сервер перегружен совсем недавно...

Извиняюсь, я не администратор, что это значит в контексте данного вопроса?
11008,44 МБ размер БД
1374,37 МБ свободно
6 дек 19, 17:09    [22034459]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Saniacot
Yasha123
а вообще о чем разговор, у вас сервер перегружен совсем недавно...

Извиняюсь, я не администратор, что это значит в контексте данного вопроса?
11008,44 МБ размер БД
1374,37 МБ свободно

сервер собирает статистику по ожиданиям с момента своего рестарта.
ну или с момента, когда насильно эту статистику почистили,
вон пример кода-шедевра у komrad-а.
у вас ожидания слишком малы, чтобы их анализировать.
значит, или сервер недавно перегрузили, или "почистили" статистику.
---
база у вас 11Гб,
а таблицу вы анализируете совсем не ту, мизерную.
смотрите на те таблицы, где ваши гигабайты сидят
---
вот время рестарта(если в 2008-ом вообще было...)
select sqlserver_start_time
from sys.dm_os_sys_info;
6 дек 19, 17:22    [22034471]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Saniacot
Member

Откуда:
Сообщений: 45
Yasha123
Saniacot
пропущено...

Извиняюсь, я не администратор, что это значит в контексте данного вопроса?
11008,44 МБ размер БД
1374,37 МБ свободно

сервер собирает статистику по ожиданиям с момента своего рестарта.
ну или с момента, когда насильно эту статистику почистили,
вон пример кода-шедевра у komrad-а.
у вас ожидания слишком малы, чтобы их анализировать.
значит, или сервер недавно перегрузили, или "почистили" статистику.
---
база у вас 11Гб,
а таблицу вы анализируете совсем не ту, мизерную.
смотрите на те таблицы, где ваши гигабайты сидят
---
вот время рестарта(если в 2008-ом вообще было...)
select sqlserver_start_time
from sys.dm_os_sys_info;

Спасибо за информацию, буду дальше разбираться:)
6 дек 19, 17:26    [22034476]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
komrad

недавно видал самописное приложение, которое собирало ожидания на сиквеле ну и обнуляло, чтобы в следующий заход не "заморачиваться" расчетами
анализ дефолтной трассы выдал эту прелесть
Это самописное приложение называется SharePoint. Они зачем то сбрасывают waitstats каждый час.
12 дек 19, 04:42    [22038260]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание индексов  [new]
komrad
Member

Откуда:
Сообщений: 5245
Mind
Это самописное приложение называется SharePoint. Они зачем то сбрасывают waitstats каждый час.


это прекрасно )
полагаю, что "это наша система - что хотим, то и творим! и не подсматривайте за нами!"
12 дек 19, 11:57    [22038534]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить