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

Откуда: Ярославль
Сообщений: 197
Есть база на MS SQL Server 2008R2. Используется практически только для чтения. Редко что в нее пишется. Работа с ней ведется через хранимые процедуры. Нагрузка на сам сервер в боевом режиме 3000 транзакций в секунду. Когда начались тормоза - запустил профайлер, поставил условие duration > 2000 ms. Получил результаты - скриншот прикрепил. Даже простая выборка по индексному полю типа lt_GetOrganizationByUID занимает почти 4 секунды. Внутри процедуры SELECT uid_org, uid_user, name FROM lt_Organizations WHERE @OrganizationUID = uid_obj. База находится в режиме 'read committed snapshot'. Файл данных на одном диске, лог транзакций на другом. В чем может быть причина тормозов? Что еще нужно посмотреть?

К сообщению приложен файл. Размер - 38Kb
26 ноя 18, 14:40    [21745027]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 981
Вначале смотрите ожидания. Нагрузку на диски / проц. Анализируете "долгие" запросы и их планы. Может банально индексов не хватает. Может индексы хреновые выбираются. Может прослушивание параметров и тд. Начните с простого - посмотреть что с машиной.
26 ноя 18, 14:46    [21745034]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
felix_ff
Member

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

маленькие значения по reads. врядли это тормоза инструкций выборки.

у вас там в процедурах случаем каких нибудь методов ole автоматизации, запросов на удаленные ресурсы, или банально waitfor-конструкций нет?
26 ноя 18, 15:23    [21745079]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Владислав Колосов
Member

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

Можете посмотреть типы ожиданий административными представлениями. Однако, они работают кумулятивно, если началось недавно, то ничего не найдёте.
26 ноя 18, 15:40    [21745103]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
felix_ff, там максимально простые выборки. никакой ole автоматизации, запросов на удаленные ресурсы, waitfor-конструкций нет. индексы вроде все нужные есть. tuning advisor ничего не нашел для оптимизации.
26 ноя 18, 15:41    [21745105]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5951
Лучше воспользуйтесь счетчиками производительности, теми, что в винде.
26 ноя 18, 15:42    [21745106]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
L_argo
Member

Откуда:
Сообщений: 441
Обновите статистику (with fullscan) по самым популярным таблицам.

зы: tunning advisor - тупица. Не находит очевиднейших вещей.
26 ноя 18, 16:07    [21745124]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
felix_ff
Member

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

отследите еще события SP:Completed и SP:StmtCompleted - вторая отследит какая именно инструкция дает такую продолжительность.

а вот если SP:Completed будет маленьким, то тогда можно посмотреть в сторону времени компиляции, нагрузки на память и пропускную способность сети.
26 ноя 18, 16:30    [21745151]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
На всякий случай: в RPC буква R - Remote
26 ноя 18, 16:32    [21745156]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
felix_ff, буду пробовать.
26 ноя 18, 16:54    [21745174]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
L_argo
Member

Откуда:
Сообщений: 441
А раньше были тормоза ?
Насколько с тех пор вырос объем данных ?
Было ли какое-то событие в базе, после которого всё это началось ?

Речь про добавление полей, индексов, правку ключевых ХП, вью и т.д.
26 ноя 18, 16:58    [21745178]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
L_argo, изменений не было. но данных стало конечно больше. Сейчас БД около 70 Гб.
26 ноя 18, 17:34    [21745220]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
В базу редко что пишется. Восновном читается. А лог транзакций постоянно растет. Сама база 70 Гб. Лог транзакций до 20 доходит. AutoShrink стоит. Но приходится вручную обрезать.
27 ноя 18, 09:26    [21745706]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
L_argo
Member

Откуда:
Сообщений: 441
gepard1980
В базу редко что пишется. Восновном читается. А лог транзакций постоянно растет. Сама база 70 Гб. Лог транзакций до 20 доходит. AutoShrink стоит. Но приходится вручную обрезать.
Модель логирования БД какая (Full, Simple) ?
Нужен настроенный джоб по урезке логов.
27 ноя 18, 10:27    [21745764]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 8203
gepard1980
В базу редко что пишется. Восновном читается. А лог транзакций постоянно растет.
Лог транзакций не может расти, тем более постоянно, если в базу ничего не пишется. Вам об этом уже писали.

Если по профайлеру у запроса duration >= 2000, а CPU = 0 или около того - значит имеют место ожидания каких-то ресурсов. И об этом вам тоже писали.
Чтобы выяснить какие именно, нужно:
1. Воспроизвести проблему в SSMS
2. Для сессии из п.1 настроить и запустить соответственно настроенную сессисю Extended Events и проанализировать полученный результат. Пример тут.
27 ноя 18, 10:33    [21745776]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
L_argo, модель SIMPLE.
27 ноя 18, 11:09    [21745803]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
invm, "Если по профайлеру у запроса duration >= 2000, а CPU = 0" - да, это так. Конечно запись в базу есть, но на порядок меньше чем выборки. 1. "Воспроизвести проблему в SSMS" - не представляется возможным, т.к. базу юзает веб-служба с нагрузкой 200 запросов в секунду. Попробую по вашей ссылке ловить где ожидания происходят.
27 ноя 18, 11:19    [21745821]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 8203
gepard1980
"Воспроизвести проблему в SSMS" - не представляется возможным, т.к. базу юзает веб-служба с нагрузкой 200 запросов в секунду.
Не нужно выполнять в SSMS 200 запросов в секунду.
Достаточно взять любой из трассы и добиться такого же результата при выполнении.
27 ноя 18, 11:26    [21745835]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
invm, пробую использовать процедуру WhoIsActive. Прикрепил скрин. Селекты на чем-то стоят.

К сообщению приложен файл. Размер - 19Kb
27 ноя 18, 11:36    [21745850]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 8203
gepard1980
Селекты на чем-то стоят.
Селекты "стоят" на физических чтениях данных.
27 ноя 18, 11:48    [21745865]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
invm, нагуглил, что длительное время ожидания может указывать на проблемы с дисковой подсистемой. или может база коррупнутая стала после внештатной перезагрузки сервера. поможет ли DBCC CHECKDB ('WebLeader', REPAIRALLOWDATA_LOSS)?
27 ноя 18, 11:59    [21745881]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
invm, выполнил процедуру Glenn Berry для получения wait stats:

К сообщению приложен файл. Размер - 37Kb
27 ноя 18, 12:14    [21745899]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
invm, для определения процентного соотношение ожиданий в системе запустил процедуру, получил следующее:

К сообщению приложен файл. Размер - 9Kb
27 ноя 18, 12:20    [21745907]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 8203
gepard1980,

Судя по всему, у вас банальная нехватка памяти. Покажите результат выполнения:
select
 pc.*
from
 (
  values
   (N'SQLServer:Memory Manager', N'Total Server Memory (KB)'),
   (N'SQLServer:Memory Manager', N'Target Server Memory (KB)'),
   (N'SQLServer:Buffer Manager', N'Page life expectancy')
 ) t(object_name, counter_name) join
 sys.dm_os_performance_counters pc on pc.object_name = t.object_name and pc.counter_name = t.counter_name;
27 ноя 18, 12:26    [21745919]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
L_argo
Member

Откуда:
Сообщений: 441
Если сервер виртуализирован, то проверьте, нет ли высоконагруженно-дисковых задач на "смежных" с ним серверах.

У нас такое было: сервер БД иногда "укладывал" почту на "смежном" сервере.

Но проще сначала убедицца, что все индексы на месте. И обновить статистики.
Это даст больше пользы, чем копание в разного рода "полезных" утилитах.

Потом убедиться, что нет бесконечно висящих транзакций. Иногда такое допускают разработчики.
Из-за этого могут начинать накапливаться неясного рода блокировки и ожидания. :)

А еще время от времени ребутайте сервер, если есть возможность.
27 ноя 18, 12:26    [21745921]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
invm, выполнил этот запрос. вернул 0 строк.
27 ноя 18, 12:36    [21745942]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4302
gepard1980
invm, выполнил этот запрос. вернул 0 строк.

select
 pc.*
from
 (
  values
   (N'MSSQL$' + @@servicename + ':Memory Manager', N'Total Server Memory (KB)'),
   (N'MSSQL$' + @@servicename + ':Memory Manager', N'Target Server Memory (KB)'),
   (N'MSSQL$' + @@servicename + ':Buffer Manager', N'Page life expectancy')
 ) t(object_name, counter_name) join
 sys.dm_os_performance_counters pc on pc.object_name = t.object_name and pc.counter_name = t.counter_name;
27 ноя 18, 12:42    [21745957]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
L_argo, индексы все сразу проверил. Для баз используется отдельный диск. Базы две. Одна очень нагруженная, но с ней проблем нет. Бесконечных транзакций тоже нет. Вот думаю может отдельный диск задействовать для этой БД. ОЗУ на сервере 56 ГБ. Под SQL я отдаю 48. Сиквел сразу их отжирает. Опять же повторюсь вторая база еще более нагруженная с миллионными таблицами, но проблем нет с ней. Если бы ОЗУ не хватало, то и она бы подвисала.
27 ноя 18, 12:42    [21745959]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
buser, этот вернул такой результат:

К сообщению приложен файл. Размер - 16Kb
27 ноя 18, 12:58    [21745984]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 8203
gepard1980
вернул такой результат
И это во время "тормозов"?
27 ноя 18, 14:01    [21746075]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
invm, база круглосуточно находится под нагрузкой, т.к. веб-сервис постоянно работает. Вот текущее положение:

К сообщению приложен файл. Размер - 55Kb
27 ноя 18, 14:35    [21746136]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 1823
У тебя явное противоречие.
gepard1980
Используется практически только для чтения. Редко что в нее пишется.

gepard1980
Нагрузка на сам сервер в боевом режиме 3000 транзакций в секунду.

1. Таблица большая ? Какой уровень изоляции транзакций ? Snapshot делает копии данных
2. Кэшируй запросы в БД в своём ПО
27 ноя 18, 15:02    [21746174]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 8203
gepard1980,

У вас в секунду около 500 полных сканов и около 1500 физических чтений. При 70 Гб БД и 40 Гб памяти.
Для начала выясните какие именно таблицы полностью сканируются и их объем.
27 ноя 18, 16:21    [21746297]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Eleanor
Member

Откуда:
Сообщений: 2156
invm
какие именно таблицы полностью сканируются и их объем.

Причем, в статистику полных сканов входят табличные переменные в хранимках…
27 ноя 18, 17:12    [21746368]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Eleanor, табличные переменные используются, чтобы например скопом удалять. Пришло например веб-службе задание - удалить тысячу объектов с такими-то уидами. я соответственно вызываю хранимку и передаю ей табличную переменную с этими уидами.
27 ноя 18, 17:55    [21746440]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Valery_B, база находится в режиме 'read committed snapshot'. Для кэширования использую Redis.
27 ноя 18, 17:56    [21746443]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
invm, я так понимаю надо планы запросов смотреть. хранимок на сервере около тысячи. самые используемые смотреть?
27 ноя 18, 17:59    [21746448]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
invm, спрашиваю, потому как никогда на такой уровень не спускался. планы не анализировал. на что обратить внимание при их просмотре?
27 ноя 18, 18:03    [21746455]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 8203
gepard1980,

У вас имеется:
1. Постоянные физические чтения. В любом случае, это говорит о недостаточном объеме памяти - оперативных данных нет в буферном пуле и их приходится вычитывать с диска.
2. Постоянные ожидания на физических чтениях. Свидетельствует о прегруженности дисков. То, что файлы БД и лога на раных дисках ни о чем не говорит - может это логические диски на одном физическом, или физические на одном канале контроллера, или вообще они виртуальные. И т.д. и т.п.
3. Постоянный рост ЖТ при простой модели восстановления. Для log_reuse_wait_desc = active_transaction такое может быть только при наличии незакрытой долгоиграющей транзакции. Но вы этого не подтверждаете. А чудес не бывает.

Исходя из перечисленного, можно дать только общие рекомендации:
1. Проверить версию сервера и обновиться до последнего SP/CU.
2. Как уже советовали, обновить статистику с полным сканированием.
3. Разбираться с дисковой подсистемой.
4. Нарастить объем памяти сервера.

Возможно эти меры временно решат проблемы.
Если характер работы с БД не предполагает вычитываний всего объема данных, то рано или поздно таки придется заняться оптимизацией запросов. И в рамках этого уже искать злостных читателей. Например, вот так:
select top (100)
 st.sql_statement, qp.query_plan, qs.*
from
 sys.dm_exec_query_stats qs cross apply
 sys.dm_exec_query_plan(qs.plan_handle) qp cross apply
 sys.dm_exec_sql_text(qs.sql_handle) qt cross apply
 (
  select substring(
    qt.text,
    qs.statement_start_offset/2 + 1,
    (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end - qs.statement_start_offset)/2 + 1
   )
 ) st(sql_statement)
order by
 cast(qs.total_logical_reads as float) / qs.execution_count desc;
28 ноя 18, 10:25    [21746918]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
invm, благодарю за советы!
28 ноя 18, 11:56    [21747083]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5951
Я бы не сказал, что памяти мало, т.к. полтора миллиона чтений в секунду - хороший результат. PLE в 1200 это подтверждает. Возможна недозагрузка по ядрам из-за отсутствия распараллеливания (особенности запросов, настроенные ограничения) или недостаточное количество ядер. Я бы на это обратил внимание.
Или задержки ввода-вывода, проверьте среднюю длину дисковых очередей.
28 ноя 18, 12:25    [21747154]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Владислав Колосов, открыл PerfMon. Вот длина очереди:

К сообщению приложен файл. Размер - 25Kb
28 ноя 18, 13:24    [21747271]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
invm, судя по данным resource monitor - вы правы. сильно нагружен диск с базами. попробую сегодня ночью перенести одну из них на другой физический диск. может поможет. если нет, придется SSD искать.
28 ноя 18, 13:45    [21747311]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
invm, вот еще:

К сообщению приложен файл. Размер - 15Kb
28 ноя 18, 13:52    [21747324]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Владислав Колосов
Member

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

Первая строка - диск C обычно. На нем длина очереди 270 - это очень плохо. Чем он у вас нагружен? Tempdb или какие-то приложения работают? Файловое хранилище может быть?
28 ноя 18, 14:04    [21747367]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 8203
Владислав Колосов
Я бы не сказал, что памяти мало, т.к. полтора миллиона чтений в секунду - хороший результат. PLE в 1200 это подтверждает.
По-вашему сервер занимается физическими чтениями от безделья? Ознакомьтесь - https://solutioncenter.apexsql.com/top-sql-server-memory-pressure-counters/
28 ноя 18, 14:07    [21747377]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 8203
gepard1980
попробую сегодня ночью перенести одну из них на другой физический диск. может поможет. если нет, придется SSD искать.
Дело ваше, но это лечение следствия, а не причины.
28 ноя 18, 14:09    [21747381]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3706
gepard1980
В... Лог транзакций до 20 доходит. AutoShrink стоит. Но приходится вручную обрезать.


А вы бекапы вообще делаете?
28 ноя 18, 14:09    [21747383]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Владислав Колосов
Member

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

Вы полагаете, что тысяча физических чтений на полтора миллиона просмотров страниц - это много? Или вы думаете, что он одну страницу просматривает полтора миллиона раз?
28 ноя 18, 14:10    [21747387]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Владислав Колосов
Member

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

PLE 1300+ как укладывается в Ваше предположение?
28 ноя 18, 14:13    [21747393]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Владислав Колосов, нет - первая строчка это как раз диск G с файлами баз данных (mdf).
/
28 ноя 18, 14:16    [21747398]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 8203
Владислав Колосов
PLE 1300+ как укладывается в Ваше предположение?
Еще раз - ознакомьтесь со статьей и посчитайте рекомендуемый минимальный PLE для условий ТС'а.
28 ноя 18, 14:23    [21747406]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

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

PLE 1300+ как укладывается в Ваше предположение?

значение PLE "просто на сейчас" не имеет никакой ценности
28 ноя 18, 14:33    [21747423]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
gepard1980,

Покажите содержание пула, чем то типа 21525605
28 ноя 18, 14:40    [21747430]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
TaPaK, вот:

К сообщению приложен файл. Размер - 27Kb
28 ноя 18, 14:55    [21747451]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
gepard1980,

Ну, имхо очередь к диску конечно не маленькая, но и не ужас. Судя по тому что у вас резултаты по "одной" строке возвращают, значить получаете сканы, не попадаете в индексы, всё это таскается между диском и памятью, тут и очередь и низкий ple. Разбирайте конкретные запросы
28 ноя 18, 15:04    [21747472]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 8203
a_voronin
А вы бекапы вообще делаете?
Даже стало любопытно: бекап чего надо сделать, что бы прекратить рост ЖТ при простой модели восстановления?

ЗЫ: Не надоело бред генерировать?
28 ноя 18, 15:07    [21747484]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Yasha123
Member

Откуда:
Сообщений: 1114
на картинке товарища на первой странице читаются поля с названиями file_word_upd, keyfilebody.
вопрос: какого типа эти поля?
если это блобы, то поздравимся: они не кэшируются, т.е. каждый раз начитываются заново с диска
28 ноя 18, 15:54    [21747555]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5951
При такой очереди диска может и не катастрофа, когда запросы "висят" мертво, но нельзя сказать, что работа комфортна.

Есть же резервы памяти, верхнюю границу можно увеличить до 60Гб для SQL?
28 ноя 18, 16:20    [21747594]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Yasha123
Member

Откуда:
Сообщений: 1114
Владислав Колосов
При такой очереди диска может и не катастрофа, когда запросы "висят" мертво, но нельзя сказать, что работа комфортна.

Есть же резервы памяти, верхнюю границу можно увеличить до 60Гб для SQL?

если это блобы, то хоть терабайт памяти зафигачь,
будет их читать с диска каждый раз заново
28 ноя 18, 16:25    [21747600]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Владислав Колосов, это я оставил для запуска студий и других программ. тоже кушать хотят. думаю увеличение с 45 до 55 например (а всего 58) ничего не даст. сиквел сожрет и эту десятку.
28 ноя 18, 16:29    [21747608]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Владислав Колосов
Member

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

там, похоже, комплексная проблема... Блобы -то да, но они разве повлияли бы на PLE?
28 ноя 18, 16:29    [21747609]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Yasha123, спасибо, уберу их из запроса.
28 ноя 18, 16:30    [21747612]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Владислав Колосов
Member

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

Ну, знаете ли... Тогда Вам следовало очень хорошо запастись памятью, студия может и 16 Гб скушать. А Вы такую нагрузку дали серверу.
28 ноя 18, 16:31    [21747613]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Владислав Колосов, ищу какой-то баланс. сиквелу - 45. 10 - приложениям.
28 ноя 18, 16:37    [21747624]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

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

Есть вероятность что у вас там много сканов и память постоянно вытесняется сначала одними таблицами потом другими. Базы большие?
Покажите распределение памяти по базам:

+ Buffer By Database
/* Generated in SQL Explorer v.1.6.4.31586 */
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000

SELECT 
  CASE WHEN database_id = 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END  as DatabaseName, 
  CAST(COUNT(*)/128. AS NUMERIC(20,2)) AS [BufferSize MB], 
  CAST(SUM(CAST(free_space_in_bytes AS BIGINT)) / (1024. * 1024) AS NUMERIC(20,2)) AS [EmptySize MB], 
  CAST(SUM(is_modified/128.) AS NUMERIC(20,2)) AS [DirtySize MB], 
  CAST(AVG(100.*(free_space_in_bytes/ (1024. * 1024))/(1/128.)) AS NUMERIC(8,2)) AS [EmptySize %], 
  COUNT(*) AS PagesInCache
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY PagesInCache desc
OPTION(MAXDOP 1)

Ну и потом для той базы что которая больше всего памяти использует запустите

+ Buffer By Object
/* Generated in SQL Explorer v.1.6.4.31586 */
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000

SELECT 
  CASE WHEN ISNULL(RES.container_id, 0) = 0 
    THEN '<<<Marked for deferred drop>>>'
  ELSE
    OBJECT_SCHEMA_NAME(p.[object_id]) + '.' + OBJECT_NAME(p.[object_id]) 
  END AS [TableName],
  p.index_id, 
  CASE WHEN i.index_id IS NOT NULL THEN ISNULL(i.name, '<<<HEAP>>>') ELSE NULL END AS [IndexName], 
  RES.type_desc,
  CAST(RES.buffers/128. AS NUMERIC(20,2)) AS [BufferSize MB],  
  CAST(RES.buffers_modified/128. AS NUMERIC(20,2)) AS [DirtySize MB],
  CAST(ROUND(CASE WHEN RES.used_pages < RES.buffers THEN 100. ELSE ISNULL(100.0 * RES.buffers / NULLIF(RES.used_pages,0),0) END, 4) AS DECIMAL(8,4)) AS [PagesCached %],
  CAST(RES.free_space_in_mb AS NUMERIC(20,2)) AS [EmptySize MB]
FROM
  (
    SELECT  -- Get allocation units + buffer descriptors grouped by container_id
      AU.container_id,
      AU.type_desc,
      SUM(BUF_GP.buffers) AS buffers,
      SUM(AU.used_pages) AS used_pages,
      SUM(BUF_GP.free_space_in_mb) AS free_space_in_mb,
      SUM(buffers_modified) AS buffers_modified,
      SUM(CASE WHEN AU.type = 1 THEN BUF_GP.RowCountLeaf ELSE 0 END) AS RowCountLeaf,
      SUM(CASE WHEN AU.type = 1 THEN BUF_GP.RowCountNonLeaf ELSE 0 END) AS RowCountNonLeaf
    FROM
    (
      SELECT  -- Get buffer descriptors grouped by allocation_unit_id
        BUF.allocation_unit_id,
        COUNT(*) AS buffers,
        SUM(CAST(BUF.free_space_in_bytes AS BIGINT)) / (1024. * 1024) AS free_space_in_mb, 
        SUM(CASE WHEN BUF.is_modified = 1 THEN 1 ELSE 0 END) AS buffers_modified,
        SUM(CASE WHEN BUF.page_level =  0 and BUF.page_type IN ('INDEX_PAGE','DATA_PAGE') THEN BUF.row_count ELSE 0 END ) AS RowCountLeaf,
        SUM(CASE WHEN BUF.page_level <> 0 and BUF.page_type IN ('INDEX_PAGE','DATA_PAGE') THEN BUF.row_count ELSE 0 END ) AS RowCountNonLeaf
      FROM sys.dm_os_buffer_descriptors AS BUF
      WHERE BUF.database_id = DB_ID()
      GROUP BY BUF.allocation_unit_id
    ) BUF_GP
    LEFT JOIN sys.allocation_units AS AU ON AU.allocation_unit_id = BUF_GP.allocation_unit_id
    GROUP BY AU.container_id, AU.type_desc
  ) RES
  LEFT JOIN sys.partitions AS p ON RES.container_id = p.hobt_id 
  LEFT JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE ABS(ISNULL(p.[object_id],101)) > 100
ORDER BY RES.buffers DESC
OPTION(MAXDOP 1);

и
+ Index Usage Statistics
/* Generated in SQL Explorer v.1.6.4.31586 */
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000

SELECT 
  OBJECT_SCHEMA_NAME(i.[object_id]) + '.' + OBJECT_NAME(i.[object_id]) AS TableName,
  ISNULL(REPLACE(i.name, NCHAR(31), ''), '<<<HEAP>>>') AS IndexName,
  i.is_primary_key AS PK,
  i.is_unique_constraint AS UQ,
  CAST(CASE WHEN i.index_id = 1 THEN 1 ELSE 0 END AS BIT) AS Clust,
  CAST(p2.SizeMB AS DECIMAL(20, 2)) AS SizeMB,
  s.user_seeks,
  s.user_scans,
  s.user_lookups,
  s.user_seeks + s.user_scans + s.user_lookups AS total_user_reads,
  CAST(s.user_scans*SizeMB/1024. AS DECIMAL(30, 2)) AS TotalScanGB,
  CASE WHEN i.index_id IN (0,1) THEN mi.MissingIndexes ELSE NULL END AS MissingIndexes
FROM sys.indexes AS i
LEFT JOIN
    (
      select p2.object_id, p2.index_id, SUM(au.used_pages) / 128.AS SizeMB
      from sys.partitions AS p2
        INNER JOIN sys.allocation_units AS au ON p2.partition_id = au.container_id
      where au.type <> 2-- LOB_DATA
      group by p2.object_id, p2.index_id
    ) p2 ON p2.object_id = i.object_id AND p2.index_id = i.index_id
LEFT JOIN 
  (
    SELECT mid.object_id, MissingIndexes = COUNT(*)
    FROM sys.dm_db_missing_index_group_stats AS migs
      INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
      INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
    WHERE mid.database_id = DB_ID()
    GROUP BY mid.object_id
  ) mi ON mi.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS s
  ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
  AND s.database_id = DB_ID()
WHERE ((OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1) OR (OBJECTPROPERTY(i.[object_id],'IsView') = 1))
ORDER BY TotalScanGB DESC
OPTION(MAXDOP 1)
28 ноя 18, 22:31    [21747923]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
gepard1980
Владислав Колосов, ищу какой-то баланс. сиквелу - 45. 10 - приложениям.
На сервере БД не должно быть других приложений! Сколько у вас свободной памяти на сервере?
28 ноя 18, 22:34    [21747925]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, первое выполнил. А в двух других скриптах не понял, где указывать самую большую БД (в моем случае это lion_data)?

К сообщению приложен файл. Размер - 31Kb
28 ноя 18, 23:03    [21747939]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, размер базы lion_data 110 Гб. Базы WebLeader 75 Гб. На сервере всего 58 Гб ОЗУ. 45 Гб выделил сиквелу. Остальное про запас оставил. Виндовый процесс-менеджер показывает 7 Гб свободно.

К сообщению приложен файл. Размер - 66Kb
28 ноя 18, 23:06    [21747940]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, из приложений только SSMS и DBForge. Естественно никаких FTP, NoSQL и т.д. на этом сервере нет. Они на другом.
28 ноя 18, 23:13    [21747942]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

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

Просто перейдите в нужную базу перед запуском скрипта.

USE lion_data
28 ноя 18, 23:59    [21747954]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
gepard1980
Mind, первое выполнил. А в двух других скриптах не понял, где указывать самую большую БД (в моем случае это lion_data)?
У вас 37% (16Гб) пустого места внутри индексов. Вы rebuild/reorginize индексов хоть раз делали?
Хотя это конечно могут быть блобы, но все равно вряд ли так много.
29 ноя 18, 00:02    [21747957]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, есть job который сначала проверку на целостность делает, а потом ребилд индексов. посмотрел сейчас лог - он всегда останавливался на ошибке при DBCC CHECKDB на базе WebLeader и дальше соответственно не шел. надо будет ночью сегодня запустить джоб на ребилд индексов. а ошибка такая:

Executing the query "DBCC CHECKDB(N'WebLeader') WITH NO_INFOMSGS
" failed with the following error: "Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:9380353) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:9380354) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:9451372) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Object ID 367395005, index ID 1, partition ID 72058363036303360, alloc unit ID 72058364643835904 (type In-row data): Page (1:9451372) could not be processed. See other errors for details.
Table error: Object ID 367395005, index ID 1, partition ID 72058363036303360, alloc unit ID 72058364643835904 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:9451372) and previous child (0:0), but they were not encountered.
Object ID 624671800, index ID 1, partition ID 72058362590003200, alloc unit ID 72058364196487168 (type In-row data): Page (1:9380354) could not be processed. See other errors for details.
Table error: Object ID 624671800, index ID 1, partition ID 72058362590003200, alloc unit ID 72058364196487168 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:9380354) and previous child (0:0), but they were not encountered.
Object ID 640671857, index ID 1, partition ID 72058362590068736, alloc unit ID 72058364196552704 (type In-row data): Page (1:9380353) could not be processed. See other errors for details.
Table error: Object ID 640671857, index ID 1, partition ID 72058362590068736, alloc unit ID 72058364196552704 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:9380353) and previous child (0:0), but they were not encountered.
Table error: page (1:22851) allocated to object ID 1627561255, index ID 1, partition ID 72058340014751744, alloc unit ID 72058341621170176 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Table error: Object ID 1627561255, index ID 1, partition ID 72058340014751744, alloc unit ID 72058341621170176 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:22851) and previous child (0:0), but they were not encountered.
Table error: page (1:22848) allocated to object ID 1643561312, index ID 1, partition ID 72058340014817280, alloc unit ID 72058341621235712 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Table error: Object ID 1643561312, index ID 1, partition ID 72058340014817280, alloc unit ID 72058341621235712 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:22848) and previous child (0:0), but they were not encountered.
CHECKDB found 0 allocation errors and 3 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.ifts_comp_fragment_1166627199_1279643' (object ID 367395005).
CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.ifts_comp_fragment_1166627199_1279308' (object ID 624671800).
CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.ifts_comp_fragment_4649743_10289803' (object ID 640671857).
CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.ifts_comp_fragment_4649743_9953028' (object ID 1627561255).
CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.ifts_comp_fragment_4649743_9953029' (object ID 1643561312).
CHECKDB found 0 allocation errors and 13 consistency errors in database 'WebLeader'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (WebLeader).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
29 ноя 18, 08:52    [21748036]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, по второму скрипту относительно базы lion_data результаты такие:

К сообщению приложен файл. Размер - 80Kb
29 ноя 18, 08:55    [21748039]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, по третьему скрипту относительно базы lion_data результаты такие. На что обратить внимание?
29 ноя 18, 08:56    [21748040]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind,

К сообщению приложен файл. Размер - 80Kb
29 ноя 18, 08:56    [21748041]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
PizzaPizza
Member

Откуда:
Сообщений: 146
gepard1980
Mind, есть job который сначала проверку на целостность делает, а потом ребилд индексов.

посмотрел сейчас лог - он всегда останавливался на ошибке при DBCC CHECKDB на базе WebLeader и дальше соответственно не шел. надо будет ночью сегодня запустить джоб на ребилд индексов. а ошибка такая:


У вас job на rebuild выпадает с ошибкой при проверке целостности?
А что вам даст на ночь запуск этого jobа? Может сначала с чекдб разобраться?
29 ноя 18, 08:59    [21748045]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
PizzaPizza, посмотрел этот plan, там вообще нету check dbcc:

К сообщению приложен файл. Размер - 12Kb
29 ноя 18, 09:05    [21748048]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
PizzaPizza
Member

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

Мне кажется, что если у вас есть постоянные ошибки целостности то с ними надо разбираться в первую очередь.
Я лично не понял про что вы говорили тут
gepard1980
он всегда останавливался на ошибке при DBCC CHECKDB на базе WebLeader и дальше соответственно не шел

Кто "он"? Job с ребилдом? Если у вас в этом джобе нет чека, то как запускается этот чек, который приводит к ошибке?
29 ноя 18, 10:07    [21748111]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
PizzaPizza, тот job я уже удалил, поэтому может что и попутал. Сделал новый, который только Check Integrity делает. Если он не справится, то придется наверно базу новую скриптом создать и перелить все таблицы из старой.
29 ноя 18, 10:34    [21748148]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
gepard1980
Mind, по третьему скрипту относительно базы lion_data результаты такие. На что обратить внимание?
А вот фиг его знает. Я думал у вас простой случай.
Все что я пока вижу, это две больших таблицы на 100% загружены в память и в них огромное пустое место, которое лучше конечно убрать путем реиндекса. А вот дальше непонятно, ибо по самой большой таблице было всего 4 скана, так что непонятно что ее заставляет сидеть в памяти, есть еще конечно один missing index, но вряд ли он решит что либо. Да и сканов по другим таблицам вообще почти нет. Либо там все настолько оптимизиронно либо кто то захинтовал все запросы forceseek-ами.
Вероятно это тупиковый путь и нужно смотреть что то другое. Например диски.

Кстати та процедура что вы привели в пером посте, если ее скопировать в ssms и запустить она будет так же долго выполняться?
29 ноя 18, 11:25    [21748246]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, хинтов в хранимках нет. В SSMS выполнил следующую процедуру:

USE WebLeader

DECLARE @OrganizationUID UNIQUEIDENTIFIER
SET @OrganizationUID = '1D7E654A-373C-4F25-909E-81645EB29294'

SELECT uid_obj, uid_user, name FROM lt_Organizations WHERE @OrganizationUID = uid_obj

Первый раз выполнялась 5 секунд. Последующие разы около секунды.
29 ноя 18, 11:45    [21748302]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
gepard1980,

OPTION (OPTIMIZE FOR UNKNOWN) или OPTION (RECOMPILE)
29 ноя 18, 11:47    [21748308]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
TaPaK, с этим хинтом всегда одна секунда.
29 ноя 18, 11:56    [21748337]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1270
gepard1980
TaPaK, с этим хинтом всегда одна секунда.

Как понимаю, ТСу после добавления в процедуру хинта OPTION (OPTIMIZE FOR UNKNOWN) или OPTION (RECOMPILE) осталось только соответствующий покрывающий индекс создать?
Ну и разобраться с постоянными ошибками целостности, обновить статистики with fullscan и перестроить индексы?
29 ноя 18, 12:47    [21748431]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5951
Я не удивлен, что при такой нагрузке на диски они дают сбои. Чудо, что вообще до сих пор живы.
29 ноя 18, 13:26    [21748489]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 1986
а разве после перестройки индексов нужно делать обновление статистики?
29 ноя 18, 13:37    [21748517]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
DaniilSeryi
Member

Откуда:
Сообщений: 1270
StarikNavy
а разве после перестройки индексов нужно делать обновление статистики?


Статистика создаётся не только при создании индексов, так что, думаю, что да.
29 ноя 18, 14:09    [21748580]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Владислав Колосов, это Azure :-)
29 ноя 18, 14:59    [21748682]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 1986
DaniilSeryi
Статистика создаётся не только

это да. но после ребилда не нужно. хотя может я и не прав, и у ТС этот момент учтен )
29 ноя 18, 15:23    [21748725]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Похоже что узкое место - диск. Придется SSD использовать.
29 ноя 18, 16:02    [21748800]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
TaPaK
gepard1980,

OPTION (OPTIMIZE FOR UNKNOWN) или OPTION (RECOMPILE)
Как же бесят такие советчики.
Вы понимаете что OPTIMIZE FOR UNKNOWN и RECOMPILE делают прямо противоположное? А что, давайте без всякого анализа и плана попробуем все хинты которые только можно, авось какой нибудь подойдет. Я уж не говорю про то, что ТС запускает даже не процедуру а запрос, где сниффинга нет по определению и добавление OPTIMIZE FOR UNKNOWN вообще безсмыссленно.
29 ноя 18, 21:38    [21749133]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
DaniilSeryi
Member

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

ТС как раз процедуру запускает, из которой и скопирован код: SELECT uid_org, uid_user, name FROM lt_Organizations WHERE @OrganizationUID = uid_obj
29 ноя 18, 21:48    [21749144]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
gepard1980
TaPaK, с этим хинтом всегда одна секунда.
Не слушайте тапки. Почти наверняка эти хинты не дали абсолютно ничего.
Во время первого запуска данных не было в кэше, серверу пришлось читать с диска, который как мы знаем у вас очень медленный или чем то загружен. При последующих запусках данные были уже в памяти поэтому выполнение "быстрое". Для простой выборки по ключу даже 1 секунда это очень-очень медленно, поэтому у вас там скорее всего скан. Так что запускайте еще раз с включенным актуальным планом и выкладывайте его сюда, желательно в виде виде файла, а не картинки ("Save Execution Plas as").
Еще перед запуском можете включить:
set statistics io on
set statistics time on

Индекс по полю uid_obj у вас есть?

И еще, то что вы выполняете это не процедура, а вырванный из процедеры запрос, разница может быть весьма значительной из-за parameter sniffing, хотя я сомневаюсь что у вас проблема именно в этом.
29 ноя 18, 21:49    [21749145]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
DaniilSeryi
Mind,

ТС как раз процедуру запускает, из которой и скопирован код: SELECT uid_org, uid_user, name FROM lt_Organizations WHERE @OrganizationUID = uid_obj
Что именно ТС называет "выполнил следующую процедуру" мы можем только догадываться. Википедия дает следующее определение: "Процедура — взаимосвязанная последовательность действий где-либо". Так что это может быть что угодно. Я сейчас окно открою и плюну на улицу, это тоже будет называться процедура.

Вот это называется запрос (query/batch/statement):
USE WebLeader

DECLARE @OrganizationUID UNIQUEIDENTIFIER
SET @OrganizationUID = '1D7E654A-373C-4F25-909E-81645EB29294'

SELECT uid_obj, uid_user, name FROM lt_Organizations WHERE @OrganizationUID = uid_obj

А вот это вызов stored procedure:
EXEC lt_GetOrganizationByUID @OrganizationUID = '1D7E654A-373C-4F25-909E-81645EB29294'
29 ноя 18, 21:59    [21749154]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, "Так что запускайте еще раз с включенным актуальным планом и выкладывайте его сюда, желательно в виде виде файла, а не картинки ("Save Execution Plas as")". Можно поподробней где этот актуальный план включать, как потом файл получить. Выполнять буду как хранимую процедуру через EXEC.
29 ноя 18, 22:07    [21749165]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

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

Опять же только предположение, возможно lion_data съедает всю память, а потом WebLeader будучи плохо оптимизированной базой страдает пытаясь сканировать таблицы с диска. Из 75Гб только 1.3Гб в памяти. Я так понимаю у вас все медленные зпросы как раз таки к WebLeader?

Попробуйте еще раз запустить вот этот запрос, но уже для базы WebLeader:
+ Index Usage Statistics
/* Generated in SQL Explorer v.1.6.4.31586 */
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000

SELECT 
  OBJECT_SCHEMA_NAME(i.[object_id]) + '.' + OBJECT_NAME(i.[object_id]) AS TableName,
  ISNULL(REPLACE(i.name, NCHAR(31), ''), '<<<HEAP>>>') AS IndexName,
  i.is_primary_key AS PK,
  i.is_unique_constraint AS UQ,
  CAST(CASE WHEN i.index_id = 1 THEN 1 ELSE 0 END AS BIT) AS Clust,
  CAST(p2.SizeMB AS DECIMAL(20, 2)) AS SizeMB,
  s.user_seeks,
  s.user_scans,
  s.user_lookups,
  s.user_seeks + s.user_scans + s.user_lookups AS total_user_reads,
  CAST(s.user_scans*SizeMB/1024. AS DECIMAL(30, 2)) AS TotalScanGB,
  CASE WHEN i.index_id IN (0,1) THEN mi.MissingIndexes ELSE NULL END AS MissingIndexes
FROM sys.indexes AS i
LEFT JOIN
    (
      select p2.object_id, p2.index_id, SUM(au.used_pages) / 128.AS SizeMB
      from sys.partitions AS p2
        INNER JOIN sys.allocation_units AS au ON p2.partition_id = au.container_id
      where au.type <> 2-- LOB_DATA
      group by p2.object_id, p2.index_id
    ) p2 ON p2.object_id = i.object_id AND p2.index_id = i.index_id
LEFT JOIN 
  (
    SELECT mid.object_id, MissingIndexes = COUNT(*)
    FROM sys.dm_db_missing_index_group_stats AS migs
      INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
      INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
    WHERE mid.database_id = DB_ID()
    GROUP BY mid.object_id
  ) mi ON mi.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS s
  ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
  AND s.database_id = DB_ID()
WHERE ((OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1) OR (OBJECTPROPERTY(i.[object_id],'IsView') = 1))
ORDER BY TotalScanGB DESC
OPTION(MAXDOP 1)
29 ноя 18, 22:12    [21749167]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

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

К сообщению приложен файл. Размер - 6Kb
29 ноя 18, 22:14    [21749168]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, индекс конечно есть по полю uid_obj кластерный.
29 ноя 18, 22:14    [21749169]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, спасибо - завтра с утра буду пробовать. Сейчас запустил ребилд индексов всей базы lion_data.
29 ноя 18, 22:17    [21749173]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
gepard1980
Mind, индекс конечно есть по полю uid_obj кластерный.
Ну тогда давайте план запроса.
29 ноя 18, 22:53    [21749201]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
StarikNavy
DaniilSeryi
Статистика создаётся не только

это да. но после ребилда не нужно. хотя может я и не прав, и у ТС этот момент учтен )
При ребилде (не реорганизации) индексов статистика обновляется с фуллсканом, но есть колоночные статистики они не привязанны к индексам, да и не всегда все индексы нужно ребилдить. Умные скрипты (например от http://ola.hallengren.com) умеют делать ребилд, а потом обновлять только те статистики которые нужно. Стандартные же планы обслуживания тупы до безобразия и были созданы "для галочки", подходят только для маленьких баз, потому как лопатят все без разбора с дефолтными настройками.
29 ноя 18, 22:59    [21749205]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
Mind,

автор
Не слушайте тапки. Почти наверняка эти хинты не дали абсолютно ничего


автор
TaPaK, с этим хинтом всегда одна секунда


ты занятный... Как разберёшься что делают эти хинты-приходи
30 ноя 18, 08:30    [21749360]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, "Попробуйте еще раз запустить вот этот запрос, но уже для базы WebLeader" - сделал, вот результат:

К сообщению приложен файл. Размер - 73Kb
30 ноя 18, 09:14    [21749402]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, прикрепил план выполнения.

К сообщению приложен файл (sp_getorgbyuid.sqlplan - 5Kb) cкачать
30 ноя 18, 09:31    [21749419]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
PizzaPizza
Member

Откуда:
Сообщений: 146
— Есть ли у вас план, мистер Фикс?
— Есть ли у меня план? Есть ли у меня план? Да у меня целых три плана!

Select * это отличный план.
30 ноя 18, 10:02    [21749474]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
Mind,
что на гуру скажет на
автор
<ColumnReference Column="@OrganizationUID" ParameterCompiledValue="{guid'1D7E654A-373C-4F25-909E-81645EB29294'}" ParameterRuntimeValue="{guid'1D7E654A-373C-4F25-909E-81645EB29294'}" />
30 ноя 18, 10:23    [21749512]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
PizzaPizza, возвращаются 9 числовых полей без блобов. Думаю это не сильно влияет на перфоманс. Есть таблица с 70 полями. Вот из нее уже select * накладно делать.
30 ноя 18, 10:35    [21749535]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 1986
Mind
Стандартные же планы обслуживания тупы до безобразия и были созданы "для галочки", подходят только для маленьких баз, потому как лопатят все без разбора с дефолтными настройками.

именно это и имел в виду
30 ноя 18, 10:36    [21749543]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 1986
а план запроса топикстартеру, сказал еще 4 страницы назад, что надо бы выложить
30 ноя 18, 10:37    [21749546]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
gepard1980
Mind, прикрепил план выполнения.
Ну собственно я именно это и боялся увидеть, но в тайне надеялся на чудо. Что вот там какой-то огромный такой косяк типа implicit conversion или что-то подобное, но нет. План со стоимостью 0.003 невозможно улучшить.
Подводя итог, могу сказать только следующее: у вас очень-очень медленные диски. У меня больше нет никаких идей.
30 ноя 18, 10:56    [21749600]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
gepard1980
Member

Откуда: Ярославль
Сообщений: 197
Mind, спасибо! Читая ответы этой ветки и наблюдая за работой БД, дисков тоже пришел к этому.
30 ноя 18, 11:05    [21749622]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
Слёзы на глазах от такого анализа. Ответ дан ещё на первой странице 21745865

автор
я именно это и боялся увидеть, но в тайне надеялся на чудо

занавес
30 ноя 18, 11:10    [21749633]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
TaPaK
Mind,
что на гуру скажет на
автор
<ColumnReference Column="@OrganizationUID" ParameterCompiledValue="{guid'1D7E654A-373C-4F25-909E-81645EB29294'}" ParameterRuntimeValue="{guid'1D7E654A-373C-4F25-909E-81645EB29294'}" />

+ О, да, мой косяк, был не прав! Как же глубоко я заблуждался!!!
Ты это хотел услышать что-ли?

Я не понял нафига ты привел эту вырезку из плана? Ты только ее увидел, сам план не смотрел? Что хотел то этим сказать? Что если не поставить рекомпайл или оптимайз фо анкноун то план сломается? Вместо поиска по кластерному сразу сканы попрут? Ты кстати так и не определился, хочешь ли ты чтобы оптимизатор узнал значения параметра и рекомпайлил тривиальный план каждый раз или все таки хочешь чтобы он компилировал под усредненные значения. Разница то огромная. Эффекта правда в данном конкретном случае будет ноль.
30 ноя 18, 11:11    [21749640]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
Mind,

автор
таки хочешь чтобы он компилировал под усредненные значения

агонь!
30 ноя 18, 11:12    [21749642]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
TaPaK
Слёзы на глазах от такого анализа. Ответ дан ещё на первой странице 21745865

автор
я именно это и боялся увидеть, но в тайне надеялся на чудо

занавес
Ну мало ли, всякое бывает. Ты же надеялся на OPTION(RECOMPILE) Да и чтобы висело по 5 секунд на 5 чтениях это насколько нужно диски нагрузить?
30 ноя 18, 11:14    [21749647]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
Mind
TaPaK
Слёзы на глазах от такого анализа. Ответ дан ещё на первой странице 21745865

пропущено...

занавес
Ну мало ли, всякое бывает. Ты же надеялся на OPTION(RECOMPILE) Да и чтобы висело по 5 секунд на 5 чтениях это насколько нужно диски нагрузить?


автор
TaPaK, с этим хинтом всегда одна секунда
30 ноя 18, 11:17    [21749654]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
TaPaK
Mind,

автор
таки хочешь чтобы он компилировал под усредненные значения

агонь!
Ну давай расскажи нам всем как работает OPTIMIZE FOR UNKNOWN, а мы послушаем. Поучимся у гуру, который умеет только давать советы без каких либо обоснований.
30 ноя 18, 11:18    [21749655]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
Mind
TaPaK
Mind,

пропущено...

агонь!
Ну давай расскажи нам всем как работает OPTIMIZE FOR UNKNOWN, а мы послушаем. Поучимся у гуру, который умеет только давать советы без каких либо обоснований.

автор
Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization.

30 ноя 18, 11:19    [21749658]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
TaPaK
автор
TaPaK, с этим хинтом всегда одна секунда
агонь!
30 ноя 18, 11:19    [21749659]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5951
Mind
Процедура — взаимосвязанная последовательность действий где-либо

Я бы добавил, что процедура - это описание, а не действия.
30 ноя 18, 11:21    [21749667]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
Владислав Колосов
Mind
Процедура — взаимосвязанная последовательность действий где-либо

Я бы добавил, что процедура - это описание, а не действия.

человек ещё не знает что это декларативный язык :)
30 ноя 18, 11:22    [21749671]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
TaPaK
Mind
пропущено...
Ну давай расскажи нам всем как работает OPTIMIZE FOR UNKNOWN, а мы послушаем. Поучимся у гуру, который умеет только давать советы без каких либо обоснований.

автор
Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization.

Заглулил, молодец! То есть настолько не уверен в своих знаниях или умениях объяснять что пришлось гуглить? И то что я сказал абсолютно тоже самое но своими словами тоже сложно было понять?
30 ноя 18, 11:23    [21749674]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
Mind
TaPaK
пропущено...

пропущено...
Заглулил, молодец! То есть настолько не уверен в своих знаниях или умениях объяснять что пришлось гуглить? И то что я сказал абсолютно тоже самое но своими словами тоже сложно было понять?

где там про "средние"
30 ноя 18, 11:24    [21749678]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
Владислав Колосов
Mind
Процедура — взаимосвязанная последовательность действий где-либо

Я бы добавил, что процедура - это описание, а не действия.
Ну пожалуйтесь в википедию.
30 ноя 18, 11:25    [21749679]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5951
Если я правильно помню, кто-то где-то рассказывал, что при Unknown предполагается равномерное распределение данных по страницам при том, что объем полезной информации занимает 30%.
30 ноя 18, 11:28    [21749687]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
TaPaK
Mind
пропущено...
Заглулил, молодец! То есть настолько не уверен в своих знаниях или умениях объяснять что пришлось гуглить? И то что я сказал абсолютно тоже самое но своими словами тоже сложно было понять?

где там про "средние"
Ты понимаешь хоть то что там написано или просто скопировал? У тебя в статистических данных 200 строк со значениями, как ты будешь их использовать? Все сразу?
Вот у тебя известно что в таблице есть:
1 значение 100
5 значений 200
20 значений 300
WHERE id = @id OPTIMIZE FOR UNKNOWN сколько строк вернет согласно оценке оптимизатора?
30 ноя 18, 11:34    [21749697]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 5671
Mind
TaPaK
пропущено...

где там про "средние"
Ты понимаешь хоть то что там написано или просто скопировал? У тебя в статистических данных 200 строк со значениями, как ты будешь их использовать? Все сразу?
Вот у тебя известно что в таблице есть:
1 значение 100
5 значений 200
20 значений 300
WHERE id = @id OPTIMIZE FOR UNKNOWN сколько строк вернет согласно оценке оптимизатора?

согласно этому хинту актуальный план будет всегда учитывать значение статистики по переданным переменным, а на столбить ваши 20 строк или сколько вы там себе нафаназировали.
Recompile же будет принудительно перекомпилировать запрос с проброшенным значением.
30 ноя 18, 11:38    [21749706]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
PizzaPizza
Member

Откуда:
Сообщений: 146
gepard1980
PizzaPizza, возвращаются 9 числовых полей без блобов. Думаю это не сильно влияет на перфоманс. Есть таблица с 70 полями. Вот из нее уже select * накладно делать.


Тривиальный план + 100% загружающий io + ошибки Table errorы = делайте чекдиск и разбирайтесь с дисками, надеюсь с бекапами у вас все хорошо
30 ноя 18, 19:04    [21750663]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2107
Наконец то. А столько пафоса было: "как разберешься - приходи". А в чем разница между "по переданным переменным" и "с проброшенным значением"?
TaPaK
согласно этому хинту актуальный план будет всегда учитывать значение статистики по переданным переменным, а на столбить ваши 20 строк или сколько вы там себе нафаназировали.
Recompile же будет принудительно перекомпилировать запрос с проброшенным значением.
Так все таки, переданные переменные будут учитываться или нет? Потому что в твоей цитате из документации сказано прямо противоположное:
TaPaK
Instructs the query optimizer to use statistical data instead of the initial values for all local variables
Вообще конечно тот кто писал документацию мягко говоря очень далек от SQL Server-а, потому что написал он полную чушь.

Во-первых, о каких local variables идет речь если учитывать что для локальных переменных никакого parameter sniffing нет и быть не может, сервер по-дефолту не знает значения локальных переменных и это опция оптимизатора вообще тут не имеет никакого эффекта.
Во-вторых "use statistical data" - то есть если не указать FOR UNKNOWN то оптимизатор не будет использовать "statistical data"? Что за бред вообще.
Мне потому и смешно что ты процитировал это запутанное и по сути безсмысленное определение и не смог даже нормально перевести его.

OPTIMIZE FOR UNKNOWN - или по сути оптимизация под неизвестное значение так и называется, потому что не важно видит оптимизатор значения параметров/переменных или нет, все равно мы ему говорим оптимизируй запрос так как будто для тебя эти значения неизвестны (UNKNOWN). Ну или еще более упрощенно - оптимизируй запрос так как если бы все параметры были переменными, для которых как мы знаем "просмотр значений" не работает по умолчанию. Именно поэтому, до существования такой опции можно было переназначить параметры локальным переменным и использовать уже их в запросе, и это приводило по сути к тому же эффекту.


Ну а теперь самое интересное, вопрос на который ты не смог ответить, о том как сервер использует статистику для оценочного определения количества строк. Неужели даже интересно не было? Тест накидать за 2 минуты и самому проверить? Для саморазвития так сказать? Нет, зачем же. Гениям это не надо.

+ Скрипт
SET NOCOUNT ON
CREATE TABLE dbo.T(id int)
INSERT T(id) VALUES(100)
GO 1
INSERT T(id) VALUES(200)
GO 5
INSERT T(id) VALUES(300)
GO 20

SET SHOWPLAN_ALL ON
GO
DECLARE @id int = 100
SELECT * FROM dbo.T WHERE id = @id OPTION(OPTIMIZE FOR UNKNOWN)
GO
SET SHOWPLAN_ALL OFF
GO
DROP TABLE T

Результат:
StmtText EstimateRows
DECLARE @id int = 100 NULL
SELECT * FROM dbo.T WHERE id = @id OPTION(OPTIMIZE FOR UNKNOWN) 8.666667
|--Table Scan(OBJECT:([master].[dbo].[T]) WHERE:([master].[dbo].[T].[id]=[@id])) 8.666667

Откуда же берутся эти 8.67? Из статистики:

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
100 0 1 0 1
200 0 5 0 1
300 0 20 0 1

(1+5+20)/3=8.66667
TaPaK
где там про "средние"
Это называется среднее арифметическое. Урок «Среднее арифметическое чисел», 5 класс


P.S. Не забудь запатентовать что опция OPTIMIZE FOR UNKNOWN ускоряет работу дисков в 5 раз.
30 ноя 18, 23:20    [21750832]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение хранимых процедур  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 2257
invm
Лог транзакций не может расти, тем более постоянно, если в базу ничего не пишется. Вам об этом уже писали..


delete?
1 дек 18, 09:48    [21750912]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2 3 4 5 6      [все]
Все форумы / Microsoft SQL Server Ответить