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

Откуда: Минск
Сообщений: 76
Добрый день, господа!

На настоящий момент работаю с базой MS SQL 2008, до этого в основном с Ораклом.
Подскажите как вправить мозги оптимизатору, я просто не понимаю как можно в огромном количестве случаев выбирать совершенно кривой план выполнения, и потом сплошь и рядом использовать хинты.
Какие параметры влияют на его действия?
На базе установлены параметры Auto create statistics - True, Auto update statistic - True. Как я понимаю, это гарантирует актуальность статистики, но может есть ещё какие-то параметры, которыми руководствуется оптимизатор для оценки стоимости выполнения? Т.е. по аналогии с Ораклом,может есть что-то подобное, аналоги терминов - System Statistics, Workload Statistics, Multiblock Read Count .
Или подскажите куда смотреть, дело не в конкретном запросе - а в том что для очень и очень многих, даже не сильно сложных без хинтов время выполнения зашкаливает, простейший hash в join и запрос летает.
Или функция которая возвращает результат запроса - сам запрос отрабатывает мгновенно, если сделать выборку из функции - всё виснет. Причем периодически может быть всё нормально, в очередной момент план "ломается" и всё - без хинтов никуда.
И ещё вопрос, что-то не нашёл нигде, как посмотреть значение параметра например базы, т.е. не через графический интерфейс щёлкать Properties, а аналогично оракловому show parameter.
Буду очень благодарен за советы.
2 апр 13, 15:49    [14127120]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Maxx
Member [скрыт]

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

ето у вас после партирования БД с Оракла в Сиквел такое ?
2 апр 13, 15:51    [14127130]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
xxxxnnnn
Подскажите как вправить мозги оптимизатору, я просто не понимаю как можно в огромном количестве случаев выбирать совершенно кривой план выполнения, и потом сплошь и рядом использовать хинты.

Создавать правильные индексы.
Обновлять статистику.
Следить за типами параметров.
2 апр 13, 15:53    [14127148]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
xxxxnnnn
Member

Откуда: Минск
Сообщений: 76
Maxx,

нет, база изначально MS SQL - просто другой проект совсем
2 апр 13, 15:55    [14127164]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
xxxxnnnn
На базе установлены параметры Auto create statistics - True, Auto update statistic - True. Как я понимаю, это гарантирует актуальность статистики

Вовсе нет
2 апр 13, 15:56    [14127172]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
xxxxnnnn
Member

Откуда: Минск
Сообщений: 76
Glory
xxxxnnnn
Подскажите как вправить мозги оптимизатору, я просто не понимаю как можно в огромном количестве случаев выбирать совершенно кривой план выполнения, и потом сплошь и рядом использовать хинты.

Создавать правильные индексы.
Обновлять статистику.
Следить за типами параметров.


всё это конечно хорошо, но я как бы уже не первый год работаю с базами - Sybase, Oracle и простейшие вещи понимаю, я же не спрашиваю почему тормозит запрос, что делать? А почему оптимизатор не может выбрать оптимальный план, почему без хинтов запрос с простейшими соединениями 5 таблиц выполняется минуту, при добавлении хинта hash - за пару секунд. Я ж не добавил индекс, не поменял типы. Статистика вроде как обновляется, что не так?
2 апр 13, 15:59    [14127190]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Maxx
Member [скрыт]

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

ну тогда вам досталось корявое наследие :(
Совет Glory , ну и начать с отлова самых "тяжолых" запрпосов и разбора с ними
Вообще откровенно - савсем все плохо,бывает ну очнеь редко :)
2 апр 13, 15:59    [14127194]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Crimean
Member

Откуда:
Сообщений: 13147
статистики статистиками, может у индексов ФФ дурацкий? или сильно фрагментированы?
если "иногда хорошо" - вполне может быть с этим связано
а оптимизатор - да - слабоуправляемый. это и + и - одновременно
2 апр 13, 16:04    [14127230]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
xxxxnnnn
Member

Откуда: Минск
Сообщений: 76
хорошо, господа, т.е. статистику нужно ещё обновлять вручную - писать скрипт проходящийся по всем таблицам и джобом запускать? Авто statistic параметры не гарантируют актуальность? ( В базе нету уж очень больших массовых вставок, коренным образом резко повлиявшими бы на статистику конкретной таблицы. )
2 апр 13, 16:07    [14127251]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
xxxxnnnn
всё это конечно хорошо, но я как бы уже не первый год работаю с базами - Sybase, Oracle и простейшие вещи понимаю, я же не спрашиваю почему тормозит запрос, что делать?

запрос тормозит потому, что оптимизатор не может построить нормальный план
А план он не может построить потому, что не выполнены предложенные пункты.

xxxxnnnn
Статистика вроде как обновляется, что не так?

Я так понимаю, вы не просто не знаете, что там со статистикой вообще
2 апр 13, 16:09    [14127264]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
xxxxnnnn
хорошо, господа, т.е. статистику нужно ещё обновлять вручную - писать скрипт проходящийся по всем таблицам и джобом запускать?

Можете воспользоваться стандарным визардом регламентных работ

xxxxnnnn
Авто statistic параметры не гарантируют актуальность?

Что для вас актуальность статистики ? Полное обновление после каждого изменения ? Раз в час ? в день ?
2 апр 13, 16:12    [14127291]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Гость333
Member

Откуда:
Сообщений: 3683
xxxxnnnn
И ещё вопрос, что-то не нашёл нигде, как посмотреть значение параметра например базы, т.е. не через графический интерфейс щёлкать Properties, а аналогично оракловому show parameter.

Параметры, например, базы:
SELECT DATABASEPROPERTYEX(ИмяБазы, СвойствоБазы);
SELECT * FROM sys.database_files;
SELECT * FROM sys.filegroups;

Параметры, например, сервера:
SELECT SERVERPROPERTY(СвойствоСервера);
SELECT * FROM sys.configurations;

Вы ведь в курсе, что база в MSSQL и база в Oracle — две большие разницы? :-)
2 апр 13, 16:29    [14127416]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
xxxxnnnn
Member

Откуда: Минск
Сообщений: 76
ок, насчёт актуальности статистики ,да, некорректно выразился - это понятие растяжимое
Т.е. всё чем мы можем повлиять на оптимизатор, это
1. Сбор статистики
2. rebuild index

Тогда ещё вопрос, для сбора статистики по всем таблицам и индексам базы подходит sp_updatestats или есть какие-то особенности для более тонкой, оптимальной настройки сбора?

И насчёт просмотра параметров базы, таблицы - есть какая-то процедура sp_ или оператор?
2 апр 13, 16:34    [14127454]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Гость333
Member

Откуда:
Сообщений: 3683
xxxxnnnn
И насчёт просмотра параметров таблицы

Какие именно параметры таблицы вас интересуют?
2 апр 13, 16:37    [14127463]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
xxxxnnnn
Т.е. всё чем мы можем повлиять на оптимизатор, это
1. Сбор статистики
2. rebuild index

Разумеется не все.
Можно, например, запрос переписать
2 апр 13, 16:37    [14127465]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Критик
Member

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

еще есть UPDATE STATISTICS,

индексы перестраивать нужно только те, которые этого требуют, определяем это на основе фрагментации,

при перестройки индексов автоматически обновляется статистика по ним (если перестраиваете сразу весь индекс, а не по отдельным секциям)
2 апр 13, 16:43    [14127491]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Crimean
Member

Откуда:
Сообщений: 13147
я бы начал с диагностики. уже понятно, что есть проблемные запросы. они работают то хорошо то плохо

первое - это батчи или это хранимки?

если батчи:
- помогает обновление статистик?
- помогает перестроение индексов?

если хранимки:
- помогает sp_recompile?
- помогает обновление статистик?
- помогает перестроение индексов?

именно в указанном порядке. потому как сценариев, похоже, больше чем кажется, учитывая определенный опыт работы с СУБД у ТС с одной стороны и новизну для него именно MS SQL с другой

p.s.

если не секрет, то результаты от выполнения покажете?

select @@version
exec sp_configure
2 апр 13, 17:15    [14127667]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
xxxxnnnn
Member

Откуда: Минск
Сообщений: 76
Всем спасибо за советы, буду разбираться и пробовать

Crimean,
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
+

name minimum maximum config_value run_value
access check cache bucket count 0 65536 0 0
access check cache quota 0 2147483647 0 0
Ad Hoc Distributed Queries 0 1 0 0
affinity I/O mask -2147483648 2147483647 0 0
affinity mask -2147483648 2147483647 0 0
affinity64 I/O mask -2147483648 2147483647 0 0
affinity64 mask -2147483648 2147483647 0 0
Agent XPs 0 1 1 1
allow updates 0 1 0 0
awe enabled 0 1 0 0
backup compression default 0 1 0 0
blocked process threshold (s) 0 86400 2 2
c2 audit mode 0 1 0 0
clr enabled 0 1 1 1
common criteria compliance enabled 0 1 0 0
cost threshold for parallelism 0 32767 5000 5000
cross db ownership chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
Database Mail XPs 0 1 1 1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
default trace enabled 0 1 1 1
disallow results from triggers 0 1 0 0
EKM provider enabled 0 1 0 0
filestream access level 0 2 0 0
fill factor (%) 0 100 0 0
ft crawl bandwidth (max) 0 32767 100 100
ft crawl bandwidth (min) 0 32767 0 0
ft notify bandwidth (max) 0 32767 100 100
ft notify bandwidth (min) 0 32767 0 0
index create memory (KB) 704 2147483647 0 0
in-doubt xact resolution 0 2 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 1024 1 1
max full-text crawl range 0 256 4 4
max server memory (MB) 16 2147483647 81920 81920
max text repl size (B) -1 2147483647 65536 65536
max worker threads 128 32767 0 0
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 0 0
network packet size (B) 512 32767 4096 4096
Ole Automation Procedures 0 1 1 1
open objects 0 2147483647 0 0
optimize for ad hoc workloads 0 1 0 0
PH timeout (s) 1 3600 60 60
precompute rank 0 1 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
Replication XPs 0 1 0 0
scan for startup procs 0 1 1 1
server trigger recursion 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMO and DMO XPs 0 1 1 1
SQL Mail XPs 0 1 0 0
transform noise words 0 1 0 0
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
xp_cmdshell 0 1 1 1

2 апр 13, 17:27    [14127738]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Гость333
Member

Откуда:
Сообщений: 3683
xxxxnnnn
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

Надо бы пропатчить. Посмотрите, сколько обновений вышло после этого RTM: http://sqlserverbuilds.blogspot.ru/
2 апр 13, 17:34    [14127778]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
xxxxnnnn
Member

Откуда: Минск
Сообщений: 76
Гость333
xxxxnnnn
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

Надо бы пропатчить. Посмотрите, сколько обновений вышло после этого RTM: http://sqlserverbuilds.blogspot.ru/


да.. знаю что надо, но времени как обычно нету - администратора БД тоже нету, надо разбираться самому, а тут куча проблем с производительностью как приоритетные задачи
2 апр 13, 17:42    [14127826]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Crimean
Member

Откуда:
Сообщений: 13147
про патчить уже сказали, но ситуация, скорее всего, от этого не изменится. но - патчить, да!
по конфигу:

> backup compression default 0 1 0 0

включить же, не?

> blocked process threshold (s) 0 86400 2 2

оно вам надо?

> clr enabled 0 1 1 1

не тут ли природа тормозов, нет? типа триггера дергают CLR код, который генерит HTML странички на основании стянутого с левого сервера XML? утрирую, но было и такое

> cost threshold for parallelism 0 32767 5000 5000

ой тонкая материя.. для жесткого OLTP - согласен. но если данных много а нагрузка mixed - рискуете просесть на "объемных" запросах

> max degree of parallelism 0 1024 1 1

ага. и после, небось, жалуетесь, что индексация медленно идет. или для перестроения индексов "разжимаете" через "maxdop"?

> Ole Automation Procedures 0 1 1 1

окак. и CLR и "это" вам надо? еще один кандидат на необъяснимые "чудеса"

> priority boost 0 1 1 1

а это-то вам для чего?? админа прет, когда консоль "мерзнет"? можно "честно" жаловаться на "мерзкие винды"? или действительно СУБД на железке далеко не единственный сервис, но достаточно важный? ну так может афинизировать лучше?
2 апр 13, 17:51    [14127876]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Crimean
Member

Откуда:
Сообщений: 13147
xxxxnnnn
администратора БД тоже нету, надо разбираться самому, а тут куча проблем с производительностью как приоритетные задачи


так пригласите "внешнего", удаленно, на время, пока понятность не образуется
как минимум будет инфа + понимание + направление для движения
2 апр 13, 17:52    [14127881]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3752
Crimean
именно в указанном порядке. потому как сценариев, похоже, больше чем кажется

сценариев вообще дофига.
Учитывая временные таблицы и даже табличные переменные....
2 апр 13, 17:53    [14127893]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 17416
интересно через сколько страниц попросят запрос и DDL
2 апр 13, 18:33    [14128029]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор MS SQL  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Ivan Durak
сценариев вообще дофига


ну, с учетом заявленного опыта "вообще", я пока ставлю на перечисленные
это или действительно автообновление не срабатывает ибо адаптивное никто не включал
(а не включал? dbcc tracestatus что вернет?)
а данных уже немало, а меняется небольшой % данных, что и дает картинку
или мы упремся в "классику" с параметрами в хранимках / мульти-строчных UDF ибо планы у нас не зависят от значений параметров и хорошо / плохо зависит от удачности параметров при вызове хранимки в момент ре-компиляции плана
2 апр 13, 18:41    [14128050]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить