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

Откуда: Москва
Сообщений: 4082
Есть сервер, достаточно мощный - 16 ядер, 192 памяти.
Есть запрос средней сложности, к основной таблице лефт джоинами присоединены еще порядка 10-15.
Сами таблицы не очень большие от миллиона до 30 миллионов записей.
На таблицах не было никаких индексов.
Запрос выполнялся от 5 до 15 минут в зависимости от условий. Параметров условий порядка 15.
Решил его оптимизировать создав необходимые индексы.
Но никаких положительных результатов это не дало!!!
Обычно помогало неплохо. А сейчас такое ощущение, что стало даже хуже.
Смотрю план выполнения, на все Table Scan сделал индексы - похрен мороз (ПМ)!
Проставил кластерные индексы на ключевые поля в таблицах - ПМ!
Делал и ребилд и перестроение индексов, потом обновления статистики - ПМ!
Убрал распараллеливание - ПМ!
Resource Governor отключен, Cost Treshold перепробовал всякие значения, Max Degree ставил в 1 - ПМ!

Кажется, что работаю вообще с другим сервером.
Что еще может быть?
Или пора переквалифицироваться в управдомы?
12 фев 20, 12:59    [22078192]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33125
Блог
SQL2008,

Кто ж знает, что за индексы вы создали?
И может быть запрос требует сканирования таблиц, т.к. нет фильтров или просто используемые фильтры выбирают большую часть таблиц...
12 фев 20, 13:06    [22078206]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
Критик
SQL2008,

Кто ж знает, что за индексы вы создали?
И может быть запрос требует сканирования таблиц, т.к. нет фильтров или просто используемые фильтры выбирают большую часть таблиц...

Индексы делал по полям, участвующим в WHERE
и по идентификаторам связей таблиц

Сообщение было отредактировано: 12 фев 20, 13:08
12 фев 20, 13:08    [22078210]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
aleks222
Member

Откуда:
Сообщений: 818
SQL2008
Критик
SQL2008,

Кто ж знает, что за индексы вы создали?
И может быть запрос требует сканирования таблиц, т.к. нет фильтров или просто используемые фильтры выбирают большую часть таблиц...

Индексы делал по полям, участвующим в WHERE
и по идентификаторам связей таблиц


Запрос покажи, страдалец.
Нибось, там уже боржоми не помогает.
Нехило, также, огласить количество строк результата запроса.
12 фев 20, 13:13    [22078228]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
Yasha123
Member

Откуда:
Сообщений: 1831
SQL2008
Параметров условий порядка 15.

т.е. он еще и с параметрами?
тогда option(recompile)
или параметрами обозваны константы?
---
запрос показывайте.
там поди сплошные or
12 фев 20, 13:19    [22078241]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7332
Теоретически может влиять разница параметров сортировки, например, в колонке и в сессии.
12 фев 20, 13:20    [22078247]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
msLex
Member

Откуда:
Сообщений: 7621
SQL2008
Смотрю план выполнения, на все Table Scan сделал индексы - похрен мороз (ПМ)!

там были кучи и вы на них навесили некластерные индексы?

SQL2008
Проставил кластерные индексы на ключевые поля в таблицах - ПМ!

а с чего вы вязли, что так будет лучше?

SQL2008
Убрал распараллеливание - ПМ!

с какой целью



SQL2008
Индексы делал по полям, участвующим в WHERE
и по идентификаторам связей таблиц


Вы сравнивали планы до и после ваших манипуляций?
12 фев 20, 13:25    [22078259]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
Yasha123
SQL2008
Параметров условий порядка 15.

т.е. он еще и с параметрами?
тогда option(recompile)
Пробовал и так и без


или параметрами обозваны константы?
Нет, параметры передаются в процедуру с формы отчета.

---
запрос показывайте.
там поди сплошные or

Честно говоря их там дохрена!
12 фев 20, 13:31    [22078272]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6789
описание "оптимизации" прям точно соотвесвует
автор
А ты стекло протирал? - Протирал. - Бампер протирал? - Протирал... - Фары протирал?! - Протирал! - По колесам стучал?! - Стучал! - НУ ТОГДА Я НЕ ЗНАЮ!!!



и да запрос показывайте, план
12 фев 20, 13:32    [22078275]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
msLex
SQL2008
Смотрю план выполнения, на все Table Scan сделал индексы - похрен мороз (ПМ)!

там были кучи и вы на них навесили некластерные индексы?

И вешал и снимал, результата ноль.

msLex
SQL2008
Проставил кластерные индексы на ключевые поля в таблицах - ПМ!

а с чего вы вязли, что так будет лучше?

А почему кластерный индекс, он же PK, может быть хуже чем его отсутствие?

msLex
SQL2008
Убрал распараллеливание - ПМ!

с какой целью

Определить степень влияния. Без разницы.


msLex
Вы сравнивали планы до и после ваших манипуляций?

До манипуляций план не снимал, не могу сравнить.

Сообщение было отредактировано: 12 фев 20, 13:37
12 фев 20, 13:37    [22078289]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
msLex
Member

Откуда:
Сообщений: 7621
SQL2008
msLex
пропущено...

а с чего вы вязли, что так будет лучше?

А почему кластерный индекс, он же PK, может быть хуже чем его отсутствие?


Кластерный индекс по PK не всегда лучше кластерного индекса по другому полю

Классический пример кластерный индекс по id документа vs кластерный индекс по дате создания документа.
12 фев 20, 13:51    [22078324]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
andy st
Member

Откуда:
Сообщений: 782
SQL2008,
в запросе туча OR и LIKE и на индексы серверу ПМ (с)
12 фев 20, 13:51    [22078326]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
msLex
кластерный индекс по id документа

Именно так у меня.
12 фев 20, 13:52    [22078329]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
andy st
SQL2008,
в запросе туча OR и LIKE и на индексы серверу ПМ (с)

Лайков нет ни одного.
12 фев 20, 13:53    [22078333]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
andy st
SQL2008,
в запросе туча OR и на индексы серверу ПМ (с)

Это сейчас проверим.
12 фев 20, 13:55    [22078338]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
msLex
Member

Откуда:
Сообщений: 7621
SQL2008
msLex
кластерный индекс по id документа

Именно так у меня.

так с чего вы взяли, что он лучше чем, например, кластерный индекс по дате документа?
12 фев 20, 14:03    [22078354]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
msLex
SQL2008
пропущено...

Именно так у меня.

так с чего вы взяли, что он лучше чем, например, кластерный индекс по дате документа?

возможно, что вы и правы... но вот дат в документе 7 и по каждой есть фильтрация, какую дату делать "кластерной"?
12 фев 20, 14:09    [22078361]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
msLex
Member

Откуда:
Сообщений: 7621
SQL2008
msLex
пропущено...

так с чего вы взяли, что он лучше чем, например, кластерный индекс по дате документа?

возможно, что вы и правы... но вот дат в документе 7 и по каждой есть фильтрация, какую дату делать "кластерной"?


смотрите по частоте запросов и диапазону дат (чем больше, тем больше будет пользы от кластерный или покрывающего индекса)

Сообщение было отредактировано: 12 фев 20, 14:44
12 фев 20, 14:43    [22078399]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
msLex, похоже что у меня не с индексами, а с данными косяк.
12 фев 20, 14:57    [22078408]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
SQL2008, были небольшие косяки, но это мелочь.
Вообще обычный джоин таблицы полтора миллиона записей с таблицей в четыре тысячи вернул полмиллиона записей за 9 минут! Это хрень, а не производительность на мой взгляд.
Коллеги!
Поделитесь своими мыслями.
Похоже, что ищу не там.
12 фев 20, 16:14    [22078512]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
Yasha123
Member

Откуда:
Сообщений: 1831
в третий раз просить показать запрос?
12 фев 20, 16:17    [22078515]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36639
SQL2008,

Да не похоже, что вы вообще то-то ищите, иначе бы уже и запрос показали, и его план.
12 фев 20, 16:18    [22078516]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6789
Yasha123
в третий раз просить показать запрос?

а смысл

автор
вернул полмиллиона записей за 9 минут
в грид?
12 фев 20, 16:18    [22078517]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
andy st
Member

Откуда:
Сообщений: 782
TaPaK
автор
вернул полмиллиона записей за 9 минут
в грид?

вероятнее всего да.
и с блобами...
и по сетке в 1Мбит/сек...
12 фев 20, 16:43    [22078545]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
msLex
SQL2008
пропущено...

А почему кластерный индекс, он же PK, может быть хуже чем его отсутствие?


Кластерный индекс по PK не всегда лучше кластерного индекса по другому полю

Классический пример кластерный индекс по id документа vs кластерный индекс по дате создания документа.

Похоже что вы оказались правы.
12 фев 20, 16:50    [22078561]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
Резюме такое - совсем без индекса работает менее чем за 2 минуты на более слабом сервере, чем с кластерным на мощном сервере (9 минут).
Замеры из одной студии, с одного сетевого соединения.
msLex - оказался прав!
Респект и уважуха.
12 фев 20, 17:09    [22078579]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
Владислав Колосов
Member

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

обычно наоборот - просмотр кластерного выгоднее адресации через IAM.
12 фев 20, 17:22    [22078602]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
msLex
Member

Откуда:
Сообщений: 7621
SQL2008
совсем без индекса работает менее чем за 2 минуты на более слабом сервере, чем с кластерным на мощном сервере (9 минут).

совершенно бессмысленно сравнивать скорость выполнения запросов на двух разных серверах под разной нагрузкой
даже на одном сервере без посторонней нагрузки это проблематично из-за "кеширования" данных
12 фев 20, 17:24    [22078604]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
Александр Бердышев
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 345
msLex
пропущено...

а с чего вы вязли, что так будет лучше?

А почему кластерный индекс, он же PK, может быть хуже чем его отсутствие?

Плохо продумали кластерный индекс, в таблице было много вставок и удалений, он стал разреженным.
Это привело к тому что количество операций чтения из медленной памяти увеличилось пропорционально тому, насколько разрежен индекс.
12 фев 20, 19:53    [22078758]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
Александр Бердышев
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 345
msLex
SQL2008
совсем без индекса работает менее чем за 2 минуты на более слабом сервере, чем с кластерным на мощном сервере (9 минут).

совершенно бессмысленно сравнивать скорость выполнения запросов на двух разных серверах под разной нагрузкой
даже на одном сервере без посторонней нагрузки это проблематично из-за "кеширования" данных

Полностью согласен.
+ ещё если делать без понимания, то можно криво сделать кластерный индекс и он не будет давать прироста в производительности.
Например, сделали кластерный индекс по нескольким полям, а первый столбце кластеризованного индекса в запросе не указали или указали криво - считайте, что никакого индекса в этом случае у вас нет.

Есть ещё одна частая ошибка при работе с индексами.
Например, индекс по полю "День_рождения" типа datetime, а в запросе сделали так: YEAR(День_рождения) - в этом случае индекс тоже не будет использоваться (практически во всех случаях применение функции поверх поля с индексом убирает использование индекса в плане запроса. Есть конечно исключения, но о них долго рассказывать).

Сообщение было отредактировано: 12 фев 20, 20:00
12 фев 20, 20:00    [22078765]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
Александр Бердышев
Плохо продумали кластерный индекс, в таблице было много вставок и удалений, он стал разреженным.

После установки индекса ничего не добавлялось, не удалялось, не обновлялось.
Дефрагментация индекса была сотые доли процента.
12 фев 20, 22:37    [22078845]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
Факт остается фактом - индекс только мешал, без него запрос выполняется в три раза быстрее.
3 минуты вместо 9-ти
12 фев 20, 22:46    [22078853]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
Idol_111
Member

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

Все может быть еще проще. У Вас сложный запрос (15+ таблиц). Сервер просто не успевает найти хороший план. И Вы ему еще параметров подкинули в виде индексов.
Попробуйте запустить ваш запрос без ограничения по времени формирования плана и посмотрите, что получится.
Ну и если будет гораздо лучший результат, тут уже дело магии это зафиксировать :).

Сообщение было отредактировано: 12 фев 20, 23:32
12 фев 20, 23:33    [22078879]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3129
Idol_111
Попробуйте запустить ваш запрос без ограничения по времени формирования плана и посмотрите, что получится.
Не подскажете темному, где такое ограничение отключается, и с какой версии это появилось?
13 фев 20, 02:40    [22078927]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30460
Ennor Tiegael
Idol_111
Попробуйте запустить ваш запрос без ограничения по времени формирования плана и посмотрите, что получится.
Не подскажете темному, где такое ограничение отключается, и с какой версии это появилось?
OPTION(QUERYTRACEON 8780)
13 фев 20, 08:44    [22078965]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4309
SQL2008
Есть сервер, достаточно мощный - 16 ядер, 192 памяти.


Не пробовали Columnstore? Тупо на все большое.
13 фев 20, 09:34    [22078983]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
msLex
Member

Откуда:
Сообщений: 7621
a_voronin
SQL2008
Есть сервер, достаточно мощный - 16 ядер, 192 памяти.


Не пробовали Columnstore? Тупо на все большое.

Columnstore хорош для массовых запросов на чтение и appendonly изменения данных. Если это классическая OLTP с update-ми, delete-ми и select-ми по ключам, то почти наверняка Columnstore сделает только хуже.
13 фев 20, 09:51    [22078996]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
Убрал вообще все индексы, запустил план выполнения, создал (по рекомендации оптимизатора) покрывающий индекс на все (даже на время модификации записи, что меня немного смутило!) и время выполнения еще уменьшилось до полутора минут. В общем не всегда то, что кажется (кластерный индекс) хорошо в реальности.
Всем спасибо за горячее участие и помощь!
13 фев 20, 10:28    [22079017]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4082
msLex
Если это классическая OLTP с update-ми, delete-ми и select-ми по ключам, то почти наверняка Columnstore сделает только хуже.

Нет. Это буферные таблицы, в которые данные заливаются одноразово и потом используются для построения отчетов. На них можно вешать какие угодно индексы и сколько угодно. Оперативной работы с данными нет.
13 фев 20, 10:31    [22079025]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение сервера при попытке оптимизации  [new]
msLex
Member

Откуда:
Сообщений: 7621
SQL2008
msLex
Если это классическая OLTP с update-ми, delete-ми и select-ми по ключам, то почти наверняка Columnstore сделает только хуже.

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

В этом случае CS может быть эффективен.
13 фев 20, 11:18    [22079059]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить